AWS Certified Data Engineer Associate DEA-C01 Practice Question

The workload needs locality for the frequent join with users and efficient pruning for the typical time-range filter. Making user_id the DISTKEY on click_events collocates the fact and dimension rows on the same node slices, eliminating the network shuffle during joins. A compound SORTKEY with event_time leading (for example, SORTKEY(event_time, user_id)) organizes data so that blocks for recent time ranges are contiguous; the Redshift query engine can skip older blocks, minimizing I/O. Including user_id as the second column in the compound key maintains reasonable clustering for equality predicates without adding the metadata overhead of an interleaved key. Using DISTSTYLE ALL would replicate several terabytes of data to every node, dramatically increasing storage cost, while DISTSTYLE AUTO or EVEN would forfeit the collocated join benefit. Choosing event_time as the DISTKEY would not improve the join and would distribute data unevenly. Therefore, defining click_events with DISTKEY user_id and a compound SORTKEY beginning with event_time best satisfies both performance goals and cost efficiency.