Data Warehouses vs Data Lakes in System Design Interviews: When to Use Them
When an interview asks you to design a system that turns raw events into business insights, the storage choice is a key decision. The two most common options are a data warehouse and a data lake. Both solve the problem of persisting large volumes of data, but they do it in very different ways.
One-Sentence Definitions
- Data Warehouse: A relational store optimized for fast, ad-hoc queries on cleaned, structured data.
- Data Lake: A flat, object-storage repository that holds raw, unstructured or semi-structured data at any scale.
The Problem They Solve
Imagine an e-commerce site that generates click streams, order records, and inventory updates every second. The business wants two things:
- Real-time dashboards showing sales per minute.
- Deep, historical analysis to train recommendation models.
Both needs require persisting the data, but the query patterns, latency expectations, and data formats differ. A data warehouse excels at the first; a data lake excels at the second.
How They Work – Step by Step
Below is a tiny end-to-end pipeline that uses both components.
- Ingestion – Events arrive via an API gateway and are placed on a streaming service (e.g., Amazon Kinesis). The raw JSON payload is written to an S3 bucket – this is the lake.
- Landing Zone – A Lambda function validates the schema and writes a copy of the data to a staging table in a warehouse (e.g., Amazon Redshift). The staging table holds a day’s worth of data.
- ETL/ELT – At the end of the day, a scheduled job runs
COPYfrom S3 into Redshift, then transforms the data (type casting, denormalization) into the final fact and dimension tables. - Query – Business analysts run SQL queries against the warehouse for dashboards. The warehouse’s columnar storage and materialized views give sub-second response times.
- Advanced Analytics – Data scientists read the raw files directly from S3 (the lake) with Spark or Athena, joining them with the warehouse tables when needed. Because the lake keeps every event, they can train models on years of history without re-ingesting.
Small Worked Example
Suppose you have 10 GB of click data per day. You store each day’s raw JSON in an S3 prefix s3://my-lake/clicks/2024-04-01/. After the day ends, you run:
COPY clicks_staging FROM 's3://my-lake/clicks/2024-04-01/'
CREDENTIALS 'aws_iam_role=...'
FORMAT AS JSON 'auto';Then you transform:
INSERT INTO clicks_fact (user_id, product_id, ts)
SELECT user_id, product_id, timestamp
FROM clicks_staging;The warehouse now holds a tidy table ready for BI tools, while the lake still contains the original events for any future re-processing.
Where It Shows Up in Classic Interview Questions
| Interview Question | Where the Warehouse Appears | Where the Lake Appears |
|---|---|---|
| Design a real-time analytics dashboard | As the query layer that serves sub-second KPI tables. | As the raw event store feeding the streaming pipeline. |
| Build a recommendation engine for a video platform | For storing aggregated user-item interaction tables used by the model. | For keeping every watch event, video metadata, and logs for offline feature engineering. |
| Create a log-processing system for a SaaS product | To host the summarized error counts and alert tables. | To archive the raw log files that may be needed for forensic analysis. |
In each case, the interviewer will probe why you chose one over the other, and may ask you to justify the data flow between them.
Trade-offs and the Alternative Interviewer May Ask About
| Aspect | Data Warehouse | Data Lake |
|---|---|---|
| Schema | Fixed, enforced at load time. | Schema-on-read; you can store anything. |
| Query Latency | Milliseconds to seconds for indexed queries. | Seconds to minutes; depends on file format and engine. |
| Cost | Higher per-query cost; storage is cheaper than compute but more expensive than raw object storage. | Very low storage cost; compute is pay-as-you-go when you run queries. |
| Governance | Strong ACID guarantees, row-level security. | Less built-in security; relies on bucket policies and IAM. |
| Scalability | Scales to petabytes, but scaling compute may require resizing clusters. | Virtually unlimited; you add more objects to the bucket. |
A typical follow-up question is "What if the latency requirement changes?" The interviewer may expect you to discuss moving from a lake-only solution (cheaper, slower) to a hybrid approach where the lake feeds a warehouse that serves low-latency queries.
30-Second Pitch You Can Say Out Loud
"A data lake is a cheap, flexible bucket that stores raw events in their original format. A data warehouse is a structured, query-optimized store for cleaned data that powers fast dashboards. In an interview, I’d keep the lake as the source of truth, ingest into a streaming layer, then ETL into the warehouse for analytics, and explain the trade-offs in cost, latency, and governance."
Common Mistakes Candidates Make
- Treating the lake as a query engine – Saying you’ll run ad-hoc SQL directly on raw files without mentioning the need for a processing engine (Athena, Spark) leads to unrealistic latency expectations.
- Ignoring data movement – Failing to describe how data flows from lake to warehouse (ETL/ELT) makes the design look like two disconnected silos.
- Mixing up cost drivers – Claiming that warehouses are always cheaper than lakes; in practice, compute-heavy queries can dominate cost.
- Skipping governance – Overlooking security, data cataloging, and schema evolution, which are essential for production systems.
How to Practice This
- Study the worked design for a cloud data warehouse in the System Design AI library, then compare it with the lake-plus-warehouse pipeline above.
- Sketch your own diagram on the canvas: start with the request path, place each component, and write a one-sentence note for each.
- Run a mock interview: explain the lake-to-warehouse flow, answer the trade-off questions, and get feedback from the AI coach.
Questions people ask
When should I choose a data lake over a data warehouse?
Choose a lake when you need to store diverse, unstructured data at low cost and you don’t require sub-second query latency. It’s ideal for archival, batch analytics, and feeding downstream pipelines.
Can I replace a data warehouse with a lake-only solution?
You can, but expect slower query performance and more complex query logic. For interactive dashboards, the lack of columnar storage and indexing makes a pure lake solution impractical.
How do I handle schema changes in a lake-warehouse architecture?
Keep the raw data immutable in the lake. Evolve the warehouse schema by adding new columns or tables, and adjust the ETL jobs to map new fields while preserving backward compatibility.
What AWS services map to these concepts?
S3 serves as the data lake, while Redshift (or Athena on top of S3 with a Glue catalog) acts as the data warehouse. The interview may expect you to name these services when describing concrete implementations.