AWS Certified Data Engineer Associate DEA-C01 Practice Question

Frequent queries restrict the fact table by sale_date and join it to the store and calendar dimensions on store_id and date surrogate keys. Making sale_date the leading column of a compound sort key lets Redshift skip older blocks, speeding the time-bound scans. Using store_id as the DISTKEY colocates rows that will be joined, minimizing network shuffles during joins. Because the dimension tables are small, DISTSTYLE ALL copies them to every node, eliminating distributed joins altogether. This combination provides the highest query performance without slowing the nightly COPY load, which only appends recent data and therefore largely preserves sort order. The other options either scatter data across nodes, lose sort-key pruning benefits, or replicate large tables unnecessarily, leading to higher scan or network costs.