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.

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?

For me. I chose D. The reason is that the" datekey" is of tyoe "integer" not " Date" .This qualifies it to be used as a non-auto-incremental surrogate key for the fact table.

Something not immediately clear to me was that distributing and partitioning are different, hence I was confused that one should not distribute over date columns. Bottom line is, do not distribute over date columns but you can partition over them. In this question they specifically ask about distribution method. Query optimization for large tables directly points to hashing.

You want to distribute by productKey and partition by date. Then all distributions will be looked at in parallel and then, within each distribution, only the desired partitions will be looked at. Thereby, the query is fully scaled out and the quickest it can be.

B) the chosen distribution column should not be used in WHERE clauses; thus, we can discard DateKey (even though it is not a Date data type) to minimize data movement. The chosen distribution column must have many unique values; thus we potentially have 2 candidates: PurchaseKey or PurchaseOrderID; however, the chosen one should have no NULLS or only a few, making PurchaseKey the ideal in order to distribute evenly. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

Is there any "official" answer to this? A. Replicated: Replicated tables have copies of the entire table on each distribution. While this option can eliminate data movement, it may not be the most efficient choice for very large tables with frequent updates. B. Hash-Distributed on PurchaseKey: Hash distribution on "PurchaseKey" may lead to data skew if "PurchaseKey" doesn't have a wide range of unique values. Additionally, it doesn't align with the primary filtering condition on "DateKey." C. Round-Robin: Round-robin distribution ensures even data distribution, but it doesn't take advantage of data locality for specific types of queries. D. Hash-Distributed on DateKey: Distributing on "DateKey" aligns with your primary filtering condition, but it's a date column. This could lead to clustering by date, especially if many users filter on the same date. None of the answers seem to fit. D could be the best guess but it's a date column.

B is correct

B is total nonsense if PurchaseKey has a unique value for every row it would end up distributing it evenly so same as round-robin. Distributing by date would slow down the query because in a situation presented in the question only 31 out of 60 distributions would be used. So in my opinion C is the correct answer.

B is the correct Answer

To me the answer should be D. A query on the table that has a WHERE clause filtering on column A will perform partition elimination and scan one partition. That same query may run faster in scenario 2 as there are fewer rows to scan in a partition. A query that has a WHERE clause filtering on column B will scan all partitions. The query may run faster in scenario 1 than in scenario 2 as there are fewer partitions to scan. https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver16 Wanted to hear from the experts here.

B is the obvious answer. Hash is optimized for higher analytical performance while Round-robin is optimized for higher loading speed.

B is right

Anyone who even one time run similar query in Synapse and look into execution plan understand that PurchaseKey doesn't help: there will be shuffle move dms operation! I suppose all these qyestions has mistake here. Because only column from GROUP BY clause will help. Or round_robin (although it will has almost the same cost as PurchaseKey if last one evenly distributed)