DP-203 · Question #8
You are designing a fact table named FactPurchase in an Azure Synapse Analytics dedicated SQL pool. The table contains purchases from suppliers for a retail store. FactPurchase will contain the follow
The correct answer is B. hash-distributed on PurchaseKey. Hash-distributed tables improve query performance on large fact tables. To balance the parallel processing, select a distribution column that: - Has many unique values. The column can have duplicate values. All rows with the same value are assigned to the same distribution. Since
Question
You are designing a fact table named FactPurchase in an Azure Synapse Analytics dedicated SQL pool. The table contains purchases from suppliers for a retail store. FactPurchase will contain the following columns. FactPurchase will have 1 million rows of data added daily and will contain three years of data. Transact-SQL queries similar to the following query will be executed daily. SELECT SupplierKey, StockItemKey, COUNT(*) FROM FactPurchase WHERE DateKey >= 20210101 AND DateKey <= 20210131 GROUP By SupplierKey, StockItemKey Which table distribution will minimize query times?
Exhibit
Options
- Areplicated
- Bhash-distributed on PurchaseKey
- Cround-robin
- Dhash-distributed on DateKey
How the community answered
(25 responses)- A16% (4)
- B72% (18)
- C8% (2)
- D4% (1)
Explanation
Hash-distributed tables improve query performance on large fact tables. To balance the parallel processing, select a distribution column that: - Has many unique values. The column can have duplicate values. All rows with the same value are assigned to the same distribution. Since there are 60 distributions, some distributions can have > 1 unique values while others may end with zero values. - Does not have NULLs, or has only a few NULLs. - Is not a date column. https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data- warehouse-tables-distribute
Topics
Community Discussion
No community discussion yet for this question.