dbt Model Boilerplate
What this is: Starter templates for each layer of the dbt (data build tool) transformation architecture — staging, intermediate, and mart models. Copy, adapt, and extend for your specific data sources and business logic.
Prerequisites: A configured dbt project, a connected data warehouse (Snowflake, BigQuery, Databricks, Redshift, etc.), and a basic understanding of SQL.
Covered in: The Data Foundations Guide on biztechprimer.com.
The three-layer architecture
Sources (raw data)
↓
Staging models — stg_* — clean and standardize one source table
↓
Intermediate models — int_* — join and combine across sources
↓
Mart models — dim_* / fct_* — business-ready, analytics-facing
Each layer has a distinct job. Mixing them creates models that are hard to maintain and test.
Layer 1: Staging Model (stg_*.sql)
Job: One source table → one staging model. Clean, rename, cast, dedup. No business logic, no joins across sources.
File: models/staging/stg_[source]_[entity].sql
Example: models/staging/stg_stripe_customers.sql
-- stg_stripe_customers.sql
-- Source: Stripe customers table via Fivetran/Airbyte
-- Grain: One row per customer (id)
-- Loads: Incremental (or full refresh for small tables)
with source as (
select * from {{ source('stripe', 'customers') }}
),
renamed as (
select
-- Primary key
id as customer_id,
-- Timestamps
created as customer_created_at,
updated as customer_updated_at,
-- Customer attributes
email as customer_email,
name as customer_name,
description as customer_description,
-- Payment info
currency as default_currency,
balance as stripe_balance_cents,
balance / 100.0 as stripe_balance_dollars, -- Stripe stores in cents
-- Metadata
metadata as metadata_json,
livemode as is_live_mode,
delinquent as is_delinquent
from source
),
final as (
select * from renamed
-- Dedup if source has duplicates (adjust key as needed)
qualify row_number() over (
partition by customer_id
order by customer_updated_at desc
) = 1
)
select * from final
Staging model schema.yml (tests + documentation):
# models/staging/schema.yml
version: 2
models:
- name: stg_stripe_customers
description: "One row per Stripe customer, cleaned and renamed from raw source."
columns:
- name: customer_id
description: "Stripe customer ID (id in source). Primary key."
tests:
- unique
- not_null
- name: customer_email
description: "Customer email address."
tests:
- not_null
- name: customer_created_at
description: "Timestamp when customer was created in Stripe (UTC)."
tests:
- not_null
Layer 2: Intermediate Model (int_*.sql)
Job: Combine and reshape staged data. Apply business logic. Prepare data for final marts. One responsibility per model.
File: models/intermediate/int_[domain]__[logic].sql
Example: models/intermediate/int_subscriptions__with_customer.sql
-- int_subscriptions__with_customer.sql
-- Joins subscription data with customer attributes
-- Adds calculated fields needed by multiple downstream marts
-- Grain: One row per subscription
with subscriptions as (
select * from {{ ref('stg_stripe_subscriptions') }}
),
customers as (
select * from {{ ref('stg_stripe_customers') }}
),
joined as (
select
-- Keys
subscriptions.subscription_id,
subscriptions.customer_id,
-- Customer attributes (from staging)
customers.customer_email,
customers.customer_name,
customers.is_delinquent,
-- Subscription attributes
subscriptions.subscription_status,
subscriptions.subscription_created_at,
subscriptions.current_period_start,
subscriptions.current_period_end,
subscriptions.cancel_at_period_end,
subscriptions.canceled_at,
-- Calculated fields
subscriptions.amount_cents / 100.0 as mrr_dollars,
subscriptions.amount_cents * 12 / 100.0 as arr_dollars,
-- Status flags
case
when subscriptions.subscription_status = 'active'
then true
else false
end as is_active,
case
when subscriptions.canceled_at is not null
and subscriptions.subscription_status != 'active'
then true
else false
end as is_churned,
-- Tenure
datediff(
'day',
subscriptions.subscription_created_at,
current_date()
) as days_as_customer
from subscriptions
left join customers
on subscriptions.customer_id = customers.customer_id
)
select * from joined
Layer 3: Mart Models (fct_*.sql and dim_*.sql)
Fact model — event/transaction grain
File: models/marts/finance/fct_mrr.sql
-- fct_mrr.sql
-- Monthly Recurring Revenue by month and subscription
-- Grain: One row per subscription per month
-- Used by: Revenue reporting, churn analysis, cohort analysis
with spine as (
-- Generate one row per month within each subscription's active window
-- Adjust date range as needed; replace with your warehouse's date-spine function
select
dateadd('month', seq4(), '2020-01-01'::date) as month_start
from table(generator(rowcount => 120)) -- 10 years of months
where month_start <= current_date()
),
subscriptions as (
select * from {{ ref('int_subscriptions__with_customer') }}
where is_active or is_churned
),
monthly_subscriptions as (
select
-- Time dimension
date_trunc('month', spine.month_start) as mrr_month,
-- Keys
subscriptions.subscription_id,
subscriptions.customer_id,
subscriptions.customer_email,
-- MRR
subscriptions.mrr_dollars,
-- Status at this point in time
case
when subscriptions.subscription_created_at <= spine.month_start
and (
subscriptions.canceled_at is null
or subscriptions.canceled_at > dateadd('month', 1, spine.month_start)
)
then 'active'
when date_trunc('month', subscriptions.canceled_at) = spine.month_start
then 'churned'
else null
end as mrr_status
from spine
cross join subscriptions
where mrr_status is not null
)
select * from monthly_subscriptions
Dimension model — entity grain
File: models/marts/core/dim_customers.sql
-- dim_customers.sql
-- One row per customer with current-state attributes
-- Grain: One row per customer_id
with customers as (
select * from {{ ref('stg_stripe_customers') }}
),
subscriptions as (
select
customer_id,
count(*) filter (where is_active) as active_subscription_count,
sum(mrr_dollars) filter (where is_active) as current_mrr,
min(subscription_created_at) as first_subscription_date,
max(canceled_at) as last_churn_date
from {{ ref('int_subscriptions__with_customer') }}
group by 1
),
final as (
select
customers.customer_id,
customers.customer_email,
customers.customer_name,
customers.customer_created_at,
customers.is_delinquent,
subscriptions.active_subscription_count,
subscriptions.current_mrr,
subscriptions.first_subscription_date,
subscriptions.last_churn_date,
-- Derived status
case
when subscriptions.active_subscription_count > 0 then 'active'
when subscriptions.last_churn_date is not null then 'churned'
else 'never_subscribed'
end as customer_status
from customers
left join subscriptions using (customer_id)
)
select * from final
Common patterns
Incremental model
-- Add this config block at the top for incremental loading
{{
config(
materialized='incremental',
unique_key='event_id',
incremental_strategy='merge'
)
}}
select * from {{ source('app', 'events') }}
{% if is_incremental() %}
where event_timestamp > (select max(event_timestamp) from {{ this }})
{% endif %}
Snapshot (SCD Type 2)
-- snapshots/scd_subscriptions.sql
-- Tracks historical changes to subscription records
{% snapshot scd_subscriptions %}
{{
config(
target_schema='snapshots',
unique_key='subscription_id',
strategy='timestamp',
updated_at='updated_at'
)
}}
select * from {{ source('stripe', 'subscriptions') }}
{% endsnapshot %}
Project structure reference
models/
├── staging/
│ ├── stripe/
│ │ ├── stg_stripe_customers.sql
│ │ ├── stg_stripe_subscriptions.sql
│ │ └── schema.yml
│ └── hubspot/
│ ├── stg_hubspot_contacts.sql
│ └── schema.yml
├── intermediate/
│ ├── int_subscriptions__with_customer.sql
│ └── int_contacts__enriched.sql
└── marts/
├── core/
│ ├── dim_customers.sql
│ └── fct_mrr.sql
└── finance/
└── fct_revenue_monthly.sql
Covered in the Data Foundations Guide on biztechprimer.com.