Transformation models and tests

In the dbt style of working (and tools like it), each transformation is a model: a file holding one SELECT statement. The tool turns each model into a view or table in the warehouse. A model refers to the ones it builds on, so the tool can work out the order to build them in and draw the dependency graph.

-- models/marts/fct_order_revenue.sql
select o.id as order_id, o.ordered_on, sum(i.qty * p.price) as revenue
from {{ ref('stg_orders') }} o
join {{ ref('stg_order_items') }} i on i.order_id = o.id
join {{ ref('stg_products') }} p on p.id = i.product_id
group by o.id, o.ordered_on

How a model is stored is its materialisation: a view (always fresh, worked out at query time), a table (rebuilt on each run) or incremental (only new or changed rows are processed each run, for big tables).

Tests are what make the warehouse trustworthy. A data test is a query that returns the rows that break a rule; zero rows means it passed. The common ones: a key is unique, a column is never null, a value is from an accepted list, and every foreign key points at a row that exists. They're declared next to the model:

models:
  - name: stg_payments
    columns:
      - name: payment_id
        data_tests: [unique, not_null]
      - name: order_id
        data_tests:
          - relationships:
              to: ref('stg_orders')
              field: id
      - name: currency
        data_tests:
          - accepted_values:
              values: ['USD']

Underneath, each test is plain SQL. The relationships test, for example, finds payments whose order doesn't exist:

SELECT p.id, p.order_id
FROM payments p
LEFT JOIN orders o ON o.id = p.order_id
WHERE p.order_id IS NOT NULL AND o.id IS NULL;

Run the tests on every build and stop the pipeline (or at least alert) when one fails, before bad data reaches a dashboard. Keep models in version control, review changes like code, and build them in a development schema before production.