Warehouses, lakes & ELT

The data warehouse

Imagine you run a small online bookstore. Every time a customer signs up, adds a book to the cart, or pays, your app writes a row to a database. That database is built for one job: handle thousands of tiny, fast actions per second — read one customer, update one order, insert one payment. We call that kind of system OLTP（online transaction processing） — online transaction processing. It is the live, beating heart of the product.

Now your manager asks a different kind of question: “What was our revenue last quarter, broken down by book category and by region?” To answer that, you do not need one row — you need to sweep across millions of orders, group them, and add them up. If you run that heavy query against the live app database, you slow down checkout for real customers while a report churns away. The two jobs — serving the app and answering analytical questions — pull in opposite directions.

The fix is a separate database built for the second job: a data warehouse. A warehouse is optimized for OLAP（online analytical processing） — online analytical processing — where queries read huge slices of many rows and aggregate them. To do this fast, most cloud warehouses use columnar storage: instead of keeping each row together on disk, they store each column together. A “revenue by category” query then reads only the two or three columns it needs, and skips the rest. Snowflake, Google BigQuery, Amazon Redshift, and Databricks SQL are all warehouses in this sense.

Why does columnar layout matter so much for cost as well as speed? Many cloud warehouses charge — directly or indirectly — by how much data a query reads. So a useful rule of thumb is that a query’s cost is roughly proportional to the bytes it scans. Read fewer columns, scan fewer bytes; pay less and finish sooner.

\text{query cost} \;\propto\; \text{bytes scanned} \;\approx\; (\text{rows read}) \times (\text{bytes per selected column})

A mental model, not an exact bill: scanning fewer columns and fewer rows (via partitions and filters) is the main way to make warehouse queries cheaper and faster.

The data lake — and the lakehouse

A warehouse loves neat tables with known columns. But a lot of real data is not neat: raw clickstream logs as JSON, app event blobs, sensor readings, PDFs, images, audio. Worse, when the data first arrives you often do not yet know how you will use it — so committing to a fixed table shape up front feels premature. Forcing all of this into warehouse tables on day one is awkward and sometimes impossible.

A data lake solves this. A lake is cheap object storage — think Amazon S3 or Google Cloud Storage — that holds raw files in whatever shape they came in: CSV, JSON, Parquet, images, anything. The key difference from a warehouse is when you impose structure. A warehouse uses schema-on-write: you must define the columns and types before you can load data. A lake uses schema-on-read: you dump the raw files now, and only decide how to interpret them as columns later, at the moment you query. (Parquet, by the way, is a popular columnar file format that lakes use to stay fast.)

For years teams felt forced to choose: the warehouse’s reliable tables and fast SQL, or the lake’s cheap, flexible storage. The lakehouse is the attempt to get both at once. Built on open table formats such as Delta Lake and Apache Iceberg, a lakehouse keeps data as cheap files in object storage, but adds the things that made warehouses trustworthy: real tables, transactions, and SQL on top. One place serves both the messy raw data and the clean analytical tables.

A plain way to remember the three: a warehouse is a tidy, well-labeled pantry where everything is already in jars; a lake is a giant cheap garage where you toss the raw groceries; a lakehouse is a garage that learned to keep a labeled pantry shelf inside it. Most analytics still runs against warehouse-style tables — whether those tables live in a warehouse or a lakehouse.

ETL → ELT and why it flipped

Raw data almost never arrives in the shape your analysis needs. It must be moved and reshaped, and there is a classic three-step recipe for that, captured by the term ETL / ELT. The three steps are Extract (pull the data out of each source system — the app database, a payments provider, an ad platform), Transform (clean it, fix types, rename, join, and reshape into tidy analytical tables), and Load (put the result into the warehouse). The only question is the order of the last two letters.

In the old world, the recipe was ETL: extract, then transform on a separate processing server, and only load the finished, polished tables into the warehouse. Why transform first? Because old warehouses were expensive and not very powerful, so you did the heavy reshaping outside and stored only the slim final result. The downside: the moment a business question changed, you often had to rebuild the pipeline from scratch, because the raw detail was thrown away.

Cloud changed the economics, and the order flipped to ELT: extract, load the raw data into the warehouse first, and then transform it inside the warehouse using SQL. Modern cloud warehouses are massively parallel and storage is cheap, so it is now perfectly affordable to keep the raw data and do the reshaping right where it lives. The big payoff: because you never throw the raw data away, when the question changes you just write a new transformation over the same raw tables — no need to re-extract from the sources.

SQL: still the lingua franca

If there is one skill that ties this whole layer together, it is SQL — Structured Query Language, the language for asking questions of tables. SQL has been around since the 1970s and keeps outliving every prediction of its death, for a simple reason: it is declarative. You describe what result you want, and the warehouse’s engine figures out how to compute it efficiently. You say “group these orders by category and sum the revenue”; you do not say how to scan the disk or split the work across machines.

Here is the “revenue by category and region” question from the very first section, written as real SQL against a clean warehouse table. Read it like a sentence: select these columns, from this table, where the date is recent, grouped this way, sorted by revenue.

SELECT
  category,
  region,
  SUM(amount) AS revenue,
  COUNT(*)    AS orders
FROM warehouse.fct_orders
WHERE order_date >= '2026-01-01'
GROUP BY category, region
ORDER BY revenue DESC;

One readable query answers a real business question. GROUP BY is the SQL form of the split-apply-combine idea: split rows into groups, apply a summary, combine into one row per group.

That GROUP BY is the same group-by aggregation you may have met in a dataframe (pandas in Python, dplyr in R). It is the SQL spelling of the split-apply-combine pattern. The reason SQL is the lingua franca is that everyone speaks it: analysts, data engineers, and even most BI tools (the dashboard tools) generate SQL under the hood. Learn it once and you can talk to the warehouse directly, no matter which vendor’s logo is on it.

dbt and analytics engineering

Once ELT puts transformation inside the warehouse, that transformation becomes a sprawl of SQL: dozens of scripts that build tables from other tables. Run by hand, in the wrong order, with logic copy-pasted between files, this quickly turns into a fragile mess. The same definition of “active customer” ends up written five slightly different ways, and no one is sure which dashboard is right.

dbt (the “data build tool”) tames this by bringing software-engineering habits to SQL. You write each transformation as a model — just a SELECT statement saved in a file — and you reference other models with a ref function instead of hard-coding table names. From those references dbt works out the dependency order automatically and builds everything in the right sequence. It also runs data tests (for example, “this id column must be unique and never null”), generates documentation, and lives in version control alongside your code.

-- models/marts/fct_orders.sql
SELECT
  o.order_id,
  o.customer_id,
  c.region,
  o.amount,
  o.order_date
FROM {{ ref('stg_orders') }}     AS o
LEFT JOIN {{ ref('stg_customers') }} AS c
  ON o.customer_id = c.customer_id

A dbt model is just a SELECT. The ref() calls tell dbt that this clean table depends on two staging models, so dbt builds those first — and warns you if a dependency would loop.

Teams usually layer their models: staging models lightly clean each raw source (rename columns, fix types), and marts join and aggregate those into the business-facing tables analysts actually query. This is just reproducible, scripted cleaning applied to the whole warehouse, and it produces the kind of tidy, trustworthy tables the rest of the company relies on.

Orchestration: scheduling the pipeline

We now have all the pieces of a data pipeline — extract from sources, load to the warehouse, transform with dbt, refresh dashboards. But these steps have dependencies and a clock: extraction must finish before transformation can start, transformation before the dashboard refresh, and the whole thing should run, say, every morning at 2am so fresh numbers are waiting when the team logs in. Something has to run the steps in the right order, on schedule, and cope when a step fails.

That something is an orchestrator — orchestration tools such as Apache Airflow, Dagster, and Prefect. You describe your pipeline as a DAG — a directed acyclic graph, which is just a picture of steps connected by arrows with no loops, so the order is always well-defined. The orchestrator then runs the DAG on a schedule, only starting each step once the steps it depends on have succeeded, retrying transient failures, and alerting a human when something is truly broken.

A good orchestrated step is idempotent: running it twice produces the same result as running it once, so a retry after a half-finished failure does not double-count or corrupt the data. Idempotency, retries, and alerting are what turn a fragile pile of scripts into a pipeline you can actually sleep through the night trusting.

Choosing tools without the hype

The modern data stack is a carnival of logos, and the fashionable answer changes every year. It is easy to feel that you must adopt a lakehouse, streaming, a feature store, and three orchestrators before you have answered a single question. You do not. The honest truth is that most companies are not Google, and a managed warehouse plus dbt plus one BI tool will answer the great majority of business questions a normal company has.

So judge tools by principles, not press releases. Here is a sober checklist for picking what you actually need.

Start from the decision, not the tool. Write down the question you need to answer and who will act on it; let that dictate the stack, not the other way around.
Pick the smallest stack that works. A cloud warehouse and SQL handle most needs. Add a lake only when you genuinely have large unstructured data you cannot fit into tables yet.
Prefer boring, well-supported tools. A widely used tool with good docs and a big community will cost you less pain than a clever niche one, even if the niche one demos better.
Demand reproducibility and version control from day one. If you cannot rerun your pipeline from scratch and get the same result, you cannot trust it — regardless of the brand on it.
Plan one step beyond today, not ten. Buy room to grow into next year, not a platform built for a scale you may never reach.

You now have the map of where data lives and how it gets reshaped: warehouses for tidy analytical tables, lakes (and lakehouses) for cheap flexible storage, ELT to transform inside the warehouse, SQL as the shared language, dbt to keep transformations honest, and orchestration to run it all reliably. The next guide closes the loop — from a one-off analysis to dashboards people trust and machine learning running in production.