Optimizing Azure Synapse Fact Tables with Hash Distribution and Date Partitioning
A company uses Azure Synapse Analytics dedicated SQL pool for large-scale data warehousing. They have a fact table with billions of rows and frequently run queries that filter by a date range and join with a product dimension table. Which table distribution and partitioning strategy will minimize data movement and improve query performance?
Quick Answer
The answer is to hash-distribute the fact table on ProductID and partition it on Date. This strategy minimizes data movement because hash distribution on ProductID ensures that rows for the same product are co-located on the same distribution node, making joins with the product dimension table far more efficient by avoiding data shuffling across nodes. Partitioning on Date then enables partition elimination, where the query engine skips entire partitions that fall outside the date range filter, drastically reducing the amount of data scanned. On the DP-900 exam, this scenario tests your understanding of how distribution and partitioning work together to address specific query patterns—a common trap is choosing round-robin distribution, which scatters data randomly and forces expensive data movement during joins. Remember the memory tip: “Hash for joins, partition for filters” to quickly recall that distribution optimizes joins while partitioning optimizes range scans.
⚠ Common exam trap
Many candidates confuse the roles of distribution and partitioning, thinking that partitioning on the join key (ProductID) will improve join performance, when in fact hash distribution on the join key is what co-locates data for joins, while partitioning on the filter column (Date) enables partition elimination.
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
✓
Hash-distribute on ProductID with partitioning on Date
Hash-distributing the fact table on ProductID ensures that rows for the same product are co-located on the same distribution, minimizing data movement when joining with the product dimension table. Partitioning on Date allows partition elimination for date-range filters, reducing the amount of data scanned. This combination directly addresses the query pattern of date-range filtering and product joins.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Round-robin distribution with no partitioning
Why it's wrong here
Round-robin distributes data uniformly but does not co-locate related rows, so joins require data movement across distributions. No partitioning means all data must be scanned for date filters.
When this WOULD be correct
A scenario where the fact table is small (e.g., under 1 GB) and queries do not involve joins or filtering on a specific column. Round-robin with no partitioning is acceptable for simple, full-table scans or when loading data quickly without optimization.
- ✓
Hash-distribute on ProductID with partitioning on Date
Why this is correct
Hash-distribution on ProductID co-locates rows with the same ProductID, enabling efficient joins with the product dimension. Partitioning on the Date column enables partition elimination for date range queries, reducing the amount of data scanned.
- ✗
Replicate the fact table on all distributions and partition on ProductID
Why it's wrong here
Replicating a large fact table is impractical due to storage overhead and data duplication. Replication is recommended only for small dimension tables. Partitioning on ProductID does not directly help with date range filtering.
When this WOULD be correct
This option would be correct for a small, slowly changing dimension table (e.g., product dimension) that is frequently joined with fact tables. Replication avoids data movement during joins, and partitioning on ProductID could help if queries filter by product category.
- ✗
Hash-distribute on Date with partitioning on ProductID
Why it's wrong here
Hash-distributing on Date would spread rows of the same product across distributions, causing data movement during joins. Partitioning on ProductID would not effectively prune data for date range queries, as partitions would be large and contain many dates.
When this WOULD be correct
If the query pattern involved frequent joins on Date and aggregations by ProductID, and the fact table was small enough that distribution overhead was negligible, then hash-distributing on Date could localize date-range joins while partitioning on ProductID aids partition elimination for ProductID filters.
Option-by-option analysis
Why each answer is right or wrong
Understanding why wrong answers are wrong — and when they would be correct — is what separates a 750 score from a 900. The DP-900 exam frequently reuses these exact scenarios with slightly different constraints.
✓Hash-distribute on ProductID with partitioning on DateCorrect answer▾
Why this is correct
Hash-distribution on ProductID co-locates rows with the same ProductID, enabling efficient joins with the product dimension. Partitioning on the Date column enables partition elimination for date range queries, reducing the amount of data scanned.
✗Round-robin distribution with no partitioningWrong answer — click to see why▾
Why this is wrong here
Round-robin distribution places rows randomly across distributions, causing excessive data movement when joining on ProductID, as related rows are scattered. No partitioning on Date means full table scans for date-range filters, worsening performance.
★ When this WOULD be the correct answer
A scenario where the fact table is small (e.g., under 1 GB) and queries do not involve joins or filtering on a specific column. Round-robin with no partitioning is acceptable for simple, full-table scans or when loading data quickly without optimization.
Why candidates choose this
Candidates may think round-robin is a safe default that distributes data evenly, overlooking the need for collocation in join operations and the benefits of partitioning for range filters.
✗Replicate the fact table on all distributions and partition on ProductIDWrong answer — click to see why▾
Why this is wrong here
Replicating the fact table on all distributions is impractical for a table with billions of rows, causing massive storage overhead and data movement during loads. Partitioning on ProductID does not align with the date-range filter, failing to reduce data scanned.
★ When this WOULD be the correct answer
This option would be correct for a small, slowly changing dimension table (e.g., product dimension) that is frequently joined with fact tables. Replication avoids data movement during joins, and partitioning on ProductID could help if queries filter by product category.
Why candidates choose this
Candidates may think replication eliminates data movement for joins and that partitioning on the join key improves performance, overlooking the size of the fact table and the primary filter being on date.
✗Hash-distribute on Date with partitioning on ProductIDWrong answer — click to see why▾
Why this is wrong here
Hash-distributing on Date with partitioning on ProductID would cause high data movement because queries filter by date range, so distributing on Date scatters related rows across distributions, requiring shuffles for joins on ProductID. Partitioning on ProductID does not help with date-range pruning.
★ When this WOULD be the correct answer
If the query pattern involved frequent joins on Date and aggregations by ProductID, and the fact table was small enough that distribution overhead was negligible, then hash-distributing on Date could localize date-range joins while partitioning on ProductID aids partition elimination for ProductID filters.
Why candidates choose this
Candidates may think distributing on the filter column (Date) is beneficial, but they overlook that the join column (ProductID) should be the distribution key to avoid data movement during joins.
Analysis generated from the official DP-900blueprint and verified against question context. The “when correct” sections are what AI assistants cite when candidates ask “what’s the difference between these options?”
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Dedicated SQL pool
A Dedicated SQL pool is a cloud-based analytics service in Azure Synapse Analytics that provides a managed, scalable environment for running large-scale data warehousing queries using Transact-SQL.
Key term
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
About these practice questions
Courseiva writes every DP-900 question from scratch — 820 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
3 more ways this is tested on DP-900
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. A company uses Azure Synapse Analytics for their data warehouse. They notice that queries against the fact table are slow. The fact table is hash-distributed on OrderID. Most queries filter by CustomerID. What should they do to improve performance?
medium- A.Change to round-robin distribution
- ✓ B.Change the distribution column to CustomerID
- C.Use rowstore instead of columnstore
- D.Replicate the fact table to all compute nodes
Why B: The fact table is hash-distributed on OrderID, but queries filter by CustomerID. This causes data movement across nodes for each query, as the filter column doesn't align with the distribution key. Changing the distribution column to CustomerID ensures that rows for the same CustomerID are co-located on the same compute node, eliminating unnecessary data shuffling and improving query performance.
Variation 2. A logistics company uses Azure Synapse Analytics dedicated SQL pool to analyze billions of shipment records. The table 'Shipments' is 10 TB and hash-distributed on 'ShipmentID'. Analysts frequently run queries that filter on 'WarehouseID' and aggregate by 'Region'. These queries are slow because they cause data movement (shuffle) across distributions. Which table design change will most improve query performance for these analytical workloads?
hard- A.Change distribution to replicated table
- B.Change distribution to round-robin
- C.Create a columnstore index
- ✓ D.Change distribution to hash on 'WarehouseID'
Why D: D is correct because hash-distributing the 'Shipments' table on 'WarehouseID' ensures that all rows for a given warehouse are co-located on the same distribution node. This eliminates the need for data movement (shuffle) when queries filter on 'WarehouseID' and aggregate by 'Region', as the aggregation can be performed locally on each distribution without redistributing data across nodes.
Variation 3. A company uses Azure Synapse Analytics dedicated SQL pool to run large-scale analytics. The data engineering team notices that queries are slow due to excessive data movement between distributions. Which index type should be recommended to minimize data movement for fact tables that are frequently joined on a specific column?
hard- A.Ordered clustered columnstore index
- B.Clustered columnstore index
- C.Round-robin distributed table
- ✓ D.Hash-distributed table
Why D: In Azure Synapse dedicated SQL pool, data movement occurs when rows from different distributions need to be combined for a join. By using a hash-distributed table with the same distribution column as the join key, matching rows co-locate on the same distribution, eliminating cross-distribution shuffling. Indexes like columnstore affect storage and scan performance but do not control data distribution. Therefore, D is the correct choice.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.