Exam DP-203 topic 1 question 73 discussion

Actual exam question from Microsoft's DP-203

Question #: 73
Topic #: 1

HOTSPOT
-

You have an Azure subscription that contains an Azure Synapse Analytics dedicated SQL pool.
You plan to deploy a solution that will analyze sales data and include the following:

• A table named Country that will contain 195 rows
• A table named Sales that will contain 100 million rows
• A query to identify total sales by country and customer from the past 30 days

You need to create the tables. The solution must maximize query performance.

How should you complete the script? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Show Suggested Answer

Suggested Answer:

by FRANCIS_A_M at April 3, 2023, 6:12 p.m.

Comments

Submit Cancel

FRANCIS_A_M

Highly Voted 2 years, 2 months ago

Correct! 1. Hash(CustomerID) 2. Replicate

upvoted 21 times

zafnad

2 years, 1 month ago

Could you please explain why 1. Hash([CustomerID]) is correct, and 2. Hash([OrderDate]) is incorrect.

upvoted 2 times

ajhak

2 years, 1 month ago

It is hash because it is a fact table (you can tell because there is the "total" column being created which is numerical). Rule of thumb, never hash on a date field, so in this case you would hash on 'CustomerID'. You want the hash to have as many unique values as possible.

upvoted 7 times

...

Spam_Account

1 year, 12 months ago

Don't hash on date, only partition on date

upvoted 16 times

...

vctrhugo

2 years ago

Never distribute on Date.

upvoted 5 times

...

AnhHoang

Most Recent 11 months, 2 weeks ago

1. You would want hash distribution to improve query performance. You don't want to hash on Date column since it can cause bottlenecks if many people query on a same date (eg. getdate()), so hash on customerid is the way. 2. You would want to replicate small tables across all distributions, so it can pick up any distribution and still have full data.

upvoted 2 times

...