🏗️ Models & Materializations — The Heart of dbt

-- This entire file IS the model. Nothing else needed!

SELECT
    1 AS id,
    'Hello dbt!' AS greeting

CREATE VIEW my_first_model AS (
    SELECT
        1 AS id,
        'Hello dbt!' AS greeting
);

SELECT
    id            AS customer_id,
    first_name,
    last_name,
    email_addr    AS email,
    created_at::DATE AS signup_date,
    CASE
        WHEN status = 'A' THEN 'active'
        WHEN status = 'I' THEN 'inactive'
        ELSE 'unknown'
    END AS customer_status
FROM
    {{ source('raw', 'customers') }}

{{ config(materialized='view') }}

-- This creates a VIEW in your warehouse
-- The query runs fresh every time someone queries stg_orders

SELECT
    id         AS order_id,
    user_id    AS customer_id,
    order_date,
    status,
    amount / 100.0 AS amount_dollars
FROM
    {{ source('raw', 'orders') }}

{{ config(materialized='table') }}

-- This creates a physical TABLE in your warehouse
-- Data is stored — queries are fast!

SELECT
    DATE_TRUNC('day', order_date) AS revenue_date,
    COUNT(*) AS total_orders,
    SUM(amount_dollars) AS total_revenue,
    AVG(amount_dollars) AS avg_order_value
FROM
    {{ ref('stg_orders') }}
WHERE
    status = 'completed'
GROUP BY 1

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

SELECT
    event_id,
    user_id,
    event_type,
    event_timestamp,
    page_url,
    device_type
FROM
    {{ ref('stg_events') }}

-- This WHERE clause is the magic!
-- It only runs on the FIRST build (full load)
-- On subsequent runs, it filters to only NEW rows
{% if is_incremental() %}
    WHERE event_timestamp > (
        SELECT MAX(event_timestamp)
        FROM {{ this }}
    )
{% endif %}

{{ config(materialized='ephemeral') }}

-- This model won't create a table or view!
-- It becomes a CTE inside any model that ref()s it

SELECT
    order_id,
    customer_id,
    amount_dollars,
    amount_dollars * 0.1 AS tax_amount,
    amount_dollars * 1.1 AS total_with_tax
FROM
    {{ ref('stg_orders') }}

-- dbt injects the ephemeral model as a CTE
WITH int_order_amounts AS (
    SELECT
        order_id,
        customer_id,
        amount_dollars,
        amount_dollars * 0.1 AS tax_amount,
        amount_dollars * 1.1 AS total_with_tax
    FROM raw.orders
)
SELECT * FROM int_order_amounts
WHERE total_with_tax > 100

{% snapshot snapshot_customers %}

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

SELECT
    customer_id,
    email,
    plan_type,
    updated_at
FROM {{ source('raw', 'customers') }}

{% endsnapshot %}

{{ config(materialized='view') }}

SELECT
    id            AS customer_id,
    first_name,
    last_name,
    LOWER(email_addr)  AS email,
    created_at::DATE AS signup_date,
    CASE
        WHEN status = 'A' THEN 'active'
        WHEN status = 'I' THEN 'inactive'
        ELSE 'unknown'
    END AS customer_status
FROM
    {{ source('raw', 'customers') }}

{{ config(materialized='ephemeral') }}

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email,
    c.signup_date,
    o.order_id,
    o.order_date,
    o.amount_dollars,
    o.status AS order_status
FROM
    {{ ref('stg_customers') }} c
LEFT JOIN
    {{ ref('stg_orders') }} o
    ON c.customer_id = o.customer_id

{{ config(materialized='table') }}

SELECT
    customer_id,
    first_name,
    last_name,
    email,
    signup_date,
    COUNT(order_id) AS total_orders,
    SUM(amount_dollars) AS lifetime_revenue,
    AVG(amount_dollars) AS avg_order_value,
    MIN(order_date) AS first_order_date,
    MAX(order_date) AS last_order_date,
    CASE
        WHEN COUNT(order_id) >= 10 THEN 'vip'
        WHEN COUNT(order_id) >= 5  THEN 'regular'
        WHEN COUNT(order_id) >= 1  THEN 'new'
        ELSE 'prospect'
    END AS customer_tier
FROM
    {{ ref('int_customer_orders') }}
GROUP BY
    customer_id, first_name, last_name, email, signup_date

{{ config(materialized='view') }}

SELECT
    id            AS customer_id,
    first_name,
    last_name,
    LOWER(email)   AS email,
    created_at::DATE AS signup_date
FROM
    {{ source('raw', 'customers') }}

{{ config(materialized='ephemeral') }}

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email,
    c.signup_date,
    o.order_id,
    o.order_date,
    o.amount_dollars
FROM
    {{ ref('stg_customers') }} c
LEFT JOIN
    {{ ref('stg_orders') }} o
    ON c.customer_id = o.customer_id

{{ config(materialized='table') }}

SELECT
    customer_id,
    first_name,
    last_name,
    email,
    signup_date,
    COUNT(order_id)       AS total_orders,
    COALESCE(SUM(amount_dollars), 0) AS lifetime_revenue,
    COALESCE(AVG(amount_dollars), 0) AS avg_order_value,
    MIN(order_date)       AS first_order_date,
    MAX(order_date)       AS last_order_date
FROM
    {{ ref('int_customer_orders') }}
GROUP BY
    customer_id, first_name, last_name, email, signup_date

$ dbt run

Running with dbt=1.7.0
Found 3 models, 0 tests, 0 snapshots, 0 seeds

Concurrency: 4 threads (target='dev')

1 of 3 START view model dev.stg_customers .............. [RUN]
1 of 3 OK created view model dev.stg_customers .......... [CREATE VIEW in 0.12s]

2 of 3 START table model dev.fct_customer_summary ........ [RUN]
2 of 3 OK created table model dev.fct_customer_summary ... [SELECT 1,247 in 0.45s]

Finished running 1 view model, 1 table model in 0 hours 0 minutes and 0.89 seconds (0.89s).
Completed successfully

Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2