A public library system's Amazon Redshift cluster holds ten years of circulation transactions. Dashboards show loans by branch and month, and about 400 staff open the dashboards each morning. The aggregation takes several minutes when it runs against the base table.
Staff accept data that is at most a few minutes old, and the data engineering team wants the dashboards to stay fast without a custom refresh job.
Which approach meets these requirements?
Choose one answer.
AA. Create a standard view over the transactions table, so that each dashboard query runs the aggregation against the latest data.
BB. Create a materialized view of the aggregation with auto refresh turned on, so that Redshift keeps it updated and the dashboards query the stored results.
CC. Write a nightly job that drops and reloads a summary table from the base table, and point the dashboards at that table.
DD. Expose the transactions as an external table in Amazon S3 through Redshift Spectrum, and aggregate the files at query time.
Show the answer and explanation
Answer: B
A. Wrong: A standard view runs the full aggregation every time, so 400 morning dashboard loads would repeat the slow query. The option does not make the dashboards fast.
B. Right: Correct. A materialized view stores the aggregated results, and auto refresh updates it as the base table changes. The dashboards read the precomputed results, and no custom refresh job is needed. Learn more: Refreshing a materialized view (opens in a new tab)
C. Wrong: A nightly reload leaves the dashboards a day behind, which misses the few-minutes freshness target. It also needs a job the team wants to avoid.
D. Wrong: Spectrum scans files in S3 for every query, which is usually slower than local storage for repeated aggregations. The option does not reduce the work each dashboard load does.
Explanation
A materialized view stores precomputed results, so repeated dashboard loads read small results instead of repeating a large aggregation. With auto refresh, Redshift updates the view as the base table changes, which keeps data within the freshness target without a custom job. Standard views, nightly reloads and Spectrum scans each miss one of those goals.
Want more on Redshift Spectrum, federated queries and materialized views? The 5 full-length practice sets have 10 more Redshift Spectrum, federated queries and materialized views questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets →