Index Ai Academy
Phase 11 · Data engineering

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.

About 45 minutes · A free account opens the whole lesson.
Already have an account? Sign in to open this lesson

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
Quick check
A guess before you start. A raw table of orders contains the same order twice, because the source system delivered it twice. Where in a warehouse is the best place to remove the repeat?
  1. Nowhere: the repeat is the source's problem
  2. In a staging model, once, so every model above it is clean
  3. In the raw table itself, by deleting the repeated row
  4. In every report, each time it is built
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.

Try it now

Pick a batch and step through the models. Each model reads only what is above it.

Quick check
From Retraining and pipelines: a model runs when everything it reads has run. In what order could these four run: fct_orders reads stg_orders and stg_customers, and the mart reads fct_orders?
  1. fct_orders, stg_orders, stg_customers, mart_daily_revenue
  2. mart_daily_revenue, fct_orders, stg_orders, stg_customers
  3. stg_orders, fct_orders, stg_customers, mart_daily_revenue
  4. stg_customers, stg_orders, fct_orders, mart_daily_revenue
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.

Quick check
What does this print: the order ids a load would add, from the incoming list?
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)
  1. [9]
  2. [8, 9]
  3. [7, 8, 9]
  4. [7, 8, 9, 9]
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.

Quick check
What does this print: the rows in a staging table, from 9 raw rows with 2 repeats, and the share that were repeats?
raw, repeats = 9, 2
print(raw - repeats, round(repeats / raw, 2))
  1. 9 0.22
  2. 7 0.22
  3. 2 0.22
  4. 7 0.78
Show the answer

7 0.22

9 − 2 = 7 rows stay, and 2 of 9 rows, 0.22, were repeats.

Quick check
Which model should compute "paid revenue by day and country"?
  1. A mart, reading the fact
  2. The raw table itself
  3. A test
  4. A staging model, reading raw
Show the answer

A mart, reading the fact

It is an answer a report reads, built on the joined fact: that is a mart.

In your own words
In your own words: why does staging join nothing, and why must an incremental load be safe to run twice?

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.

Quick check
What does a not-null test on stg_orders.order_id return when the rule holds?
  1. All the rows
  2. 0 rows
  3. An error
  4. 1 row
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

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)
In the lesson you can change this and run it.

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.

Drill

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)]))
In the lesson, your code is checked as you write it.
Drill

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]))
In the lesson, your code is checked as you write it.

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.

In your own words
A model's test fails after a new batch arrives. What do you look at first, and what do you not do?

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

In your own words
Why read the mart in a report and not the raw table?

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?

  1. Clean one raw table: types, names, repeats
  2. Join every table into one wide table for reports
  3. Compute the final report
  4. Delete the raw data

Answer it at the end of the lesson to see if you're right, and why.

Before this lesson

Keep learning

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
  1. System design fundamentals FreeRequirements, rough numbers, the standard parts and where systems break.40 min
  2. Caches, queues and background jobsSpeed reads with caches, and move slow work to queues that retry safely.40 min
  3. Designing AI systemsLatency, cost, routing, fallbacks and guardrails for features built on models.40 min
  4. Containers and deploymentPackage an app as an image, configure it with the environment, and roll out safely.45 min
  5. CI/CD pipelinesTest every change automatically, and ship with gates, flags and rollbacks.40 min
  6. Docker ComposeSeveral containers together: service names, volumes, secrets and start order.40 min
  7. Kubernetes basicsDesired state, Deployments and Services, labels, probes and rolling updates.40 min
  8. Cloud for AI engineersAccess policies and least privilege, storage, compute choices, managed model endpoints, secrets and budgets.30 min
  9. 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
  10. Reliability and incidentsSLOs, error budgets, alerts, on-call and blameless postmortems.40 min
  11. 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
  12. 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
  13. A/B testing a model changeHash assignment, per-user results from raw logs, sample ratio checks, guardrails and a decision rule.40 min
  14. 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
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. 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
  27. 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
  28. 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
  29. 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
  30. 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
  31. 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
  32. 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
  33. 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
  34. Git for teamsBranches, pull requests, rebasing and how a three-way merge finds conflicts.40 min
  35. Writing for engineersREADMEs, design docs, commit messages and bug reports people can act on.35 min
  36. 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
  37. 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
  38. Interviews and your portfolioShow your work, and solve interview problems the way professionals do.40 min
  39. Code reviewWhat to look for, how to give and take feedback, and automating the routine checks.35 min
  40. Reading unfamiliar codeFind your way around a big codebase fast, with tools that map it for you.35 min
  41. Working with AI coding toolsGive agents good context, keep tasks small, and check every change they make.35 min
  42. Capstone: ship a production AI serviceAn AI-backed service with validation, caching, rate limits, fallbacks, logging and evals.45 min
  43. 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