ELT & staging layers
Load raw first, transform inside the warehouse, and organize the flow as raw → staging → marts so every step is inspectable and re-runnable.
Old-school ETL transformed data in flight — extract from source, clean it in a middleware tool, load only the polished result. Modern practice inverted the middle letters: ELT. Land the raw data in the warehouse unchanged, then transform with SQL inside it. The inversion sounds cosmetic. It isn't.
- Raw is evidence. When a number looks wrong, you can query what the source actually sent — not what your cleaning code turned it into. Debugging ETL means re-running opaque middleware; debugging ELT means
SELECT * FROM raw WHERE .... - Transforms become versionable SQL. Cleaning logic lives in files, in git, reviewed like code — because it is code. Change the logic, re-run, and the whole downstream rebuilds from untouched raw.
- You keep optionality. The column you dropped as useless in January is the feature the ML team needs in June. With ELT it's still sitting in raw.
The three layers
Every serious warehouse converges on roughly three layers (Databricks markets this as bronze/silver/gold; dbt people say staging/intermediate/marts; the idea is identical):
- Raw — source data as it arrived. Append-only, never edited, boring on purpose. Harbor Lane: the CSV files themselves (in cloud reality: an object-store bucket or raw tables loaded by an ingest tool).
- Staging — one view per source, doing only mechanical cleanup: types cast, names standardized, obvious garbage filtered,
lower()andtrim()applied. One source in, one staging view out, no joins, no business logic.stg_orders,stg_web_customers. - Marts — the star schema from Module 1, built from staging:
fct_order_lines,dim_product,dim_customer. Business logic lives here and only here. This is the layer analysts, dashboards, and AI agents are allowed to touch.
The discipline this buys: when something is wrong, the layer tells you where. Wrong in raw → source problem, call the vendor. Right in raw, wrong in staging → your cleanup broke. Right in staging, wrong in the mart → business logic bug. Without layers, every incident starts with two hours of 'where did this even come from?'
If you're writing a JOIN or an IF-the-business-says in a staging view, stop — that's mart logic trying to sneak upstream. Staging views should be so mechanical they're boring to review. Boring staging is a feature: it means all the decisions worth arguing about live in one reviewable layer.