Exam DP-203 topic 1 question 39 discussion

From the documentation the answer is clear enough. B is the right answer. When choosing a distribution column, select a distribution column that: "Is not a date column. All data for the same date lands in the same distribution. If several users are all filtering on the same date, then only 1 of the 60 distributions do all the processing work."

I think the answer should be D for that specific query. If you look at the datatypes, DateKey is an INT datatype not a DATE datatype.

Answer should be D. PurchaseKey is not related with this query.

Even if Datekey is int, it is used in "WHERE" clause so it is not suitable as well. 1: "Is used in JOIN, GROUP BY, DISTINCT, OVER, and HAVING clauses. When two large fact tables have frequent joins, query performance improves when you distribute both tables on one of the join columns. When a table is not used in joins, consider distributing the table on a column or column set that is frequently in the GROUP BY clause." 2: "Is not used in WHERE clauses. When a query's WHERE clause and the table's distribution columns are on the same column, the query could encounter high data skew, leading to processing load falling on only few distributions. This impacts query performance, ideally many distributions share the processing load."

The answer is "D" since the question is referring to "three years of data" so they are asking for date partitioning

D. Distribué par hachage sur DateKey Cette distribution optimise les requêtes qui filtrent par DateKey en minimisant les mouvements de données entre les nœuds.

Correct Answer: D

It should be D.

The answer should be D. because this query is searched by datekey

To optimize parallel processing in Azure Synapse Analytics, it's important to select a distribution column or set of columns that: Has many unique values. Does not have many nulls. Is not a date column. Partitioning by DateKey does not meet these criteria. Date columns typically have limited unique values, can contain many nulls, and, by nature, are date fields. Given these constraints, DateKey is not suitable for distribution. For fact tables, which usually exceed 2 GB in size, a hash distribution is recommended. In this scenario, using PurchaseKey as the distribution key is ideal as it aligns with all the specified criteria: it has many unique values, is unlikely to have nulls, and is not a date column. This approach ensures more efficient data distribution and query performance. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

The real approach in actual project is limiting the dateKey: DistributionKey AS HASHBYTES('MD5', CONCAT(YEAR(CAST(DateKey AS DATE)), FORMAT(CAST(DateKey AS DATE), 'MM'))) ) WITH ( DISTRIBUTION = HASH(DistributionKey), CLUSTERED COLUMNSTORE INDEX );

total votes on B:85 on D; 23

Answer could be only D

You don't want to hash on the Date column generally, definitely not when its being included in the Where clause, PurchaseKey is the only acceptable option given as the table is too large for roundrobin. I want to know if those other columns in the group by could possibly be used also?

Question 20 and 39 Is same

B is my correct answer

Explain me something - if you use Purchasekey as a hash distribution, and then want to do a partition, which column will you use for partition, we mostly date column for partition, but if we use date column then during query execution where you want to query data for let's say Jan month, wouldn't the query will need data from multiple nodes? eventually slowing the results? isn't it easy to keep data on a single node get faster results. Am I missing anything here?