What's changed: In-scope coverage: added purpose-built stores/storage (DocumentDB/Keyspaces/MemoryDB/S3 Tables/EFS/CloudFront/Backup)
2.2Data Warehouse and Querying (Redshift, Athena)
Understand Amazon Redshift (columnar warehouse), querying S3 directly with Redshift Spectrum and Athena, and choosing ETL vs. ELT.
Large analytical queries suit a columnar data warehouse. On AWS, use Amazon Redshift, and Athena for querying S3 directly.
2.2.1Redshift and Athena
- Amazon Redshift: a columnar DWH that runs large analytical (OLAP) queries fast.
- Redshift Spectrum: query S3 data directly from Redshift (no load).
- Athena: serverless SQL on S3; billed per data scanned, uses the Glue catalog.
- ETL vs. ELT: transform-then-load (ETL) or load-then-transform-in-warehouse (ELT), by requirement.
Common on DEA: ad-hoc SQL on S3, serverless = Athena, steady large-scale BI/analytics = Redshift, query S3 directly from Redshift = Spectrum, Athena uses the Glue catalog.
Split analytical queries by "serverless ad-hoc = Athena / steady large-scale BI = Redshift." Athena runs Presto/Trino-based SQL on S3 serverlessly, billed by data scanned, so the prior section’s Parquet + partitioning pays off (it shares the Glue Data Catalog). Redshift is a columnar DWH where table design drives performance: distribution style (DISTSTYLE)/DISTKEY places data across nodes, and SORTKEY optimizes block skipping (zone maps)—match them to join and filter keys. Read S3 without loading via Redshift Spectrum; scale with separated storage/compute using RA3 nodes or Redshift Serverless; share data with Data Sharing; handle semi-structured data with SUPER type/PartiQL; and load with COPY (parallel). Materialized views and automation (auto VACUUM/ANALYZE/WLM) are also key. Choose ETL (transform first) / ELT (transform in the warehouse) by requirement.
| Requirement | Use |
|---|---|
| Ad-hoc SQL on S3, serverless | Athena |
| Steady large-scale BI/analytics | Redshift |
| Query S3 from Redshift | Redshift Spectrum |
| Optimize join/filter performance | DISTKEY / SORTKEY |
Scenario: occasional S3 analysts coexist with a team running heavy daily BI. Ad-hoc goes to Athena (Glue catalog, Parquet to limit scanned data). Steady BI loads into Redshift via COPY with a DISTKEY on the join key and a SORTKEY on date. Some data is read directly from S3 via Spectrum to avoid loading.
Q. Serverless SQL on S3? Athena (billed per scan). Q. Steady large-scale analytics? Redshift. Q. Read S3 without loading? Spectrum. Q. Redshift performance key? DISTKEY/SORTKEY. Q. Cheaper Athena? Parquet + partitioning.
Watch the mix-ups: (1) Athena bills per scan—SELECT * or non-partitioned data gets expensive. (2) Redshift has always-on cost (for sporadic queries, Athena/Serverless can be cheaper). (3) A bad DISTKEY causes data skew and slowness. (4) Spectrum is also billed by S3 data scanned (Parquet + partitioning helps).
In Redshift, DISTKEY and SORTKEY design greatly affect query performance; choose them to match join and filter keys.
2.2.2Section summary
- Athena (serverless SQL on S3) / Redshift (large DWH)
- Query S3 from Redshift = Spectrum; performance via DISTKEY/SORTKEY
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. What lets you run ad-hoc SQL on S3 serverlessly, billed per data scanned?
Q2. Which columnar warehouse runs large, steady BI/analytical queries fast?
Q3. You want to query S3 data directly from Redshift without loading it. What do you use?
Keep track of your progress
The full study guide is free to read. Sign up free to practice with the question bank, track what you have read, review your mistakes, and highlight passages.