Partitioning Strategy for Large Fact Tables in Azure Synapse Dedicated SQL Pool

Quick Answer

The reason partition count needs a ceiling comes down to how Synapse dedicated SQL pool already splits a table before partitioning is even applied: every table is spread across a fixed set of distributions, and each partition you define is then created separately within every one of those distributions. Add too many partitions on top of that existing split, and each individual partition ends up holding a very small number of rows, which works against the clustered columnstore index's core strength, since it needs enough rows per segment to build efficient, well-compressed row groups, and small fragmented partitions produce small, poorly compressed ones instead, hurting both storage efficiency and query performance. Keeping the partition count under a reasonable ceiling, such as fewer than 60, avoids that fragmentation. This consideration works alongside a related one: aligning partition boundaries with the distribution key so partition switching or merging stays contained within each distribution rather than requiring data to move across distributions. Both practices are really about respecting the fact that distribution and partitioning are two separate, stacked ways of dividing the same table, and decisions about one need to account for the other. When a Synapse dedicated SQL pool question asks about partitioning strategy for a large fact table, look for answers that keep partition counts modest and that harmonize partition boundaries with how the table is already distributed.