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

Questions 16–20

1. 16foundation · easy

   What is the primary benefit of partitioning a large fact table in an analytical data warehouse?

   • AIt eliminates the need for indexes on the table
   • BIt reduces the total amount of storage required for the table
   • CIt automatically compresses the data in each partition
   • DIt improves query performance by allowing partition elimination
   Reveal Solution Discussion Select an answer first
2. 17foundation · easy

   A data engineer is designing a folder structure for a data lake that will store daily sales data. The data will be queried frequently by date ranges and occasionally by product category. Which partition strategy best optimizes performance and manageability?

   • AStore all files in a single folder with a naming convention
   • BPartition by date first, then by product category
   • CPartition by product category first, then by date
   • DPartition by product category only
   Reveal Solution Discussion Select an answer first
4. 18application · medium

   A retail analytics team uses Azure Synapse dedicated SQL pool for a large sales fact table. The table is partitioned by month and distributed by hash on StoreID. Analysts run queries that aggregate sales by product category for a specific quarter. You notice that queries scan all partitions for the quarter. What is the most likely cause?

   • AThe table is too large and the optimizer chooses a full scan.
   • BThe distribution column is StoreID, which is not the partition column.
   • CThe query predicate on the partition column is not sargable.
   • DThe table has too many partitions, causing overhead.
   Reveal Solution Discussion Select an answer first
5. 19application · medium

   A marketing analytics team stores campaign performance data in Azure Data Lake Storage Gen2. The data is used for weekly reports and ad-hoc queries that filter by campaign_id and by event_date. The current layout is /campaigns/2024/05/15/data.parquet. Queries are slow. You need to identify whether partitioning is necessary and implement it. What should you do?

   • AKeep the current layout because partitioning is only needed for data larger than 1 TB.
   • BPartition by campaign_id and event_date, e.g., /campaigns/campaign_id=123/date=2024-05-15/data.parquet.
   • CConvert the files to ORC format for better performance.
   • DPartition only by event_date, ignoring campaign_id.
   Reveal Solution Discussion Select an answer first
6. 20expert · hard

   A large e-commerce company uses Azure Synapse Analytics dedicated SQL pool for its data warehouse. The fact table contains order details and is partitioned by order_date. Queries that filter by order_date are fast, but queries that filter by customer_id are slow because they scan all partitions. You need to improve performance for customer_id queries without degrading order_date query performance. You have a limited budget for storage and maintenance. What should you do?

   • ACreate a clustered columnstore index on customer_id.
   • BCreate a materialized view on customer_id that aggregates the fact table.
   • CAdd a secondary partition on customer_id to the existing table.
   • DRecreate the table with hash distribution on customer_id and keep partitioning on order_date.
   Reveal Solution Discussion Select an answer first