Cool dbt Feature: Project Variables
Photo by AltumCode on Unsplash

The most significant benefit of the project variable feature in dbt is that it enables us to avoid hardcoding information into our queries. Why is this advantageous? Well, consider the following scenario in the context of dbt-bigquery 1.6.4.

example for context

Let’s consider a sample play history of music data from Last FM. We’ll use this data source to create a dimensional table that contains a unique list of tracks.

Now, our objective is to determine what qualifies as a “favorite” track. For our definition, we’ll consider a track as a favorite if it has been played more than 50 times.

One way to approach this is to hardcode this definition directly into the query itself. Here’s an example of how it might look:

Query containing hardcoded definition of if_favorite


But what if, at some point, we decide to change the threshold for a “favorite” track to, say, 51 plays?

introduce project variables

If we ever need to update the definition, we would have to modify the query in the model. But what if we’re using this definition in multiple queries? Then making updates becomes tedious and prone to error.

To avoid the hassle of future changes, we use project variables. Simply define your project variable in the dbt_project.yml file:

Defintion of project variable fav_cut_off in dbt_project.yml


Once the variable is defined, we can use the project variable function in the model’s query:

Query containing dynamic definition of if_favorite using project variables


After our dbt run, we can refer to the target/compiled folder to confirm that the query matches our expectations.

types of variables

Project variables can take multiple forms. For example, we can use intergers, strings and dates.

Examples of intergers, strings and dates as project variables


However, it’s important to note that the value will be replaced by the Jinja placeholder. In the case of strings and dates, we still need to use quotations around the Jinja placeholder for the query to be valid.

Query containing hardcoded definition of if_favorite


When in doubt, simply run the model containing project variables and check the results in project’s target/compiled folder. Does it look like you expected? Happy querying.

Cool dbt Feature: Project Variables
Older post

Creating Tables with LOBs using dbt in Azure Synapse: The Columnstore Index Error

A work-around to the default settings when creating tables using the dbt-synapse adapter

Newer post

Berlin Marathon Data: 1974-2023

Berlin Marathon results among runners from 1974 to 2023, including data from Kaggle for the years 1974-2019 and additional results from 2021-2023

Cool dbt Feature: Project Variables