Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
A data engineer is designing a data warehouse solution in Azure Synapse Analytics. They have a very large fact table, `FactSales`, that will be frequently queried with filters on a `RegionID` column. The goal is to distribute the data to minimize data movement during queries that filter by `RegionID`. Which table distribution method should be chosen for `FactSales`?
- AReplicated table
- BHash distribution on a different column (e.g., `SaleID`)
- CRound-robin distribution
- DHash distribution on `RegionID`
Show answer & explanationAnswer & explanation
Correct answer: D. Hash distribution on `RegionID`
Hash distribution on the `RegionID` column ensures that rows with the same `RegionID` are stored on the same distribution. This minimizes data movement (shuffling) when queries filter or join on `RegionID`, leading to faster query performance.
Why the other options are wrong
- A. Replicated tables copy the entire table to each compute node, which is good for small dimension tables but inefficient for very large fact tables.
- B. Hash distribution on a different column like `SaleID` would not optimize queries filtering by `RegionID` as the data for specific regions would be scattered across distributions.
- C. Round-robin distributes data evenly but randomly, leading to data movement for filtered queries.
Synapse Dedicated SQL Pool Table Distribution
The strategy for physically storing data across the underlying compute nodes in an Azure Synapse Analytics Dedicated SQL Pool, crucial for query performance.
- Three types: Hash, Round-robin, Replicated.
- Hash distribution is best for large fact tables with frequent joins/filters on the distribution key.
- Round-robin is simple, good for staging, or when no clear join/filter key.
- Replicated is best for small dimension tables.
Memory trick: Distribute smart, query fast.