Transformations as code

Why it matters

In many teams, the SQL that builds reporting tables lives in scheduled queries, BI tool views and analysts' laptops. Nobody knows which version is current, changes break dashboards without warning, and there are no tests. Analytics engineering applies software engineering habits to this work: version control, code review, automated tests, documentation and repeatable builds. dbt (data build tool, from dbt Labs) is the most widely used tool for it, and "dbt" now appears in many analyst and analytics engineer job postings. The same ideas appear in alternatives such as SQLMesh and Dataform.

The core idea: a model is a SELECT

In dbt, every transformation is a model: a .sql file that contains one SELECT statement. dbt wraps it in the CREATE TABLE or CREATE VIEW for you and runs it in your warehouse (Snowflake, BigQuery, Redshift, Databricks, PostgreSQL, DuckDB and others).

-- models/marts/fct_sales.sql
select
    o.order_id,
    o.order_date,
    c.customer_key,
    p.product_key,
    o.quantity,
    o.quantity * o.unit_price as revenue
from {{ ref('stg_orders') }} as o
join {{ ref('dim_customer') }} as c on c.customer_email = o.customer_email
join {{ ref('dim_product') }}  as p on p.product_sku    = o.product_sku

{{ ref('stg_orders') }} is a Jinja template function that refers to another model. dbt uses these references to build a dependency graph (DAG), so it always builds stg_orders before fct_sales, and it resolves the right schema for development or production.

Layers

A common project structure:

• Sources: raw tables loaded by an ingestion tool, declared in YAML ({{ source('shop', 'orders') }}).
• Staging models (stg_): one per source table; rename columns, cast types, light cleaning. No joins.
• Intermediate models (int_): reusable joins and logic.
• Marts (fct_, dim_): the fact and dimension tables (previous lesson) that BI tools and analysts query.

Each layer has one job, so problems are easier to find.

Tests live next to the code

Tests are declared in YAML and run with dbt test (or dbt build, which runs and tests models in dependency order):

# models/marts/schema.yml
models:
  - name: fct_sales
    description: One row per order line.
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_key
        tests:
          - relationships:
              to: ref('dim_customer')
              field: customer_key
  - name: dim_customer
    columns:
      - name: customer_key
        tests: [unique, not_null]

The four built-in "generic" tests are unique, not_null, accepted_values and relationships (referential integrity). Each compiles to a SQL query that returns failing rows; zero rows means pass. Packages such as dbt_utils and dbt-expectations add more, and you can write custom tests in SQL. Newer dbt versions also support unit tests on model logic with fixed input rows.

Documentation and lineage

description fields in YAML plus dbt docs generate produce a browsable site showing every model, its columns, tests and a lineage graph of what feeds what. When a stakeholder asks "where does this revenue number come from?", you can show them.

The workflow

1. Create a git branch.
2. Edit or add a model and its tests.
3. Run dbt build --select fct_sales+ against your development schema (the + includes downstream models).
4. Open a pull request; a teammate reviews the SQL; CI runs the build and tests.
5. Merge; the production job runs on a schedule (dbt Cloud, Airflow, Dagster, GitHub Actions...).

Materialisations control how each model is built: view, table, incremental (only process new rows, for big event tables) or ephemeral (inlined as a CTE).

Why analysts should care

• Metric logic is written once and reused, which ends the "two different numbers" problem from Module 1.
• Broken assumptions are caught by tests before a stakeholder sees a wrong dashboard.
• Changes are reviewed and reversible.

You can try dbt for free with dbt Core (open source, command line) and DuckDB on your own laptop, with no cloud account needed.

Common mistakes

• Hard-coding table names instead of using ref() and source(), which breaks the DAG.
• Putting all logic in one giant model instead of layers.
• No tests on primary keys, so duplicates silently inflate metrics.
• Treating dbt as a scheduler for everything; it transforms data already in the warehouse, and ingestion is a separate tool.

Quick recap

• A dbt model is a SELECT in a file; ref() links models into a DAG.
• Layers: sources, staging, intermediate, marts.
• Tests (unique, not_null, accepted_values, relationships) and docs live with the code.
• Git, review and CI make transformations reliable.