DEA-C01 Practice Question 15: Redshift Materialized View Rewrite

AWS Certified Data Engineer – Associate (DEA-C01) · Test 2 · question 5 of 10

Data Operations and Supportmedium

A sales dashboard in a third-party BI tool runs the same query against Amazon Redshift every few minutes. The query joins a 3-billion-row sales fact table to two small dimension tables with inner joins and computes SUM and COUNT grouped by region and day. New sales rows are appended in small micro-batches throughout the day. The query takes about 40 seconds and is slowing down other workloads. The BI tool generates its own SQL, which the team cannot change, and the dashboard must reflect recent loads.

Which approach will most reduce the work Redshift does for this dashboard?

Show answer and explanation

Answer:

B. Create a materialized view with the join and aggregation, enable AUTO REFRESH YES, and let automatic query rewriting route the BI tool's queries to the view

A materialized view stores the precomputed result of its query. Because this view uses only inner joins and SUM/COUNT aggregations, Redshift can refresh it incrementally: it finds the rows added to the base tables since the last refresh and applies only those changes. It does not recompute 3 billion rows. With autorefresh, Redshift refreshes the view soon after the base tables change, so the dashboard stays close to current. Automatic query rewriting solves the "cannot change the SQL" constraint. Redshift "rewrite[s] queries to use materialized views ... even for queries that don't explicitly reference a materialized view", and it "only uses materialized views that are up to date", so results stay correct. Joins, SUM, COUNT, and GROUP BY are all on the supported list.

Option A fails because the data changes all the time. The result cache only serves a result while the underlying tables are unchanged, and a micro-batch load every few minutes keeps invalidating it. Most dashboard runs would still do the full 40-second join.

Option C changes scheduling, not work. Short query acceleration runs short queries ahead of long ones. A 40-second aggregation over billions of rows is not a short query, and even if it were prioritized, it would still use the same resources on every run.

Option D gives no speedup. A standard view is only saved query text. Redshift expands it into the same join and aggregation every time the dashboard queries it. Repointing the BI dataset is also the kind of change the team was trying to avoid.

References: Refreshing a materialized view; Automatic query rewriting to use materialized views

Practice Test 2 ›

Study guides for DEA-C01

AWS Developer Associate exam guide: DVA-C02 to DVA-C03AWS Developer Associate exam: DVA-C02 ends 30 November 2026 and DVA-C03 starts 1 December. What changes, which to sit, format, price and study plan.

How AWS Certification Exams Work: Scoring and RenewalHow AWS certification exams work: levels, booking, question types, unscored questions, scaled scores, pass marks, validity and how recertification works.

What Is Amazon RDS? Multi-AZ, Read Replicas and BackupsWhat is Amazon RDS? Engines, Multi-AZ, read replicas, backups, RDS Proxy, Aurora and pricing explained, with the traps that AWS exams set around each one.

Microsoft Fabric certification: DP-700 vs DP-600Microsoft Fabric certification means two exams, DP-600 and DP-700. Their roles, skills, format, cost in India, and which one to take first.

Databricks Data Engineer Associate vs AWS DEA-C01Databricks Data Engineer Associate vs AWS Certified Data Engineer – Associate - format, cost, topics and which one to take for your job, as of 2026.

Which AWS certification should you get? 2026 roadmapWhich AWS certification to get first, by role and experience, with each exam's status in October 2026, what is changing, and prices in USD and INR.

What Is AWS Glue? Data Catalog, Crawlers and ETL JobsWhat is AWS Glue? The Data Catalog, crawlers, ETL jobs and DPUs, workflows, streaming, job bookmarks, Flex and Data Quality, plus the traps AWS exams set.