ELT: load raw data first, then transform it with SQL

For a long time the usual pattern was ETL: extract data from source systems, transform it on a separate server, and load only the finished result. If the transformation had a bug, the raw data was gone and had to be extracted again.

Cloud warehouses such as BigQuery, Snowflake, Redshift and Databricks made storage cheap and computing power easy to scale, so most teams now do ELT. A loading tool (Fivetran, Airbyte or a small script) copies source tables into the warehouse as they are, and all reshaping happens afterwards, inside the warehouse, in SQL. The raw copy is always there, so you can rebuild everything when a rule changes; the logic is plain SQL that analysts already know; and the warehouse does the heavy work.

At Mangrove, a loader copies the POS exports, the app's orders and the back office's store list into raw schemas every hour. The rule is that nobody edits a raw table, ever. Every change to the data happens in a model: a file with one SELECT statement. A tool such as dbt (data build tool, open source and also sold as a hosted service) wraps each SELECT in a CREATE VIEW or CREATE TABLE AS and runs it. Other tools, such as Dataform and SQLMesh, work on similar ideas, so what you learn here carries over.

Models never type table names. They call two functions written in Jinja, the template language dbt uses, inside double curly braces:

-- models/staging/pos/stg_pos__sale.sql
select
    upper(trim(till_id))        as till_id,
    cast(receipt_no as integer) as receipt_no,
    cast(store_id as integer)   as store_id,
    cast(sold_at as timestamp)  as sold_at,
    cast(round(cast(total as numeric) * 100) as integer) as total_cents,
    _loaded_at                              -- when the loader copied this row
from {{ source('pos', 'sale') }}            -- a raw table, declared in a .yml file

-- models/marts/dim_store.sql
select id as store_id, name as store_name, city
from {{ ref('stg_backoffice__site') }}      -- another model
where kind = 'store'

source('pos', 'sale') points at a raw table you declared once in a YAML file. ref('stg_backoffice__site') points at another model. Before running anything, dbt compiles each ref() into a full table name: your own development schema while you work (for example dbt_tomas.stg_backoffice__site), the production schema when the scheduled job runs. Because every dependency goes through these two functions, dbt can read all the files and draw the DAG of the project. dbt run then builds models in dependency order, and dbt run --select stg_pos__sale+ builds one model plus everything downstream of it.

Three layers: staging, intermediate, marts

A project with 200 models needs a shape, or nobody can find anything. Most teams follow the layering in dbt Labs' guide to project structure. Each layer has one job, and each layer reads only from the one below it.

• Staging (stg_<source>__<entity>): one model per raw table, one row in for one row out. Rename columns to the house style, cast types, convert units (dollars as text into integer cents), standardise codes (trim spaces, one case), and remove exact re-sent duplicates. No joins and no business rules. Staging models are usually views. Nothing else in the project reads raw tables.
• Intermediate (int_...): the joins and reshaping that more than one mart needs. Attach sale lines to their sale header. Union till sale lines and delivered app order lines into one shape. Leave cancelled orders out. Users never query this layer.
• Marts (fct_..., dim_...): the tables dashboards and analysts read. Here that is the star schema from bi-01: fct_sale_line at the grain "one row per sale line", with dim_store, dim_article and dim_date around it. Marts are tables or incremental tables, because people read them all day.

The layers put each rule in exactly one place. If the POS starts sending prices with a comma as the decimal mark, you fix one staging model and every mart is right on the next run. If "cancelled app orders are not sales" lives in one intermediate model, finance's report and the trading dashboard cannot disagree about it.

Materialisations: view, table or incremental

A model's materialisation says what dbt creates from your SELECT. You set it per model or per folder, and the SELECT itself does not change.

• View (the default): nothing is stored; the query runs every time someone reads it. Cheap to build, always current, slow when heavy and read often. Good for staging.
• Table: rebuilt from scratch on every run. Fast to read, but the build cost grows with the data, including old rows that never change.
• Incremental: the first run builds the whole table; later runs insert or update only new or changed rows. Cheap for big facts, with new ways to go wrong.
• Ephemeral: not created at all; dbt pastes the SQL into each model that refers to it, as a common table expression.

The fact table is the big one. Mangrove's stores make about 400,000 checkout transactions a day, at roughly 4 lines each, so about 1.6 million sale lines a day and roughly 1.2 billion over two years. Rebuilding all of that every night to add one day is wasteful. Here is the incremental version:

-- models/marts/fct_sale_line.sql
{{ config(
    materialized = 'incremental',
    unique_key   = 'sale_line_id'    -- reprocessed rows replace their old copy
) }}

select
    sale_line_id, store_id, article_id,
    sold_on, channel, qty, gross_cents, _loaded_at
from {{ ref('int_sale_lines_unioned') }}

{% if is_incremental() %}
  -- later runs: rows the loader copied since the last run,
  -- plus a 3-day lookback for tills that upload late
  where _loaded_at > (select max(_loaded_at) from {{ this }}) - interval '3 days'
{% endif %}

is_incremental() is true when the table already exists and you did not ask for a full rebuild. {{ this }} is the table being built, so the subquery finds the newest row already in it. With unique_key set, a row that comes through again replaces its old copy instead of becoming a duplicate (whether dbt does this with a MERGE or a delete-then-insert depends on the warehouse). dbt run --full-refresh rebuilds the table from nothing, which you do after changing the model's logic.

Two details decide whether an incremental model stays correct. First, filter on when the row was loaded, never on when the sale happened. Tills keep selling when the internet drops and upload the sales hours or days later. If the filter were sold_at > max(sold_at), a store that uploads Saturday's sales on Monday, after Monday's rows are in, would never get its Saturday into the table. Second, add a lookback window sized to how late data really arrives. Three days of reprocessing is about 6.4 million rows a night, around half of one percent of the table.

Tests are queries that return the rows that break a rule

A data test in dbt is a SELECT that looks for bad rows. If it returns zero rows, the test passes. If it returns rows, they are the evidence, and you can run the same query to see them. You declare the four built-in generic tests next to the model, in a YAML file (in dbt 1.8 and later the key is data_tests:; older versions use tests:):

# models/marts/_marts.yml
version: 2
models:
  - name: fct_sale_line
    description: One row per sale line, from tills and delivered app orders.
    columns:
      - name: sale_line_id
        description: Channel prefix plus the source's own line key.
        data_tests:
          - unique
          - not_null
      - name: store_id
        data_tests:
          - not_null
          - relationships:          # every store_id must exist in dim_store
              to: ref('dim_store')
              field: store_id
      - name: channel
        data_tests:
          - accepted_values:
              values: ['store', 'app']

The arguments' exact layout has changed a little between versions, so check the docs for yours. What each test runs is roughly this:

-- unique: keys that appear more than once
select sale_line_id, count(*) as n
from analytics.fct_sale_line
where sale_line_id is not null
group by sale_line_id
having count(*) > 1;

-- relationships: child rows whose key has no parent (nulls are left to not_null)
select f.store_id
from analytics.fct_sale_line f
left join analytics.dim_store d on d.store_id = f.store_id
where f.store_id is not null
  and d.store_id is null;

not_null returns rows where the column is null, and accepted_values returns values outside the list. Notice that unique, relationships and accepted_values ignore nulls: catching nulls is not_null's job, so key columns get both. If your dim_store uses surrogate keys with history (type 2, from bi-01), the fact carries store_key and the test is the same.

A rule that no generic test covers becomes a singular test: a .sql file in the tests/ folder that returns the failing rows. For example, the lines of each till sale must add up to the receipt total:

-- tests/sale_lines_match_receipt_total.sql
select s.till_id, s.receipt_no, s.total_cents,
       sum(l.qty * l.price_cents) as lines_cents
from {{ ref('stg_pos__sale') }} s
join {{ ref('stg_pos__sale_line') }} l
  on l.till_id = s.till_id and l.receipt_no = s.receipt_no
group by s.till_id, s.receipt_no, s.total_cents
having s.total_cents <> sum(l.qty * l.price_cents)

dbt build runs models and tests together in graph order: build a model, run its tests, then move on. When a test with the default severity (error) fails, every model downstream is skipped, so a broken staging model cannot flow into the fact table, and the dashboard keeps showing the last good build. A test set to severity: warn reports the problem and lets the run continue: use that for rules that are worth watching but should not stop the morning's numbers.

Test what would make a number wrong: the primary key of every model (unique plus not_null proves the grain), foreign keys in marts, code columns such as channel and status, and one or two business rules per mart. Fifty tests that fail every week teach people to ignore all of them.

Tests check the rows that arrived. A source freshness check asks whether rows arrived at all. You give each source the column that records load time and two limits, and dbt source freshness compares the newest value with the clock:

# models/staging/pos/_pos__sources.yml
version: 2
sources:
  - name: pos
    schema: raw_pos
    loaded_at_field: _loaded_at
    freshness:
      warn_after: {count: 6, period: hour}
      error_after: {count: 24, period: hour}
    tables:
      - name: sale
      - name: sale_line

Where the freshness settings sit in the file also differs slightly between recent versions. How to schedule these checks, alert the right person and show "data as of" on the dashboard comes later in this course.

Documentation, lineage, version control and CI

The description fields in the YAML files are the documentation. They sit next to the tests, so they get updated when the model does. dbt docs generate builds a small website from them: every model, its columns and descriptions, its tests, the SQL, and a lineage graph. Lineage answers two questions you will hear every week: "where does this number come from?" (follow the graph up to the raw tables) and "if I change this model, what breaks?" (follow it down to the marts and dashboards).

All models live in a Git repository. Each person develops in their own schema, makes changes on a branch, and opens a pull request that someone else reviews before it reaches production. The review checks logic and the grain. A machine checks the rest: CI. At Mangrove, a GitHub Actions job runs on every pull request. It builds the changed models and everything downstream of them into a temporary schema and runs their tests. dbt can pick that set for you by comparing the project with the production run's saved state:

# CI: build and test only what changed, plus everything downstream of it;
# unchanged parents are read from production instead of rebuilt
dbt build --select state:modified+ --defer --state ./prod-artifacts

The pull request is merged only when that build is green. A good reviewer also compares a few totals between production and the pull request's copy, such as last week's sales by city: a change meant to tidy status codes should not move sales by 4%.