Exam DP-203 All Questions

View all questions & answers for the DP-203 exam

Exam DP-203 topic 1 question 20 discussion

Actual exam question from Microsoft's DP-203

Question #: 20
Topic #: 1

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, IsOrderFinalized, COUNT(*)

FROM FactPurchase -

WHERE DateKey >= 20210101 -

AND DateKey <= 20210131 -
GROUP By SupplierKey, StockItemKey, IsOrderFinalized
Which table distribution will minimize query times?

A. replicated
B. hash-distributed on PurchaseKey
C. round-robin
D. hash-distributed on IsOrderFinalized

Show Suggested Answer

Suggested Answer: B 🗳️

by GameLift at Sept. 10, 2021, 1:35 p.m.

Comments

Submit Cancel

FredNo

Highly Voted 3 years, 8 months ago

Selected Answer: B

Correct

upvoted 47 times

Deepshikha1228

3 years ago

B is correct

upvoted 2 times

...

GameLift

Highly Voted 3 years, 11 months ago

Is it hash-distributed on PurchaseKey and not on IsOrderFinalized because 'IsOrderFinalized' yields less distributions(rows either contain yes,no values) compared to PurchaseKey?

upvoted 31 times

Podavenna

3 years, 11 months ago

Yes, your logic is correct!

upvoted 9 times

...

adaod14

Most Recent 5 months, 2 weeks ago

Selected Answer: B

Correct Answer: B. Hash-Distributed on PurchaseKey 💡 Explanation: Azure Synapse Analytics supports three table distribution methods: Replicated, Hash-Distributed, and Round-Robin. The best choice depends on the workload and query patterns. 1️⃣ Why Use Hash Distribution? The FactPurchase table will have 1 million rows added daily and contain three years of data → This results in over 1 billion rows, making hash distribution the best option for efficient querying. Queries GROUP BY SupplierKey, StockItemKey, and IsOrderFinalized → A hash-distributed table will ensure these columns are evenly distributed across compute nodes, reducing data movement and improving query performance. 2️⃣ Why Hash on PurchaseKey? PurchaseKey is the primary key (unique for each purchase). Ideal for distributing data evenly across compute nodes, avoiding data skew. Improves aggregation queries where GROUP BY is used on multiple dimensions.

upvoted 4 times

...

RAG11

5 months, 3 weeks ago

Selected Answer: D

D, Since the query doesn't reference PurchaseKey for filtering or grouping, hash distribution on PurchaseKey doesn't help with optimizing the query and would not be the best choice.

upvoted 1 times

...

thanglai

7 months, 1 week ago

Hash-Distributed: For large fact tables where joins or aggregations are common

upvoted 2 times

...

EmnCours

8 months, 3 weeks ago

Selected Answer: B

Correct Answer: B

upvoted 1 times

...

Okkier

1 year, 1 month ago

Selected Answer: C

Optimal Distribution Given that the query performs a GROUP BY on SupplierKey, StockItemKey, and IsOrderFinalized, the most balanced approach is to use Round-robin distribution. While it does not ensure that rows with the same key are stored together, it avoids data skew and ensures even distribution, which helps in achieving better performance for aggregate queries.

upvoted 3 times

JustImperius

7 months ago

I hear where you are coming from. The fact that the PurchaseKey is not used in the query also made me think this for a sec. So even when using the hash the situation becomes like a round-robin distribution in terms of data access in this specific query. But generally hash will be more efficient because of the predictable hash distribution structure, which is better than a completely random approach. Seeing as we are using the purchaskey which i assume is unique, we don't need to worry about skew at all. So I Still think B is the correct choice here.

upvoted 1 times

...

hypersam

7 months, 2 weeks ago

hash distribution on high cardinatlity column can also avoid data skew

upvoted 1 times

...

MBRSDG

1 year, 4 months ago

Selected Answer: B

almost exactly what's shown in a example of the official docs --> https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute#choose-a-distribution-column

upvoted 2 times

...

sdg2844

1 year, 7 months ago

Selected Answer: B

Correct. Column with many unique values. Also, it's USUALLY not a column that is used in whereclauses or groupings or such, which this isn't.

upvoted 1 times

...

pperf

1 year, 10 months ago

Selected Answer: B

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute#choosing-a-distribution-column

upvoted 1 times

...

jiajiani

1 year, 11 months ago

why the answer says it cannot be a date column?

upvoted 1 times

...

74gjd_37

1 year, 11 months ago

Selected Answer: B

Hash-distributed tables improve query performance on large fact tables. The PurchaseKey has many unique values, does not have NULLs and is not a date column.

upvoted 2 times

jiajiani

1 year, 11 months ago

why we cannot use data column?

upvoted 3 times

...

kkk5566

1 year, 11 months ago

Selected Answer: B

B is correct

upvoted 1 times

...

SolutionA

2 years ago

in this case the sql where condition is on datekey so hash-distributed on PurchaseKey or Round robin distributed table the sql cost will be the same as it will be full table scan

upvoted 1 times

SolutionA

2 years ago

on second thought if purchasekey is not unique what is the constraint and how its created , as the question didn't mention more details , i would go with round robin not the has distributed

upvoted 3 times

...