How dbt works (SQL + Jinja + YAML) - Mechanics & Internals

Overview - How dbt works (SQL + Jinja + YAML)

What is it?

dbt (data build tool) is a tool that helps analysts and engineers transform raw data into clean, organized tables using SQL, Jinja templating, and YAML configuration. It lets you write SQL queries that build your data models, use Jinja to add logic and reuse code, and YAML to configure how models run and document your data. This makes data transformation easier, repeatable, and more reliable.

Why it matters

Without dbt, teams often write messy, hard-to-maintain SQL scripts that are difficult to track and update. dbt solves this by organizing transformations into clear models with dependencies, automating runs, and documenting data lineage. This saves time, reduces errors, and helps teams trust their data for decision-making.

Where it fits

Before learning dbt, you should know basic SQL and understand data warehousing concepts. After mastering dbt, you can explore advanced data engineering topics like orchestration tools, testing frameworks, and data observability.

Mental Model

Core Idea

dbt works by combining SQL for data queries, Jinja for dynamic code generation, and YAML for configuration to create modular, maintainable data transformation pipelines.

Think of it like...

Imagine dbt as a kitchen where SQL is the recipe, Jinja is the chef who can customize recipes on the fly, and YAML is the menu that organizes and describes all dishes. Together, they produce consistent meals (clean data) efficiently.

┌─────────────┐      ┌─────────────┐      ┌─────────────┐
│   YAML      │─────▶│   dbt Core  │─────▶│   Data      │
│ (Config &   │      │ (Runs SQL & │      │ Warehouse   │
│  Docs)      │      │  Jinja Logic)│      │ (Models)    │
└─────────────┘      └─────────────┘      └─────────────┘
          ▲                  ▲
          │                  │
          │                  │
      ┌─────────┐       ┌─────────┐
      │  SQL    │       │  Jinja  │
      │(Queries)│       │(Templates)│
      └─────────┘       └─────────┘

Build-Up - 7 Steps

1

Foundation

Introduction to dbt and SQL Models

Concept: dbt uses SQL files called models to define how raw data is transformed into clean tables.

In dbt, you write SQL SELECT statements in files called models. Each model creates a table or view in your data warehouse. For example, a model might select and clean customer data from raw tables. dbt runs these models in order, building your data step-by-step.

Result

You get new tables or views in your warehouse that represent cleaned and transformed data.

Understanding that dbt models are just SQL queries helps you see dbt as a tool that organizes and runs your SQL transformations automatically.

2

Foundation

Using YAML for Configuration and Documentation

Concept: YAML files in dbt configure model behavior and add documentation to your data pipeline.

dbt uses YAML files to set properties like descriptions, tests, and dependencies for models. For example, you can write a YAML file to describe what a model does or to define tests that check data quality. This keeps your project organized and your data trustworthy.

Result

Your dbt project has clear documentation and automated checks, improving collaboration and data quality.

Knowing that YAML controls configuration and docs separates code logic (SQL) from metadata, making projects easier to maintain.

3

Intermediate

Jinja Templating for Dynamic SQL

🤔Before reading on: do you think Jinja lets you write SQL that changes based on conditions or loops? Commit to your answer.

Concept: Jinja is a templating language that lets you add logic like variables, loops, and conditions inside your SQL models.

Inside your SQL files, you can use Jinja syntax like {{ var_name }} or {% if condition %} to make your queries dynamic. For example, you can loop over a list of columns or change filters based on environment variables. This reduces repetition and makes your SQL flexible.

Result

Your SQL models can adapt automatically, reducing manual edits and errors.

Understanding Jinja's role reveals how dbt enables reusable and adaptable SQL code, a big step beyond static queries.

4

Intermediate

Model Dependencies and DAG Execution

🤔Before reading on: do you think dbt runs models in any order or follows dependencies? Commit to your answer.

Concept: dbt builds a Directed Acyclic Graph (DAG) of models based on dependencies and runs them in the correct order.

When one model selects from another, dbt knows the dependency. It creates a DAG to run models so that upstream tables are ready before downstream ones. This ensures data builds correctly without manual ordering.

Result

Your data pipeline runs smoothly, respecting dependencies automatically.

Knowing about the DAG helps you design models that build reliably and understand dbt's power in managing complex pipelines.

5

Intermediate

Testing and Data Quality with YAML

Concept: dbt lets you define tests in YAML to check your data for errors or unexpected values.

You can add tests like uniqueness, non-null, or custom SQL checks in YAML files. When you run dbt test, it runs these checks and reports failures. This helps catch data issues early.

Result

Your data pipeline includes automated quality checks, increasing trust in your data.

Understanding tests as code means data quality becomes part of your development workflow, not an afterthought.

6

Advanced

Macros and Reusable Jinja Functions

🤔Before reading on: do you think macros are just variables or can they contain complex logic? Commit to your answer.

Concept: Macros are reusable Jinja functions that let you write complex logic once and use it across many models.

You define macros in .sql or .yml files using Jinja. For example, a macro can generate a date filter or a common calculation. Then you call the macro inside your models. This DRYs up your code and makes maintenance easier.

Result

Your project has less repeated code and is easier to update.

Knowing macros unlocks advanced templating power, making your dbt projects scalable and maintainable.

7

Expert

Materializations and Performance Optimization

🤔Before reading on: do you think dbt always creates tables, or can it create views or incremental tables? Commit to your answer.

Concept: Materializations control how dbt builds models: as tables, views, or incremental loads to optimize performance.

By default, dbt creates tables, but you can configure models to be views or incremental tables that update only new data. This saves time and resources on large datasets. Materializations are set in YAML or model configs.

Result

Your data builds faster and uses warehouse resources efficiently.

Understanding materializations is key to scaling dbt projects and balancing freshness with cost.

Under the Hood

dbt parses your project files, compiles SQL models by rendering Jinja templates with variables and macros, and builds a dependency graph from model references. It then runs SQL queries in the correct order on your data warehouse, applying configurations from YAML files. Tests and documentation are also generated and executed as part of the workflow.

Why designed this way?

dbt was designed to separate concerns: SQL for data logic, Jinja for code reuse, and YAML for configuration. This modularity makes projects easier to maintain and collaborate on. The DAG execution ensures data dependencies are respected without manual orchestration. Alternatives like monolithic scripts were harder to manage and error-prone.

┌───────────────┐      ┌───────────────┐      ┌───────────────┐
│  Project      │─────▶│  dbt Compiler │─────▶│  Dependency   │
│ (SQL, Jinja,  │      │ (Render SQL)  │      │  Graph (DAG)  │
│  YAML files)  │      └───────────────┘      └───────────────┘
└───────────────┘              │                      │
                               ▼                      ▼
                      ┌───────────────┐      ┌───────────────┐
                      │  Warehouse    │◀────│  dbt Runner   │
                      │  Executes SQL │      │ (Runs models) │
                      └───────────────┘      └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does dbt replace your data warehouse? Commit to yes or no.

Common Belief: dbt is a database or data warehouse that stores data.

Reality: dbt does not store data; it runs SQL transformations on your existing data warehouse.

Why it matters: Thinking dbt stores data leads to confusion about its role and can cause wrong architecture decisions.

Quick: Do you think Jinja is a database language? Commit to yes or no.

Common Belief: Jinja is a SQL dialect or database language.

Reality: Jinja is a templating language that generates SQL code before it runs in the warehouse.

Why it matters: Misunderstanding Jinja causes errors in writing templates and misusing dbt features.

Quick: Does dbt automatically schedule your data jobs? Commit to yes or no.

Common Belief: dbt automatically runs your data pipelines on a schedule without extra tools.

Reality: dbt itself does not schedule runs; you need external tools like Airflow or dbt Cloud for scheduling.

Why it matters: Assuming dbt schedules jobs can cause missing data updates and pipeline failures.

Quick: Can dbt models reference any SQL object without declaring dependencies? Commit to yes or no.

Common Belief: You can write any SQL in dbt models without declaring dependencies explicitly.

Reality: dbt tracks dependencies only when you use ref() function; otherwise, it cannot manage build order.

Why it matters: Not using ref() breaks the DAG, causing build errors and inconsistent data.

Expert Zone

1

Macros can access context variables like execution environment, enabling dynamic behavior based on where and how dbt runs.

2

Materializations can be customized or extended by writing your own, allowing fine control over how models build and store data.

3

dbt's compilation step caches rendered SQL, improving performance on large projects by avoiding repeated template rendering.

When NOT to use

dbt is not ideal for real-time or streaming data transformations; tools like Apache Kafka or Spark Structured Streaming are better suited. Also, for complex procedural logic beyond SQL, dedicated ETL tools or Python-based pipelines may be preferable.

Production Patterns

In production, teams use dbt with version control, CI/CD pipelines for automated testing and deployment, and orchestration tools for scheduling. They modularize projects with packages, use snapshots for slowly changing dimensions, and enforce strict testing and documentation standards.

Connections

Software Build Systems (e.g., Make, Bazel)

dbt's DAG and dependency management is similar to how build systems track file dependencies and run tasks in order.

Understanding build systems helps grasp how dbt ensures transformations run in the right sequence without manual intervention.

Template Engines in Web Development

Jinja templating in dbt is the same technology used in web frameworks to generate dynamic HTML pages.

Knowing web templating clarifies how dbt generates SQL dynamically, making code reusable and adaptable.

Project Management with Configuration Files

Using YAML for configuration and documentation in dbt parallels how many software projects use YAML or JSON to manage settings and metadata.

Recognizing this pattern shows how separating code from configuration improves maintainability and collaboration.

Common Pitfalls

#1 Not using ref() function for model dependencies.

Wrong approach:

SELECT * FROM raw_customers;

Correct approach:

SELECT * FROM {{ ref('raw_customers') }};

Root cause:

Learners treat dbt models like normal SQL scripts and forget dbt needs ref() to track dependencies and build order.

#2 Hardcoding values instead of using Jinja variables.

Wrong approach:

WHERE order_date >= '2023-01-01'

Correct approach:

WHERE order_date >= '{{ var('start_date', '2023-01-01') }}'

Root cause:

Beginners do not realize Jinja can make SQL dynamic, leading to repeated manual edits and less flexible code.

#3 Placing configuration inside SQL files instead of YAML.

Wrong approach:

-- config(materialized='table')
SELECT * FROM source_table;

Correct approach:

models:
- name: model_name
materialized: table

Root cause:

Confusing where to put configuration causes inconsistent project structure and harder maintenance.

Key Takeaways

dbt combines SQL, Jinja templating, and YAML configuration to create modular, maintainable data transformation pipelines.

Using ref() to declare dependencies lets dbt build a DAG and run models in the correct order automatically.

Jinja templating makes SQL dynamic and reusable, reducing repetition and errors.

YAML files separate configuration and documentation from code, improving project clarity and collaboration.

Materializations control how data builds, enabling performance optimization and scalability in production.

Practice

1. What is the main role of Jinja in dbt projects?

easy

A. To add logic and dynamic behavior to SQL queries

B. To write raw SQL queries without any modification

C. To manage configuration and documentation files

D. To execute the SQL queries on the database

Solution

1. Step 1: Understand Jinja's purpose in dbt

   Jinja is a templating language that allows adding logic like loops and conditions inside SQL files.

2. Step 2: Differentiate roles of SQL, Jinja, and YAML

   SQL writes queries, YAML manages configs/docs, and Jinja adds dynamic logic to SQL.

3. Final Answer:

   To add logic and dynamic behavior to SQL queries -> Option A

4. Quick Check:

   Jinja = logic in SQL [OK]

Hint: Jinja = logic inside SQL, YAML = configs/docs [OK]

Common Mistakes:

• Confusing Jinja with YAML for configs
• Thinking Jinja executes SQL queries
• Assuming Jinja writes raw SQL without changes

2. Which of the following is the correct way to use a Jinja variable inside a dbt SQL model?

easy

A. SELECT * FROM var('table_name')

B. SELECT * FROM {{ var('table_name') }}

C. SELECT * FROM {% var('table_name') %}

D. SELECT * FROM [[ var('table_name') ]]

Solution

1. Step 1: Recall Jinja syntax for variables

   Jinja variables are inserted using double curly braces {{ }} around expressions.

2. Step 2: Identify correct syntax for var function

   The correct syntax is {{ var('variable_name') }} to access a variable in dbt.

3. Final Answer:

   SELECT * FROM {{ var('table_name') }} -> Option B

4. Quick Check:

   Jinja variables use {{ }} [OK]

Hint: Use {{ var('name') }} to insert variables in SQL [OK]

Common Mistakes:

• Using single curly braces or wrong brackets
• Confusing Jinja tags {% %} with variable insertion {{ }}
• Using square brackets instead of curly braces

3. Given this dbt model SQL code, what will be the output SQL after rendering?

SELECT
  user_id,
  {% if var('include_email', false) %}
    email,
  {% endif %}
  created_at
FROM users

Assuming the variable include_email is set to true in dbt_project.yml.

medium

A. SELECT user_id, true, created_at FROM users

B. SELECT user_id, created_at FROM users

C. Syntax error due to misplaced Jinja

D. SELECT user_id, email, created_at FROM users

Solution

1. Step 1: Check the value of the variable include_email

   The variable include_email is true, so the if condition passes and the email column is included.

2. Step 2: Render the SQL with the if block included

   The SQL will have user_id, email, and created_at columns selected from users.

3. Final Answer:

   SELECT user_id, email, created_at FROM users -> Option D

4. Quick Check:

   include_email true means email included [OK]

Hint: If var true, include block inside {% if %} [OK]

Common Mistakes:

• Ignoring the variable value and excluding email
• Thinking Jinja syntax causes SQL errors
• Confusing variable default values

4. You wrote this YAML config in your dbt project:

models:
  my_project:
    +materialized: table
      users:
        +tags: ['important']

Why does dbt raise an error when running?

medium

A. Because the indentation for 'users' is incorrect under 'my_project'

B. Because '+materialized' cannot be set in YAML

C. Because tags must be a string, not a list

D. Because 'models' key is missing

Solution

1. Step 1: Check YAML indentation rules for dbt configs

   In dbt, model configs under a project must be indented properly; 'users' should be at the same level as '+materialized'.

2. Step 2: Identify the indentation error

   'users' is indented too far, making it a child of '+materialized' which is invalid.

3. Final Answer:

   Because the indentation for 'users' is incorrect under 'my_project' -> Option A

4. Quick Check:

   YAML indentation matters for nested configs [OK]

Hint: Check YAML indentation carefully for nested configs [OK]

Common Mistakes:

• Ignoring YAML indentation importance
• Thinking '+materialized' is invalid syntax
• Assuming tags cannot be lists

5. You want to create a dbt model that selects only active users from a table, but the 'active' flag is stored in a YAML config. Which approach correctly combines SQL, Jinja, and YAML to achieve this?

hard

A. Use Jinja to read YAML directly inside SQL without defining variables

B. Write WHERE active = true directly in SQL without YAML or Jinja

C. Define 'active_flag: true' in YAML, then use WHERE active = {{ var('active_flag') }} in SQL with Jinja

D. Set 'active_flag' in YAML but forget to use Jinja in SQL, so filter is missing

Solution

1. Step 1: Store the filter value in YAML as a variable

   Define 'active_flag: true' in YAML under vars or config to make it accessible.

2. Step 2: Use Jinja to insert the variable in SQL WHERE clause

   Use WHERE active = {{ var('active_flag') }} so the SQL filters active users dynamically.

3. Final Answer:

   Define 'active_flag: true' in YAML, then use WHERE active = {{ var('active_flag') }} in SQL with Jinja -> Option C

4. Quick Check:

   YAML vars + Jinja in SQL = dynamic filters [OK]

Hint: Use YAML vars + Jinja {{ var() }} in SQL WHERE [OK]

Common Mistakes:

• Hardcoding filter in SQL ignoring YAML
• Not using Jinja to insert YAML vars
• Trying to read YAML directly in SQL without var()