How Do You Combine Live, Current, and Archived Redshift Data?
A retail company uses Amazon Aurora PostgreSQL to process and store live transactional data. The company uses an Amazon Redshift cluster for a data warehouse. An extract, transform, and load (ETL) job runs every morning to update the Redshift cluster with new data from the PostgreSQL database. The company has grown rapidly and needs to cost optimize the Redshift cluster. A data engineer needs to create a solution to archive historical data. The data engineer must be able to run analytics queries that effectively combine data from live transactional data in PostgreSQL, current data in Redshift, and archived historical data. The solution must keep only the most recent 15 months of data in Amazon Redshift to reduce costs. Which combination of steps will meet these requirements? (Choose two.)
Community Votes
100% of anonymous learners picked answer A. Votes are pick records left by other test-takers — they are not the verified answer.
Community Insight
Tests whether you can pair Redshift Federated Query for live PostgreSQL with a Spectrum-over-S3 archive for history beyond 15 months; the trap is assuming Spectrum can read PostgreSQL or S3 Glacier Flexible Retrieval.
Amazon Redshift Federated Query lets Redshift read live Aurora PostgreSQL tables without copying them, while a monthly UNLOAD job moves data older than 15 months to Amazon S3 and Redshift Spectrum queries it externally. This page confirms A and C as the correct two-step combination for cutting Redshift storage costs while keeping live, current, and archived data joinable.
Many learners answer only A despite the 'Choose two.' instruction, and the most common wrong second pick is D: Redshift Spectrum cannot query S3 Glacier Flexible Retrieval, so the archived history would be unqueryable (option B is similarly wrong because Spectrum is an S3 query engine, not a live database connector).
Community Discussion (10 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
Redshift Federated Query (A) creates an external schema that maps to Aurora PostgreSQL, so Redshift can read the live transactional tables directly in the same SQL statement that queries local Redshift tables, with no daily copy of live rows needed. Option C satisfies the cost and lifecycle requirement: UNLOAD writes rows older than 15 months to Amazon S3 (for example as Parquet), the DELETE frees Redshift storage, and a Redshift Spectrum external table makes those archived S3 objects queryable again. Spectrum reads S3 Standard data and its results can be joined with local Redshift data and federated PostgreSQL data in one query, which is exactly the 'combine live, current, and archived' requirement. Together A and C keep only the most recent 15 months inside Redshift while preserving historical analytics at low cost.Why the Other Options Are Wrong
Option B is wrong because Redshift Spectrum is not a live database connector — it reads external tables registered in the AWS Glue Data Catalog that point at Amazon S3, not at an Aurora PostgreSQL endpoint; Federated Query, not Spectrum, is the feature that reaches PostgreSQL. Option D fails on a hard technical limit: Spectrum cannot query S3 Glacier Flexible Retrieval objects directly, since it supports S3 Standard, S3 Intelligent-Tiering, and Glacier Instant Retrieval only, so archiving to Glacier Flexible Retrieval would make the historical data inaccessible to analytics. Option E is wrong because Redshift materialized views operate on local Redshift tables and cannot blend federated live data with Spectrum external data in one live definition, and even if they could, a materialized view archives nothing and deletes no rows, so the 15-month cost target is never met.Community Comment Notes
The thread converges strongly on A and C, with lalitjhawar noting that Federated Query "allows Redshift to directly query the live transactional data in the PostgreSQL database" without importing it. artworkad supplies the decisive elimination rule for D: "Redshift Spectrum cannot read from S3 Glacier Flexible Retrieval." Only one voter, Palee, picked A and D, arguing that C "doesn't talk about archiving Historical data," but C explicitly UNLOADs the over-15-month rows to S3, so that reading misses the option's wording. tgv and SambitParida independently land on A & C, the latter repeating the Glacier limitation.Official Reference
Exam Strategy
This is a 'Choose two.' item, so commit to exactly two letters — a single-letter answer such as A is automatically incomplete. Eliminate B and D first because Redshift Spectrum only reads S3-backed external tables (and never Glacier Flexible Retrieval), then confirm C covers the 15-month archival requirement.
Frequently Asked Questions
Why can't Redshift Spectrum query the historical data if it is archived to S3 Glacier Flexible Retrieval?
Spectrum only reads S3 Standard, S3 Intelligent-Tiering, and Glacier Instant Retrieval, so Glacier Flexible Retrieval objects must be restored and copied to a supported class before Spectrum can query them.
Does Redshift Federated Query remove the need for the daily ETL job into Redshift?
No. Federated Query adds direct read access to live Aurora PostgreSQL for analytics, but the current Redshift data and the S3 history still come from the existing ETL run and the monthly UNLOAD job.
Related Analysis
Practice All DEA-C01 Questions
Access 100 questions with complete answers and detailed explanations.
View Full DEA-C01 Practice Test →