Data Warehouse Distribution: Hash, Round-Robin, Replicated

In a massively parallel processing (MPP) architecture like Azure Synapse Analytics dedicated SQL pool, data is distributed across multiple compute nodes (called distributions). Each distribution is a unit of storage and processing that holds a portion of the table's data. The goal of distribution is to maximize query performance by minimizing data movement between nodes during query execution. Data movement is the most expensive operation in an MPP system—it involves shuffling rows across the network to collocate data needed for joins or aggregations. The distribution method determines how rows are assigned to distributions, directly impacting query performance and storage balance.

Hash distribution uses a deterministic hash function on one or more columns of the table (the distribution column or columns). When a row is inserted, the system computes a hash value of the distribution column(s) and maps that hash to a specific distribution. The same distribution column value always goes to the same distribution, ensuring that rows with the same key are colocated. This colocation is powerful for star schema scenarios where fact tables join with dimension tables on the same distribution key. For example, if a fact table and a dimension table both use the same CustomerID as the distribution column, then all rows for a given customer in both tables reside on the same node, avoiding data movement during joins.

Key characteristics:

Round-robin distribution distributes rows evenly across all distributions without any consideration of the data content. When a row is inserted, the system assigns it to the next distribution in a cyclic order. This ensures that each distribution gets approximately the same number of rows, providing perfect load balance for storage and for operations that scan the entire table (like full table scans). However, because there is no colocation of related rows, any join or aggregation that groups by a column will require data movement to shuffle rows to the correct distribution. Round-robin is the default distribution for tables created without specifying a distribution method.

Key characteristics:

Replicated tables store a full copy of the table on each distribution node. This eliminates data movement entirely for queries that join the replicated table with other tables, because every node already has the complete data. Replication is ideal for small dimension tables (typically less than 2 GB compressed) that are frequently joined with large fact tables. The downside is that any write operation (INSERT, UPDATE, DELETE) must update all copies, which increases write latency. Also, storage overhead multiplies by the number of distributions (60 copies), so it is not suitable for large tables.

Key characteristics:

The choice depends on table size, usage pattern, and query workload. The general guidance from Microsoft is:

Queries in a dedicated SQL pool are executed in parallel across all distributions. The query optimizer generates a plan that may require data movement if the required rows are not colocated. Data movement can be a broadcast move (sending a small table to all nodes) or a shuffle move (redistributing rows based on a join key). Hash-distributed tables on the join key avoid shuffle moves. Replicated tables avoid both broadcast and shuffle moves. Round-robin tables often require full shuffles, which are expensive.

Distribution is defined at table creation using the WITH (DISTRIBUTION = ...) clause. For hash distribution, you must also specify a distribution column. Examples:

-- Hash distribution on CustomerID
CREATE TABLE FactSales (
    SalesID INT NOT NULL,
    CustomerID INT NOT NULL,
    Amount DECIMAL(10,2)
)
WITH (DISTRIBUTION = HASH(CustomerID));

-- Round-robin distribution (default)
CREATE TABLE StagingData (
    ID INT,
    Name NVARCHAR(50)
)
WITH (DISTRIBUTION = ROUND_ROBIN);

-- Replicated table
CREATE TABLE DimProduct (
    ProductID INT NOT NULL,
    ProductName NVARCHAR(100)
)
WITH (DISTRIBUTION = REPLICATE);

To verify distribution, query sys.pdw_table_distribution_properties:

SELECT 
    t.name AS TableName,
    p.distribution_policy_desc AS DistributionType
FROM sys.tables t
JOIN sys.pdw_table_distribution_properties p
    ON t.object_id = p.object_id;

To check data skew for hash-distributed tables:

DBCC PDW_SHOWSPACEUSED('FactSales');

This command shows the row count per distribution. High variation indicates data skew.

Distribution works together with table indexing (clustered columnstore, clustered index, heap). For hash-distributed tables, the distribution column is often the same as the partition column for partitioned tables, but not required. Replicated tables cannot be partitioned. Round-robin tables can be partitioned, but partitioning does not reduce data movement for joins.

In a real-world Azure Synapse Analytics deployment, a retail company uses a dedicated SQL pool to analyze sales data. Their fact table, FactSales, has over 2 billion rows. They initially used round-robin distribution for fast loading, but queries joining with the Customer dimension took over 30 seconds due to data movement. After analyzing query patterns, they changed to hash distribution on CustomerID, which collocated rows from FactSales and DimCustomer. Query times dropped to under 2 seconds. However, they discovered significant data skew because a few customers had millions of rows. They mitigated this by using a composite distribution column (CustomerID + SaleDate) to distribute more evenly.

Another scenario: A financial services company uses replicated distribution for small dimension tables like DimCurrency (50 rows) and DimDate (10 years of dates, about 3650 rows). These tables are joined with a large fact table of transactions. Because they are replicated, every node has a local copy, and joins complete without any data movement. The company monitors table sizes using DMVs to ensure the replicated tables stay under 2 GB. When the DimProduct table grew to 3 GB due to new products, they switched it to hash distribution on ProductID to avoid the 2 GB replication limit.

A common misconfiguration is using round-robin for large fact tables that are frequently aggregated. For example, a marketing analytics team used round-robin for a 500 GB table and ran daily aggregation queries that grouped by CampaignID. Each query required shuffling the entire table across the network, taking 45 minutes. After switching to hash on CampaignID, the same query completed in 5 minutes. The team learned to always consider distribution when designing tables.

Performance considerations: Hash distribution can still cause data movement if the join column is not the distribution column. Replicated tables consume storage; with 60 distributions, a 1 GB replicated table uses 60 GB. Round-robin is best for staging tables that are truncated and reloaded frequently, as it provides even distribution without the overhead of hashing.