model-builder

dbt Model Builder

Quick Start

Create well-structured dbt models following best practices for staging, intermediate, and mart layers.

Instructions

Step 1: Create staging models

Staging models clean and standardize raw data:

-- models/staging/stg_orders.sql
with source as (
    select * from {{ source('raw', 'orders') }}
),

renamed as (
    select
        order_id,
        customer_id,
        order_date,
        order_total,
        order_status,
        created_at,
        updated_at
    from source
)

select * from renamed

Add schema file:

# models/staging/schema.yml
version: 2

models:
  - name: stg_orders
    description: Cleaned and standardized orders from raw data
    columns:
      - name: order_id
        description: Unique order identifier
        tests:
          - unique
          - not_null
      - name: customer_id
        description: Customer who placed the order
        tests:
          - not_null

Step 2: Create mart models

Mart models contain business logic:

-- models/marts/fct_orders.sql
with orders as (
    select * from {{ ref('stg_orders') }}
),

customers as (
    select * from {{ ref('stg_customers') }}
),

final as (
    select
        orders.order_id,
        orders.customer_id,
        customers.customer_name,
        orders.order_date,
        orders.order_total,
        orders.order_status
    from orders
    left join customers
        on orders.customer_id = customers.customer_id
)

select * from final

Step 3: Create incremental models

For large datasets, use incremental models:

-- models/marts/fct_events.sql
{{
    config(
        materialized='incremental',
        unique_key='event_id',
        on_schema_change='fail'
    )
}}

with events as (
    select * from {{ source('raw', 'events') }}
    
    {% if is_incremental() %}
    where event_timestamp > (select max(event_timestamp) from {{ this }})
    {% endif %}
)

select * from events

Step 4: Add documentation