Ultimate dbt Jinja Functions Cheat Sheet

Jinja is the game changing feature of dbt Core that allows us to create dynamic SQL code. In addition to the standard Jinja library, dbt Core includes additional functions and variables to make working with dbt even more powerful out of the box.

See our original post, The Ultimate dbt Jinja Cheat Sheet, to get started with Jinja fundamentals like syntax, variable assignment, looping and more. Then dive into the information below which covers Jinja additions added by dbt Core.

This cheatsheet references the extra functions, macros, filters, variables and context methods that are specific to dbt Core.

Enjoy!

dbt Core: Functions

These pre-defined dbt Jinja functions are essential to the dbt workflow by allowing you to reference models and sources, write documentation, print, write to the log and more.

Functions
ref(project_or_package, model_name, version=latest_version)	This function allows you to reference models within or accross dbt projects. The version and project_or_package parameters are optional. `select * from {{ ref('model_name') }} select * from {{ ref('model_name', version=1) }} select * from {{ ref('project_or_package', 'model_name') }}`
source(source_name, table_name )	This function allows you to create a dependency between the current model and a source. `select * from {{ source('source_name', 'table_name') }}`
log(msg, info=False)	This function writes a message to the log and/or stdout. To write to the both the log file and stdout set the info arg to True. If set to False, the function will only write to the log file. `{{ log('Logging something', info=True) }}`
print("string to print")	Use this function when you wish to print messages to the log and stdout. This is the same as log() with info=True. `{{ print("Print something: " ~ value1 ~ ", " ~ value2) }}`
doc('doc_block_name')	The doc function allows you reference {% docs blocks %} from markdown (.md) files in the description field of property files(.yml). It is important to note that the doc() function is how you reference the contents of a {% docs block %}. `{% docs table_sales_description %} # docs - go - here {% enddocs %} #schema.yml version: 2 models: - name: sales description: '{{ doc("table_sales_description") }}'` The {% docs block %} can be used to override preset overviews.
return(data)	Use this function to return data to the caller. This function will preserve the data type: dict, list, int etc. `{% macro get_list() %} {{ return(['column_1', 'column_2', 'column_2']) }} {% endmacro %}` `select -- getdata() returns a list! {% for item in get_list() %} {{ item }} {% if not loop.last %},{% endif %} {% endfor %} from {{ ref('some_model') }}`
env_var('env_var_name', 'default_value')	env_var is used to incorporate environment variables anywhere you can use Jinja code. `"{{ env_var('DB_PORT') \| as_number }}"` Use the optional second argument to set a default and avoid compilation errors. `#dbt_project.yml models: my_project: +materialized: "{{ env_var('DBT_MATERIALIZATION', 'table') }}"`
var('variable_name')	Use when configuring packages in multiple environments or defining values across multiple models in a package. For variables that rarely change, define them in the dbt_project.yml file. Variables that change frequently can be defined through the CLI. `#dbt_project.yml vars: start_date: '2023-10-01'` `select * from orders where start_date >'{{ var("start_date") }}'`

dbt Core: Macros

These macros are provided in dbt Core to improve the dbt workflow.

Macros
run_query()	This macro allows you to run SQL queries and store their results. It returns a table object with the query result. `{% set results = run_query('select order from orders_table') %}`
debug()	Call this macro within a macro to open up a python debugger. The DBT_MACRO_DEBUGGING environment variable must be set. The debug macro is only available in dbt Core/Datacoves. Only use {{ debug }} in a development environment. `{% macro my_macro() %} {% set result = my_macro() %} {{ debug() }} {% endmacro %}`

dbt Core: Filters

These dbt Jinja filters are used to modify data types.

Filters
as_bool	This Jinja filter will coerce the output into a boolean value. If the conversion can’t happen then it will return an error. `{{ <my_value> \| as_bool }}`
as_number	This Jinja filter will coerce the output into an numeric value. If the conversion can’t happen then it will return an error. `{{ <my_value> \| as_number }}`
as_native	This Jinja filter will return the native python type (set, list, tuple, dict, etc). Best for iterables, use as_bool or as_number for boolean or numeric values. `{{ <my_value> \| as_native }}`

dbt Core: Project context variables

These dbt core "variables" such as config, target, source, and others contain values that provide contextual information about your dbt project and configuration settings. They are typically used for accessing and customizing the behavior of your dbt models based on project-specific and environment-specific information.

Project Context Variables
adapters	dbt uses the adapter to communicate with the database. Setup correctly using the Database specific adapter i.e Snowflake, RedShift. The adapter has methods that will be translated into SQL statements specific to your database.
builtins	The builtins variable is a dictionary containing keys for dbt context methods ref, source, and config. `{% macro ref(modelname) %} {% set db_name = builtins.ref(modelname).database \| lower %}} {% if db_name.startswith('staging') or db_name.endswith('staging') %} {{ return(builtins.ref(modelname).include(database=false)) }} {% else %} {{ return(builtins.ref(modelname)) }} {% endif %} {% endmacro %}`
config	The config variable allows you to get configuration set for dbt models and set constraints that assure certain configurations must exist for a given model: config.get('<config_key>', default='value'): Fetches a configuration named <config_key> for example "materialization". - If no default is provided or the configuration item is not defined, it this will return None `{%- set unique_key = config.get('unique_key', default='id') -%}` config.require("<config_key>"): Strictly requires a key named <config_key> is defined in the configuration. - Throws error if not set. `{%- set unique_key = config.require('unique_key') -%}` config.get and config.require are commonly seen in the context of custom materializations, however, they can be used in other macros, if those macros are used within a model, seed, or snapshot context and they have the relevant configurations set.
dbt_version	The dbt_version variable is helpful for debugging by returning the installed dbt version. This is the dbt version running not what you define in your project. e.g. If you make a project with dbt 1.3 and run it on another machine with dbt 1.6, this will say 1.6
execute	The execute variable is set to True when dbt runs SQL against the databse such as when executing dbt run. When running dbt commands such as dbt compile where dbt parses your project but no SQL is run against the database execute is set to False. This variable is helpful when your Jinja is relying on a result from the database. Wrap the jinja in an if statement. `{% set payment_method_query %} SELECT DISTINCT payment_method FROM {{ ref('raw_payments') }} ORDER BY 1 {% endset %} {% set payment_methods = run_query(payment_method_query) %} {% if execute %} {# Extract the payment methods from the query results #} {% set payment_methods_list = payment_methods.columns[0].values() %} {% else %} {% set payment_methods_list = [] %} {% endif %}`
flags	This variable holds the value of the flags passed in the command line such as FULL_REFRESH, STORE_FAILURES, and WHICH(compile, run, build, run-operation, test, and show). It allows you to set logic based on run modes and run hooks based on current commands/type. `{% if flags.STORE_FAILURES %} --your logic {% else %} --other logic {% endif %}`
graph	The graph variable is dictionary that hold the information about the nodes(Models, Sources, Tests, Snapshots) in your dbt project.
model	This is the graph object of the current model. This object allows you to access the contents of a model, model structure and JSON schema, config settings, and the path to the model.
modules	This variable contains Python Modules for working with data including:datetime, pytz, re, and itertools. `modules.<desired_module>.<desired_function>` `{% set now = modules.datetime.datetime.now() %}`
project_name	This variable returns the name for the root-level project that is being run.
target	This variable contains information about your dbt profile target, such as your warehouse connection information. Use the dot notation to access more such as: target.name or target.schema or target.database, ect

dbt Core: Run context variables

These special variables provide information about the current context in which your dbt code is running, such as the model, schema, or project name.

Run Context Variablels
database_schemas	Only available in the context for on-run-end. This variable allows you to reference the databases and schemas. Useful if using multiple different databases
invocation_id	This function outputs a UUID every time you run or compile your dbt project. It is useful for auditing. You may access it in the query-comment, info dictionary in events and logs, and in the metadata dictionary in dbt artifacts.
results	Only available in the context for on-run-end. This variable contains a list of Results Objects. Allows access to the information populated in run results JSON artifact.
run_started_at	This variable outputs a timestamp for the start time of a dbt run and defaults to UTC. It Is a python datetime object. Use standard strftime formatting. `select '{{ run_started_at.strftime("%Y-%m-%d") }}' as run_start_utc`
schemas	Only available in the context for on-run-end. This variable allows you to reference a list of schemas for models built during a dbt run. Useful for granting privileges.
selected_resources	This variable allows you to access a list of selected nodes from the current dbt command. The items in the list depend on the parameters of —select, —exclude,—selector.
this	{{ this }} is the database representation of the current model. Use the dot notation to access more properties such as: {{ this.database }} and {{ this.schema }}.
thread_id	The thread_id is a unique identifier assigned to the Python threads that are actively executing tasks, such as running code nodes in dbt. It typically takes the form of names like "Thread-1," "Thread-2," and so on, distinguishing different threads.

dbt Core: Context methods

These methods allow you to retrieve information about models, columns, or other elements of your project.

Context Methods
set(value, default)	Allows you to use the python method set(), which converts an iterable into a unique set of values. This is NOT the same as the jinja expression set which is used to assign a value to a variable. This will return none if the value entered is not an iterable. In the example below both the python set() method and the jinja set expression are used to remove a duplicate element in the list. `{% set my_list = ['a', 'b', 'b', 'c'] %} {% set my_set = set(my_list) %} {% do print(my_set) %} {# evaluates to {'a','b', 'c'} #}`
set_strict(value)	Same as the set method above however it will raise a TypeError if the entered value is not a valid iterable. `{% set my_set = set_strict(my_list) %}`
exceptions	Is used to raise errors and warnings in a dbt run: raise_compiler_error will raise an error, print out the message. Model will FAIL. `exceptions.raise_compiler_error("Custom Error message")` warn will raise a compiler warning and print out the set method. Model will still PASS. `exceptions.warn("Custom Warning message")`
fromjson(string, default)	Is used to deserialize a JSON string into a python dict or list.
fromyaml(string, default)	Is used to deserialize a YAML string into a python dict or list.
tojson(value, default)	Serializes a Python dict or list to a JSON string.
toyaml(value, default )	Serializes a Python dict or list to a YAML string.
local_md5	This variable locally creates an MD5 hash of the given string. `{%- set hashed_value = local_md5(“String to hash”) -%} select '{{ hashed_value }}' as my_hashed_value`
zip(*args, default)	This method is allows you to combine any number of iterables. `{% set my_list_a = [1, 2] %} {% set my_list_b = ['Data', 'Coves'] %} {% set my_zip = zip(my_list_a, my_list_b) \| list %} {% do print(my_zip) %} {# Result [(1, 'Data'), (2, 'Coves')] #}`
zip_strict(value)	Same as the zip method but will raise a TypeError if one of the given values are not a valid iterable. `{% set my_list_a = 123 %} {% set my_list_b = 'Datacoves' %} {% set my_zip = zip_strict(my_list_a, my_list_b) \| list %} {# This will fail #}`

Please contact us with any errors or suggestions.

FAQ

What are config.meta_get and config.meta_requre how do they differ from config.get() differ from config.require()?

Starting with dbt 1.10, custom keys must be nested below the meta dictionary. In order to retreive these values, you must use config.meta_get('key', default='value') to fetch a meta dictionary config value and it can optionally return a default if it is not set. config.meta_require('key') strictly requires the config meta key to exist and throws a compilation error if it is missing. Use config.meta_get() for optional meta settings and config.meta_require() when a meta value is mandatory.

What are dbt Jinja filters and when should I use as_bool, as_number, and as_native?

dbt Jinja filters coerce Jinja output into specific Python types. as_bool converts a value to a boolean, as_number converts it to a numeric type, and as_native returns the native Python type such as a list, dict, or tuple. These are especially important when using env_var() since environment variables are always returned as strings. For example, {{ env_var('DB_PORT') | as_number }} ensures the port is treated as an integer, not a string.

‍

What does run_query() return and how do I use the result?

run_query() executes SQL and returns an Agate table object, not a Python list. To use the result, you need to extract values from it. The most common pattern is accessing the first column with .columns[0].values(). Always wrap run_query() inside an {% if execute %} block to avoid errors during the parse phase when no SQL has been run yet.

What does the execute variable do and why does it matter?

execute is set to True only when dbt is actually running SQL against the database (during dbt run or dbt test). During the parse phase, execute is False and no SQL is executed. This matters when your Jinja logic depends on query results. If you try to call run_query() without wrapping it in {% if execute %}, dbt will throw a compilation error during parsing because no data has been returned yet.

What is the config variable and how does config.get() differ from config.require()?

The config variable provides access to the configuration set for a dbt model. config.get('key', default='value') safely fetches a config value and returns a default if it is not set. config.require('key') strictly requires the config key to exist and throws a compilation error if it is missing. Use config.get() for optional settings and config.require() when a config value is mandatory for the model to run correctly.

What is the difference between ref() and source() in dbt?

ref() is used to reference other dbt models within your project. It builds the dependency graph and resolves the correct schema and table name at compile time. source() is used to reference raw data tables defined in your sources.yml file. Use ref() when pointing to a dbt model, and source() when pointing to upstream raw data. This distinction is how dbt tracks lineage end to end.

What is the difference between var() and env_var() in dbt?

var() pulls values defined in dbt_project.yml or passed via the CLI with --vars. It is best for project-level configuration like date ranges or feature flags. env_var() reads environment variables from the operating system or your dbt platform, making it suitable for secrets, credentials, and environment-specific settings. Never use env_var() directly in model SQL for sensitive values since those values may appear in compiled SQL or logs. Use DBT_ENV_SECRET_ prefixed variables for secrets.

What is the modules variable in dbt Jinja and what can it do?

The modules variable gives you access to built-in Python standard library modules directly inside Jinja. The available modules include datetime, pytz, re, and itertools. This makes it possible to work with dates, run regex operations, and manipulate iterables inside your macros without writing a Python model. A common use case is getting the current date: {% set today = modules.datetime.date.today() %}.

What is the target variable and what information can I access from it?

target contains information about your active dbt profile connection. You can access target.name (the name of your target, such as dev or prod), target.schema, target.database, and target.type (the warehouse adapter). It is widely used in macros to write conditional logic that behaves differently across environments, for example only enabling certain tests or materializations in production.

What is the this variable and how is it used?

{{ this }} represents the database object of the current model being compiled. It gives you access to this.database, this.schema, and this.name. It is most commonly used in incremental models to filter new rows against the existing table, for example: WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}). It saves you from hardcoding schema or table names.

When should I use log() vs print() in dbt macros?

Both write messages to stdout, but log(msg, info=True) writes to both the log file and stdout, while log(msg, info=False) writes only to the log file. print() is equivalent to log() with info=True. Use log() with info=False for verbose debugging you only want in logs. Use print() or log(..., info=True) when you need the message visible during an active dbt run.

‍