Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 41 ยท Jinja and Macros

dbt Context Functions: target, var, env_var and Friends

Learn the functions and objects dbt adds to Jinja: target, var, env_var, this, config, execute, run_query, log, exceptions and run_started_at, with a runnable stand-in context.

Plain Jinja knows nothing about warehouses. dbt enriches it with a context: objects and functions available in every model and macro. ref() and source() are the best known, but the rest of the context is what lets one project behave differently in dev and prod, accept parameters, and introspect the warehouse. This lesson tours it.

In the examples we build a stand-in context with Python, real Jinja and fake dbt functions, labelled each time. It behaves like dbt for these features, but it is not dbt.

target: where am I running?

target describes the active connection from profiles.yml:

Property Example Meaning
target.name dev, prod The target name you chose
target.schema dbt_maria Default schema
target.type snowflake The adapter
target.database analytics Database or project
target.threads 8 Parallel threads

Typical use: do less work in development.

SQL
select * from {{ ref('stg_events') }}
{% if target.name == 'dev' %}
where event_date >= dateadd('day', -3, current_date)
{% endif %}
Python Stand-in context: target

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Use this sparingly. The more your dev SQL differs from prod, the less dev tells you about prod. Limiting rows or date ranges is the common, safe case.

var(): project and command-line variables

Variables are parameters. Define defaults in dbt_project.yml:

YAML
vars:
  start_date: "2024-01-01"
  excluded_countries: ["XX", "ZZ"]

Read them with var('name'), optionally with a fallback for when it is not defined:

SQL
select * from {{ ref('stg_orders') }}
where ordered_at >= '{{ var('start_date') }}'
  and country not in ({% for c in var('excluded_countries') %}'{{ c }}'{% if not loop.last %}, {% endif %}{% endfor %})

Override them for one run on the command line:

Terminal
dbt run --vars '{start_date: "2025-06-01"}'

var('x', 'default') returns the default if nothing defines x; without a default, a missing variable is a compile error. Prefer defaults in dbt_project.yml so a plain dbt run always works.

Python Stand-in context: var with defaults and overrides

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The same template, rendered twice, produced a default run and an overridden one. This is how you backfill a date range without editing code.

env_var(): environment variables

Reads from the shell environment. It is the right place for secrets and per-machine settings:

YAML
password: "{{ env_var('DBT_PASSWORD') }}"
SQL
{{ env_var('DBT_ENV', 'dev') }}      -- with a default

Variables whose names start with DBT_ENV_SECRET are scrubbed from logs. Remember that env vars used in a model change its compiled SQL, which matters for caching and CI.

this: the relation being built

{{ this }} is the table or view the current model creates, with database and schema. It is used mainly in incremental models and hooks:

SQL
{{ config(post_hook = "grant select on {{ this }} to role reporter") }}
SQL
-- in an incremental model
where updated_at > (select max(updated_at) from {{ this }})

this.schema, this.name and this.identifier give the parts.

config(), is_incremental() and model

config() sets configs as you saw. You can also read them: {{ config.get('materialized') }}. is_incremental() returns true when the model is materialized incrementally, the table already exists, and no --full-refresh was given; the incremental chapter builds on it. The model object holds the whole node (name, path, tags, columns, config), handy for debugging: {{ model.name }}.

execute: parse time versus run time

dbt renders each file twice: once at parse time (to find refs and configs) and once at compile time with the real warehouse. The flag execute is true only in the second pass. Macros that query the warehouse (for example to look up distinct values) must guard against running at parse time:

SQL
{% set values = [] %}
{% if execute %}
    {% set results = run_query('select distinct status from ' ~ ref('stg_orders')) %}
    {% set values = results.columns[0].values() %}
{% endif %}

run_query(sql) sends SQL to the warehouse while compiling and returns an agate table. It is powerful (generate columns from data) but it means compilation needs a connection and costs warehouse time, so use it with care. dbt compile and dbt docs generate connect for the same reason.

log(), print() and exceptions

SQL
{{ log('Building for ' ~ target.name, info=True) }}   {# writes to the console #}
{% do exceptions.warn('Deprecated variable used') %}   {# prints a warning #}
{% do exceptions.raise_compiler_error('start_date must be set') %}  {# stops the run #}

exceptions.raise_compiler_error is how you make a macro fail early with a clear message. Use it to validate arguments:

Python Stand-in context: raising a clear error

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Dates, modules and run metadata

  • run_started_at is the start time of the invocation (a Python datetime), e.g. {{ run_started_at.strftime('%Y-%m-%d') }}.
  • invocation_id is a unique id per command, useful for audit columns.
  • modules.datetime, modules.re, modules.itertools and modules.pytz expose a few safe Python modules.
  • flags holds command-line flags such as flags.FULL_REFRESH.
Python Stand-in context: run_started_at and modules

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

adapter: talking to the warehouse in a portable way

adapter exposes methods that hide dialect differences: adapter.get_relation(...), adapter.get_columns_in_relation(...), adapter.quote(...), and adapter.dispatch(...), the mechanism behind cross-database macros. The next lesson uses dispatch to write one macro that adapts to Postgres, Snowflake and BigQuery.

Summary of the context

Name Kind Use
ref, source functions link models and raw tables
config function set or read configs
var, env_var functions parameters and secrets
target object current connection
this object relation being built
execute flag true when really running
run_query function run SQL while compiling
log, exceptions helpers messages and errors
run_started_at, invocation_id, flags, modules values run metadata
adapter object dialect-aware helpers

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How do you read a variable with a fallback of 'all' when it is not defined?

    The second argument of var() is the default returned when the variable is undefined.
  2. 2. Why wrap run_query() calls in {% if execute %}?

    At parse time dbt only collects refs and configs. Warehouse queries should only run in the real compile pass, where execute is true.
  3. 3. What does {{ this }} refer to?

    this resolves to the database, schema and name of the object being created.
  4. 4. Which is the right place for a database password in profiles.yml?

    env_var reads secrets from the environment so they stay out of files.

Summary

  • dbt extends Jinja with ref, source, config, var, env_var, target, this, execute, run_query, log, exceptions, adapter and more.
  • target and var make one project behave differently per environment or run; defaults keep dbt run working.
  • Guard warehouse queries in macros with execute, and fail early with exceptions.raise_compiler_error.
  • run_started_at and modules give dates and small Python helpers.

Next: Macro Patterns: Cross-Database Macros, run_query and Overrides