Understanding the loading strategy with Synapse-dedicated SQL pools
The different options that you have available for the table design of a dedicated SQL pool, distributed or replicated tables, and the decision regarding the use of column stores or heaps and partitioning on top will influence the way in which you load data into it.
Certainly, loading into a hash-distributed table can be quite a quick process. But when you consider the additional compute step to calculate the hash keys to distribute the incoming rows to their target distribution and compare it to a round-robin-distributed table, where this step is not required, you can imagine that loading data into the latter will be faster.
Another consideration for a staging table in a dedicated SQL pool would be to use heap tables instead of column store ones. Again, you can avoid additional compute overhead for the column store and load data quickly.
In the end, it all comes down to performance. Therefore, following...
