Redshift Spectrum, federated queries and materialized views: DEA-C01 study notes and practice questions
What to know about Redshift Spectrum, federated queries and materialized views for the DEA-C01 exam, from the Data Store Management domain (26% of the exam guide), then 2 free questions to test it. Part of the DEA-C01 study guide.
A ski resort group keeps live lift-pass account data in an Amazon Aurora PostgreSQL database. Three years of ride logs sit in Amazon S3 in Parquet format and are queried through Amazon Redshift Spectrum.
Each Monday, the finance team needs one report that joins current account status from Aurora with the ride logs in S3. The account data changes during the week, and the team does not want a nightly copy of the operational database.
Which approach meets these requirements?
Choose one answer.
AA. Create an external schema with a secret for the Aurora credentials, then run one query joining live Aurora and Spectrum tables.
BB. Use AWS DMS to copy the Aurora tables into Redshift every night, and run the report against the copies with the Spectrum external tables.
CC. Export the Aurora tables to CSV each Monday with a manual query, upload them to S3, and join the files with the ride logs in Spectrum.
DD. Connect Amazon Athena to Aurora with a JDBC connection, and move the ride logs into the Aurora database as a table before the report runs.
B. Wrong: A nightly copy works, but the account data is stale for most of the week, and the team asked to avoid copying the operational database. The option therefore misses a stated requirement.
C. Wrong: Manual exports are slow and error-prone, and the data is a copy taken at export time. The option adds weekly work that the team wants to remove.
D. Wrong: Moving three years of logs into the operational database adds load and storage to the system the team wants to protect. The option also changes the data layout more than the report requires.
Explanation
Federated queries let Redshift read live operational data in Aurora PostgreSQL, while Spectrum reads the Parquet logs in S3, so one query can join both. The credentials stay in Secrets Manager through a secret reference. Nightly copies, manual exports and moving logs into the operational database each add a copy or a manual step.
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 →
Study Redshift Spectrum, federated queries and materialized views