Variables and control flow in dbt - Deep Dive

Overview - Variables and control flow

What is it?

Variables in dbt are placeholders that store values you can reuse in your SQL models or macros. Control flow lets you decide which parts of your code run based on conditions, like if something is true or false. Together, they help make your data transformations flexible and dynamic. This means you can write one model that changes behavior depending on inputs or data.

Why it matters

Without variables and control flow, every data transformation would be rigid and repetitive. You would have to write many versions of the same logic for different cases, which wastes time and causes errors. Variables and control flow let you write smarter, reusable code that adapts to different situations, saving effort and reducing mistakes in your data projects.

Where it fits

Before learning variables and control flow, you should understand basic SQL and how dbt models work. After this, you can explore advanced dbt macros, Jinja templating, and dynamic model generation. This topic is a key step in making your dbt projects modular and maintainable.

Mental Model

Core Idea

Variables hold values and control flow directs which code runs based on conditions, making your dbt models flexible and reusable.

Think of it like...

Think of variables as labeled boxes where you keep things you might need later, and control flow as traffic lights that decide when cars (code) can go or stop based on the situation.

┌─────────────┐       ┌───────────────┐
│  Variables  │──────▶│ Store values  │
└─────────────┘       └───────────────┘
         │                      │
         ▼                      ▼
┌───────────────────────────────┐
│       Control Flow             │
│  (if, else, for, while logic) │
└───────────────────────────────┘
         │
         ▼
┌───────────────────────────────┐
│   Decide which code runs       │
└───────────────────────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding dbt Variables Basics

Concept: Introduce what variables are in dbt and how to define and use them.

In dbt, variables are defined in your dbt_project.yml or passed at runtime. You can access them in your models or macros using the Jinja syntax: {{ var('variable_name', 'default_value') }}. For example, if you define a variable called 'start_date', you can use it to filter data dynamically in your SQL code.

Result

You can write SQL that changes based on the variable's value without editing the SQL itself.

Understanding variables lets you write flexible SQL that adapts to different inputs, reducing repetition.

2

Foundation

Basic Control Flow with If Statements

Concept: Learn how to use if-else statements in dbt's Jinja templating to control code execution.

Control flow in dbt uses Jinja syntax. An if statement looks like this: {% if condition %} -- code runs if condition is true {% else %} -- code runs if condition is false {% endif %} You can use variables in conditions to decide which SQL code to generate.

Result

Your model can generate different SQL depending on the variable or data conditions.

Control flow lets you customize your SQL logic dynamically, making models smarter.

3

Intermediate

Using Variables Inside Control Flow

🤔Before reading on: Do you think variables can be used directly inside if conditions in dbt? Commit to your answer.

Concept: Combine variables and control flow to create dynamic SQL that changes based on variable values.

You can write code like: {% if var('run_full_load', false) %} SELECT * FROM source_table {% else %} SELECT * FROM source_table WHERE updated_at > '{{ var('last_run_date') }}' {% endif %} This means if 'run_full_load' is true, you get all data; otherwise, only recent data.

Result

Your dbt model runs different queries depending on the variable values you set.

Knowing how variables influence control flow unlocks powerful dynamic data transformations.

4

Intermediate

Looping with For Statements in dbt

🤔Before reading on: Can you use loops in dbt models to repeat SQL code? Commit to your answer.

Concept: Learn how to use for loops in Jinja to repeat code blocks, useful for generating repetitive SQL parts.

For example, to select multiple columns dynamically: {% set columns = ['id', 'name', 'email'] %} SELECT {% for col in columns %} {{ col }}{% if not loop.last %}, {% endif %} {% endfor %} FROM users This generates a SELECT statement listing all columns in the list.

Result

You get SQL code with all columns listed without writing each manually.

Loops reduce manual repetition and errors when writing similar SQL code multiple times.

5

Advanced

Combining Variables, Control Flow, and Macros

🤔Before reading on: Do you think macros can use variables and control flow to generate complex SQL? Commit to your answer.

Concept: Macros are reusable code blocks in dbt that can accept variables and use control flow to produce dynamic SQL.

Define a macro in a .sql file: {% macro filter_by_date(run_full_load, last_run_date) %} {% if run_full_load %} 1=1 {% else %} updated_at > '{{ last_run_date }}' {% endif %} {% endmacro %} Use it in a model: SELECT * FROM source_table WHERE {{ filter_by_date(var('run_full_load', false), var('last_run_date', '2023-01-01')) }} This lets you reuse logic and keep models clean.

Result

Your models become modular and easier to maintain with reusable dynamic filters.

Combining these features creates scalable, maintainable dbt projects with less duplicated code.

6

Expert

Advanced Control Flow: Nested Conditions and Error Handling

🤔Before reading on: Can nested if statements and error checks improve dbt model robustness? Commit to your answer.

Concept: Learn to write nested if-else blocks and handle missing or invalid variables gracefully in dbt.

Example nested condition: {% if var('env') == 'prod' %} {% if var('run_full_load', false) %} -- full load SQL {% else %} -- incremental SQL {% endif %} {% else %} -- dev environment SQL {% endif %} Also, use 'var' with default values to avoid errors if variables are missing. Example: {{ var('missing_var', 'default_value') }} This prevents dbt from failing unexpectedly.

Result

Your dbt models handle complex logic and unexpected inputs safely and clearly.

Mastering nested control flow and error handling prevents bugs and makes your data pipelines reliable.

Under the Hood

dbt uses the Jinja templating engine to process variables and control flow before running SQL. When you run dbt, it reads your models and macros, replaces variables with their values, evaluates if/else and loops, and generates final SQL code. This SQL is then sent to the database for execution. Variables are stored in a context dictionary during compilation, and control flow statements are Python-like instructions that guide how the template is rendered.

Why designed this way?

dbt uses Jinja because it is a powerful, flexible templating language that integrates well with SQL. This design separates code generation from execution, allowing users to write dynamic SQL without complex programming. Alternatives like embedding logic directly in SQL would be less flexible and harder to maintain. Jinja's syntax is familiar to many and supports variables, loops, and conditions cleanly.

┌───────────────┐
│  dbt Model    │
│ (with Jinja)  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Jinja Engine  │
│ - Replace vars│
│ - Evaluate if │
│ - Run loops   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Generated SQL │
│ (plain SQL)   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Database      │
│ Executes SQL  │
└───────────────┘

Myth Busters - 3 Common Misconceptions

Quick: Do you think variables in dbt can change during model execution? Commit to yes or no.

Common Belief: Variables in dbt can change their values while the model runs, like normal programming variables.

Reality: Variables in dbt are fixed during the compilation phase and cannot change during SQL execution in the database.

Why it matters: Expecting variables to change during execution leads to confusion and bugs because dbt compiles SQL once before running it.

Quick: Do you think control flow in dbt runs inside the database? Commit to yes or no.

Common Belief: Control flow statements like if and for run inside the database as part of SQL execution.

Reality: Control flow in dbt runs during the compilation step in Jinja, not inside the database. The database only sees the final SQL.

Why it matters: Misunderstanding this causes errors when trying to use Jinja logic as SQL commands, which the database cannot interpret.

Quick: Can you use any Python code inside dbt models? Commit to yes or no.

Common Belief: Since dbt uses Jinja, you can write any Python code inside your models.

Reality: dbt only supports Jinja templating syntax, which is a limited subset of Python-like expressions, not full Python code.

Why it matters: Trying to run full Python code causes compilation errors and blocks model runs.

Expert Zone

1

Variables can be scoped differently: project-level, profile-level, or passed at runtime, affecting their availability and override behavior.

2

Control flow evaluation happens entirely at compile time, so any data-dependent logic must be expressed in SQL, not Jinja.

3

Using macros with variables and control flow allows building complex reusable components, but overusing them can make debugging harder.

When NOT to use

Avoid complex control flow inside models when simple SQL CASE statements or database-native features can do the job more efficiently. For very dynamic logic, consider using dbt macros or external orchestration tools instead of heavy Jinja logic inside models.

Production Patterns

In production, teams use variables to configure environments (dev, prod) and control incremental vs full loads. Control flow is used to toggle features, generate dynamic filters, and build modular macros that reduce code duplication and improve maintainability.

Connections

Programming Variables and Conditionals

Variables and control flow in dbt are a specialized form of general programming concepts adapted for SQL templating.

Understanding basic programming logic helps grasp how dbt uses Jinja to generate dynamic SQL code.

Template Engines in Web Development

dbt's use of Jinja is similar to how web frameworks generate HTML dynamically using variables and control flow.

Knowing how template engines work in web development clarifies how dbt compiles SQL from templates.

Decision Making in Business Processes

Control flow in dbt models mirrors decision points in business workflows where different actions happen based on conditions.

Seeing control flow as business decisions helps understand why and when to use conditional logic in data transformations.

Common Pitfalls

#1 Using variables without default values causing errors if variable is missing.

Wrong approach:

{{ var('missing_var') }}

Correct approach:

{{ var('missing_var', 'default_value') }}

Root cause:

Not providing a default means dbt fails if the variable isn't set, breaking the model.

#2 Writing SQL inside if statements without proper Jinja syntax causing compilation errors.

Wrong approach:

{% if var('flag') %} SELECT * FROM table {% endif %}

Correct approach:

{% if var('flag') %} SELECT * FROM table {% endif %}

Root cause:

Actually, this looks correct; common mistake is missing the Jinja delimiters or mixing SQL and Jinja incorrectly. For example, forgetting {% %} or {{ }} causes errors.

#3 Trying to change variable values inside a model after initial assignment.

Wrong approach:

{% set my_var = 'a' %} {% set my_var = 'b' %}

Correct approach:

Use different variable names or pass new values via var() at runtime; variables are immutable once set in a template.

Root cause:

Jinja variables are immutable within a template scope; reassigning causes confusion or unexpected behavior.

Key Takeaways

Variables store values that make your dbt models flexible and reusable without rewriting SQL.

Control flow directs which parts of your code run, enabling dynamic SQL generation based on conditions.

dbt uses Jinja templating to process variables and control flow before running SQL in the database.

Combining variables, control flow, and macros lets you build modular, maintainable, and scalable data transformations.

Understanding the compile-time nature of variables and control flow prevents common mistakes and improves model reliability.