Table Design with Hash Distribution and Partitioning in Azure Synapse Analytics

Quick Answer

This design works because it matches each optimization technique to the specific access pattern it is built for. Hash-distributing on CustomerID means every row for a given customer lands on the same distribution node, so when the fact table is joined on CustomerID, Synapse does not need to shuffle data across nodes to bring matching rows together, and that data movement is usually the single biggest cost in a large distributed join, so eliminating it has an outsized effect on performance. Partitioning on OrderDate solves a different problem: when queries filter by date range, the engine can skip entire partitions that fall outside the range instead of scanning the full table, which is partition elimination. The two techniques are complementary rather than competing, because distribution controls how data is spread across compute nodes for joins, while partitioning controls how data is organized within each node for filtering. A design that only distributed on CustomerID would still scan unnecessary date ranges, and one that only partitioned on OrderDate would still incur data movement on every join. When you see a large fact table description that names both a frequent join column and a frequent filter column, expect the correct answer to hash-distribute on the join column and partition on the filter column, rather than trying to use one technique to solve both problems.