Get Your CertDEA-C01PlayCompete
‹ All DEA-C01 questions

DEA-C01 Practice Question 25: Redshift Spectrum Cold Data

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

Data Store Managementmedium

An Amazon Redshift cluster stores five years of fact_sales. Ninety-eight percent of queries touch only the last 90 days, but at quarter end a handful of analysts run year-over-year comparisons that join the full history to the current dimension tables. The team wants to shrink Redshift managed storage while keeping those quarterly queries working in the same BI tool, against the same Redshift connection, with no change to how analysts connect.

Which approach should the data engineer implement?

Select one answer
Show answer and explanation

Answer:

  • C. UNLOAD rows older than 90 days to Amazon S3 as partitioned Parquet, register them through an external schema, and query them with Amazon Redshift Spectrum

Redshift Spectrum lets you "efficiently query and retrieve structured and semi-structured data from files in Amazon S3 without having to load the data into Amazon Redshift tables." An external schema exposes the S3 Parquet files as external tables inside the same database, so a single SQL statement can join the cold external history to the warm local fact and dimension tables. The cold rows leave Redshift managed storage and sit in much cheaper S3 storage, and because most of the processing happens in the Spectrum layer, the quarterly scan does not need a bigger cluster. The BI tool keeps its existing Redshift connection, nothing changes for the analysts.

Option A does not remove any data from the cluster. A materialized view is an *additional* stored result set derived from the base table, so it increases storage rather than reducing it.

Option B turns a quarterly report into a manual restore project. Snapshot restores take time, a second cluster costs money for as long as it runs, and the restored cluster cannot be joined to the live dimension tables in one query.

Option D gets the storage layout right but breaks the stated requirement. Athena is a separate service with a separate endpoint, so analysts would change tools for the quarterly run and would lose the ability to join the S3 history to the Redshift-resident dimension tables in a single query. Spectrum delivers the same S3 scan economics without that split.

Reference: Amazon Redshift Spectrum

Practice Test 3 ›

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.