Microsoft DP-300 Exam - Topic 5 Question 113 Discussion

Actual exam question for Microsoft's DP-300 exam

Question #: 113
Topic #: 5

[All DP-300 Questions]

You are designing a date dimension table in an Azure Synapse Analytics dedicated SQL pool. The date

dimension table will be used by all the fact tables.

Which distribution type should you recommend to minimize data movement?

AHASH

BREPLICATE

CROUND_ROBIN

Show Suggested Answer

Suggested Answer: B

A replicated table has a full copy of the table available on every Compute node. Queries run fast on replicated tables since joins on replicated tables don't require data movement. Replication requires extra storage, though, and isn't practical for large tables.

Incorrect Answers:

C: A round-robin distributed table distributes table rows evenly across all distributions. The assignment of rows to distributions is random. Unlike hash-distributed tables, rows with equal values are not guaranteed to be assigned to the same distribution.

As a result, the system sometimes needs to invoke a data movement operation to better organize your data before it can resolve a query.

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

by Oneida at Apr 07, 2025, 02:46 PM

Limited Time Offer

25%

Off

Get Premium DP-300 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Ronald

3 months ago

Wait, are we sure REPLICATE is the best choice here? Sounds too easy!

upvoted 0 times

...

Lanie

3 months ago

HASH is better for larger tables, though.

upvoted 0 times

...

Zita

3 months ago

ROUND_ROBIN just spreads it out randomly, right? Not ideal.

upvoted 0 times

...

Emerson

3 months ago

I thought REPLICATE was only for small tables?

upvoted 0 times

...

Tracie

4 months ago

I’d go with REPLICATE for the date dimension. Less data movement!

upvoted 0 times

...

Bette

4 months ago

I agree with Nilsa about REPLICATE, but I wonder if there are any scenarios where HASH could be more beneficial.

upvoted 0 times

...

Ricarda

4 months ago

I practiced a similar question, and I feel like ROUND_ROBIN is not the best choice for minimizing data movement. It might lead to uneven data distribution.

upvoted 0 times

...

Moon

4 months ago

I'm not entirely sure, but I remember something about HASH distribution being good for evenly distributing data. Maybe it could work here too?

upvoted 0 times

...

Nilsa

4 months ago

I think we should go with REPLICATE since the date dimension will be used by all fact tables, which might reduce data movement.

upvoted 0 times

...

Kenneth

5 months ago

I'm leaning towards ROUND_ROBIN. Since this is a dimension table, the data won't be skewed, so ROUND_ROBIN could provide a good balance of performance and simplicity.

upvoted 0 times

...

Stephaine

5 months ago

REPLICATE might be a good choice here. That way the date dimension table will be available on all compute nodes, which could help reduce data movement across the fact tables.

upvoted 0 times

...

Tambra

5 months ago

Hmm, I'm a bit unsure about this one. I know HASH is good for minimizing data movement, but I'm not sure if that's the best choice for a date dimension table that will be used by all the fact tables.

upvoted 0 times

...

Brittni

5 months ago

I think the key here is to minimize data movement, so HASH distribution seems like the best option to me.

upvoted 0 times

...

Laquanda

10 months ago

HASH distribution, definitely. Anything to avoid the dreaded 'data movement' in my reports. I'm not trying to be the laughingstock of the data team!

upvoted 0 times

...

Tabetha

10 months ago

Hmm, HASH distribution seems like the logical choice. Can't go wrong with that. Unless you want to be the one explaining all the extra data movement to the boss.

upvoted 0 times