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.

In addition to not using a Date column, the azure documentation also says: To minimize data movement, select a distribution column or set of columns that: 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.

d is correct

Hi Community, it's B. We are not talking about partitions. We are talking about distribution and we know that its a poor choice to choose a low cardinality column like datekey for a distribution.

In the Where clause, DateKey is the one which can affect to query. Partition should be with DateKey

Hash-distributed on DateKey Characteristics: Rows are distributed based on the hash of DateKey, which is also the column used in the query filter (WHERE DateKey >= ... AND DateKey <= ...). This ensures that rows satisfying the query predicate are grouped on fewer nodes, minimizing data movement and speeding up the query. Suitability: Perfect for the FactPurchase table in this scenario because: The query filters on DateKey. Filtering and aggregation on DateKey avoids significant data movement across nodes

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