dbt Data Modeling: Guide, Pitfalls & Best Practices

What Is dbt data modeling?

dbt data modeling transforms raw warehouse tables into clean, documented, and tested datasets called “models.” Each model is a SQL file that selects from upstream sources, allowing analysts to codify business logic in version-controlled code.

Why choose dbt over ad-hoc SQL?

dbt enforces modular SQL, automated dependency graphs, and CI-ready tests. These features cut query duplication, surface upstream changes early, and enable code reviews—benefits that ad-hoc notebooks or legacy editors cannot guarantee.

How do I build my first dbt model?

Step 1 – Create the model SQL file

Add stg_orders.sql in models/. Write a SELECT that cleans raw orders.

Step 2 – Declare the model

Add the file path or folder to dbt_project.yml so dbt includes the model during dbt run.

Step 3 – Reference upstream models

Use ref('stg_orders') to join cleaned orders to other models. dbt builds lineage automatically.

What are dbt materializations?

Materializations define how the SQL compiles: view, table, incremental, or ephemeral. Choose incremental for large fact tables to process only new data and slash warehouse costs.

dbt data modeling best practices

Commit one business concept per model, prefix staging models with stg_, and add schema.yml tests for every key column. This makes lineage obvious and prevents silent data drift.

Common pitfalls and fixes

Hard-coding database names breaks cross-environment deploys. Fix by using dbt’s {{ target.schema }} variable instead.

Skipping tests lets bad data into downstream dashboards. Add not_null and unique tests to catch issues during CI.

Nesting sub-queries instead of reusable models increases runtime. Refactor shared logic into separate models and reference them.

Real-world example using Galaxy

Open Galaxy’s desktop SQL editor, connect to your warehouse, and author stg_orders.sql with AI Copilot. The Copilot autocompletes ref() calls, names the model, and suggests column docs—speeding up dbt development without leaving the IDE.

Full dbt model code sample

-- models/stg_orders.sql
{{ config(materialized='table') }}
select
id as order_id,
user_id,
status,
total_amount,
created_at::date as order_date
from {{ source('raw', 'orders') }}
where created_at >= '2023-01-01'