Variables and Environments

The Same Code Must Run Safely in More Than One Place

Every real dbt project eventually needs to run in more than one place: a developer iterating on their laptop, a continuous integration job validating a pull request, and a scheduled production job that stakeholders actually depend on for real numbers. All three of these need to run essentially the same dbt project code — the same models, the same tests, the same macros — but they absolutely cannot all point at the same database, schema, or warehouse. A developer testing a risky change to a core model should never be able to accidentally overwrite the productionfct_orders table that a live dashboard queries every morning.

The standard pattern for solving this is a small set of named environments — conventionallydev, staging (sometimes called ci), and prod — each pointing at a different database, schema, or warehouse, while running the exact same project code. The project itself does not need a different copy per environment. What changes between environments is where that code's output lands, and in some cases, small pieces of behavior that legitimately need to differ by environment — a lower row limit while iterating in dev, for instance.

dbt implements this environment pattern through profiles and targets. A profiles.yml file (kept outside the dbt project itself, usually in~/.dbt/profiles.yml locally, or configured directly in dbt Cloud) defines one or more named targets under a profile — each target specifying its own database connection details: account, warehouse, database, schema, and credentials. Which target is active for a given invocation of dbt is what actually determines whether you are running against dev, staging, or prod — the project's SQL files themselves never hardcode any of this.

freshmart:
  target: dev
  outputs:

    dev:
      type: snowflake
      account: freshmart_account
      user: "{{ env_var('DBT_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: transformer
      database: analytics_dev
      warehouse: dev_wh
      schema: dbt_jsmith
      threads: 4

    staging:
      type: snowflake
      account: freshmart_account
      user: "{{ env_var('DBT_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: transformer
      database: analytics_staging
      warehouse: ci_wh
      schema: dbt_ci
      threads: 8

    prod:
      type: snowflake
      account: freshmart_account
      user: "{{ env_var('DBT_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: transformer
      database: analytics_prod
      warehouse: prod_wh
      schema: analytics
      threads: 16

Every field that differs between these three targets — the database, the warehouse, the schema, even the thread count for parallelism — is exactly the kind of thing that should never be hardcoded inside a model's SQL. A model file that says select * from analytics_prod.raw.ordershas broken this entire pattern, because now that model can only ever run against production, regardless of which target is actually active. dbt's source() and ref()functions exist precisely so a model never needs to know which database or schema it is running against — that decision is made entirely by which target is active, external to the model's SQL.

target.name: Letting Model Logic Know Which Environment Is Running

Most of the time, a model should not need to know or care which target is active — that is the whole point of using ref() and source() instead of hardcoded table names. But there are legitimate cases where a model or macro's behavior genuinely needs to branch based on environment, and dbt exposes this through a built-in Jinja context variable calledtarget, with target.name giving you the name of the currently active target as a plain string — 'dev', 'staging', or 'prod', matching whatever the target is actually named in profiles.yml.

The classic use case: limiting data volume in dev for faster iteration

A common and genuinely useful pattern is limiting how much data a model processes while a developer is iterating locally, since running a full historical transformation over years of production data on every save-and-rerun cycle is slow and usually unnecessary for checking whether the SQL logic itself is correct.

select
    order_id,
    customer_id,
    order_status,
    order_total,
    ordered_at
from {{ source('app_db', 'orders') }}

{% if target.name == 'dev' %}
  -- Only look at the last 3 days of data while iterating locally --
  -- keeps local runs fast without changing the model's logic at all
  where ordered_at >= dateadd('day', -3, current_timestamp())
{% endif %}

Notice precisely what this does and does not do: it does not change what the model computes for staging or prod at all — the {% if %} block is entirely absent from the compiled SQL in those environments, and the model runs over the full source data exactly as it would without this conditional. Only in dev does the extra WHERE clause get compiled in, cutting the volume down dramatically for a faster local development loop.

A second common pattern: environment-specific materialization

Some teams also use target.name to make a model materialize as a lightweight view in dev (fast to create, cheap to throw away and recreate) but as a full table in prod (where query performance for downstream consumers matters more than build speed).

{{
    config(
        materialized = 'table' if target.name == 'prod' else 'view'
    )
}}

select
    customer_id,
    sum(order_total) as lifetime_value
from {{ ref('fct_orders') }}
group by 1

This keeps local development fast — a view compiles nearly instantly and always reflects the latest upstream logic without a rebuild — while ensuring the version stakeholders actually query in production gets the query-performance benefits of a materialized table.

vars: in dbt_project.yml, and --vars on the Command Line

Where target.name is specifically about environment identity, dbt varsare a general-purpose mechanism for passing configuration values into models and macros — values that are not secrets, and are not tied to which environment is running, but that still need to be configurable without editing SQL. A project-level default is set in dbt_project.ymlunder a top-level vars: key, and any individual invocation of dbt can override that default for just that one run using the --vars command-line flag.

name: 'freshmart'
version: '1.0.0'
config-version: 2

vars:
  start_date: '2020-01-01'
  payment_methods: ['credit_card', 'paypal', 'gift_card', 'bank_transfer']
  enable_new_discount_logic: false

Inside a model or macro, these values are read with the var() Jinja function, which takes the variable's name and, critically, an optional second argument giving a default value to fall back on if the variable is not set anywhere at all — not in dbt_project.yml, and not overridden via --vars.

select
    order_id,
    customer_id,
    order_total,
    ordered_at
from {{ ref('stg_orders') }}
where ordered_at >= '{{ var("start_date", "2000-01-01") }}'

Overriding start_date for one specific run, without touchingdbt_project.yml at all, is done with --vars on the command line, passed as an inline YAML dictionary:

dbt run --select stg_orders --vars '{"start_date": "2024-06-01"}'

This is genuinely useful for a one-off backfill or a targeted re-run without permanently changing the project's default behavior for every other invocation — the override in --varsonly applies to that specific command, and the next ordinary dbt run falls back to whatever dbt_project.yml declares.

Why the default argument to var() matters more than it looks

Calling var("start_date") with no second argument at all will raise a compilation error the moment start_date is not defined anywhere — which is exactly the right behavior for a variable that a model genuinely cannot run correctly without. But for anything where a sensible fallback exists, providing a default is what keeps a model safe to run in a context where nobody thought to set that variable at all — a new CI job that doesn't know about every var a mature project has accumulated, or a teammate running a model locally for the first time without a fully populated dbt_project.yml in front of them.

{% if var('enable_new_discount_logic', false) %}
  -- new promotional discount stacking rules
  case
    when d.discount_type = 'stacked' then d.discount_amount * 1.1
    else d.discount_amount
  end as final_discount_amount
{% else %}
  d.discount_amount as final_discount_amount
{% endif %}

Defaulting enable_new_discount_logic to false means any invocation of dbt that doesn't explicitly opt in — an unrelated CI job, an old scheduled run definition nobody has updated yet — gets the safe, existing behavior rather than accidentally picking up unfinished new logic simply because nobody remembered to set the flag for that particular run.

env_var(): Reading Real Operating-System Environment Variables

env_var() is a completely different mechanism from var(), even though the names look similar. env_var() reads an actual operating-system-level environment variable — something set outside of dbt entirely, by your shell, your CI system's secrets manager, or your orchestration tool — rather than anything declared inside the dbt project's own YAML files. This makes it the correct tool for exactly one job: getting secrets and infrastructure-specific values into dbt without ever committing them to source control.

freshmart:
  target: prod
  outputs:
    prod:
      type: snowflake
      account: freshmart_account
      user: "{{ env_var('DBT_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: transformer
      database: analytics_prod
      warehouse: prod_wh
      schema: analytics
      threads: 16

Nowhere in this file is an actual password. {{ env_var('SNOWFLAKE_PASSWORD') }}tells dbt to look up an environment variable literally named SNOWFLAKE_PASSWORD at run time, wherever dbt happens to be running — a developer's own shell (where they've exported it in their own local environment, never committed anywhere), or a CI system's secrets store injected as an environment variable just for that job's execution. The actual secret value never touches the dbt project's files at all, and profiles.yml stays safe to keep in version control (or is itself excluded from the repository entirely, which many teams also do as an extra layer of caution).

export DBT_USER=jsmith
export SNOWFLAKE_PASSWORD=a-real-secret-value-never-committed

dbt run --target prod

env_var() also accepts an optional default value as a second argument, exactly likevar() does — but reaching for a default here deserves more caution than it does with project vars. A default for a genuine secret like a password almost never makes sense (there is no safe fallback for a missing credential), whereas a default for a non-secret, infrastructure-level setting — a warehouse size, a target concurrency limit — can be perfectly reasonable.

{{ env_var('SNOWFLAKE_WAREHOUSE_SIZE', 'X-SMALL') }}

Two Mechanisms That Look Similar and Solve Different Problems

Beginners very commonly conflate vars and env_var() because both are ways of getting an external value into a dbt run, and both have a similar-looking function-call syntax in Jinja. The actual distinction is about where the value lives and what kind of value it should ever be used for, and getting this wrong has real consequences — usually a secret ending up somewhere it should never be.

A subtler distinction worth internalizing: vars are dbt-native and dbt-aware — dbt itself resolves precedence between dbt_project.yml and --vars, andvar() is a first-class part of the Jinja context dbt provides. env_var(), by contrast, is dbt's bridge out to something entirely outside of dbt's own configuration system — the operating system's process environment, which dbt neither controls nor validates beyond simply reading it. This is exactly why env_var() is the right (and really the only correct) tool for anything that must be managed by infrastructure and secrets tooling rather than by the dbt project's own configuration files.

generate_schema_name: Sending Dev Runs to Per-Developer Schemas

By default, when a model does not set an explicit schema config, dbt builds it into whatever schema is configured on the active target in profiles.yml. This is fine for prod, where everything landing cleanly in one shared, well-known schema is exactly what you want. It becomes a problem in dev the moment more than one developer is working against the same database: if every developer's target schema is the same, one developer's in-progress, possibly broken model can silently collide with — or overwrite — another developer's tables of the exact same name.

The standard fix is a custom schema config on a model combined with overriding dbt's built-in generate_schema_name macro — the macro dbt actually calls, for every single model, to compute the schema it should build into. Overriding it project-wide lets you route dev runs into a per-developer schema (commonly named after the developer, likedbt_jsmith) while prod runs land in the clean, shared target schema with no suffix at all.

{% macro generate_schema_name(custom_schema_name, node) -%}

    {%- set default_schema = target.schema -%}

    {%- if target.name == 'prod' -%}

        {#- In prod, ignore any custom schema config entirely and always
            build into the clean target schema -#}
        {{ default_schema }}

    {%- else -%}

        {#- In dev and staging, build into a schema unique to this developer
            or run, so nobody's in-progress work collides with anyone else's -#}
        {{ default_schema }}_{{ target.name }}

    {%- endif -%}

{%- endmacro %}

With target.schema set to dbt_jsmith in one developer's owndev target (as configured back in Part 01's profiles.yml), and this override in place, that developer's models land in dbt_jsmith_dev — clearly separated from a teammate's own dbt_asharma_dev schema, and both entirely separate fromanalytics, the clean prod schema every dashboard actually queries.

This connects directly to the schema configuration options a model itself can set, covered in the materializations module — a model's own schema: config (via custom_schema_namein the macro above) can still request a specific sub-schema for organizational reasons, and this macro decides how that request is actually honored differently per environment, rather than replacing model-level schema configuration entirely.

The Same Codebase, Three Environments, What Actually Differs at Each Layer

Bringing every mechanism in this module together: here is exactly what changes, and what stays identical, as the same dbt project runs across dev, CI, and prod for a fictional grocery-delivery company's order pipeline.

name: 'freshmart'
version: '1.0.0'
config-version: 2

vars:
  start_date: '2020-01-01'
  payment_methods: ['credit_card', 'paypal', 'gift_card', 'bank_transfer']
  enable_new_discount_logic: false

select
    order_id,
    customer_id,
    order_status,
    order_total,
    ordered_at
from {{ source('app_db', 'orders') }}
where ordered_at >= '{{ var("start_date") }}'

{% if target.name == 'dev' %}
  and ordered_at >= dateadd('day', -3, current_timestamp())
{% endif %}

What is worth noticing across this whole table: not one line of any model's SQL had to change to get this behavior across three environments. Every difference is driven entirely by which target is active (target.name, target.schema), which variables are set at the project or CLI level (var()), and which secrets are present in the surrounding process environment (env_var()) — exactly the separation of concerns this module set out to build. A developer can run this exact codebase locally, break things repeatedly, and never once put production data or credentials at risk.

How env_var() Maps Onto dbt Cloud's Environments and Jobs UI

Everything in Part 04 assumed env_var() reads a value set some external way — a shell export, a CI secrets manager. Teams running on dbt Cloud instead of self-hosted orchestration get a purpose-built version of exactly that mechanism: an Environment Variablespanel in the dbt Cloud project settings, where a variable's name and value are entered directly in the UI, then read inside the project with the exact same {{ env_var('...') }}call covered in Part 04. Nothing about how a model or profiles.yml reads the value changes — what changes is where the value is set and who can see it.

The one dbt-Cloud-specific convention worth knowing: prefixing a variable's name withDBT_ENV_SECRET_ tells dbt Cloud to treat its value as sensitive — it is redacted from run logs and from any compiled SQL shown in the UI, even in contexts where compiled SQL is normally visible for debugging. A variable without that prefix is still only settable by someone with project-admin access, but its value is not specially redacted from logs, so it is the wrong place for an actual credential.

-- dbt Cloud manages the underlying profiles.yml equivalent for you when
-- you connect a warehouse through its UI, but a var referenced in project
-- code (e.g. inside a macro building a connection string, or a seed
-- config) uses the identical syntax as any other env_var() call:

{{ env_var('DBT_ENV_SECRET_SNOWFLAKE_PASSWORD') }}

-- Set in dbt Cloud's Project Settings → Environment Variables, scoped to
-- the Production environment specifically -- a Development-environment
-- job reading the same variable name gets whatever value was set for
-- Development instead, without any change to the project's own code.

This gives dbt Cloud projects the same dev/staging/prod separation this whole module has been building toward, but configured through a UI instead of separate profiles.yml targets and shell exports — the same target.name-driven branching in Part 02 and the sameenv_var() secret-handling discipline in Part 04 still apply underneath; only the mechanism for actually setting the values differs.

Building Only What Changed: Comparing a PR's Models Against Production State

Every mechanism so far in this module (targets, vars, env_var(), per-developer schemas) answers "how do dev, CI, and prod stay safely separate." This Part answers a related but distinct question that only comes up once a project has grown large: a CI job that rebuilds the entire project on every pull request, to validate even a one-line change to a single model, becomes slow and expensive in direct proportion to how big the project has gotten — exactly the same problem incremental models solve for a single table, but here the "table" is the whole CI run.

Slim CI is the standard fix: instead of building every model from scratch in CI, dbt is told which models actually changed in this pull request, builds only those (plus whatever depends on them), and for everything else it silently reads straight from the equivalent object already sitting in the production schema — without rebuilding it, and without it ever leaving production. Two flags make this possible together: --state, which points dbt at a previous manifest.json (typically production's) to diff the current project against, and --defer, which tells dbt that any upstream model this PR does not rebuild should resolve its {{ ref() }} calls against that deferred manifest's already-built relations instead of failing because the CI schema never built them.

# 1. Pull production's manifest.json down as the comparison baseline
#    (dbt Cloud does this automatically for you; self-hosted CI needs an
#    explicit step to fetch the artifact from the last successful prod run)

dbt build \
  --select state:modified+ \
  --defer \
  --state ./prod-manifest

Reading this command piece by piece: state:modified+ is a selector meaning "every model whose compiled SQL, config, or referenced macros differ from the version recorded in./prod-manifest, plus everything downstream of those models" (the trailing+ is the same graph operator used elsewhere in dbt's selector syntax). --defertells dbt that any model not selected — meaning it is identical to production and does not need rebuilding — should have its ref() calls resolved against production's actual built relation from --state's manifest, rather than an object that was never built in this CI run's own temporary schema at all.

This connects directly back to target.name-based branching from Part 02: a CI job using slim CI typically still runs against its own isolated staging/citarget and schema for the models it does rebuild — --defer only changes howunselected models are resolved, it does not mean CI writes into production. The two mechanisms compose: environment separation keeps CI's own writes isolated, while slim CI keeps CI from having to redundantly rebuild everything production already has correctly built.

The one thing that makes slim CI actually work is having a trustworthy manifest.jsonto diff against in the first place — which is why dbt Cloud automatically stores the manifest from every successful production run specifically so the next CI job can compare against it, and why a self-hosted setup needs its own equivalent step (uploading the prod manifest as a build artifact after every successful production deploy, then downloading it at the start of the next CI job) for--state to have anything meaningful to point at.

--profile, --target, and --profiles-dir: the Full Resolution Order

Every earlier Part in this module assumed a single, obvious answer to "which target is active" — usually whatever target: defaults to in profiles.yml, per Part 01. In practice, several different flags and files can all influence that answer at once, and a project with more than one profile, or a CI system invoking dbt with explicit overrides, needs the actual resolution order to reason about which target a given invocation will use.

A closely related but separate question is which profile (as opposed to which target within a profile) is active at all — relevant the moment more than one dbt project, or more than one named profile, exists on the same machine or in the same CI environment.

name: 'freshmart'
version: '1.0.0'
config-version: 2

profile: 'freshmart'   # must match a top-level key in profiles.yml

vars:
  start_date: '2020-01-01'

profile: 'freshmart' in dbt_project.yml tells dbt which top-level key to look up inside profiles.yml — the same profiles.yml shape shown back in Part 01, where freshmart: was the top-level key holding the dev,staging, and prod targets. This matters distinctly fromtarget: profile selects which company/project's whole set of targets to use, while target selects which one of that set's targets is active. A monorepo running two separate dbt projects, or a consultant working across multiple clients' projects on one laptop, needs both dimensions resolved correctly and independently.

# Normally resolves via dbt_project.yml's profile: 'freshmart' declaration:
dbt run

# Explicitly override which profile's targets to use for this invocation,
# without editing dbt_project.yml -- useful when testing a project against
# a differently-named profile temporarily:
dbt run --profile freshmart_sandbox --target dev

Finally, profiles.yml itself has to be found somewhere on disk, and that location is resolved independently of both profile and target. By default dbt looks in ~/.dbt/profiles.yml, but --profiles-dir (or theDBT_PROFILES_DIR environment variable) can point it anywhere else entirely — common in CI, where a job may write a freshly generated profiles.yml into a temporary, job-scoped directory rather than relying on a persistent home directory that might not even exist in an ephemeral container.

mkdir -p /tmp/dbt_ci_profile

cat <<EOF > /tmp/dbt_ci_profile/profiles.yml
freshmart:
  target: staging
  outputs:
    staging:
      type: snowflake
      account: freshmart_account
      user: "${SNOWFLAKE_CI_USER}"
      password: "${SNOWFLAKE_CI_PASSWORD}"
      role: transformer
      database: analytics_staging
      warehouse: ci_wh
      schema: dbt_ci
      threads: 8
EOF

dbt run --profiles-dir /tmp/dbt_ci_profile

Assembling profiles.yml at run time from CI secrets like this, rather than committing any version of it to the repository at all, is a common pattern precisely because it keeps every connection detail — including ones that are not exactly "secret" but are still environment-specific, like warehouse or schema — entirely out of version control, generated fresh for each job from whatever the CI system's own secret store already holds.

A worked resolution trace, end to end

Putting the whole chain together for one concrete invocation makes the precedence order concrete rather than abstract. Suppose a CI job runs the following, in a container that has no~/.dbt/profiles.yml at all, and whose generated profiles.yml declarestarget: "{{ env_var('DBT_TARGET', 'dev') }}" instead of a hardcoded target name:

DBT_TARGET=staging dbt run --profiles-dir /tmp/dbt_ci_profile

# Step 1 -- which profiles.yml does dbt read at all?
#   --profiles-dir was passed explicitly -> /tmp/dbt_ci_profile/profiles.yml
#   (the default ~/.dbt/profiles.yml is never even considered)
#
# Step 2 -- which profile (top-level key) inside that file?
#   No --profile flag was passed, so dbt falls back to dbt_project.yml's
#   own profile: 'freshmart' declaration
#
# Step 3 -- which target inside that profile?
#   No --target flag was passed. dbt itself has no built-in DBT_TARGET
#   variable, but THIS profiles.yml was deliberately written with
#   target: "{{ env_var('DBT_TARGET', 'dev') }}" -- so dbt resolves that
#   Jinja expression, reads DBT_TARGET=staging from the environment, and
#   the profile's own target: setting evaluates to 'staging'
#
# Resolved: profiles.yml at /tmp/dbt_ci_profile, profile 'freshmart',
# target 'staging' -- because this project chose to wire target: through
# env_var(), not because dbt reads DBT_TARGET automatically.

Every one of these three questions — which file, which profile, which target — is resolved completely independently of the other two, which is exactly why a confusing "why did this run against the wrong environment" incident is worth tracing through all three separately withdbt debug rather than assuming any one setting alone explains the outcome.

Five Misconceptions About dbt Variables and Environments

What This Looks Like on Day One

5 Interview Questions — With Complete Answers

Five Mistakes That Compromise Environment Safety

Variable and Environment Errors — And Exactly Why They Happen