Transformations: staging, models and tests
Raw, staging, facts and marts as SQL models run in dependency order in DuckDB, tests as queries that count the rows that break a rule, an incremental load that is safe to rerun, and the same models as dbt files.
What it covers6
Why it matters
A warehouse full of raw tables is a pile, not a source of truth. The numbers a team trusts come from a chain of small SQL models, each one reading the layer below it: raw tables are cleaned in staging, joined into facts, and summed into marts. Each model is a file in version control, each has tests, and a rerun does not change the answer. This lesson builds that chain in DuckDB and then shows the same files as dbt reads them.
What you'll be able to do
- Layer a warehouse as raw, staging, fact and mart, and say what each layer is allowed to do
- Run SQL models in dependency order and write a test as a query that counts the rows that break a rule
- Load new rows incrementally so that a rerun adds nothing twice
- Read the same models as dbt files: ref(), schema.yml tests, dbt run and dbt test
Show the answer
In a staging model, once, so every model above it is clean
Fixing it once in staging means every fact and mart built on it is right. Raw stays as received, so you can always see what arrived.
The idea: raw, staging, facts and marts
Data quality checks and contracts taught how to check a delivery. This lesson is about what you build on it, in four layers.
- Raw is what arrived, untouched: the tables exactly as the source wrote them, repeats and odd spellings included.
- Staging cleans one raw table at a time and nothing more: it casts types, renames columns, converts cents to dollars, lowers case and removes repeats. It joins nothing.
- Facts (and dimensions) join the cleaned tables into the things the business counts: an order with its customer's country.
- Marts are the answers a report reads: paid revenue by day and country.
Each model is one select, saved as a table or a view, and each names the models it reads. Those names form a graph with no loops, the same DAG you met in Trees and graphs and will meet again in Retraining and pipelines: a model runs once everything it reads has run. The picture builds the layers for a small shop and counts the rows at each one.
Pick a batch and step through the models. Each model reads only what is above it.
Show the answer
stg_customers, stg_orders, fct_orders, mart_daily_revenue
Both staging models come first in either order, then the fact that reads them, then the mart that reads the fact.
Full refresh and incremental
A full refresh rebuilds a table from scratch on every run. It is simple and always right, and it gets slow as the table grows. An incremental load adds only the rows that are new since the last run. It is fast, and it has a rule: it must be safe to run twice. Key the new rows on something that identifies them, such as order_id, and a rerun finds nothing new and adds nothing. A load that appends a whole batch each time would double count on a rerun.
The cost of an incremental load is that it can miss changes to rows it already holds. Teams run a full refresh on a schedule, say weekly, to correct any drift, and use incrementals in between.
existing = {1, 2, 3, 4, 5, 6, 7}
incoming = [7, 8, 9, 9]
new = []
for order_id in incoming:
if order_id not in existing and order_id not in new:
new.append(order_id)
print(new)Show the answer
[8, 9]
7 is already in the fact. 8 and 9 are new, and the second 9 is skipped because it was just added.
raw, repeats = 9, 2 print(raw - repeats, round(repeats / raw, 2))
Show the answer
7 0.22
9 − 2 = 7 rows stay, and 2 of 9 rows, 0.22, were repeats.
Show the answer
A mart, reading the fact
It is an answer a report reads, built on the joined fact: that is a mart.
Write a sentence or two, then compare it with a good answer in the lesson.
In code: three models, a test and an incremental load
The setup builds the raw tables in DuckDB and defines the four models as SQL strings, with the names of the models each one reads. The reset() helper starts every cell from the same raw tables, so you can run the cells in any order.
First, run the models in dependency order, using the ready-step loop from the setup, and count the rows in each.
Setup (runs before every cell) · 76 lines
The lesson runs this once, before its first cell, and every cell, drill and example starts with what it defines. A cell that changes a setup object changes it for the cells after it, until the page reloads.
import duckdb BATCH_1 = [ (1, 10, 2500, "PAID", "2026-03-01"), (2, 11, 1800, "paid", "2026-03-01"), (2, 11, 1800, "paid", "2026-03-01"), (3, 10, 4000, "Paid", "2026-03-02"), (4, 12, 999, "refunded", "2026-03-02"), (5, 13, 5000, "PAID", "2026-03-02"), (6, 11, 2200, "paid", "2026-03-03"), (6, 11, 2200, "paid", "2026-03-03"), (7, 12, 3100, "PAID", "2026-03-03"), ] BATCH_2 = [ (7, 12, 3100, "PAID", "2026-03-03"), (8, 10, 1500, "paid", "2026-03-04"), (9, 11, 2700, "paid", "2026-03-04"), ] CUSTOMERS = [(10, "Ana@Example.com", "us"), (11, "ben@example.com", "de"), (12, "Cy@Example.com", "us")] MODELS = { "stg_orders": """ create or replace table stg_orders as select order_id, customer_id, amount_cents / 100.0 as amount, lower(status) as status, cast(created_at as date) as order_date from (select *, row_number() over ( partition by order_id order by created_at desc) as rn from raw_orders) where rn = 1""", "stg_customers": """ create or replace table stg_customers as select customer_id, lower(email) as email, upper(country) as country from raw_customers""", "fct_orders": """ create or replace table fct_orders as select o.order_id, o.customer_id, coalesce(c.country, 'unknown') as country, o.amount, o.status, o.order_date from stg_orders o left join stg_customers c using (customer_id)""", "mart_daily_revenue": """ create or replace table mart_daily_revenue as select order_date, country, round(sum(amount), 2) as revenue, count(*) as orders from fct_orders where status = 'paid' group by 1, 2""", } NEEDS = { "stg_orders": [], "stg_customers": [], "fct_orders": ["stg_orders", "stg_customers"], "mart_daily_revenue": ["fct_orders"], } con = duckdb.connect() def rows(sql): return con.execute(sql).fetchall() def insert(table, data): values = ", ".join(str(tuple(r)) for r in data) con.execute(f"insert into {table} values {values}") def reset(): for table in ["raw_orders", "raw_customers", *MODELS]: con.execute(f"drop table if exists {table}") con.execute("""create table raw_orders (order_id int, customer_id int, amount_cents int, status varchar, created_at varchar)""") con.execute("""create table raw_customers (customer_id int, email varchar, country varchar)""") insert("raw_orders", BATCH_1) insert("raw_customers", CUSTOMERS) def run_order(needs): done, order = set(), [] while len(order) < len(needs): ready = sorted(m for m in needs if m not in done and all(d in done for d in needs[m])) order.append(ready[0]) done.add(ready[0]) return order
reset() order = run_order(NEEDS) print(order) for name in order: con.execute(MODELS[name]) print(name.ljust(20), rows(f"select count(*) from {name}")[0][0], "rows")
The order starts with the two staging models, then the fact, then the mart. Staging turns 9 raw order rows into 7, because two orders were delivered twice. The fact keeps all 7, and the mart has 6 rows, one for each day and country with a paid order.
A test is a query
A test in a warehouse is a query that returns the rows that break a rule. The rule holds when it returns none. The three most useful are not null (a key is never empty), unique (a key appears once) and relationships (every customer in an order exists in the customer table). The last one is a real join, not a range check.
Uses the setup above, 76 lines.
reset() for name in run_order(NEEDS): con.execute(MODELS[name]) tests = { "raw_orders: unique order_id": ("select count(*) - count(distinct order_id) from " "raw_orders"), "stg_orders: unique order_id": ("select count(*) - count(distinct order_id) from " "stg_orders"), "stg_orders: order_id not null": ("select count(*) from stg_orders where " "order_id is null"), "stg_orders: customer exists": """select count(*) from stg_orders where customer_id not in (select customer_id from stg_customers)""", } for name, sql in tests.items(): print(name.ljust(32), rows(sql)[0][0], "rows break it")
The raw table fails the unique test with 2 rows, which is the duplicates, and the staging table passes with 0: that is what staging is for. The relationships test finds 1 order, the one for customer 13, who is in no customer row. The fact kept it with country unknown, and the test makes sure someone notices.
Show the answer
0 rows
A test returns the rows that break the rule, so a holding rule returns none.
Now the incremental load. The second batch is added to the raw table, staging is rebuilt, and the fact adds only the orders it does not already hold.
Uses the setup above, 76 lines.
reset() for name in run_order(NEEDS): con.execute(MODELS[name]) insert("raw_orders", BATCH_2) con.execute(MODELS["stg_orders"]) add = """insert into fct_orders select o.order_id, o.customer_id, coalesce(c.country, 'unknown'), o.amount, o.status, o.order_date from stg_orders o left join stg_customers c using (customer_id) where o.order_id not in (select order_id from fct_orders)""" for run in (1, 2): con.execute(add) print( "run", run, "| rows in fct_orders:", rows("select count(*) from fct_orders")[0][0], ) con.execute(MODELS["mart_daily_revenue"]) print("mart rows:", rows("select count(*) from mart_daily_revenue")[0][0])
Run 1 adds orders 8 and 9, so the fact goes from 7 rows to 9. Run 2 finds nothing new and leaves it at 9. The mart is rebuilt from the fact and has 8 rows. Keying the load on order_id is what makes the rerun harmless, and it also catches a late order with an old date, which a filter on dates would miss.
Worked example: the day's revenue, and a check on it
Build the models, then read the paid revenue by day and compare the grand total with the raw table, as a reconciliation check.
Uses the setup above, 76 lines.
reset() for name in run_order(NEEDS): con.execute(MODELS[name]) by_day = rows( "select order_date, sum(revenue) from mart_daily_revenue group by 1 order by 1" ) for day, total in by_day: print(day, total) mart_total = sum(total for day, total in by_day) raw_paid = rows("""select sum(amount_cents) / 100.0 from ( select distinct order_id, amount_cents, lower(status) as s from raw_orders) where s = 'paid'""")[0][0] print("mart total:", mart_total, "| raw paid, deduplicated:", raw_paid)
Paid revenue is 43 on March 1, 90 on March 2 and 53 on March 3, 186 in all. The raw table, deduplicated and filtered to paid orders, gives the same 186, so the models lose nothing and invent nothing. A reconciliation like this is the cheapest test of a whole chain: the number at the end must match the number at the start.
The rows an incremental load adds
Write new_rows(existing_ids, incoming). existing_ids is a set of order ids already loaded, and incoming is a list of (order_id, amount) pairs, possibly with repeats. Return the pairs to add: those whose id is not in existing_ids, keeping only the first pair for each id, in order.
Uses the setup above, 76 lines.
def new_rows(existing_ids, incoming): return [] print(new_rows({1, 2}, [(2, 5.0), (3, 7.5), (3, 7.5), (4, 1.0)]))
Count the rows that break each rule
Write test_counts(orders, customers). orders is a list of dictionaries with order_id and customer_id, and customers is a list of customer ids. Return a dictionary with "not_null" (orders whose order_id is None), "duplicates" (the number of order rows beyond the first for each non-null order_id) and "orphans" (orders whose customer_id is not in customers).
Uses the setup above, 76 lines.
def test_counts(orders, customers): return {"not_null": 0, "duplicates": 0, "orphans": 0} sample = [{"order_id": 1, "customer_id": 10}, {"order_id": 1, "customer_id": 10}, {"order_id": None, "customer_id": 99}] print(test_counts(sample, [10]))
The daily revenue from the mart, drawn:
Uses the setup above, 76 lines.
import matplotlib.pyplot as plt reset() for name in run_order(NEEDS): con.execute(MODELS[name]) rev = rows( "select order_date, sum(revenue) from mart_daily_revenue group by 1 order by 1" ) days = [str(d) for d, total in rev] plt.bar(days, [total for d, total in rev]) plt.xlabel("day") plt.ylabel("paid revenue ($)") plt.show() print([(d, total) for d, total in zip(days, [t for _, t in rev])])
The orphan order sits in the March 2 bar under country unknown: it is counted, and the test above says it needs a look.
Write a sentence or two, then compare it with a good answer in the lesson.
On the job: the same models in dbt
In a real warehouse the four SQL strings are four files in a repository, and a tool called dbt runs them. dbt is the layer in this lesson turned into a program: it reads each file, finds the other models it names with ref(), runs them in dependency order, and runs the tests. You write only SQL and a little YAML, and the DAG, the order and the table creation are dbt's.
Where it comes up in a week
A dashboard shows revenue 2% above finance's number. You trace it: the mart reads the fact, the fact reads staging, staging reads raw, and a test on raw order ids finds repeats that a new source started sending. You fix it once, in the staging model, add the unique test that would have caught it, and the dashboard, the finance extract and the model features built on the fact are all right at the next run.
Goes deeper in
- Data quality checks and contracts: the checks at the edge of the warehouse.
- Columnar files and a local warehouse: the tables these models write.
- Retraining and pipelines: the same dependency-ordered pipeline for a model.
- Feature pipelines and training-serving skew: the features built on a fact.
Write a sentence or two, then compare it with a good answer in the lesson.
Practice what you just read
- 5 code examples you can edit and run
- a practice exercise that checks your code
- a 5-question quiz
- 9 review cards for spaced repetition
Try a quiz question
What should a staging model do?
Before this lesson
Where this lesson leads.
575 lessons in thirteen phases. 176 are free without an account, and a free account opens the rest. See every lesson.
Phase 11 · about 34 hours of study
Professional practice
Design, ship and run real systems, work well in a team, and get hired
43 lessons 1 free
- System design fundamentals FreeRequirements, rough numbers, the standard parts and where systems break.40 min
- Caches, queues and background jobsSpeed reads with caches, and move slow work to queues that retry safely.40 min
- Designing AI systemsLatency, cost, routing, fallbacks and guardrails for features built on models.40 min
- Containers and deploymentPackage an app as an image, configure it with the environment, and roll out safely.45 min
- CI/CD pipelinesTest every change automatically, and ship with gates, flags and rollbacks.40 min
- Docker ComposeSeveral containers together: service names, volumes, secrets and start order.40 min
- Kubernetes basicsDesired state, Deployments and Services, labels, probes and rolling updates.40 min
- Cloud for AI engineersAccess policies and least privilege, storage, compute choices, managed model endpoints, secrets and budgets.30 min
- Metrics, dashboards and alert rulesCounters, gauges and histograms, a rate from a counter, a percentile from buckets, a dashboard panel and an alert rule that fires on a sustained condition.40 min
- Reliability and incidentsSLOs, error budgets, alerts, on-call and blameless postmortems.40 min
- Incident lab: from the page to the postmortemOne incident run from the page to the all-clear: decide the mitigation, work out the impact in failed requests and error budget, and write a blameless postmortem, a status update and a runbook entry.40 min
- Measuring an AI feature from its logsTyped views over event logs, metric specs, cost from a price table, and rollups you can rerun.40 min
- A/B testing a model changeHash assignment, per-user results from raw logs, sample ratio checks, guardrails and a decision rule.40 min
- Columnar files and a local warehouseRows against columns, the encodings that shrink a column, Parquet's row groups and the ranges that let a query skip them, and SQL over files with DuckDB.40 min
- Data quality checks and contractsSix properties of a delivery you can test, each as a count of rows that break it, a contract with the producer, and when to block, quarantine or warn.40 min
- Transformations: staging, models and testsRaw, staging, facts and marts as SQL models run in dependency order in DuckDB, tests as queries that count the rows that break a rule, an incremental load that is safe to rerun, and the same models as dbt files.45 min
- Streams and windowsEvents that never stop arriving: event time against arrival time, tumbling, sliding and session windows, a watermark for late events, and counting each event once.40 min
- Labels and annotator agreementLabels as measurements: agreement and Cohen's kappa, the pair of classes annotators confuse, votes and gold questions, and what wrong labels do to a model and a test score.45 min
- Experiment tracking and reproducible runsA run record of settings, seed, data and code behind every score, a run log in a dozen lines, and comparing settings across seeds.40 min
- Hyperparameter searchWhat a grid costs, random search on a log scale, stopping weak settings early, and why the winner's validation score flatters it.40 min
- Versioning data and modelsFiles stored under the hash of their contents, a manifest that names a whole dataset, and lineage from data and code to each model.35 min
- A model registry and promotionVersions under one name, an alias for the live one, gates a candidate must pass, group by group, and a rollback that moves a pointer.40 min
- Feature pipelines and training-serving skewWhy a model that passes offline fails live: statistics refit at serving, two code paths, and features from the future, with the fix for each.40 min
- Batch and online inferencePredict ahead of time or on request: when the inputs exist, latency near a server's limit, batching what waits, cost, and a hybrid with a freshness limit.40 min
- Monitoring a model: drift and decayWhy a model worsens with no code change: data drift and concept drift, a measure of how far inputs have moved, thresholds set from noise, and labels that arrive late.45 min
- Retraining and pipelinesWhen to retrain and on which weeks, training as steps in dependency order, a cache that reruns only what changed, and guards before and after.45 min
- Designing ML systemsOne method for an ML design, goal and metric to retraining, with the sums that size it: requests a second at peak, a year of feature storage, a label delay and a latency budget.45 min
- Datasheets: documenting a datasetThe document that travels with a dataset: a profile by column and by group, claims a program can check, splits that don't leak, a scan for personal data, and the license and limits only people can write.45 min
- Curation, active learning and synthetic dataThree levers on a training set: removing copies and broken rows, letting a model choose which rows to label, and manufacturing rows, which helps only when their source knows more than you do.45 min
- Measuring fairnessBreak a model's results down by group: selection rate, true and false positive rates and precision, why they can't all be equal, what each fix costs, and when a group is too small to judge.40 min
- Privacy engineeringWhy removing names isn't anonymity, how to hold less (minimization, retention, keyed pseudonyms), the idea behind differential privacy and its budget, what a model remembers, and a deletion that reaches every store.40 min
- A red-team labAttack a simulated assistant methodically: eight kinds of attack run as a suite, success rates beside ordinary requests blocked, fresh attacks to test the fixes, where a defense belongs, and findings kept as release tests.45 min
- What regulation asks of an engineerRisk tiers that follow a system's use, the requirements for high-risk systems as artifacts an engineer builds, evidence tied to a model version, a model card a program can check, and legal dates kept as data.35 min
- Git for teamsBranches, pull requests, rebasing and how a three-way merge finds conflicts.40 min
- Writing for engineersREADMEs, design docs, commit messages and bug reports people can act on.35 min
- Scoping and estimating workA vague request cut into tickets with acceptance criteria and a first shippable slice, estimates as ranges, an honest update for a slip, and a ticket linked to its pull request.30 min
- Roles, résumé and the searchWhat AI, ML and data engineering postings ask for, résumé bullets that carry a number, a 100-word project summary, a take-home as a portfolio piece and how to read an offer.35 min
- Interviews and your portfolioShow your work, and solve interview problems the way professionals do.40 min
- Code reviewWhat to look for, how to give and take feedback, and automating the routine checks.35 min
- Reading unfamiliar codeFind your way around a big codebase fast, with tools that map it for you.35 min
- Working with AI coding toolsGive agents good context, keep tasks small, and check every change they make.35 min
- Capstone: ship a production AI serviceAn AI-backed service with validation, caching, rate limits, fallbacks, logging and evals.45 min
- Capstone: take a model to productionA churn model through a data contract, a tracked run, a registry gate by slice, hybrid serving, a PSI monitor and one retrain, shipped with a datasheet, a model card, a fairness audit, a design doc and a runbook.45 min
Never trained an AI? Teach a small one to write, right in your browser. It takes about 20 seconds and needs no account. Teach it to write