Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
A data architect is designing a data warehouse solution using Azure Synapse Analytics. They are creating a large fact table that will store billions of rows of IoT sensor data. This data will primarily be queried based on a `SensorID` column, which has a high number of distinct values and is frequently used in JOIN and WHERE clauses. The goal is to optimize query performance for these specific types of queries. Which table distribution strategy should the architect choose for this fact table in a Synapse Dedicated SQL Pool?
- AHeap
- BReplicated
- CRound-robin
- DHash-distributed
Show answer & explanationAnswer & explanation
Correct answer: D. Hash-distributed
Hash-distributed tables are ideal for large fact tables where queries frequently filter or join on a specific column with a high number of distinct values. Distributing data based on `SensorID` ensures that rows with the same `SensorID` are stored on the same compute node, minimizing data movement during queries and improving performance.
Why the other options are wrong
- A. Heap is a table structure without a clustered index, and while it can be used, it's not a distribution strategy itself in Synapse and doesn't address the distributed query performance optimization like Hash-distributed or Replicated tables do.
- B. Replicated tables copy the entire table to every compute node. This is suitable for small dimension tables (typically < 2 GB) but highly inefficient for a fact table with billions of rows due to excessive storage and data movement overhead.
- C. Round-robin distributes data evenly across all distributions without any key, which is good for staging but not optimal for performance-critical queries involving joins or filters on specific columns.
Synapse Dedicated SQL Pool Hash-distributed Table
A table distribution strategy in Azure Synapse Analytics where rows are distributed across compute nodes based on a hash function applied to a designated column, optimizing query performance for join and filter operations on that column.
- Distributes data based on a hash of a chosen column.
- Best for large fact tables with frequent joins/filters on the distribution column.
- Requires a column with high cardinality (many distinct values).
- Minimizes data movement during query execution.
Memory trick: Hash for high cardinality joins, Replicated for small dimensions, Round-robin for staging.