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?