DEA-C01 Data Operations and Support Practice Question
A company uses Amazon Redshift for its data warehouse. During a routine audit, the data engineer discovers that some queries are returning stale data even though the underlying source data has been updated. The engineer confirms that the COPY command completes successfully and that no errors are reported. Which action should the engineer take to ensure queries reflect the latest data?
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Refresh the materialized views that the queries are using.
Materialized views in Amazon Redshift are not automatically refreshed when the underlying base tables are updated via COPY. To ensure queries return the latest data, the materialized views must be manually refreshed using the REFRESH MATERIALIZED VIEW command. Option A (VACUUM) reclaims disk space and re-sorts rows but does not update materialized views. Option B (RESET ALL) clears the query result cache, but stale materialized views persist even if the cache is cleared. Option C (ANALYZE) updates table statistics for query planning, not the actual data content. Therefore, refreshing the materialized views is the necessary action.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Run the VACUUM command on the source tables.
Why it's wrong here
VACUUM reclaims storage and re-sorts rows; it does not refresh query results against updated source data. It is tempting because it maintains table health after COPY loads, but it would be the correct action for performance degradation, not for stale reads caused by snapshot isolation.
- ✗
Clear the Redshift result cache by running RESET ALL.
Why it's wrong here
RESET ALL clears session-level parameters and query groups, not the result cache; it cannot make stale reads fresh. The tempting appeal is that cache clearing sounds like a freshness fix, and it would be right if the stem pointed to a cached result set rather than uncommitted or unrefreshed data.
- ✗
Run the ANALYZE command on the source tables.
Why it's wrong here
ANALYZE refreshes table statistics for the query planner, so it changes execution plans, not the stored rows themselves. It is tempting because it is a standard post-COPY maintenance step, and it would be correct if queries were slow due to outdated statistics rather than returning stale data.
- ✓
Refresh the materialized views that the queries are using.
Why this is correct
Materialized views store precomputed results and do not automatically reflect base-table changes, so queries reading them return stale data even after COPY succeeds. Refreshing the views re-executes their defining queries against updated source tables, satisfying the requirement for current results.
Go deeper
Related to this question
About these practice questions
This DEA-C01 question is part of Courseiva's 1,321-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DEA-C01 practice question is part of Courseiva's free Amazon Web Services certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DEA-C01 exam.