Exam DP-203 All Questions

View all questions & answers for the DP-203 exam

Exam DP-203 topic 5 question 5 discussion

Actual exam question from Microsoft's DP-203

Question #: 5
Topic #: 5

HOTSPOT -
You need to design an analytical storage solution for the transactional data. 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: Round-robin -
Round-robin tables are useful for improving loading speed.
Scenario: Partition data that contains sales transaction records. Partitions must be designed to provide efficient loads by month.

Box 2: Hash -
Hash-distributed tables improve query performance on large fact tables.
Scenario:
✑ You plan to create a promotional table that will contain a promotion ID. The promotion ID will be associated to a specific product. The product will be identified by a product ID. The table will be approximately 5 GB.
✑ Ensure that queries joining and filtering sales transaction records based on product ID complete as quickly as possible.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

by Julia01 at Sept. 7, 2022, 8:21 p.m.

Comments

Submit Cancel

greenlever

Highly Voted 2 years, 8 months ago

replicated hash

upvoted 54 times

...

Jerrie86

Highly Voted 2 years, 5 months ago

Data is more than 100GB : hash Dimension data less than 2GB: replicated Staging table data less than 5Gb:Round Robin So replicated and Hash

upvoted 22 times

...

Sathya_sree

Most Recent 2 months, 3 weeks ago

retail - replicated, promotional - hash

upvoted 1 times

...

8ac3742

8 months ago

For large size dimension tables, Round-Robin is inappropriate because only Hash can ensure that the row with the same hash value can end up on the same distribution so it will optimize the performance of join and group.

upvoted 1 times

...

Dusica

1 year, 2 months ago

Replicated Round Robin - because data is short lived only while campaign

upvoted 1 times

...

hassexat

1 year, 9 months ago

replicated & hash

upvoted 1 times

...

hassexat

1 year, 9 months ago

Replicated --> Because is not a staging table and is moreless 2GB Hash --> Because is 200GB

upvoted 1 times

...

kkk5566

1 year, 9 months ago

replicated ,hash tables are best for queries with joins and aggregations.

upvoted 1 times

...

peacejh

1 year, 10 months ago

In the text it says that the table is 200GB, so hash. In the answer explanation it suddenly is only 5 GB

upvoted 2 times

...

andjurovicela

1 year, 11 months ago

Box1: (clearly) replicated Box2: I can see why someone would say round-robin since it is not uncommon for large dim_tables (and that is what this promotions table will essentially be) to use this distribution BUT per Microsoft doc below using round-robin makes sense in situation tht simply do not apply here: - When getting started as a simple starting point since it is the default --> NOT THE CASE - If there is no obvious joining key --> THERE IS, product id which will be present in the fact transactions table as well If there is no good candidate column for hash distributing the table - THERE IS, PromotionID - If the table does not share a common join key with other tables - IT DOES, ProductID - If the join is less significant than other joins in the query - NO INF on this - When the table is a temporary staging table - IT IS NOT source: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute therefore, box2: Hash

upvoted 4 times

...

auwia

1 year, 12 months ago

Retail Store contains info about store (like address), it's clearly a dimension table, by the consequence it is REPLICATED. The second is correct: HASH.

upvoted 4 times

...

pavankr

2 years ago

So on which answer we should reply on??????? Why this web site guy is guiding us all wrong answers?????

upvoted 3 times

...

JosephVishal

2 years, 5 months ago

Box1: Replicated Box2: Hash. Since, the Retail store table, will be used in queries and there is no mention of data loads to this table. It should be replicated and not Round-Robin.

upvoted 2 times

...