The short answer
In a high-throughput enterprise data warehouse, queries frequently join massive central with four or 5 simultaneously. If table distributions are misaligned, the Data Movement Service (DMS) billions of rows across all 60 distributions, causing query run times to explode.
To achieve near-zero network data movement across complex joins, follow a disciplined physical distribution blueprint.
Hash distribute large fact tables on the dominant join key
Identify the primary entity that drives joins across your reporting workloads (for example, customer_id or order_id). Create the primary fact tables with DISTRIBUTION = HASH(customer_id).
When joining two multi-million-row fact tables on that key, Synapse executes a colocated join. Matching rows reside on the exact same distribution, eliminating ShuffleMoveOperation.
Set all dimension tables under 2GB to REPLICATED
Create all supporting dimension tables (such as dim_date, dim_product, dim_store, and dim_status) with DISTRIBUTION = REPLICATED.
Synapse caches a complete copy of each replicated table on every compute node. During a multi-table join, the compute node joins its local fact distribution against its local dimension cache in memory without transferring any bytes across the network.
Ensure identical data types on join columns
A subtle but severe trap is mismatched column between joining tables (for example, joining INT against BIGINT, or VARCHAR(50) with differing collations).
When data types do not match exactly, Synapse cannot guarantee that hash values will align across the 60 distributions. The is forced to perform an implicit conversion and execute a ShuffleMoveOperation over the network, ruining join colocation.