Data Warehouse vs Data Lake, ETL vs ELT

Picture twenty services posting order events as JSON. Finance wants a daily revenue number it can trust. The data science team wants every raw event, including fields nobody has asked about yet. Same bytes, different shapes.

A data warehouse serves finance. Data is checked, typed and arranged into tables before anyone queries it, usually fact tables (one row per sale) joined to dimension tables (customer, product, date). A data lake serves the data scientists. Files land in cheap storage roughly as produced, and structure is applied when someone reads them.

ETL and ELT are the two orders of work. Extract, Transform, Load cleans first and loads only the result. Extract, Load, Transform copies raw data into the target and transforms it there, usually in SQL. That swap decides what you keep, what you lose, and who pays for mistakes.

Below, one messy dataset of 50,000 events goes through both paths in SQLite. It is a single-machine simulation of the pattern, not a benchmark of any cloud product.

In a warehouse the table definition comes first. amount is an integer, country is a two-letter code, day is a date. A row that does not fit is rejected or repaired before it lands. That is schema-on-write.

The payoff is on the reading side. An analyst writing SUM(amount) never learns that one service sends amounts as dollar strings; that was solved once, upstream. Every dashboard then shares one definition of revenue.

The cost is rigidity. Someone has to decide the columns before the data arrives, and a field that was not in the design simply does not make it in. Changing the model later means changing the pipeline and often reloading history. The modelling effort pays off when many business users run similar, well-understood queries.

Cloud warehouses keep this model with different machinery. Snowflake's documentation describes separate storage and compute, independent compute clusters called virtual warehouses, and table data stored in a compressed columnar format split into micro-partitions. BigQuery's docs also describe columnar storage, with storage and compute scaling independently. Columnar layout suits analytics: a query touching three columns out of forty reads only those three.

A data lake flips the order. Files go into object storage as they arrive: JSON, CSV, logs, images, Parquet. When someone queries the data, their query decides which fields to pull out and how to cast them. That is schema-on-read.

This suits exploration: a field added last Tuesday can be read today, with no pipeline change. It also suits data with no tabular shape, such as images or free text.

The weakness hides in the phrase "each reader decides". If the parsing rule for amount lives in one notebook, the next person may write a different rule and get a different total. The experiment below shows it happening. Multiply it by hundreds of folders with unclear owners, no catalogue and no documentation, and you get what practitioners call a data swamp: lots of data, little of it trustworthy or findable.

Governance, not technology alone, keeps a lake usable: a catalogue saying what each dataset is and who owns it, access control on sensitive folders, and zones (raw and never edited, then cleaned, then curated). Without those, cheap storage just lets the swamp grow faster.

The generator below makes events with realistic mess. Amounts are sometimes integers, sometimes numeric strings, sometimes strings like "$42", sometimes null. Country codes come in mixed case or are missing. Every seventh event carries a coupon field that the original schema never planned for. The ETL side is not a strawman: rows it cannot load go to a dead-letter table with a reason, which is what a careful pipeline does.

Output with Python 3 and SQLite 3.45.1 (the seed is fixed, so the counts repeat):

ETL rejected 7,454 rows for a bad amount and 7,082 for a missing country. The two reasons overlap: a row with both problems is filed under the first check it fails, which is why the ELT side counts 8,292 unknown countries, more than the 7,082 in the dead-letter table. ELT kept every row, marked missing countries as UNKNOWN, stripped the dollar sign in SQL and left 3,458 amounts as NULL because those events really had no amount.

Both runs took well under a second. Timing a toy like this says nothing about warehouse engines, so the code does not print it.

Four follow-up checks, run in the same session as the code above:

Check 1 shows that "revenue on 1 October" depends on the rules. ETL counts 7,213 sales totalling 1,807,041. ELT counts all 10,000 events for the day and sums 2,325,072, because it kept rows with an unknown country and repaired the dollar amounts. Neither number is wrong; they answer slightly different questions, so pin down the definition before arguing about the pipeline.

Check 2 is the classic argument for ELT. A week later, marketing asks about the FALL10 coupon. The raw table still has it on 7,143 events worth 1,654,173, one query away. The warehouse table never had a coupon column. Fixing that in ETL means changing the transform and re-extracting history, if the sources still hold it.

Check 3 is the classic argument against careless schema-on-read. A second analyst reads the same raw JSON but forgets to strip $. SQLite does not raise an error: CAST('$42' AS INTEGER) quietly returns 0. Here 3,996 rows became zero and total revenue came out at 10,679,178 instead of 11,669,053, about 8.5% low, with no warning anywhere. The Python ETL check, by contrast, refused those rows and logged why.

Check 4 shows that ETL is not helpless. Because rejected rows were kept, adding a rule for dollar strings and replaying the dead-letter table recovered 3,345 rows, raising fact_sales to 38,809. The other 651 dollar-amount rows also lack a country, so they stay rejected. So a dead-letter table lets ETL change its mind about failed rows, but fields dropped from good rows, like coupon, are still gone.

ETL made sense when the warehouse was a fixed on-premises box with scarce compute and expensive storage: you cleaned and shrank the data elsewhere and loaded only what reports needed. Databricks' glossary page on ELT tells this history: ELT emerged once cloud platforms offered compute that scales on demand and cheap object storage, which made it practical to load everything and transform inside the target.

The main gain is the one the experiment showed. Raw data stays available, so history can be re-transformed when the business logic changes, without going back to the source systems. Transformations also move into SQL inside the platform, where they can be kept in version control, reviewed and tested like any other code.

ELT has real costs. Quality problems surface downstream instead of at the door, as the silent zero cast showed. Raw data often holds personal information, so masking and access control must happen inside the platform; dbt Labs' guide notes that landing sensitive raw data raises compliance risk. Storage and compute bills grow with everything you keep and re-process. In regulated domains such as finance or healthcare, validating or masking data before it lands is still sensible, and hybrids are common: a light ETL step hashes or drops sensitive fields, then ELT handles the rest.

Running a lake and a warehouse side by side means copying data between them, storing it twice and keeping two sets of permissions in step. The lakehouse idea, set out by Armbrust, Ghodsi, Xin and Zaharia in a CIDR 2021 paper, is to put warehouse features directly on top of lake storage. The paper argues that the two-tier setup causes stale data, reliability problems, extra cost, lock-in and weak support for machine learning. It proposes open file formats such as Parquet with a metadata layer above them. Its authors work at Databricks, which sells a lakehouse, and the benchmark result in the paper (a Parquet-based system competitive with cloud warehouses on TPC-DS) is their own measurement.

Open table formats build this in practice. The Delta Lake protocol brings ACID properties to files in an object store: writers add data files, then commit by appending an entry to a log in _delta_log, which defines the new table version. Delta's docs also list schema enforcement at ingestion and versioning for rollback. Apache Iceberg lists schema evolution without rewriting the table, hidden partitioning, time travel, rollback, and engines such as Spark, Trino and Flink sharing the same tables.

In terms of the experiment, the raw zone stays cheap and complete, as in ELT, while curated tables get enforced schemas and transactions, as in a warehouse. Warehouses have moved the other way too: BigQuery's docs describe external tables whose data stays in Cloud Storage with only metadata in BigQuery. The labels blur. The underlying choices, where the schema is enforced and where the transform runs, do not.

Q: Data warehouse vs data lake in one minute?
A: A warehouse holds cleaned, modelled tables with the schema enforced on write, for trusted BI. A lake holds raw files of any type in cheap storage with the schema applied on read, for exploration and ML. Many companies run both, or a lakehouse.

Q: ETL vs ELT, and why did ELT grow?
A: ETL transforms on a separate engine before loading; ELT loads raw data and transforms inside the target, usually in SQL. ELT grew with elastic cloud compute and cheap storage, because keeping raw data lets you re-transform history without re-extracting. ETL still fits data that must be validated or masked first.

Q: What does schema-on-read cost you?
A: Each reader writes its own parsing rules, so two queries over the same raw data can disagree with no error. In the test above, forgetting to strip a dollar sign cast 3,996 amounts to zero and understated revenue by about 8.5%. Shared, tested transformations reduce that risk.

Q: What is a data swamp and how do you prevent it?
A: A lake whose data has no owner, description or quality checks, so nobody trusts or finds it. Prevent it with a catalogue, ownership, access control, raw-then-curated zones and tests on curated tables.

Q: What does a lakehouse add over a plain lake?
A: A table format such as Delta Lake or Apache Iceberg that adds a transaction log and metadata over open files, giving ACID commits, schema enforcement or evolution, and time travel, so BI and ML can share one copy of the data.

Q: Twenty services emit JSON events; analysts want daily revenue and data scientists want raw events. Design it.
A: Land every event unchanged in an append-only raw zone partitioned by date, with PII restricted or hashed. Transform with versioned SQL into a tested fact_sales table, sending failed rows to a quarantine table with a reason. Analysts query curated tables, data scientists read raw, and a changed revenue definition is rebuilt from raw.