dbt-modeling

dbt Modeling

Purpose

Transform AI agents into experts on writing production-quality dbt models, providing guidance on CTE patterns, SQL structure, and best practices for creating maintainable and performant data models across all medallion architecture layers.

When to Use This Skill

Activate this skill when users ask about:

• Writing or refactoring dbt models
• Implementing CTE (Common Table Expression) patterns
• Creating staging, intermediate, or mart models
• Structuring SQL for readability and maintainability
• Implementing proper model dependencies with ref() and source()
• Converting existing SQL to dbt model format
• Debugging model SQL structure issues

---

CTE Pattern Structure

All dbt models should follow this consistent CTE pattern for readability and maintainability:

-- Import CTEs (staging and intermediate models)
with customers as (
    select * from {{ ref('stg_customers') }}
),

orders as (
    select * from {{ ref('stg_orders') }}
),

-- Logical CTEs (business logic)
customer_metrics as (
    select
        customer_id,
        count(*) as order_count,
        sum(order_amount) as lifetime_value
    from orders
    group by customer_id
),

-- Final CTE (column selection and standardization)
final as (
    select
        -- Primary key
        customers.customer_id,

        -- Attributes
        customers.customer_name,
        customers.customer_email,

        -- Metrics
        coalesce(customer_metrics.order_count, 0) as lifetime_orders,
        coalesce(customer_metrics.lifetime_value, 0) as lifetime_value,

        -- Metadata
        current_timestamp() as dbt_updated_at

    from customers
    left join customer_metrics
        on customers.customer_id = customer_metrics.customer_id
)

select * from final

Official dbt Documentation: How we structure our dbt projects

---

Bronze Layer: Staging Model Template

Purpose: One-to-one with source tables. Clean and standardize only.