AWS Certified Data Engineer Associate DEA-C01 Practice Question

A company's Amazon Redshift RA3 cluster hosts a 5-TB fact table that receives new rows each night. Business analysts issue the same complex aggregation query every morning to populate dashboards, but the query still takes about 40 minutes even after regular VACUUM and ANALYZE operations. As the data engineer, you must cut the runtime dramatically, keep administration effort low, and avoid a large cost increase. Which approach will best meet these requirements?

Enable Amazon Redshift Concurrency Scaling so the query can execute on additional transient clusters.
Increase the WLM queue's slot count and enable short query acceleration to allocate more memory to the query.
Create a materialized view that pre-aggregates the required data, schedule an automatic REFRESH after the nightly load, and direct the dashboard to query the materialized view.
Change the fact table's distribution style to ALL so every node stores a full copy, eliminating data shuffling during joins.

Report Issue

Answer Description

Creating a materialized view lets Amazon Redshift store the pre-computed, aggregated result set on disk. When analysts query the materialized view, Redshift returns the stored result almost immediately instead of re-scanning and joining the 5-TB fact table, yielding a large runtime reduction. Scheduling an automatic refresh immediately after the nightly data load maintains accuracy while requiring minimal ongoing management.

Changing the fact table to an ALL distribution style would duplicate terabytes of data across every node, greatly increasing storage space and load time. Concurrency scaling adds transient clusters to improve throughput when many queries run simultaneously, but it seldom reduces the elapsed time of a single long query. Adjusting WLM queues or enabling short query acceleration allocates resources differently but will not eliminate the heavy table scan and aggregation work that dominates the query's runtime.

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.