System design interview

Lakehouse vs Traditional Warehouse

MediumLocked55 min read

Compare Iceberg/Delta/Hudi lakehouses vs Snowflake/BigQuery/Redshift on latency, ACID, cost, openness, and ML.

system-designlakehousewarehouseicebergdelta

Start here

The problem in plain words

Three groups want the same sales data. Data engineers want to process it with Spark, data scientists want to train models in Python, and business analysts want dashboards. Today each group has its own copy, so there are about five copies, and they never quite agree.

A traditional data warehouse, such as Snowflake, BigQuery or Redshift, is a polished product that owns its storage and its engine. It is excellent for dashboards and easy to run, but the data lives inside it, and other tools have a harder time reaching it.

A lakehouse takes a different route. The data stays as ordinary files in cheap cloud storage, and a thin layer of table information on top makes those files behave like reliable database tables. Several different engines can then work on the same tables.

Words you will meet

Object storage
Cheap, huge cloud storage for files, such as Amazon S3. It stores files but knows nothing about tables.
Parquet
A popular columnar file format for analytics data.
Open table format
A standard way, such as Apache Iceberg, Delta Lake or Apache Hudi, of recording which files make up a table, with a transaction log, so many engines can use it safely.
ACID
Reliability rules for changes: a change is all or nothing, readers see a consistent view, and committed data stays committed.
Snapshot / time travel
Each commit creates a version of the table that you can still read later, such as the table as it was last Friday.
Lakehouse
Open table formats over object storage, queried by engines like Spark and Trino.
Data warehouse
A managed system that owns storage and a SQL engine and is tuned for fast analytics.
Compute and storage separation
Paying for storage and for processing power separately, so you can scale each on its own.
Five copies of one table
copycopycopySales dataone truth, in theoryWarehouse copyfor dashboardsML copyfor trainingSpark copyfor jobs
Press Play to watch it move

Each arrow is one thing happening. Play it step by step, or read the list below.

  1. 1.Analysts need the data in the warehouse, so it is copied there.
  2. 2.Data scientists need files for Python and notebooks, so it is copied again.
  3. 3.Engineers need it in the lake for Spark, so it is copied once more. Each copy costs money and drifts, so reports stop matching.

The numbers, as things you can picture

  • Cloud storage and warehouse spend already existYou are not starting from nothing. The question is how to combine what you pay for today.

What the interviewer wants

Compare the two approaches fairly on speed, reliability, cost, openness and machine learning, and recommend a pattern for a mid-size company without picking a vendor fight.

Interview framing

Compare lakehouse formats (Iceberg, Delta Lake, Hudi) with traditional warehouses (Snowflake, BigQuery, Redshift). Discuss query latency, ACID, cost models, openness, and ML compatibility.

Background from first principles

A traditional cloud warehouse stores analytical tables in a managed service. You load data, SQL works well, governance and BI UX are polished, and you pay a productized compute/storage model. Many companies succeed here.

A lakehouse puts warehouse-like table semantics on cheap object storage using open table formats. Files are typically Parquet. Metadata layers (Iceberg/Delta/Hudi) provide transactions, schema evolution, and time travel so multiple engines (Spark, Trino, sometimes warehouses themselves) can share one copy of the facts.

The pain that creates lakehouses: copying the same sales facts into five systems for BI, ML, and ad hoc Spark, then watching definitions drift.

Without sharing:
  raw --> warehouse copy
      --> ML copy
      --> yet another export

With lakehouse:
  object storage tables (open format)
      ^
      +-- Spark / Trino / WH readers

Full problem statement

Give a practical comparison for a mid-size DE org that already has cloud object storage and some warehouse spend. Cover open table formats, managed warehouse storage/compute, ACID at table level, ML and BI access, cost levers, operational burden (small files, compaction), and when hybrid is normal. Avoid vendor wars; be accurate.

What "good" looks like

You name the duplication pain. You define lakehouse clearly. You compare axes: latency, ACID, cost, openness, ML. You admit warehouses often win out-of-the-box BI latency. You admit lakehouses need performance engineering. You recommend a hybrid pattern many companies actually run.

Clarifying questions

  • Primary consumers: BI, ML, or both?
  • Existing Snowflake/BQ commitment and skills?
  • Need for multi-engine access to the same tables?
  • Who will own compaction and table maintenance?
  • Regulatory / governance tooling expectations?

Scale prompts

  • Storage cost of second and third copies.
  • Compaction frequency for streaming small files.
  • BI query latency targets vs ML scan throughput.
  • Engineering time cost vs managed service premium.

Out of scope

  • Claiming lakehouses need no warehouse-like discipline.
  • Absolutism that one side makes the other obsolete.
  • Ignoring governance on the lake.
  • Declaring warehouses obsolete.
  • Declaring lakehouses free of ops.
  • Ignoring security on object storage.

First principles expanded

Analytical data needs:

1. Durable storage. 2. A table abstraction (schema, snapshots, transactions). 3. Compute engines to query and transform. 4. Governance for people and tools.

Traditional warehouses bundle many of these. Lakehouses assemble open storage + open table format + chosen engines. Neither absolves you from modeling, quality, and access control.

Restated problem

"Compare Iceberg/Delta/Hudi lakehouses with Snowflake/BigQuery/Redshift-style warehouses on latency, ACID, cost, openness, and ML. Recommend a practical pattern for a mid-size DE org that already has object storage and some warehouse spend."

What good looks like

  • Duplication pain story.
  • Correct definition of open table formats.
  • Balanced comparison (no fanboying).
  • Small-file ops honesty.
  • Hybrid recommendation.

Clarifying questions expanded

  • BI vs ML traffic split?
  • Contractual warehouse commit?
  • Need Spark and SQL on same tables?
  • Platform staffing for compaction?
  • Governance tool preferences?

Estimation prompts

  • Cost of extra full copies per year.
  • Engineering hours for table maintenance.
  • BI latency targets (p95 seconds).
  • Streaming file creation rate and compaction interval.

Room expectations and out-of-scope polish

Interviewers want you to sound like someone who has paid a cloud bill and compacted tables, not someone reciting a vendor slide. Mention governance: object storage buckets still need IAM, row/column controls, and PII handling. Mention that "open" does not mean "unguarded."

Keep multi-region DR and exotic catalog federation out of scope unless asked. Keep the conversation on copies, engines, latency, cost, and ops ownership.