dbt Examples & Guidelines

This model combines customer data from multiple sources to create a unified view.

-- models/marts/customer_360.sql
WITH customers AS (
    SELECT * FROM {{ ref('stg_customers') }}
),

orders AS (
    SELECT * FROM {{ ref('stg_orders') }}
),

customer_orders AS (
    SELECT
        customer_id,
        COUNT(*) as total_orders,
        SUM(amount) as lifetime_value
    FROM orders
    GROUP BY customer_id
)

SELECT
    c.*,
    COALESCE(co.total_orders, 0) as total_orders,
    COALESCE(co.lifetime_value, 0) as lifetime_value
FROM customers c
LEFT JOIN customer_orders co
    ON c.customer_id = co.customer_id

Calculate key product metrics including revenue, units sold, and return rate.

-- models/marts/product_analytics.sql
WITH products AS (
    SELECT * FROM {{ ref('stg_products') }}
),

sales AS (
    SELECT * FROM {{ ref('stg_sales') }}
),

returns AS (
    SELECT * FROM {{ ref('stg_returns') }}
)

SELECT
    p.product_id,
    p.product_name,
    p.category,
    COUNT(DISTINCT s.order_id) as total_orders,
    SUM(s.quantity) as units_sold,
    SUM(s.quantity * s.price) as revenue,
    COUNT(DISTINCT r.return_id) as total_returns,
    ROUND(COUNT(DISTINCT r.return_id)::FLOAT / 
          NULLIF(COUNT(DISTINCT s.order_id), 0) * 100, 2) as return_rate
FROM products p
LEFT JOIN sales s
    ON p.product_id = s.product_id
LEFT JOIN returns r
    ON s.order_id = r.order_id
GROUP BY 1, 2, 3