Exam DP-203 All Questions

View all questions & answers for the DP-203 exam

Exam DP-203 topic 5 question 1 discussion

Actual exam question from Microsoft's DP-203

Question #: 1
Topic #: 5

HOTSPOT -
You need to design a data storage structure for the product sales transactions. The solution must meet the sales transaction dataset requirements.
What should you include in the solution? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer:

Box 1: Hash -
Scenario:
Ensure that queries joining and filtering sales transaction records based on product ID complete as quickly as possible.
A hash distributed table can deliver the highest query performance for joins and aggregations on large tables.
Box 2: Set the distribution column to the sales date.
Scenario: Partition data that contains sales transaction records. Partitions must be designed to provide efficient loads by month. Boundary values must belong to the partition on the right.
Reference:
https://rajanieshkaushikk.com/2020/09/09/how-to-choose-right-data-distribution-strategy-for-azure-synapse/

by pangas2567 at Sept. 8, 2022, 10:01 a.m.

Comments

Submit Cancel

Jerrie86

Highly Voted 2 years, 4 months ago

This case study was in my exam and I scored 970. I chose productid.

upvoted 60 times

RoyP654

2 years ago

Good Job, Congrats!

upvoted 8 times

...

jongert

1 year, 5 months ago

Congrats! The answer is productid, since ms documentation states NOT to distribute by a date column. When doing so, all data for a given date is partitioned into one distribution. When processing, this hinders parallelism.

upvoted 4 times

...

Julia01

Highly Voted 2 years, 9 months ago

Id choose product id as well since it will be used in joins "Ensure that queries joining and filtering sales transaction records based on product ID complete as quickly as possible."

upvoted 21 times

mokrani

2 years, 7 months ago

Why not sales date for distribution column ? Partition data that contains sales transaction records. Partitions must be designed to provide efficient loads by month. Boundary values must belong to the partition on the right...

upvoted 1 times

kl8585

2 years, 7 months ago

because it's asking about distribution, not partition. The requirements say "ensure that queries joining and filtering sales transaction records based on product ID complete as quikly as possible". The best way to do so is hash distrinuting on product ID, this way all rows with the same product id will be on the same node and there will be no data shuffling, hence fast queries

upvoted 16 times

...

imatheushenrique

Most Recent 3 months ago

Hash and productId. Consider using the round-robin distribution for your table in the following scenarios: When getting started as a simple starting point since it is the default If there is no obvious joining key; If there is no good candidate column for hash distributing the table; If the table does not share a common join key with other tables ;If the join is less significant than other joins in the query; When the table is a temporary staging table

upvoted 1 times

...

7082935

10 months, 3 weeks ago

I'll repeat advice I read from another question: NEVER set distribution on a DATE column. However, partition on DATE is good.

upvoted 2 times

...

kkk5566

1 year, 9 months ago

Hash and Distrubution on Product ID

upvoted 3 times

...

XiltroX

2 years, 6 months ago

In MS's own documentation, it is not recommended to use a date column for distribution. Therefore, the second option should be ProductID

upvoted 8 times

pavankr

2 years ago

So then why this guy is misleading us?? I find lot of answers misleading us.

upvoted 3 times

...

OldSchool

2 years, 6 months ago

Hash and Distrubution on Product ID, never make distribution on Date.: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute#choose-a-distribution-column-with-data-that-distributes-evenly Partition on Date as explained here: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition

upvoted 12 times