dbt-advanced

-- models/marts/fct_daily_revenue.sql — Incremental model (only processes new data)
{{
  config(
    materialized='incremental',
    unique_key='date_day',
    on_schema_change='sync_all_columns'
  )
}}

SELECT
    DATE_TRUNC('day', ordered_at) as date_day,
    COUNT(DISTINCT order_id) as total_orders,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_order_value
FROM {{ ref('stg_orders') }}

{% if is_incremental() %}
    -- Only process rows newer than the latest data in this model
    WHERE ordered_at > (SELECT MAX(date_day) FROM {{ this }})
{% endif %}

GROUP BY 1

-- snapshots/customer_snapshot.sql — Track historical changes
{% snapshot customer_snapshot %}

{{
  config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='timestamp',
    updated_at='updated_at',
  )
}}

SELECT * FROM {{ source('raw', 'customers') }}

{% endsnapshot %}
-- Creates columns: dbt_valid_from, dbt_valid_to, dbt_updated_at
-- Tracks every change to customer records over time

-- macros/cents_to_dollars.sql — Reusable transformation
{% macro cents_to_dollars(column_name) %}
    ROUND({{ column_name }}::numeric / 100, 2)
{% endmacro %}

-- Usage in models:
-- SELECT {{ cents_to_dollars('amount_cents') }} as amount

-- tests/generic/test_positive_value.sql — Generic test
{% test positive_value(model, column_name) %}

SELECT *
FROM {{ model }}
WHERE {{ column_name }} < 0

{% endtest %}

# models/schema.yml — Apply tests to models
models:
  - name: fct_daily_revenue
    columns:
      - name: total_revenue
        tests:
          - not_null
          - positive_value
      - name: date_day
        tests:
          - unique
          - not_null

models/
├── staging/          # 1:1 with source tables, light cleaning
│   ├── stg_orders.sql
│   └── stg_customers.sql
├── intermediate/     # business logic, joins
│   └── int_orders_with_customers.sql
└── marts/           # final tables for BI/analytics
    ├── fct_daily_revenue.sql
    └── dim_customers.sql