1: Developing and optimizing dbt models

Learning Objectives

By the end of this chapter, you will be able to:

• Identify and verify raw object dependencies using ref(), source(), and installed dbt packages.
• Select the right core materialization (view, table, incremental, ephemeral, materialized_view) and configure version control workflow around it.
• Apply modular design and DRY principles across staging, intermediate, and mart layers, including SQL and Python models.
• Convert business logic into performant SQL and manage downstream access with grants configuration.
• Use dbt run, dbt test, dbt docs, and dbt seed correctly, including the difference between run and build.
• Construct logical model flows and build clean DAGs by reading the graph that dbt compile produces.
• Configure projects through dbt_project.yml, including model paths, target paths, materialization defaults, and variables.
• Declare and configure sources, including freshness thresholds and downstream {{ source() }} references.

Executive Summary

• The DAG that dbt resolves is not a side effect of file naming; it is the literal output of every ref() and source() call in your compiled SQL. Understanding this resolution is the foundation of every other topic in this chapter.
• Materialization is a per-model trade-off between storage cost, query latency, and run-time. view, table, incremental, ephemeral, and materialized_view each fit a specific shape of workload, and the wrong pick is the single most common cause of regression.
• DRY discipline in dbt is enforced through macros, packages, intermediate models, and the staging/intermediate/mart layering convention, not through code comments or naming alone.
• The CLI surface most candidates fail on is the difference between dbt run (models only) and dbt build (models + tests + seeds + snapshots in DAG order); the second is what production deployments use.
• Sources are first-class declarations, not raw table strings. Every {{ source('schema', 'table') }} reference is validated against a sources: block in YAML, and missing entries are a compile error, not a runtime error.

Assumptions

• The reader has working SQL fluency on at least one cloud warehouse (Snowflake, BigQuery, Databricks SQL, Redshift, or Postgres).
• All examples use the fictional project acme_analytics against the warehouse database ACME_RAW (sources) and ACME_ANALYTICS (transformed models), with raw schemas JAFFLE, STRIPE, and MARKETING.
• Terminology follows dbt's official documentation; vendor-specific syntax is noted where adapter behavior diverges.