dbt variable interpolation

Overview

Mage allows users to reference variables specific to your pipeline or project. These variables are accessible throughout your dbt profiles, and pipelines by using the following syntax.

Syntax	Description	Example
{{ env_var('...') }}	Get from environment variables.	{{ env_var('ENV') }}
{{ variables('...') }}	Get from runtime variables.	{{ variables('execution_date') }}

DBT also comes with variable and environment variable support. By default we forward pipeline variables as dbt Project variables.

Variable interpolation examples

dbt models

  SELECT
    *
  # get the global environment variable for prod/dev
  FROM {{ env_var('ENV') }}_users.accounts
  WHERE
    # limit query based on the runtime variable, execution_date
    created_at BETWEEN '2023-01-01' AND {{ var('execution_date') }}

dbt profiles

jaffle_shop:
  target: dev
  outputs:
    dev:
      type: postgres
      host: host.docker.internal
      user: postgres
      password: postgres
      port: 5432
      dbname: postgres
      schema: {{ variables('DBT_SCHEMA') }}
      threads: 4

dbt targets

Here, we’ve used a manually entered target to specify a postgres database dependent on the environment postgres_{{ env_var('ENV') }}.

Environment	Key	Example string	Result
prod	ENV	postgres_{{ env_var('ENV') }}	postgres_prod
dev	ENV	postgres_{{ env_var('ENV') }}	postgres_dev