dbt — Data Transformation in Warehouse Using SQL

dbt revolutionized working with data. SQL models that dbt compiles and runs — versioning, testing and documentation as code.

What is dbt¶

dbt transforms data in warehouse using SELECT statements. Takes care of DDL, dependencies, tests and documentation.

dbt Models¶

-- models/staging/stg_orders.sql
WITH source AS (
    SELECT * FROM {{ source('raw', 'orders') }}
)
SELECT
    id AS order_id,
    user_id AS customer_id,
    created_at AS order_date,
    amount_cents / 100.0 AS amount_eur,
    status
FROM source
WHERE status != 'test'

Tests¶

# schema.yml
version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: amount_eur
        tests: [not_null]

Incremental Models¶

{{ config(materialized='incremental', unique_key='order_id') }}

SELECT * FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
WHERE order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}

Summary¶

dbt is standard for warehouse transformations. SQL models, tests and documentation as code.