Data Warehouse vs Data Lake in AWS: Redshift, S3 and Athena Explained

Quick answer

Use Amazon S3 + Athena when you want to store raw or semi-structured data cheaply and query it occasionally without managing infrastructure. Use Amazon Redshift when analysts or BI tools run frequent structured SQL queries and need consistent, fast responses. Use both (the lakehouse pattern) when you need cheap long-term storage and fast querying from the same data.

Simple explanation

In AWS terms: the storage unit is Amazon S3. The catalogue and query layer on top is AWS Glue and Athena. The filing cabinet is Amazon Redshift.

Data warehouse vs data lake at a glance

What a data warehouse is in AWS

A data warehouse is a database designed for analytical SQL queries across large amounts of structured data. You load clean, transformed data with a defined schema. This is schema-on-write: the structure is fixed before data enters.

In AWS, that service is Amazon Redshift. Redshift stores data in columnar format distributed across managed compute nodes. Aggregate queries (SUM, COUNT, GROUP BY across millions of rows) are very fast because Redshift only reads the columns relevant to your query, not entire rows.

Redshift is the right choice when:

• Business analysts and BI tools (Tableau, QuickSight, Looker) run frequent structured queries
• Dashboards and reports must load in seconds under concurrent load
• Data quality and schema consistency are critical
• You have a stable schema that does not change week to week

To understand how Redshift distributes data and optimizes queries internally, see the Redshift architecture guide. For cost planning, the Redshift pricing guide covers node types, reserved pricing, and Serverless.

What a data lake is in AWS

A data lake stores data in its raw or lightly processed state in Amazon S3. There is no predefined schema required when you write the data. You decide how to interpret it at query time. This is schema-on-read.

Because S3 storage is cheap (around $23/TB/month for Standard), you can afford to keep everything: raw JSON API responses, CSV exports, Parquet files, application logs, and more. Nothing has to be discarded because it does not fit a schema yet.

Athena is the query engine that sits on top. It runs standard SQL on files in S3 without you provisioning or managing a server. You point Athena at an S3 path and a table definition in the Glue Data Catalog, and Athena reads and queries the files directly.

A data lake is the right choice when:

• Data formats vary or change frequently: JSON APIs, nested structures, semi-structured logs
• You are doing exploratory analysis or building machine learning features from raw data
• Multiple teams need different views of the same underlying data without copying it
• You want to store data before you fully know how it will be used

For a deeper look at how to organize a data lake with raw, processed, and curated zones, see the data lake architectures guide.

How it works in AWS: the typical data flow

Most AWS data platforms follow a layered flow from raw ingestion to structured analytics. Here is what that looks like in practice:

1. Raw data lands in S3. Application logs, database exports, and API responses are written to an S3 raw zone, often as JSON or CSV. Nothing is transformed yet.
2. Glue crawlers catalog the data. AWS Glue scans the S3 prefix and registers table definitions in the Glue Data Catalog, making the raw files queryable by Athena immediately.
3. Athena queries raw or processed data. Data scientists and analysts run exploratory SQL on S3 data via Athena without provisioning anything. Queries on Parquet files with date-based partitions are fast and cheap.
4. Glue ETL jobs transform and promote data. A Glue ETL job reads from the raw zone, cleans and reshapes the data, and writes curated Parquet files to a processed S3 zone or loads them directly into Redshift.
5. Curated data loads into Redshift. The most frequently queried, structured data is loaded into Redshift via the COPY command (covered in the loading data into Redshift guide). BI tools connect to Redshift for dashboards and reporting.
6. Redshift Spectrum bridges both worlds. For historical or infrequently accessed data still in S3, Redshift Spectrum lets you query it from inside Redshift with standard SQL, joining it against local Redshift tables in the same query.

Raw ingestion          Processing             Querying
     |                     |                      |
  S3 (raw)  --> Glue ETL --> S3 (processed)  --> Athena
                        \                         (exploration, ML)
                         --> Redshift COPY --> Redshift
                                                  (BI, dashboards)
                                    ^
                          Redshift Spectrum
                       (S3 data queried from
                        inside Redshift)

Apache Iceberg is worth knowing at this stage. Iceberg is an open table format that adds ACID transactions, schema evolution, and time travel (querying data as it looked at a past timestamp) to S3-backed tables. Both Athena and Redshift Spectrum support Iceberg natively, making it the standard for lakehouse implementations.

For the full picture of how batch and streaming pipelines connect these layers, see designing data pipelines in AWS.

When to use a data warehouse

Start with or move to Redshift when:

• You have regular, concurrent users (analysts, BI tools) querying structured data throughout the day
• Dashboards are timing out or returning inconsistent results under load from Athena
• You need sub-second query responses on aggregated data
• Data governance requires enforced schemas and type constraints
• You are running hundreds of queries per day and Athena per-scan costs are adding up

Load only the data that needs fast, frequent access into Redshift. Historical raw data that is rarely queried belongs in S3, not in expensive managed storage. For tuning Redshift once it is set up, see the Redshift performance optimization guide.

When to use a data lake

Start with or stay with S3 + Athena when:

• You are a small team or early-stage product with low query volume
• Data formats change frequently or are not yet well-defined
• You want to store raw data for future use cases you have not defined yet
• Queries are infrequent: a few per day at most
• You need to support data science or ML workflows that process raw or semi-structured data
• You want to minimize operational overhead with no clusters to size or maintain

Athena query costs drop dramatically when you store data in columnar Parquet or ORC format and partition it by date or a relevant key. See the guide on S3 cost optimization for storage-side savings.

When to use both: the lakehouse pattern

The lakehouse is not a third option. It is the default for most mature data platforms. S3 is the single storage layer. Athena and Redshift both query it, each for different workloads.

The pattern works like this: everything lands in S3 (cheap, durable, scalable). Raw and historical data stays there permanently. A subset of curated, frequently-queried data gets loaded into Redshift managed storage for BI performance. Redshift Spectrum bridges the two: it lets Redshift query S3-backed data in the same SQL statement as local Redshift tables.

-- Redshift Spectrum: join local Redshift table with S3-backed Spectrum table
SELECT
    r.customer_name,
    s.order_count,
    s.total_revenue
FROM redshift_local.customers r
JOIN spectrum_schema.daily_order_summary s
    ON r.customer_id = s.customer_id
WHERE s.date = '2026-03-01'
ORDER BY s.total_revenue DESC;

Use both when:

• You need cheap storage for large historical datasets alongside fast querying for recent or curated data
• Different teams have different needs: data scientists use Athena on raw S3 data, analysts use Redshift for dashboards
• You want one copy of data accessible by multiple query engines without duplication
• You are adopting open table formats like Apache Iceberg for ACID transactions and schema evolution on S3

Redshift vs Athena: direct comparison

Both services run SQL. The difference is what they are optimized for and how they are priced.