-- models/staging/stripe/stg_stripe__customers.sqlwith source as (
select*from {{ source('stripe', 'customers') }}
),
renamed as (
select-- ids
id as customer_id,
-- stringslower(email) as email,
name as customer_name,
-- timestamps
created as created_at,
-- metadata
_fivetran_synced as _loaded_at
from source
)
select*from renamed
-- models/staging/stripe/stg_stripe__payments.sql
{{
config(
materialized='incremental',
unique_key='payment_id',
on_schema_change='append_new_columns'
)
}}
with source as (
select*from {{ source('stripe', 'payments') }}
{% if is_incremental() %}
where _fivetran_synced > (selectmax(_loaded_at) from {{ this }})
{% endif %}
),
renamed as (
select-- ids
id as payment_id,
customer_id,
invoice_id,
-- amounts (convert cents to dollars)
amount /100.0as amount,
amount_refunded /100.0as amount_refunded,
-- status
status as payment_status,
-- timestamps
created as created_at,
-- metadata
_fivetran_synced as _loaded_at
from source
)
select*from renamed
Pattern 3: Intermediate Models
-- models/intermediate/finance/int_payments_pivoted_to_customer.sqlwith payments as (
select*from {{ ref('stg_stripe__payments') }}
),
customers as (
select*from {{ ref('stg_stripe__customers') }}
),
payment_summary as (
select
customer_id,
count(*) as total_payments,
count(casewhen payment_status ='succeeded'then1end) as successful_payments,
sum(casewhen payment_status ='succeeded'then amount else0end) as total_amount_paid,
min(created_at) as first_payment_at,
max(created_at) as last_payment_at
from payments
groupby customer_id
)
select
customers.customer_id,
customers.email,
customers.created_at as customer_created_at,
coalesce(payment_summary.total_payments, 0) as total_payments,
coalesce(payment_summary.successful_payments, 0) as successful_payments,
coalesce(payment_summary.total_amount_paid, 0) as lifetime_value,
payment_summary.first_payment_at,
payment_summary.last_payment_at
from customers
leftjoin payment_summary using (customer_id)
Pattern 4: Mart Models (Dimensions and Facts)
-- models/marts/core/dim_customers.sql
{{
config(
materialized='table',
unique_key='customer_id'
)
}}
with customers as (
select*from {{ ref('int_payments_pivoted_to_customer') }}
),
orders as (
select*from {{ ref('stg_shopify__orders') }}
),
order_summary as (
select
customer_id,
count(*) as total_orders,
sum(total_price) as total_order_value,
min(created_at) as first_order_at,
max(created_at) as last_order_at
from orders
groupby customer_id
),
finalas (
select-- surrogate key
{{ dbt_utils.generate_surrogate_key(['customers.customer_id']) }} as customer_key,
-- natural key
customers.customer_id,
-- attributes
customers.email,
customers.customer_created_at,
-- payment metrics
customers.total_payments,
customers.successful_payments,
customers.lifetime_value,
customers.first_payment_at,
customers.last_payment_at,
-- order metricscoalesce(order_summary.total_orders, 0) as total_orders,
coalesce(order_summary.total_order_value, 0) as total_order_value,
order_summary.first_order_at,
order_summary.last_order_at,
-- calculated fieldscasewhen customers.lifetime_value >=1000then'high'when customers.lifetime_value >=100then'medium'else'low'endas customer_tier,
-- timestampscurrent_timestampas _loaded_at
from customers
leftjoin order_summary using (customer_id)
)
select*fromfinal
-- models/marts/core/fct_orders.sql
{{
config(
materialized='incremental',
unique_key='order_id',
incremental_strategy='merge'
)
}}
with orders as (
select*from {{ ref('stg_shopify__orders') }}
{% if is_incremental() %}
where updated_at > (selectmax(updated_at) from {{ this }})
{% endif %}
),
customers as (
select*from {{ ref('dim_customers') }}
),
finalas (
select-- keys
orders.order_id,
customers.customer_key,
orders.customer_id,
-- dimensions
orders.order_status,
orders.fulfillment_status,
orders.payment_status,
-- measures
orders.subtotal,
orders.tax,
orders.shipping,
orders.total_price,
orders.total_discount,
orders.item_count,
-- timestamps
orders.created_at,
orders.updated_at,
orders.fulfilled_at,
-- metadatacurrent_timestampas _loaded_at
from orders
leftjoin customers on orders.customer_id = customers.customer_id
)
select*fromfinal
-- Delete+Insert (default for most warehouses)
{{
config(
materialized='incremental',
unique_key='id',
incremental_strategy='delete+insert'
)
}}
-- Merge (best for late-arriving data)
{{
config(
materialized='incremental',
unique_key='id',
incremental_strategy='merge',
merge_update_columns=['status', 'amount', 'updated_at']
)
}}
-- Insert Overwrite (partition-based)
{{
config(
materialized='incremental',
incremental_strategy='insert_overwrite',
partition_by={
"field": "created_date",
"data_type": "date",
"granularity": "day"
}
)
}}
select*,
date(created_at) as created_date
from {{ ref('stg_events') }}
{% if is_incremental() %}
where created_date >= dateadd(day, -3, current_date)
{% endif %}
dbt Commands
# Development
dbt run # Run all models
dbt run --select staging # Run staging models only
dbt run --select +fct_orders # Run fct_orders and its upstream
dbt run --select fct_orders+ # Run fct_orders and its downstream
dbt run --full-refresh # Rebuild incremental models# Testing
dbt test# Run all tests
dbt test --select stg_stripe # Test specific models
dbt build # Run + test in DAG order# Documentation
dbt docs generate # Generate docs
dbt docs serve # Serve docs locally# Debugging
dbt compile # Compile SQL without running
dbt debug # Test connection
dbt ls --select tag:critical # List models by tag