Built-in Jinja context variables in dbt - Deep Dive

Overview - Built-in Jinja context variables

What is it?

Built-in Jinja context variables are special variables automatically available when writing Jinja code in dbt. They provide useful information about the current state, environment, or data being processed. These variables help you write dynamic and flexible SQL models by accessing metadata and runtime details. You don't need to define them; they come ready to use.

Why it matters

Without built-in Jinja context variables, you would have to manually pass or hardcode important information like the current model name, database, or execution environment. This would make your dbt projects less flexible and harder to maintain. These variables let you write smarter, reusable code that adapts automatically, saving time and reducing errors.

Where it fits

Before learning built-in Jinja context variables, you should understand basic Jinja templating and how dbt runs SQL models. After this, you can explore advanced dbt features like macros, hooks, and custom context variables to further customize your data transformations.

Mental Model

Core Idea

Built-in Jinja context variables are like automatic helpers that tell your dbt code about its current environment and data, so your code can adjust itself without extra input.

Think of it like...

Imagine you are cooking in a kitchen where the stove automatically tells you its temperature and the timer shows how long you've been cooking. You don't have to guess or set these yourself; they just provide useful info so you can cook perfectly. Built-in Jinja context variables are like that stove and timer for your dbt code.

┌───────────────────────────────┐
│        Jinja Template         │
│  ┌─────────────────────────┐  │
│  │ Built-in Context Vars    │  │
│  │ ┌───────────────┐       │  │
│  │ │ model_name    │◄──────┤  │
│  │ │ database      │◄──────┤  │
│  │ │ execution_time│◄──────┤  │
│  │ └───────────────┘       │  │
│  └─────────────────────────┘  │
│  Generates dynamic SQL code     │
└───────────────────────────────┘

Build-Up - 6 Steps

1

Foundation

What are Jinja context variables

Concept: Introduction to the idea that Jinja templates have automatic variables available.

When you write SQL in dbt using Jinja, you can use special variables that are already there for you. These variables give you information like the current model's name or the database you are working in. You don't have to create or pass them; they are built-in.

Result

You can access variables like {{ model.name }} or {{ database }} directly in your SQL code.

Understanding that some variables are always available helps you write dynamic code without extra setup.

2

Foundation

Common built-in context variables

Concept: Learn the most useful built-in variables you will use in dbt Jinja templates.

Some common built-in variables include: - model: Information about the current model (like name, schema) - database: The current database name - target: The current dbt target environment - run_started_at: Timestamp when the run started These let you customize SQL based on where and when it runs.

Result

You can write SQL that changes based on the model or environment, like filtering data differently.

Knowing these variables lets you make your models smarter and adaptable.

3

Intermediate

Using model variable properties

🤔Before reading on: do you think model.name and model.schema are strings or objects? Commit to your answer.

Concept: The model variable is an object with properties describing the current model.

The model variable has properties like: - model.name: the model's name as a string - model.schema: the schema where the model is built - model.alias: the alias name if set You can use these to dynamically refer to your model's details in SQL or macros.

Result

You can write code like SELECT * FROM {{ model.schema }}.{{ model.name }} to refer to the current model's table.

Understanding model as an object with properties unlocks powerful dynamic referencing in your SQL.

4

Intermediate

Accessing environment info with target

🤔Before reading on: does target contain only the environment name or more details? Commit to your answer.

Concept: The target variable holds details about the current dbt environment and connection.

The target variable includes: - target.name: the environment name (like dev or prod) - target.database: the database used - target.schema: the schema used - target.type: the type of database (like snowflake or redshift) You can use this to write environment-specific logic.

Result

You can write conditional SQL like {% if target.name == 'prod' %} ... {% endif %} to run code only in production.

Knowing target's details helps you safely manage different environments in one project.

5

Advanced

Using run_started_at for time logic

🤔Before reading on: do you think run_started_at is a string or a datetime object? Commit to your answer.

Concept: run_started_at gives the timestamp when the dbt run began, useful for time-based logic.

run_started_at is a string timestamp like '2024-06-01T12:00:00Z'. You can parse it or use it to filter data based on when the run started. For example, you can add a WHERE clause to only process recent data relative to the run time.

Result

You can write SQL that adapts to the run time, like filtering rows newer than run_started_at minus 1 day.

Using run_started_at lets your models react to the exact time they run, enabling dynamic data freshness checks.

6

Expert

Customizing context with built-in variables

🤔Before reading on: can you override built-in context variables in dbt? Commit to your answer.

Concept: While built-in variables are automatic, you can extend or override context in macros for advanced control.

In dbt macros, you can add or override context variables by passing extra arguments or using the context dictionary. This lets you customize how built-in variables behave or add new ones for complex logic. However, overriding built-ins should be done carefully to avoid confusion.

Result

You can create macros that behave differently based on custom context, improving reusability and flexibility.

Knowing how to extend or override context variables empowers you to build highly dynamic and maintainable dbt projects.

Under the Hood

When dbt runs a model, it compiles the SQL using the Jinja templating engine. During this process, it automatically injects built-in context variables into the template's environment. These variables are Python objects or strings representing metadata about the current run, model, and environment. Jinja accesses these variables at render time to replace placeholders with actual values, producing the final SQL sent to the database.

Why designed this way?

Built-in context variables were designed to reduce boilerplate and manual configuration. Instead of requiring users to pass metadata explicitly, dbt provides these variables automatically to simplify template writing. This design balances flexibility and ease of use, allowing users to write dynamic SQL without complex setup. Alternatives like manual parameter passing were more error-prone and less scalable.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│  dbt Runtime  │──────▶│ Jinja Engine  │──────▶│ Compiled SQL  │
│ (runs models) │       │ (renders code)│       │ (to database) │
└──────┬────────┘       └──────┬────────┘       └──────┬────────┘
       │                       │                       │
       │ Injects built-in       │ Uses built-in         │ Executes SQL
       │ context variables      │ context variables     │
       ▼                       ▼                       ▼
┌─────────────────────────────────────────────────────────────┐
│ Built-in Context Variables: model, target, run_started_at... │
└─────────────────────────────────────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think built-in context variables can be changed globally by the user? Commit yes or no.

Common Belief: Built-in context variables are fixed and cannot be changed or extended by the user.

Reality: While you cannot change the core built-in variables globally, you can extend or override context variables locally within macros or specific templates.

Why it matters: Believing they are immutable limits your ability to customize dbt projects and leverage advanced macro techniques.

Quick: Do you think the model variable contains the actual data rows? Commit yes or no.

Common Belief: The model variable holds the data inside the model, so you can access rows directly.

Reality: The model variable only contains metadata about the model, like its name and schema, not the data itself.

Why it matters: Confusing metadata with data can lead to incorrect assumptions and errors in SQL logic.

Quick: Does run_started_at update during the run or stay constant? Commit your answer.

Common Belief: run_started_at changes as the run progresses, reflecting the current time.

Reality: run_started_at is fixed at the start of the run and does not change during execution.

Why it matters: Misunderstanding this can cause bugs when relying on run_started_at for time calculations.

Quick: Can you use built-in context variables outside of dbt models? Commit yes or no.

Common Belief: Built-in context variables are available everywhere, including outside dbt runs.

Reality: They are only available during dbt runs when Jinja templates are rendered; outside dbt, they do not exist.

Why it matters: Expecting these variables outside dbt leads to errors and confusion.

Expert Zone

1

The model variable's properties can differ slightly depending on the adapter and database, affecting how you write portable SQL.

2

target contains nested dictionaries with connection details that can be used for fine-grained environment control beyond just name and schema.

3

run_started_at is a string timestamp, so you often need to parse it into a datetime object within Jinja or SQL for accurate time calculations.

When NOT to use

Built-in context variables are not suitable when you need to pass dynamic user input or external data at runtime. In those cases, use variables passed via dbt vars or external configuration files instead.

Production Patterns

In production, built-in context variables are used to write environment-aware models that adapt to dev, staging, and prod without code changes. They also enable audit logging by embedding run timestamps and model metadata into tables.

Connections

Environment Variables

Both provide dynamic information about the runtime environment but at different layers; environment variables come from the OS, while built-in context variables come from dbt.

Understanding environment variables helps grasp how built-in context variables supply runtime info, enabling dynamic behavior in code.

Template Engines

Built-in context variables are a feature of Jinja, a template engine used in dbt to generate SQL dynamically.

Knowing how template engines inject context clarifies how dbt compiles flexible SQL from static templates.

Software Configuration Management

Built-in context variables automate configuration by providing metadata, similar to how config management tools inject environment-specific settings.

Recognizing this connection shows how dbt manages complexity by automating environment-aware configuration.

Common Pitfalls

#1 Trying to access model data rows through the model variable.

Wrong approach:

SELECT * FROM {{ model }}

Correct approach:

SELECT * FROM {{ model.schema }}.{{ model.name }}

Root cause:

Confusing the model metadata object with the actual database table or view.

#2 Using run_started_at as a datetime object directly without parsing.

Wrong approach:

WHERE event_time > '{{ run_started_at }}'::timestamp - interval '1 day'

Correct approach:

WHERE event_time > (to_timestamp('{{ run_started_at }}', 'YYYY-MM-DD"T"HH24:MI:SS"Z"') - interval '1 day')

Root cause:

Assuming run_started_at is already a timestamp type instead of a string.

#3 Overriding built-in variables globally causing unexpected behavior.

Wrong approach:

{% set model = 'my_custom_value' %} ... {{ model.name }}

Correct approach:

Use a different variable name for custom values to avoid shadowing built-ins.

Root cause:

Not realizing that built-in variables have special meaning and should not be overwritten.

Key Takeaways

Built-in Jinja context variables provide automatic metadata and environment info to dbt models, enabling dynamic SQL generation.

These variables include model details, target environment info, and run timestamps, which help write adaptable and maintainable code.

Understanding the structure and type of these variables prevents common mistakes like confusing metadata with data or misusing timestamps.

Advanced users can extend or override context variables locally in macros for greater flexibility, but should do so carefully.

Built-in context variables are a core feature that makes dbt powerful for managing complex data transformations across environments.