data aws warehouse claude-curated
Two dominant analytics patterns on AWS: a managed MPP data warehouse (Redshift) versus a data lake of Parquet files on S3 queried by external engines (Athena, Spark, Redshift Spectrum). They solve overlapping problems with very different trade-offs.
The two architectures
- Redshift — columnar MPP warehouse. Storage and compute are coupled (provisioned clusters) or partially decoupled (RA3 nodes with managed storage, Redshift Serverless). Data is loaded into Redshift-managed storage, queried via SQL.
- S3 + Parquet lake — Parquet files in S3, schema in the Glue Data Catalog. Any compatible engine reads in place: Athena, EMR/Spark, Redshift Spectrum, Trino, DuckDB. No ingestion into a proprietary store.
Comparison
| Dimension | Redshift | S3 + Parquet Lake |
|---|---|---|
| Cost model | Cluster /GB | S3 storage /TB scanned, EMR $/hour, etc.) |
| Query latency | Sub-second to seconds (warm result cache, sort keys, dist keys) | Seconds to minutes (cold metadata, no indexes) |
| Concurrency | High with concurrency scaling, but bounded by cluster size | Effectively unbounded — each engine/user spins up independently |
| Scale | Petabytes (RA3, Serverless) | Effectively unlimited — S3 is the scale frontier |
| Schema enforcement | Strong — typed columns, constraints (informational), DDL-managed | Loose — schema-on-read, type drift goes silent unless enforced |
| Mutability | Native INSERT/UPDATE/DELETE/MERGE | Append-only by default; mutations need Iceberg / Hudi / Delta |
| Indexing | Sort keys, dist keys, zone maps | Partition pruning + columnar pruning only |
| Vendor lock-in | Proprietary storage format | Open format — Parquet readable by any engine |
| Operational overhead | Vacuum, analyse, key tuning (less with Serverless) | Compaction, partitioning, file-size management |
When to pick Redshift
- Predictable BI dashboard workloads where the same SQL runs hundreds of times an hour and latency matters.
- Heavy joins between curated dimensional models (star/snowflake schemas).
- Workloads needing transactional
MERGEand SCD Type 2 dimensions without bolting on a table format. - Teams already standardised on SQL with no Spark/Python footprint.
When to pick S3 + Parquet
- Cheap retention of large raw datasets — pay storage only, query rarely.
- Multi-engine access — data scientists in Spark, analysts in Athena, ML pipelines in SageMaker, all reading the same files.
- Exploratory or ad-hoc analytics where bytes-scanned pricing aligns with usage.
- Streaming ingest landing zones (Firehose → S3 → Parquet conversion) where data lands continuously.
- Large-scale batch processing where Spark is the natural fit.
The hybrid pattern (most real architectures)
Almost no production setup is purely one or the other. The common shape:
- Raw / bronze on S3 — every event, log, CDC record lands here cheaply. Parquet, partitioned by date.
- Cleaned / silver on S3 — deduplicated, validated, joined with reference data. Still on S3, still queryable by anything.
- Curated / gold in Redshift — pre-aggregated, dimensionally modelled tables for BI. Loaded from silver via
COPYor Spectrum.
The lake handles cheap retention and exploration; the warehouse serves the production dashboards. See Data Lakehouse for the convergence pattern.
Redshift Spectrum
Spectrum lets a Redshift cluster query S3 directly, joining S3 tables with native Redshift tables in one SQL statement:
SELECT u.name, s.event_count
FROM redshift_users u
JOIN spectrum_schema.events s ON u.id = s.user_id
WHERE s.day = '2026-04-29';Spectrum compute is separate from cluster compute and billed per TB scanned (similar to Athena). It’s the bridge for the hybrid pattern — keep cold data on S3, query it from the warehouse without moving it.
Redshift Serverless
Removes cluster sizing and management. Pays per RPU-second of actual query time, with managed storage on S3-backed RMS. Closes the operational gap with Athena for spiky workloads — minimum charge is small, idle is free, autoscale handles concurrency. For unpredictable load patterns, Serverless is often the cheapest Redshift option.
Cost back-of-envelope
A 10 TB dataset, queried lightly (~50 queries/day, each scanning 100 GB):
- Athena on S3 + Parquet — storage ~5 × 50 × 30 × 0.1 = 980.
- Provisioned Redshift (small RA3 cluster) — ~$3,000–6,000/month regardless of query count. Storage included.
- Redshift Serverless — depends entirely on query duration; spiky workloads can land between the two.
Inverting the same dataset with thousands of dashboard queries per hour flips the answer — Redshift’s flat cost dominates Athena’s per-query model.
Schema enforcement caveat
The lake’s “schema-on-read” flexibility is also its biggest operational risk. A producer changes a column type from int to string and silent corruption propagates to every downstream consumer. Mitigations:
- Enforce schema at write time (Glue contract validation, dbt tests on landing).
- Use Iceberg / Delta / Hudi for managed schema evolution with explicit DDL.
- Monitor Glue crawlers — they can mask type drift by widening to
string.
Redshift forces you to declare types up front; the lake pushes that discipline onto your pipeline tooling.
Decision shortcut
- One workload, predictable SQL, low latency, BI-shaped → Redshift.
- Many engines, large cheap retention, ad-hoc / ML / exploration → S3 lake.
- Both → hybrid, with Spectrum or Serverless bridging.