Implement a Partition Strategy DP-203 Practice Questions (Page 3)

Questions 11–15

1. 11application · medium

   A logistics company streams GPS tracking data from delivery vehicles to Azure Event Hubs. The data is landed in Azure Data Lake Storage Gen2. Analysts need to query routes by vehicle_id and by trip date. The current writer creates a new file every 5 minutes with all vehicles. File counts are high and queries are slow. You need to implement a file partition strategy that reduces file count and improves query performance. What should you do?

   • AWrite one file per hour containing all vehicles.
   • BWrite files partitioned by vehicle_id and date, e.g., /gps/vehicle_id=123/date=2024-05-15/data.parquet.
   • CWrite all data to a single file per day.
   • DWrite a new file every 5 minutes with a partition on vehicle_id only.
   Reveal Solution Discussion Select an answer first
2. 12application · medium

   A financial analytics platform stores historical market data in Azure Data Lake Storage Gen2. Analysts run queries that aggregate data by trading date and by sector. The current layout is /marketdata/year=2024/month=05/day=15/data.parquet. Queries that filter by sector are slow because they scan all files. You need to optimize the layout for sector-based queries while keeping date-based queries efficient. What should you do?

   • ACreate a new folder structure: /marketdata/sector=tech/date=2024-05-15/data.parquet.
   • BKeep the current date-based layout and add a sector column to each file.
   • CUse Azure Synapse dedicated SQL pool external tables without changing the layout.
   • DStore all data in a single large Parquet file sorted by sector.
   Reveal Solution Discussion Select an answer first
4. 13expert · hard

   A large e-commerce company uses Azure Synapse dedicated SQL pool for a sales fact table. The table is partitioned by month and distributed by hash on CustomerID. Analysts run two types of queries: (1) daily sales reports that filter on a specific date, and (2) customer lifetime value queries that filter on a specific customer ID. The table has 5 years of data. You need to optimize performance for both query types. What should you do?

   • AUse a round-robin distribution and partition by date.
   • BChange the distribution column to CustomerID and keep monthly partitions.
   • CChange the partition column to date and keep monthly partitions.
   • DCreate a copy of the table with different partitioning and distribution for each query type.
   Reveal Solution Discussion Select an answer first
5. 14application · medium

   A manufacturing company stores sensor readings in Azure Data Lake Storage Gen2. Each reading includes a sensor ID, a timestamp, and a value. The data is used for real-time dashboards and historical analysis. The current layout is /sensors/{sensorId}/readings_<timestamp>.csv. Queries that filter by date and sensor ID are slow. You need to improve query performance. What should you do?

   • AKeep the current structure but convert CSV files to Parquet.
   • BUse a single large file per sensor per day.
   • CChange the folder structure to /sensors/{date}/{sensorId}/readings_<timestamp>.parquet
   • DAdd a partition key column to each file and rely on predicate pushdown.
   Reveal Solution Discussion Select an answer first
6. 15application · medium

   A data warehouse in Azure Synapse Analytics dedicated SQL pool has a large fact table distributed with hash distribution on ProductKey. The table is partitioned by OrderDateKey using monthly partitions. Queries that aggregate sales by product for a single month are slow. You analyze the query plan and see that all 60 partitions are being scanned. What is the most likely cause?

   • AThe distribution column is ProductKey, which is not the partition column.
   • BThe table has too many partitions, causing overhead.
   • CThe query predicate on OrderDateKey is not sargable due to a function call.
   • DThe table is too small and the optimizer chooses a full scan.
   Reveal Solution Discussion Select an answer first