Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureHard
A data engineer is designing a data warehouse solution using Azure Synapse Analytics. They have a large fact table that stores transactional data. This table will be frequently queried with filters on specific columns (e.g., 'CustomerID', 'ProductID') and will be involved in many JOIN operations. To optimize query performance, which table distribution strategy should be chosen for this fact table in a dedicated SQL pool?
- ARound-robin
- BHash-distributed
- CReplicated
- DHeap
Show answer & explanationAnswer & explanation
Correct answer: B. Hash-distributed
Hash-distributed tables are ideal for large fact tables in a dedicated SQL pool. Distributing data based on a common join key (like CustomerID or ProductID) minimizes data movement during JOINs and allows for parallel processing, significantly improving query performance.
Why the other options are wrong
- A. Round-robin distributes data evenly but randomly, leading to data movement (shuffling) for most JOINs and filters, which is inefficient for large fact tables.
- C. Replicated tables copy the entire table to each compute node, which is efficient for small dimension tables but highly inefficient for large fact tables due to storage and write overhead.
- D. Heap is a table structure without a clustered index, not a distribution strategy. All Synapse tables have a distribution strategy, even if they are a heap or clustered index.
Synapse Dedicated SQL Pool Hash-distributed Table
A Hash-distributed table in Azure Synapse Analytics partitions data across distributions based on a hash function applied to a selected column.
- Optimized for large fact tables.
- Minimizes data movement for JOINs on the distribution key.
- Improves query performance by enabling parallel processing.
Memory trick: Hash for Big Facts, Replicate for Small Dims, Round-Robin for Unknowns.