Andy Brownsword has a revelation:
When designing tables in a Synapse Dedicated SQL Pool, I initially focussed on the ‘SQL’ and not so much the ‘pool’. How tables are stored is fundamentally different to other Azure SQL offerings, so it’s easy to get tripped up and impact efficiency and performance.
Let’s see how the SQL pools work differently and how to tailor your tables for optimal performance.
Click through for an explanation of the three distribution strategies and when you might use each. As a quick note, the REPLICATED option sounds like it’d be really great, but in practice, there are some major drawbacks as table sizes get larger. That’s a big part of why we save them for small, rarely-changing dimensions.