Environments and RBAC Across the Stack

A stack has more than one environment even if the repo has one branch. Developers need somewhere to build that nobody else can see. CI needs a schema it can throw away after every pull request. Production needs grants that don’t move. And all three have to touch the same warehouse without stepping on each other or leaking data sideways.

Up to now this series has run everything as one identity into one place — svc_dbt writing to a DBT_TPCH schema, svc_airflow loading raw. That’s fine for a proof of concept and a liability in production, where a single incident exposes the whole shortcut: every table owned by the wrong role, every dashboard pointed at a developer’s scratch schema, and no way to tell from Snowflake’s side who ran the query that spiked the bill. This post fixes that before it happens. Two databases, a dbt macro that keeps them straight, per-developer sandboxes, and a Snowflake grant model that says exactly who can read raw, who can write marts, and who can only look.

Two databases, one shape

The first decision is where dev and prod live. There are three options and only one is right for a small stack.

You can split by account — a whole separate Snowflake account for non-prod. That’s real isolation, and it’s overkill for one team: separate billing, separate users, separate everything to keep in sync, and you still need cross-account shares to test against prod-shaped data. You can split by schema inside one database — raw, staging, marts for prod and raw_dev, staging_dev for dev. That keeps one database but smears the environment into schema names, so a fully-qualified table like ANALYTICS.MARTS.orders no longer tells you whether it’s real. Or you can split by database: ANALYTICS_DEV and ANALYTICS_PROD, each with the same three schemas — RAW, STAGING, MARTS.

The database split is the sweet spot. The environment is the first identifier in every path, so ANALYTICS_PROD.MARTS.orders and ANALYTICS_DEV.MARTS.orders are unmistakably different things that are otherwise structurally identical. A grant scoped to ANALYTICS_PROD can’t accidentally reach dev. Tearing down a dev environment is DROP DATABASE ANALYTICS_DEV — one statement, no risk of clipping a prod schema that lived next door. And because the schema layout is identical across both, the same dbt project, the same models, the same ref() graph run against either one with nothing changed but the database name. That last property is the whole point: the environment is a coordinate, not a code path.

Where dbt puts things: generate_schema_name

dbt has an opinion about schema names that surprises everyone once. When a model sets +schema: marts, dbt does not build into a schema called marts. It calls a macro, generate_schema_name, and the built-in version concatenates your target schema with the custom one. If your profile’s schema: is dbt_prod and the model says marts, the table lands in dbt_prod_marts. That default exists to keep two developers who share a database from colliding, and it’s exactly wrong for a clean production layout, where you want the schema to be MARTS, full stop.

So every serious dbt project overrides it. Drop this in macros/generate_schema_name.sql:

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

    {%- if target.name == 'prod' -%}
        {{ custom_schema_name | trim }}

    {%- elif custom_schema_name is none -%}
        {{ target.schema }}

    {%- else -%}
        {{ target.schema }}_{{ custom_schema_name | trim }}

    {%- endif -%}

{%- endmacro %}

Read it as three cases. In prod, use the custom schema verbatim — a model tagged marts builds into MARTS, no prefix. In every other target, a model with no custom schema builds into the profile’s own schema:, and a model with a custom schema gets the concatenated form. The environment is decided entirely by target.name, which is the hook the rest of this post pulls on.

Pair it with the schema config in dbt_project.yml:

models:
  tpch:
    staging:
      +schema: staging
    marts:
      +schema: marts

Now the outcome depends only on which target you run:

Model	target: prod (schema PROD)	target: dev (schema dbt_alice)
stg_orders	ANALYTICS_PROD.STAGING	ANALYTICS_DEV.dbt_alice_staging
orders (mart)	ANALYTICS_PROD.MARTS	ANALYTICS_DEV.dbt_alice_marts
an un-tagged model	ANALYTICS_PROD.PROD	ANALYTICS_DEV.dbt_alice

Prod gets clean, stable schema names that a BI tool and a grant can rely on. Dev gets everything tucked under a per-developer prefix so nobody clobbers anybody. Same models, same macro — the only variable is the target.

Per-developer sandboxes

The dev column above hints at the pattern: every developer builds into a schema named after them. Alice’s models live in dbt_alice*, Bob’s in dbt_bob*, and neither can overwrite the other’s orders table because they’re in different schemas of the same ANALYTICS_DEV database. The way you get there is to make the profile’s schema: a per-developer value instead of a constant:

tpch:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      role: developer
      private_key_path: "{{ env_var('SNOWFLAKE_KEY_PATH') }}"
      private_key_passphrase: "{{ env_var('SNOWFLAKE_KEY_PASSPHRASE') }}"
      warehouse: TRANSFORMING_WH
      database: ANALYTICS_DEV
      schema: "dbt_{{ env_var('USER') }}"
      threads: 8

schema: "dbt_{{ env_var('USER') }}" reads the developer’s shell username, so Alice’s checkout resolves to dbt_alice and Bob’s to dbt_bob from the same committed profiles.yml. Note the user: and key come from the environment too — in dev each developer authenticates as themselves, with their own Snowflake login and their own key, not through the shared svc_dbt service user. That keeps QUERY_HISTORY honest even in development: a runaway query in a sandbox is attributable to a person.

For dbt to create dbt_alice on the fly, the developer role needs CREATE SCHEMA on ANALYTICS_DEV — dbt makes the schema if it’s missing on the first build. That grant is deliberately a dev-only privilege; the production transformer role, as we’ll see, gets no CREATE SCHEMA, because prod’s three schemas already exist and nothing should be inventing new ones at 2 a.m.

Two habits make sandboxes cheap to live with. First, dbt build --defer --state ./prod-artifacts lets Alice build only the handful of models she’s changed and resolve every unchanged ref() against production instead of rebuilding the whole warehouse into her sandbox — the same slim-CI trick from the production post, applied to a laptop. Second, sandboxes are disposable by construction: DROP SCHEMA ANALYTICS_DEV.dbt_alice CASCADE when she’s done, or a scheduled job that drops any dbt_* schema untouched for thirty days. Because the whole sandbox is one schema prefix, cleanup is one statement, not archaeology.

Cosmos picks the environment: target_name

Local dbt reads target: from profiles.yml. Airflow doesn’t run dbt that way — Cosmos builds the profile in memory from a connection, so it has to say which target this DAG is. That’s the target_name on ProfileConfig, and it’s the single dial that threads the environment all the way from Airflow through dbt into the schema macro and the grant model:

from cosmos import DbtDag, ProjectConfig, ProfileConfig, ExecutionConfig
from cosmos.profiles import SnowflakePrivateKeyFileProfileMapping


def tpch_dag(dag_id, target_name, conn_id, database):
    return DbtDag(
        dag_id=dag_id,
        project_config=ProjectConfig("/opt/airflow/dbt/tpch"),
        execution_config=ExecutionConfig(
            dbt_executable_path="/opt/airflow/dbt-venv/bin/dbt",
        ),
        profile_config=ProfileConfig(
            profile_name="tpch",
            target_name=target_name,
            profile_mapping=SnowflakePrivateKeyFileProfileMapping(
                conn_id=conn_id,
                profile_args={"database": database, "schema": "PROD"},
            ),
        ),
        schedule="@daily",
        start_date=datetime(2026, 6, 1),
        catchup=False,
    )


prod = tpch_dag("tpch_prod", "prod", "snowflake_prod", "ANALYTICS_PROD")
ci = tpch_dag("tpch_ci", "ci", "snowflake_ci", "ANALYTICS_DEV")

target_name="prod" sets target.name to prod inside every model, so the macro’s first branch fires and marts land in ANALYTICS_PROD.MARTS. The connection behind snowflake_prod logs in as svc_dbt with the transformer role. Swap the three arguments and the same project runs as a CI job into ANALYTICS_DEV under whatever identity snowflake_ci carries — the profile mapping reads the account, user, role, and key from the connection, and profile_args overlays the database. Nothing on disk changes between environments; the DAG factory is the environment map, expressed once in code. Which brings us to the identities themselves.

Functional roles vs access roles

Snowflake’s grant system is a graph — privileges go to roles, roles go to other roles, roles go to users — and the mistake everyone makes early is granting privileges straight to the role a user assumes. It works, and it rots. Six months later transformer has forty grants scattered across three databases, half of them added for a one-off, and nobody can say what removing one would break.

The pattern that survives is two layers of roles. Access roles are bundles of object privileges, and nothing else — “read the marts schema”, “write the raw schema”. They map one-to-one onto a thing you can do to a specific object, and they’re never granted to a user. Functional roles represent a job — ingest, transformer, reporter — and hold no direct object grants at all. They’re assembled by granting the access roles they need. Users only ever get functional roles.

The indirection buys two things. Auditing “who can read MARTS?” becomes “which functional roles hold ar_prod_marts_r?” — a single question with a list answer, instead of a crawl through every role’s grants. And composing a new job is granting existing access roles, not re-deriving privileges: a “read marts and staging” analyst role is two GRANT ROLE statements, reusing bundles you already trust.

The last piece of the convention is the top: every functional role is granted to SYSADMIN. SYSADMIN is Snowflake’s built-in role for managing objects, and because role grants inherit privileges upward, granting ingest, transformer, and reporter to SYSADMIN means SYSADMIN can do anything any of them can — so an admin can manage, test, and troubleshoot every environment from one role without anyone handing out ACCOUNTADMIN. Skip that grant and you get orphaned roles that only their service users can exercise, which is how you end up debugging prod as a bot.

Three jobs, three roles

The reason the stack needs distinct roles isn’t tidiness — it’s that ingest, transform, and read are genuinely different jobs with different blast radii, and collapsing them means the thing that loads a file can also drop a mart.

ingest writes raw and nothing else. Whatever lands bytes in Snowflake — svc_ingest running COPY INTO, or a Fivetran/Airbyte service user — needs USAGE on the database and the RAW schema, CREATE TABLE to land into, and USAGE + OPERATE on the loading warehouse. It has no grant anywhere near STAGING or MARTS. If an ingestion job goes haywire, the worst it can touch is raw, which is by definition reproducible from the source.

transformer reads raw and owns staging and marts. This is dbt’s role. It needs SELECT on RAW (its sources), and CREATE TABLE + CREATE VIEW on STAGING and MARTS (its outputs) — dbt decides table-vs-view per model, so it needs both. It gets USAGE + OPERATE on the transforming warehouse. Crucially it holds no INSERT on raw: dbt reads raw, it does not write it, and a role that can’t write raw can’t corrupt the source of truth no matter what a model does.

reporter only selects marts. The BI tool’s service user and human analysts assume this. It gets USAGE on the database and MARTS schema, SELECT on the tables and views there, and USAGE + OPERATE on a reporting warehouse — so dashboard queries run on separate compute from the pipeline and can’t slow a build. It has no path to raw or staging at all. A dashboard cannot read a half-built intermediate table because it was never granted the schema, and a curious analyst cannot query personally-identifiable columns that never made it out of raw.

The privileges themselves are worth naming precisely, because they’re the vocabulary of every grant below:

• USAGE on a warehouse lets a role submit queries to it; OPERATE lets it resume, suspend, and (with MODIFY) resize. Reader roles get USAGE and OPERATE but not MODIFY — they can wake a warehouse, not resize it.
• USAGE on a database and schema is the price of admission — without it, a role can’t even see the objects inside, let alone query them. Every grant below starts here.
• CREATE TABLE / CREATE VIEW on a schema lets a role build new objects in it, and the role becomes the owner of what it builds.
• SELECT on tables and views is read. Nothing more.

Future grants and managed access

There’s a gap in the model as described, and it’s the one that bites every stack that skips this section. reporter has SELECT on the tables in MARTS today. Tomorrow dbt ships a new model, revenue_by_region, and creates a brand-new table. Who granted reporter access to it? Nobody. SELECT ON ALL TABLES grants on the tables that existed when you ran it — it is not a standing rule. So the dashboard 404s on the new mart until someone re-runs the grant, and now every dbt deploy has a manual “and don’t forget to re-grant” step that someone will forget.

Future grants are the standing rule. GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_PROD.MARTS TO ROLE reporter says: any table created here from now on is automatically readable by reporter. dbt builds revenue_by_region, and reporter can query it the instant it exists, no human in the loop. You grant both — ALL for what’s already there, FUTURE for everything to come, and VIEWS as well as TABLES because dbt materializes either:

GRANT SELECT ON ALL    TABLES IN SCHEMA ANALYTICS_PROD.MARTS TO ROLE reporter;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_PROD.MARTS TO ROLE reporter;
GRANT SELECT ON ALL    VIEWS  IN SCHEMA ANALYTICS_PROD.MARTS TO ROLE reporter;
GRANT SELECT ON FUTURE VIEWS  IN SCHEMA ANALYTICS_PROD.MARTS TO ROLE reporter;

That closes the loop — but it opens a subtler question about ownership. When transformer creates a table, transformer owns it, and in plain Snowflake an object’s owner can grant privileges on it to anyone. So dbt, mid-run, technically holds the power to hand out access to the marts it builds. You almost certainly don’t want the transform role to also be a grant-management role; that’s authority creep hiding in a CREATE TABLE.

The fix is managed access schemas. Create the schema WITH MANAGED ACCESS and Snowflake takes grant authority away from object owners and centralizes it on the schema owner (plus roles with MANAGE GRANTS). Now transformer can still create and own marts — it just can’t grant on them. Only the central admin decides who reads MARTS, and it decides once, via the future grant. Ownership stops implying the right to share:

CREATE SCHEMA IF NOT EXISTS ANALYTICS_PROD.MARTS WITH MANAGED ACCESS;

Managed access and future grants are the pair that makes a self-serve pipeline safe: dbt builds freely, the reader role reads automatically, and nobody can widen access as a side effect of shipping a model.

The worked script

Here’s the whole thing for production, in the order Snowflake wants it — objects as SYSADMIN, roles and grants as SECURITYADMIN (which carries MANAGE GRANTS), users as USERADMIN. Run it once and the RBAC model above is real.

-- ── Compute and storage (SYSADMIN owns the objects) ───────────────
USE ROLE SYSADMIN;

CREATE WAREHOUSE IF NOT EXISTS LOADING_WH
  WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;
CREATE WAREHOUSE IF NOT EXISTS TRANSFORMING_WH
  WAREHOUSE_SIZE = 'SMALL'  AUTO_SUSPEND = 60 AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;
CREATE WAREHOUSE IF NOT EXISTS REPORTING_WH
  WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE DATABASE IF NOT EXISTS ANALYTICS_PROD;
CREATE SCHEMA IF NOT EXISTS ANALYTICS_PROD.RAW;
CREATE SCHEMA IF NOT EXISTS ANALYTICS_PROD.STAGING WITH MANAGED ACCESS;
CREATE SCHEMA IF NOT EXISTS ANALYTICS_PROD.MARTS   WITH MANAGED ACCESS;

-- ── Access roles: object privileges, nothing else ─────────────────
USE ROLE SECURITYADMIN;

CREATE ROLE IF NOT EXISTS ar_prod_raw_w;       -- write raw   (ingest)
CREATE ROLE IF NOT EXISTS ar_prod_raw_r;       -- read raw    (transform)
CREATE ROLE IF NOT EXISTS ar_prod_staging_all; -- build staging
CREATE ROLE IF NOT EXISTS ar_prod_marts_all;   -- build marts
CREATE ROLE IF NOT EXISTS ar_prod_marts_r;     -- read marts  (reporter)

-- write raw
GRANT USAGE ON DATABASE ANALYTICS_PROD          TO ROLE ar_prod_raw_w;
GRANT USAGE ON SCHEMA   ANALYTICS_PROD.RAW      TO ROLE ar_prod_raw_w;
GRANT CREATE TABLE ON SCHEMA ANALYTICS_PROD.RAW TO ROLE ar_prod_raw_w;

-- read raw
GRANT USAGE ON DATABASE ANALYTICS_PROD     TO ROLE ar_prod_raw_r;
GRANT USAGE ON SCHEMA   ANALYTICS_PROD.RAW TO ROLE ar_prod_raw_r;
GRANT SELECT ON ALL    TABLES IN SCHEMA ANALYTICS_PROD.RAW TO ROLE ar_prod_raw_r;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_PROD.RAW TO ROLE ar_prod_raw_r;

-- build staging (transformer owns what it creates here)
GRANT USAGE ON DATABASE ANALYTICS_PROD         TO ROLE ar_prod_staging_all;
GRANT USAGE ON SCHEMA   ANALYTICS_PROD.STAGING TO ROLE ar_prod_staging_all;
GRANT CREATE TABLE, CREATE VIEW ON SCHEMA ANALYTICS_PROD.STAGING
  TO ROLE ar_prod_staging_all;

-- build marts
GRANT USAGE ON DATABASE ANALYTICS_PROD       TO ROLE ar_prod_marts_all;
GRANT USAGE ON SCHEMA   ANALYTICS_PROD.MARTS TO ROLE ar_prod_marts_all;
GRANT CREATE TABLE, CREATE VIEW ON SCHEMA ANALYTICS_PROD.MARTS
  TO ROLE ar_prod_marts_all;

-- read marts, now and forever (the future grant that matters)
GRANT USAGE ON DATABASE ANALYTICS_PROD       TO ROLE ar_prod_marts_r;
GRANT USAGE ON SCHEMA   ANALYTICS_PROD.MARTS TO ROLE ar_prod_marts_r;
GRANT SELECT ON ALL    TABLES IN SCHEMA ANALYTICS_PROD.MARTS TO ROLE ar_prod_marts_r;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_PROD.MARTS TO ROLE ar_prod_marts_r;
GRANT SELECT ON ALL    VIEWS  IN SCHEMA ANALYTICS_PROD.MARTS TO ROLE ar_prod_marts_r;
GRANT SELECT ON FUTURE VIEWS  IN SCHEMA ANALYTICS_PROD.MARTS TO ROLE ar_prod_marts_r;

-- ── Functional roles: jobs, assembled from access roles ───────────
CREATE ROLE IF NOT EXISTS ingest;
CREATE ROLE IF NOT EXISTS transformer;
CREATE ROLE IF NOT EXISTS reporter;

GRANT ROLE ar_prod_raw_w       TO ROLE ingest;
GRANT ROLE ar_prod_raw_r       TO ROLE transformer;
GRANT ROLE ar_prod_staging_all TO ROLE transformer;
GRANT ROLE ar_prod_marts_all   TO ROLE transformer;
GRANT ROLE ar_prod_marts_r     TO ROLE reporter;

-- compute per job
GRANT USAGE, OPERATE ON WAREHOUSE LOADING_WH      TO ROLE ingest;
GRANT USAGE, OPERATE ON WAREHOUSE TRANSFORMING_WH TO ROLE transformer;
GRANT USAGE, OPERATE ON WAREHOUSE REPORTING_WH    TO ROLE reporter;

-- roll every job up to SYSADMIN so admins can manage it
GRANT ROLE ingest      TO ROLE SYSADMIN;
GRANT ROLE transformer TO ROLE SYSADMIN;
GRANT ROLE reporter    TO ROLE SYSADMIN;

-- ── Identities: users get functional roles only ──────────────────
USE ROLE USERADMIN;

CREATE USER IF NOT EXISTS svc_ingest
  DEFAULT_ROLE = ingest DEFAULT_WAREHOUSE = LOADING_WH;
CREATE USER IF NOT EXISTS svc_dbt
  DEFAULT_ROLE = transformer DEFAULT_WAREHOUSE = TRANSFORMING_WH;

USE ROLE SECURITYADMIN;
GRANT ROLE ingest      TO USER svc_ingest;
GRANT ROLE transformer TO USER svc_dbt;
GRANT ROLE reporter    TO USER bi_service;   -- BI tool
GRANT ROLE reporter    TO USER analyst_alice; -- and humans who read

Dev is the same shape with two deliberate differences — no managed access on the schemas (developers iterate too fast to route every grant through an admin), and a developer role that can make sandbox schemas:

USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS ANALYTICS_DEV;
CREATE SCHEMA   IF NOT EXISTS ANALYTICS_DEV.RAW;

USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS developer;

GRANT USAGE        ON DATABASE ANALYTICS_DEV TO ROLE developer;
GRANT CREATE SCHEMA ON DATABASE ANALYTICS_DEV TO ROLE developer;  -- dbt_<name>
GRANT USAGE ON SCHEMA ANALYTICS_DEV.RAW      TO ROLE developer;
GRANT SELECT ON ALL    TABLES IN SCHEMA ANALYTICS_DEV.RAW TO ROLE developer;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_DEV.RAW TO ROLE developer;
GRANT USAGE, OPERATE ON WAREHOUSE TRANSFORMING_WH TO ROLE developer;

GRANT ROLE developer TO ROLE SYSADMIN;
GRANT ROLE developer TO USER dev_alice;
GRANT ROLE developer TO USER dev_bob;

CREATE SCHEMA is what lets dbt spin up dbt_alice on first build; ownership of that schema then gives Alice full rein inside it and nowhere else. The production transformer role, by contrast, never gets CREATE SCHEMA — prod’s three schemas are fixed, and a role that can’t create schemas can’t scatter half-built experiments across the production database.

Final thoughts

The environment map isn’t a diagram you draw once and file away — it’s a set of grants that either exist or don’t. Write it down as a table anyway: for dev, CI, and prod, the database, the schema rule, the role, the warehouse, the secret path, and who owns it. That table is the spec; the script above is the implementation; generate_schema_name and Cosmos’s target_name are what keep code and spec pointing at the same place.

The payoff shows up the day something goes wrong, which is the only day RBAC design ever gets tested. A query spikes the bill and QUERY_HISTORY names the exact service user. A dashboard breaks and it’s obviously reporter missing a grant, not a mystery. A developer’s experiment stays in dbt_alice and never leaks into a mart. None of that is luck — it’s the difference between three roles that mean three things and one role that means “everything”, decided months earlier in a script nobody had to touch again. Get the boundaries right while the stack is small, because the first incident is exactly when you’ll have no time to draw them.

Next: Warehouses and Cost in Production