What is the difference between a data warehouse and a data lake?

Expected Interview Answer

Step-by-Step Explanation

1. Step 1

   Identify the data types

   Structured tables suit a warehouse; mixed raw, semi-structured, and unstructured data suit a lake.

2. Step 2

   Choose schema timing

   Schema-on-write (model before load) for warehouses; schema-on-read (structure at query time) for lakes.

3. Step 3

   Decide the pipeline

   ETL transforms before loading a warehouse; ELT loads raw first, transforming inside or from a lake.

4. Step 4

   Match the workload

   BI and reporting favor the warehouse; data science and ML exploration favor the lake.

5. Step 5

   Plan governance and cost

   Warehouses cost more but enforce quality; lakes are cheap but need cataloging to avoid a data swamp.

6. Step 6

   Consider a lakehouse

   Combine cheap lake storage with warehouse-style tables and transactions when you need both.

Code Example

-- Data warehouse: define schema first, then load clean data (schema-on-write)
CREATE TABLE sales (
  order_id   INT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount     DECIMAL(10,2),
  order_date DATE
);
INSERT INTO sales VALUES (101, 5, 249.99, '2026-07-21');

-- Data lake: raw JSON files sit in object storage; structure is applied at query time
-- (schema-on-read) using an engine like Spark, Athena, or Trino
SELECT payload:customer_id::int   AS customer_id,
       payload:amount::decimal    AS amount
FROM raw_events
WHERE payload:event_type = 'purchase';

Follow-up Questions

• What is a data swamp and how do you prevent one?
• How does a lakehouse combine warehouse and lake features?
• When would you choose ELT over ETL?
• How do warehouses achieve fast query performance?
• Where do data marts fit relative to a warehouse?