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

models/marts/schema.yml

version: 2

models:

• name: fct_orders description: Order facts with customer information columns:
  • name: order_id description: Unique order identifier tests:
    • unique
    • not_null
  • name: order_total description: Total order amount tests:
    • not_null
    • dbt_utils.accepted_range: min_value: 0

Model Layering

Staging (stg_):

• Clean and standardize raw data

• One-to-one with source tables

• Minimal transformations

• Column renaming and type casting

Intermediate (int_):

• Complex transformations

• Join multiple staging models

• Not exposed to end users

Marts (fct_, dim_):

• Business logic

• Fact and dimension tables

• Exposed to end users

Best Practices

• Follow naming conventions (stg_, int_, fct_, dim_)

• Use CTEs for readability

• Document all models and columns

• Add tests to all models

• Use refs for dependencies

• Implement incremental models for large datasets

• Configure materialization appropriately

• Use sources for raw data

Advanced

For detailed information, see:

• Staging Patterns - Staging model best practices

• Marts Patterns - Fact and dimension table patterns

• Incremental - Incremental model strategies