DEA-C01 Practice Question 4: Redshift Distribution Key Join

Redshift's optimizer must bring matching rows onto the same slice before it can join them. With both tables distributed EVEN, neither side is positioned by customer_id, so the planner emits DS_DIST_BOTH and redistributes both relations across the network, the dominant cost here. The documented remedy is to "distribute the fact table and one dimension table on their common columns": designate the dimension's key and the fact table's corresponding foreign key as the DISTKEY. Matching rows then already sit together, the redistribution step collapses to DS_DIST_NONE, and the network traffic disappears. Distribution style is fixed at table creation, so this means recreating the tables (or letting DISTSTYLE AUTO and automatic table optimization converge on the same result).

Option A is the right technique applied to the wrong table. ALL distribution is for dimensions small enough that replicating them is cheap; the docs warn that it "multiplies storage space requirements and increases load times and maintenance operations". Replicating 300 million rows to every node is a very expensive way to avoid a redistribution.

Option B confuses ordering with placement. Sort keys control how rows are ordered within a slice and drive zone-map block skipping for range-restricted predicates. They do not influence which slice a row lives on, so the DS_DIST_BOTH step remains. Interleaved sort keys also carry significant VACUUM REINDEX maintenance cost.

Option C adds capacity rather than removing work. Concurrency scaling spins up transient clusters to absorb queued *concurrent* queries; it does not speed up a single long-running query, and the same tens of billions of rows would still be shuffled.

Reference: Choose the best distribution style