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.

The Query is filtering particular date range,. so fast processing hash distribution is applied in date column.

D, this avoids scanning irrelevant data which helps other query operations which triggers data movement to move less data as it's filtered B does not make sense at all coz everytime this will be a unique value

Hash distribution is generally preferred for large fact tables. Among the hash distribution options, hash-distributed on DateKey seems most beneficial because the query has a WHERE clause filtering on DateKey. Distributing on DateKey will help to localize data access for the given query pattern, minimizing data movement and potentially improving query performance. Therefore, Option D: hash-distributed on DateKey is the most likely correct answer.

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

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