DEV Community

Sagara
Sagara

Posted on

No More Manual --vars: Trying Out dbt Projects on Snowflake's New SQL Environment Variables (env.yml)

#snowflake #dbt

Original (Japanese): https://dev.classmethod.jp/articles/dbt-projects-on-snowflake-with-sql-environment-variables/

This is Sagara.

A new feature for dbt Projects on Snowflake has been released: SQL Environment Variables. This feature allows you to define per-environment variables in a project-root file called env.yml, where you can embed and evaluate SQL such as CURRENT_USER().

https://docs.snowflake.com/en/release-notes/2026/other/2026-07-23-dbt-projects-on-snowflake-environment-variables

https://docs.snowflake.com/en/user-guide/data-engineering/dbt-projects-on-snowflake-environment-variables

Previously on this blog, I introduced a way to dynamically specify per-user development schemas using the Workspace's --vars flag.

https://dev.classmethod.jp/articles/dbt-projects-on-snowflake-vars/

While this approach works, it required manually setting --vars '{"dev_schema": "dbt_ssagara"}' for each user in the Workspace's execution command field, and this had to be re-configured for each command and Run/Build type. With the new SQL Environment Variables, I thought that since we can now evaluate CURRENT_USER() on the env.yml side to construct the schema name, this --vars configuration itself might no longer be necessary. So I actually tried it out.

Feature Overview

env.yml is a Git-manageable configuration file placed at the root of the dbt Projects on Snowflake project (at the same level as dbt_project.yml). You can define named configurations (environments) for each environment, and each environment's env: section can specify not only plain text values but also SQL that returns a single row, single-column VARCHAR.

env_config:
  default_environment: dev

  environments:
    - name: dev
      env:
        DBT_TARGET_SCHEMA: "{{ select 'DBT_DEV_' || UPPER(CURRENT_USER()) }}"
Enter fullscreen mode Exit fullscreen mode

The key point is the timing of evaluation. When you run a dbt project, Snowflake first resolves env.yml and injects the values as environment variables into the execution context, and only then does dbt Core itself start up. In other words, CURRENT_USER() is evaluated using the outer execution context of the executing user and role in the Workspace, before dbt runs. The value determined on the env.yml side is then referenced from profiles.yml using something like env_var('DBT_TARGET_SCHEMA').

schema: "{{ env_var('DBT_TARGET_SCHEMA') }}"
Enter fullscreen mode Exit fullscreen mode

The great thing about this update is that values previously passed via --vars as var('dev_schema') can now be fully self-contained within env.yml.

Limitations

  • env.yml has a 2MB size limit (roughly equivalent to 12,000 lines)
  • All key names within env: must be uppercase and start with DBT_. Also, key names (left-hand side) must be plain text only; SQL cannot be used
  • The only Jinja pattern allowed in env: values is {{ select ... }}; loops, conditionals, filters, etc. cannot be used. Also, macros such as ref() and source() are prohibited anywhere within env.yml
  • If you use flags such as --env-file-dir in a CI/CD workflow, Snowflake CLI 3.21 or later is required
  • The priority order for environment variable values is: "runtime specification such as --env-vars > shell environment variables (only when using --use-shell-env-vars) > values in env.yml"

Preparation

1. Preparing Snowflake Objects

For this verification, I'll use the following objects. If you already have similar ones, feel free to substitute accordingly.

Type Name
Warehouse DBT_ENVVAR_DEMO_WH
Database DBT_ENVVAR_DEMO_DB
Developer role DBT_ENVVAR_DEV_ROLE
Output destination schema (pre-created) DBT_DEV_SAGARA_SATOSHI
Executing user SAGARA_SATOSHI

As ACCOUNTADMIN, create the warehouse, database, and role.

USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE WAREHOUSE DBT_ENVVAR_DEMO_WH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE;

CREATE OR REPLACE DATABASE DBT_ENVVAR_DEMO_DB;

CREATE OR REPLACE ROLE DBT_ENVVAR_DEV_ROLE;
Enter fullscreen mode Exit fullscreen mode

Grant usage permissions on the warehouse and database to the role.

GRANT USAGE ON WAREHOUSE DBT_ENVVAR_DEMO_WH
  TO ROLE DBT_ENVVAR_DEV_ROLE;

GRANT USAGE ON DATABASE DBT_ENVVAR_DEMO_DB
  TO ROLE DBT_ENVVAR_DEV_ROLE;
Enter fullscreen mode Exit fullscreen mode

Also create the output destination schema.

CREATE SCHEMA IF NOT EXISTS DBT_ENVVAR_DEMO_DB.DBT_DEV_SAGARA_SATOSHI;

GRANT USAGE ON SCHEMA DBT_ENVVAR_DEMO_DB.DBT_DEV_SAGARA_SATOSHI
  TO ROLE DBT_ENVVAR_DEV_ROLE;

GRANT CREATE TABLE ON SCHEMA DBT_ENVVAR_DEMO_DB.DBT_DEV_SAGARA_SATOSHI
  TO ROLE DBT_ENVVAR_DEV_ROLE;
Enter fullscreen mode Exit fullscreen mode

Finally, grant the role to the verification user SAGARA_SATOSHI.

GRANT ROLE DBT_ENVVAR_DEV_ROLE TO USER SAGARA_SATOSHI;
Enter fullscreen mode Exit fullscreen mode

2. Adding env.yml to the dbt Project

Create a dbt project as usual in the Workspace.

2026-07-24_21h47_46

Add env.yml at the same project root level as dbt_project.yml. The project structure should look something like this:

your_dbt_project/
├── dbt_project.yml
├── env.yml
├── models/
│   └── envvar_schema_probe.sql
└── profiles.yml
Enter fullscreen mode Exit fullscreen mode

Here's the content of env.yml:

env_config:
  default_environment: dev

  environments:
    - name: dev
      env:
        DBT_TARGET_SCHEMA: "{{ select 'DBT_DEV_' || UPPER(CURRENT_USER()) }}"
Enter fullscreen mode Exit fullscreen mode

When executed as SAGARA_SATOSHI, using the evaluated result of CURRENT_USER(), the environment variable DBT_TARGET_SCHEMA is expected to have the following value:

DBT_DEV_SAGARA_SATOSHI
Enter fullscreen mode Exit fullscreen mode

3. Changing the Schema Setting in profiles.yml

Change the part that previously referenced var('dev_schema') in the previous article to env_var('DBT_TARGET_SCHEMA').

<project_name>:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: ""
      user: ""
      role: DBT_ENVVAR_DEV_ROLE
      warehouse: DBT_ENVVAR_DEMO_WH
      database: DBT_ENVVAR_DEMO_DB
      schema: "{{ env_var('DBT_TARGET_SCHEMA') }}"
      threads: 8
Enter fullscreen mode Exit fullscreen mode

Here's the diff before and after the change:

# Before: referencing a value passed via --vars
schema: "{{ var('dev_schema') }}"

# After: referencing the environment variable resolved by env.yml
schema: "{{ env_var('DBT_TARGET_SCHEMA') }}"
Enter fullscreen mode Exit fullscreen mode

4. Creating a Model for Verification

Let's create a model that lets us check the environment variable, the schema resolved by dbt as target.schema, and the executing Snowflake user, all within a single table.

models/envvar_schema_probe.sql

{{ config(materialized='table') }}

SELECT
  '{{ env_var("DBT_TARGET_SCHEMA") }}' AS env_target_schema,
  '{{ target.schema }}' AS dbt_target_schema,
  CURRENT_USER() AS session_user,
  CURRENT_ROLE() AS session_role,
  CURRENT_TIMESTAMP() AS built_at
Enter fullscreen mode Exit fullscreen mode

The columns we want to check in this model are as follows:

Column What to Check
ENV_TARGET_SCHEMA Schema name injected from env.yml
DBT_TARGET_SCHEMA Schema name resolved as dbt's target
SESSION_USER Snowflake user at the time of model execution
SESSION_ROLE Role at the time of model execution
BUILT_AT Time when the model was executed

Trying It Out

1. Running dbt from the Workspace (without specifying --vars)

In the Run panel, configure the following. The key point here—unlike the article I wrote previously—is that we don't configure any Additional flags at all.

2026-07-24_21h52_06

If it runs without any errors, we're good.

2026-07-24_21h53_18

2. Checking the Execution Results

If the execution succeeds, the table should be output to a schema that includes the username, as shown below.

2026-07-24_21h54_36

Let's check the results in Snowflake. We get the expected results.

USE ROLE DBT_ENVVAR_DEV_ROLE;

SELECT *
FROM DBT_ENVVAR_DEMO_DB.DBT_DEV_SAGARA_SATOSHI.ENVVAR_SCHEMA_PROBE;
Enter fullscreen mode Exit fullscreen mode

2026-07-24_21h56_20

Cleanup

If you don't need the objects created during this verification, run the following as ACCOUNTADMIN to delete them:

USE ROLE ACCOUNTADMIN;

DROP DATABASE IF EXISTS DBT_ENVVAR_DEMO_DB;
DROP WAREHOUSE IF EXISTS DBT_ENVVAR_DEMO_WH;
DROP ROLE IF EXISTS DBT_ENVVAR_DEV_ROLE;
Enter fullscreen mode Exit fullscreen mode

Conclusion

I tried using SQL Environment Variables in dbt Projects on Snowflake to see if I could achieve per-developer output schema switching without --vars.

By simply evaluating CURRENT_USER() on the env.yml side and referencing it via env_var() in profiles.yml, I was able to confirm that the previous operational approach—manually setting --vars for each user in the Workspace, as described in my earlier article—is no longer necessary. As the number of developers increases, it becomes easier to have gaps or missed configurations with --vars, so being able to consolidate this logic into a Git-managed env.yml felt like a real win.

I think many people set up per-developer schemas when developing with dbt, so I hope you'll find this useful!

Top comments (0)