Design and implement data storage →hardMultiple Select

DP-203 Design and implement data storage Practice Question

Which THREE considerations are important when designing a table distribution strategy for an Azure Synapse Analytics dedicated SQL pool? (Choose three.)

⚠ Common exam trap

Candidates often confuse round-robin distribution as a good choice for large fact tables because it distributes data evenly, but they overlook the severe performance penalty from data movement during joins and aggregations.

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

Align distribution keys on tables that are frequently joined together

Option A is correct because co-locating the distribution key on tables that are frequently joined ensures matching rows land on the same distribution, letting joins execute locally and eliminating costly shuffle (data movement) operations. Option B is correct because a distribution key with many unique values spreads rows evenly across the 60 distributions, minimizing data skew that would otherwise create hot distributions and slow query performance. Option E is correct because table size and join frequency are the core drivers of the distribution decision: small tables may be replicated, while large frequently-joined tables should be hash-distributed on a shared key. Option C is not correct because round-robin is generally recommended for staging or temporary tables, not large fact tables, which benefit from hash distribution on a join/filter column. Option D is not correct because replicated tables are intended for small dimension tables (roughly under 2 GB compressed); replicating large tables consumes excessive storage and rebuild time on every write.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✓
Align distribution keys on tables that are frequently joined together
Why this is correct
Aligning distribution keys on tables that are frequently joined together ensures that the join columns are hash-distributed on the same key, enabling collocated joins. This avoids data movement across distributions during query execution, which significantly improves performance.
✓
Minimize data skew by choosing a distribution key with many unique values
Why this is correct
Minimizing data skew by choosing a distribution key with many unique values helps ensure that data is evenly distributed across all distributions, preventing performance bottlenecks caused by one distribution handling a disproportionate amount of data.
✗
Use round-robin distribution for large fact tables to distribute data evenly
Why it's wrong here
Round-robin distributes rows evenly but provides no join key alignment, forcing shuffles when large fact tables join dimensions, so it suits staging or isolated tables. It is tempting because even distribution sounds ideal, yet hash distribution on the frequent join column is what avoids that movement.
✗
Use replicated tables for large tables to avoid data movement
Why it's wrong here
Replication copies the full table to every compute node, which is impractical for large fact tables and consumes storage while slowing loads. It is tempting because eliminating data movement for joins is genuinely valuable, but that benefit applies to small dimension tables, not large ones.
✓
Consider the size of the table and the frequency of joins
Why this is correct
Table size determines whether replication or hash distribution suits a table, while join frequency dictates co-locating join keys to avoid costly data movement across distributions. Both directly shape distribution choice, making them core design considerations for dedicated SQL pool performance.

Go deeper

DP-203 Design and implement data storage Practice Question

Why each option matters

Option-by-option breakdown

Related to this question