S051 · dbt — Models, Tests, Docs, Warehouse-Native ELT

name: my_dbt
version: 1.0.0
profile: my_dbt
model-paths: ["models"]
test-paths:  ["tests"]
target-path: "target"
clean-targets: ["target", "dbt_packages"]
 
models:
  my_dbt:
    staging:
      +materialized: view
    marts:
      +materialized: table

my_dbt:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      user: postgres
      password: demo
      port: 5432
      dbname: warehouse
      schema: analytics
      threads: 4

version: 2
sources:
  - name: raw
    database: warehouse
    schema: raw
    freshness:
      warn_after: {count: 12, period: hour}
      error_after: {count: 24, period: hour}
    loaded_at_field: _loaded_at
    tables:
      - name: orders
        columns:
          - name: order_id
            tests: [unique, not_null]
          - name: customer_id
            tests: [not_null]
      - name: customers
        columns:
          - name: customer_id
            tests: [unique, not_null]

with src as (
    select * from {{ source('raw', 'orders') }}
)
select
    order_id::bigint       as order_id,
    customer_id::bigint    as customer_id,
    amount::numeric(10,2)  as amount,
    status::text           as status,
    order_date::timestamp  as order_ts
from src
where order_id is not null

with src as (
    select * from {{ source('raw', 'customers') }}
)
select
    customer_id::bigint as customer_id,
    lower(email)        as email,
    trim(city)          as city,
    signup_date::date   as signup_date
from src

version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests: [not_null]
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'paid', 'refunded', 'cancelled']
  - name: stg_customers
    columns:
      - name: customer_id
        tests: [unique, not_null]

select
    c.customer_id,
    c.email,
    c.city,
    c.signup_date,
    count(o.order_id)      as lifetime_orders,
    coalesce(sum(o.amount), 0) as lifetime_revenue
from {{ ref('stg_customers') }} c
left join {{ ref('stg_orders') }} o
    on o.customer_id = c.customer_id
    and o.status = 'paid'
group by 1,2,3,4

{{
    config(
        materialized='incremental',
        unique_key='order_id',
        on_schema_change='sync_all_columns'
    )
}}
 
select
    o.order_id,
    o.customer_id,
    o.amount,
    o.status,
    o.order_ts,
    c.city as customer_city
from {{ ref('stg_orders') }} o
left join {{ ref('stg_customers') }} c using (customer_id)
 
{% if is_incremental() %}
  where o.order_ts > (select coalesce(max(order_ts), '1970-01-01') from {{ this }})
{% endif %}

version: 2
models:
  - name: fct_orders
    description: One row per order. Incremental by order_ts.
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
  - name: dim_customers
    description: One row per customer with lifetime aggregates.
    columns:
      - name: customer_id
        tests: [unique, not_null]

-- Fails if any paid order has a non-positive amount.
select order_id, amount
from {{ ref('fct_orders') }}
where status = 'paid' and amount <= 0