AWS Certified Data Engineer Associate DEA-C01 Practice Question

A company loads 2 TB of time-series sensor events into an Amazon Redshift table every day by appending new rows. Business intelligence dashboards filter on event_date for the most recent 7 days and aggregate results by device_id, joining to a small device metadata table. The current heap table causes long scan times. Which schema change will most effectively reduce dashboard latency without adding load-time complexity?

Create an external Redshift Spectrum table partitioned by event_date and load new partitions daily.
Convert the table to a compound sort key of event_date, device_id and use device_id as the DISTKEY.
Leave the heap layout unchanged but schedule VACUUM and ANALYZE to run after each daily load.
Change the table to EVEN distribution and define an INTERLEAVED sort key on device_id, event_date.

Report Issue

Answer Description

Dashboards always apply a range predicate on event_date, so physically co-locating recent dates together is the primary performance need. Converting the table to a compound sort key with event_date as the leading column stores new data in the same order it is inserted, letting Redshift skip blocks that fall outside the 7-day window while avoiding the extra maintenance that interleaved keys require. Setting device_id as the DISTKEY places rows for the same device on the same node, improving the join to the small reference table with minimal overhead. Interleaved keys improve symmetry across columns but incur frequent reindexing costs and provide less benefit for time-based range filtering. EVEN distribution with an interleaved key still suffers from larger scans. Redshift does not support native table partitioning, so attempting to partition by event_date is not possible inside the cluster. Merely enabling VACUUM and ANALYZE maintenance does not address the fundamental lack of a sort key optimized for query predicates.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.