Ultimate dbt Jinja Functions Cheat Sheet

Last Updated:

October 4, 2023

October 4, 2023

Mayra Peña
Linkedin

Data Engineering | Technical Customer Success.
Solving enterprise data challenges quickly with dbt & Airflow.

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") }}'

Want more flexibility? Migrate your dbt Cloud project in under an hour.

Book a call

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.

Author:

Mayra Peña

Looking for an enterprise data platform?

Book a Demo

Table of Contents

Other Posts

Get our free ebook

dbt Cloud vs dbt Core

Comparing dbt Core and dbt Cloud? Download our eBook for insights on features, pricing, and total cost. Find the best fit for your business!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.