DAS-C01 · Question #96
A large retailer has successfully migrated to an Amazon S3 data lake architecture. The company's marketing team is using Amazon Redshift and Amazon QuickSight to analyze data, and derive and…
The correct answer is D. The nightly data refreshes left the dashboard tables in need of a vacuum operation that could not. After large-scale INSERT, UPDATE, or DELETE operations in Amazon Redshift, tables accumulate unsorted rows and dead rows (from MVCC tombstones). Redshift's zone maps become inaccurate, causing queries to scan far more blocks than necessary. A VACUUM operation reclaims space…
Question
A large retailer has successfully migrated to an Amazon S3 data lake architecture. The company's marketing team is using Amazon Redshift and Amazon QuickSight to analyze data, and derive and visualize insights. To ensure the marketing team has the most up-to-date actionable information, a data analyst implements nightly refreshes of Amazon Redshift using terabytes of updates from the previous day. After the first nightly refresh, users report that half of the most popular dashboards that had been running correctly before the refresh are now running much slower. Amazon CloudWatch does not show any alerts. What is the MOST likely cause for the performance degradation?
Options
- AThe dashboards are suffering from inefficient SQL queries.
- BThe cluster is undersized for the queries being run by the dashboards.
- CThe nightly data refreshes are causing a lingering transaction that cannot be automatically closed
- DThe nightly data refreshes left the dashboard tables in need of a vacuum operation that could not
How the community answered
(46 responses)- A13% (6)
- B2% (1)
- C7% (3)
- D78% (36)
Explanation
After large-scale INSERT, UPDATE, or DELETE operations in Amazon Redshift, tables accumulate unsorted rows and dead rows (from MVCC tombstones). Redshift's zone maps become inaccurate, causing queries to scan far more blocks than necessary. A VACUUM operation reclaims space, resorts rows according to the sort key, and regenerates zone maps - restoring query performance. Because VACUUM runs in the background and can be slow on large tables, it may not complete automatically before users run dashboards the next morning. CloudWatch showing no alerts (option C) rules out a lingering transaction lock. The problem appearing only after the refresh points to a data-state issue (dirty tables), not a query or cluster sizing issue (options A and B).
Topics
Community Discussion
No community discussion yet for this question.