Synapse Dedicated SQL Pool Table Design

Quick Answer

The logic here mirrors how Synapse handles any large-scale aggregation: if you distribute the fact table by the column you group on, all the rows that need to be combined for a given product_id already live on the same node, so the aggregation can happen locally instead of requiring rows to be shuffled across the cluster first. Since the query pattern described is filtering by date and then aggregating by product_id, hash-distributing on product_id directly serves the more expensive, more frequent operation, aggregation across a large fact table, while the date filter is handled separately through table partitioning, which lets the engine skip irrelevant date ranges before the aggregation even starts. The clustered columnstore index complements this by storing the table in a highly compressed, column-oriented layout that scans and aggregates far faster than a row-based heap or standard rowstore index would, particularly at the scale implied by a large fact table. Choosing to hash-distribute on the date column instead would speed up the filter but leave the aggregation doing expensive cross-node data movement, which is usually the costlier operation. The general pattern worth remembering: when a query's dominant, expensive operation is aggregating by a specific column, distribute on that column, and use partitioning and the columnstore index to handle filtering and raw scan speed on top of that distribution choice.