dbt Model Layers

The 3 Layers

Staging Layer

Purpose: Create a clean, consistent version of each raw source table. One staging model per source table.

-- models/staging/stripe/stg_stripe__payments.sql
WITH source AS (
    SELECT * FROM {{ source('stripe', 'payments') }}
),

renamed AS (
    SELECT
        id AS payment_id,
        order_id,
        amount::numeric(10,2) AS amount,
        currency,
        status,
        created_at::timestamp AS created_at
    FROM source
    WHERE _fivetran_deleted = false
)

SELECT * FROM renamed

Intermediate Layer

Purpose: Complex transformations that bridge staging to marts. Joins, aggregations, business logic that doesn't belong in staging or marts alone.

-- models/intermediate/int_orders__joined.sql
SELECT
    o.order_id,
    o.customer_id,
    c.customer_name,
    c.customer_segment,
    o.order_date,
    o.status,
    p.amount AS payment_amount,
    p.currency
FROM {{ ref('stg_shopify__orders') }} o
LEFT JOIN {{ ref('stg_shopify__customers') }} c ON o.customer_id = c.customer_id
LEFT JOIN {{ ref('stg_stripe__payments') }} p ON o.order_id = p.order_id

Marts Layer

Purpose: Business-ready tables consumed by BI tools, analysts, and other consumers. Organized by business domain (finance, marketing, product).

-- models/marts/finance/fct_orders.sql
{{
  config(materialized='table')
}}

SELECT
    order_id,
    customer_id,
    customer_name,
    customer_segment,
    order_date,
    status,
    payment_amount,
    currency,
    CASE WHEN status = 'returned' THEN -payment_amount ELSE payment_amount END AS net_amount
FROM {{ ref('int_orders__joined') }}

models/
├── staging/
│   ├── stripe/
│   │   ├── _stripe__sources.yml
│   │   └── stg_stripe__payments.sql
│   └── shopify/
│       ├── _shopify__sources.yml
│       ├── stg_shopify__orders.sql
│       └── stg_shopify__customers.sql
├── intermediate/
│   └── int_orders__joined.sql
└── marts/
    ├── finance/
    │   ├── fct_orders.sql
    │   └── dim_customers.sql
    └── marketing/
        └── fct_campaigns.sql

Naming Conventions