Exam DP-203 topic 1 question 20 discussion

Correct

Is it hash-distributed on PurchaseKey and not on IsOrderFinalized because 'IsOrderFinalized' yields less distributions(rows either contain yes,no values) compared to PurchaseKey?

Correct Answer: B. Hash-Distributed on PurchaseKey 💡 Explanation: Azure Synapse Analytics supports three table distribution methods: Replicated, Hash-Distributed, and Round-Robin. The best choice depends on the workload and query patterns. 1️⃣ Why Use Hash Distribution? The FactPurchase table will have 1 million rows added daily and contain three years of data → This results in over 1 billion rows, making hash distribution the best option for efficient querying. Queries GROUP BY SupplierKey, StockItemKey, and IsOrderFinalized → A hash-distributed table will ensure these columns are evenly distributed across compute nodes, reducing data movement and improving query performance. 2️⃣ Why Hash on PurchaseKey? PurchaseKey is the primary key (unique for each purchase). Ideal for distributing data evenly across compute nodes, avoiding data skew. Improves aggregation queries where GROUP BY is used on multiple dimensions.

D, Since the query doesn't reference PurchaseKey for filtering or grouping, hash distribution on PurchaseKey doesn't help with optimizing the query and would not be the best choice.

Hash-Distributed: For large fact tables where joins or aggregations are common

Correct Answer: B

Optimal Distribution Given that the query performs a GROUP BY on SupplierKey, StockItemKey, and IsOrderFinalized, the most balanced approach is to use Round-robin distribution. While it does not ensure that rows with the same key are stored together, it avoids data skew and ensures even distribution, which helps in achieving better performance for aggregate queries.

almost exactly what's shown in a example of the official docs --> https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute#choose-a-distribution-column

Correct. Column with many unique values. Also, it's USUALLY not a column that is used in whereclauses or groupings or such, which this isn't.

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute#choosing-a-distribution-column

why the answer says it cannot be a date column?

Hash-distributed tables improve query performance on large fact tables. The PurchaseKey has many unique values, does not have NULLs and is not a date column.

B is correct

in this case the sql where condition is on datekey so hash-distributed on PurchaseKey or Round robin distributed table the sql cost will be the same as it will be full table scan

B is correct

B. Hash the purchasekey to evenly distribute the data into 60 distributions.

B is the Correct Answer