Your first dbt project: models, tests and documentation, step by step

# Install dbt Core with the adapter for your warehouse, for example Snowflake
python -m pip install dbt-core dbt-snowflake

dbt init shop_analytics      # creates the project and asks for connection details
cd shop_analytics
dbt debug                    # checks the connection works

models/
  staging/
    shop/
      _shop__sources.yml
      stg_shop__orders.sql
      stg_shop__customers.sql
  marts/
    fct_orders.sql
    dim_customers.sql
    _marts__models.yml

version: 2

sources:
  - name: shop
    database: analytics
    schema: raw
    tables:
      - name: orders
        loaded_at_field: _loaded_at
        freshness:
          warn_after: {count: 12, period: hour}
          error_after: {count: 24, period: hour}
      - name: customers

-- models/staging/shop/stg_shop__orders.sql
with source as (
    select * from {{ source('shop', 'orders') }}
),

renamed as (
    select
        order_id,
        customer_id,
        cast(order_date as date)          as order_date,
        cast(amount as numeric(10, 2))    as order_amount,
        lower(status)                     as order_status
    from source
)

select * from renamed

-- models/marts/dim_customers.sql
with customers as (
    select * from {{ ref('stg_shop__customers') }}
),

orders as (
    select * from {{ ref('stg_shop__orders') }}
    where order_status = 'completed'
),

customer_orders as (
    select
        customer_id,
        min(order_date)   as first_order_date,
        max(order_date)   as last_order_date,
        count(*)          as lifetime_orders,
        sum(order_amount) as lifetime_value
    from orders
    group by customer_id
)

select
    c.customer_id,
    c.customer_name,
    co.first_order_date,
    co.last_order_date,
    coalesce(co.lifetime_orders, 0) as lifetime_orders,
    coalesce(co.lifetime_value, 0)  as lifetime_value
from customers c
left join customer_orders co on co.customer_id = c.customer_id

version: 2

models:
  - name: dim_customers
    description: One row per customer, with lifetime order metrics.
    columns:
      - name: customer_id
        description: Primary key.
        data_tests:
          - unique
          - not_null
      - name: lifetime_orders
        data_tests:
          - not_null

  - name: fct_orders
    columns:
      - name: customer_id
        data_tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: order_status
        data_tests:
          - accepted_values:
              values: ['completed', 'refunded', 'cancelled']

dbt build                          # run models, tests, seeds and snapshots in dependency order
dbt build --select dim_customers+  # one model and everything downstream of it
dbt source freshness               # check raw data is up to date
dbt docs generate && dbt docs serve  # browse documentation and the lineage graph

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

select event_id, user_id, event_type, event_timestamp
from {{ source('app', 'events') }}

{% if is_incremental() %}
where event_timestamp > (select max(event_timestamp) from {{ this }})
{% endif %}

{% snapshot customers_snapshot %}
{{ config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='timestamp',
    updated_at='updated_at'
) }}
select * from {{ source('shop', 'customers') }}
{% endsnapshot %}