dbt Model

What Is a dbt Model?

In dbt (data build tool), a model is a .sql or .py file that contains the business logic used to transform raw data into clean, analytics-ready datasets. Each model compiles to a single SELECT statement and can be materialized as a view, table, incremental table, or ephemeral CTE.

Why dbt Models Matter

Modern analytics stacks separate data movement (ELT) from transformation. dbt owns the T layer, and models are its fundamental building block. Because models are:

• Version-controlled. Stored as code in Git, they allow peer review, testing, and reproducibility.
• Modular. Each model does one job; complex pipelines emerge by ref-ing other models.
• Warehouse-native. dbt compiles your SQL to run where the data lives—no extra orchestration needed.
• Documented & testable. YAML configuration lets you add descriptions, ownership, and data tests that run with every deployment.

Core Anatomy of a Model

1. The File

Located in your models/ directory, a model typically contains:

-- models/orders.sql
{{config(materialized='table')}}

SELECT *
FROM {{ ref('raw_orders') }}


2. Materialization

The {{config}} block controls how the query result is stored:

• view – lightweight, always fresh, depends on source tables.
• table – stored snapshot created on each run.
• incremental – appends new data based on logic you define.
• ephemeral – compiled as a CTE inside downstream models (never hits the warehouse).

3. Dependencies via ref()

Instead of hard-coding physical table names, you call ref('model_name'). dbt builds a dependency graph, orders execution, and renames objects safely through environment changes.

Best Practices

Adopt a Layered Architecture

Use patterns such as staging → intermediate → mart to keep logic readable and reusable.

Keep Models Narrow in Scope

One model = one transformation concern. If a query exceeds ~200 lines or mixes domains, split it up.

Leverage Tests & Documentation

Add unique, not_null, and accepted_values tests in YAML. Document both columns and models so analysts understand provenance.

Incremental Where It Hurts

Large fact tables benefit from incremental loads. Dimension tables change less; stick to full-refresh tables or views for transparency.

Practical Example: Orders & Revenues

Stage Raw Data

-- models/stg_orders.sql
SELECT
id AS order_id,
user_id,
status,
amount_cents / 100.0 AS order_total,
created_at::date AS order_date
FROM {{ source('stripe','orders_raw') }}


Build Fact Model

-- models/fct_revenue.sql
{{config(materialized='incremental')}}

WITH orders AS (
SELECT *
FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
WHERE order_date > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}
)
SELECT
order_date,
SUM(order_total) AS gross_revenue
FROM orders
GROUP BY 1;


Running dbt run --select fct_revenue creates or updates the fct_revenue table efficiently.

Common Misconceptions

• “A model must be a physical table.” Not true—views and ephemeral CTEs are valid, and often cheaper, choices.
• “dbt models replace my ETL tool.” They handle transformation, not extraction or loading; you still need an EL tool for ingestion.
• “Incremental is always faster.” Incremental adds complexity and is slower when you need to reprocess history.

How Galaxy Fits In

Because a dbt model is ultimately SQL, you can author and test model queries inside Galaxy’s desktop SQL editor. Galaxy’s AI Copilot autocompletes ref() calls, infers column metadata, and optimizes queries before you commit them to your dbt repository. After deploying with dbt, you can still query the materialized objects from Galaxy and share validated examples with teammates via Galaxy Collections.

Troubleshooting & Debugging

Use dbt build --select model_name --full-refresh

This forces a clean rebuild if schema drift or incremental logic causes errors.

Log Statements

dbt logs compiled SQL in target/compiled/. Examine it to confirm Jinja blocks render as expected.

Leverage Tests

Test failures pinpoint rows violating expectations, saving time compared to manual debugging.

Real-World Use Cases

• Product Analytics. Aggregate session and feature-flag events into daily active usage marts.
• Revenue Recognition. Build GAAP-compliant revenue schedules from billing event streams.
• Marketing Attribution. Stitch ad spend, clicks, and conversions at the campaign level.

Conclusion

dbt models bring software-engineering rigor to data transformation workflows. By treating SQL as code—complete with modular design, dependency management, testing, and documentation—teams gain confidence in the accuracy and maintainability of their analytics pipelines. Whether edited in a code IDE or a modern SQL tool like Galaxy, mastering dbt models is a foundational skill for any analytics engineer.