Designing account-level isolation for a multi-environment Snowflake account: one environment, one identity, one grant at a time. Nine databases, fifty-five roles, and a handful of controls I built the shape for before I had the infrastructure to turn them on.
Snowflake account provisioning · 15-section script, annotated
At a glance, Dev · Stg · Prod (same shape in each): 3 databases, 5 schemas, 17 roles, 1 warehouse per environment. Plus 4 roles that aren't tied to any one environment (DATA_ENGINEER_TEAM_ROLE, HUMAN_ENGINEER_USER_ROLE, and two PII classification roles) and the account-wide GOVERNANCE database, for 55 roles and 9 environment databases in total.
Contents
Layers × environments · Compute isolation · Cost governance · RBAC · Identity isolation · Humans vs. machines · Hardening identities · Network policy · PII masking · Honesty as architecture
One Snowflake account. Three environments: dev, stg, and prod. Three layers: raw, refine, and curate. Put those together and you get a real design problem before a single query runs. How do you stop a test load in dev from ever touching prod? How do you make sure a leaked dev credential can't be used to reach prod? How do you let an analyst read data without also letting them run the kind of DDL only an engineer should touch? And how do you do all of that without a permissions spreadsheet that goes stale the week after you write it?
This document walks through fine-grained account provisioning, access control, and cost isolation for a multi-environment Snowflake account, where everyone in the organization needs some kind of access, and the real problem is controlling, precisely, who can touch what. Nothing here depends on a specific tool: every role and user name below is a placeholder for whatever actually connects to your Snowflake account, so swap the names and keep the pattern. Each section follows the same structure: why I made a decision, then the SQL that implements it. Where a control isn't switched on yet, I explain what it's waiting on and how to turn it on.
What you'll take away:
- A naming convention that makes "which environment, which layer" a property of an object's path, not a lookup table someone has to maintain.
- Why compute isolation matters even when every workload reads the same data, and how to size a warehouse per consumer instead of per layer.
- How schema-level least privilege actually pays for itself, and where it's overkill for a small team.
- The single highest-leverage identity decision: one service account per environment, never one shared account with role-switching.
- How to design different access, and different auth, for machines versus humans, and how to build governance controls you can't enforce yet so turning them on later is a one-line change.
Layers × environments
Why: I put raw, refine, and curate in separate databases per environment, not separate schemas inside one shared database. A database boundary is where Snowflake's grant model and its cost/usage accounting both naturally fall out for free: I get "who can touch curated data" and "how much did the curate layer cost this month" as properties of the object hierarchy, instead of a discipline everyone has to remember (query tags, naming conventions) to reconstruct later. It also means the environment axis and the layer axis compose cleanly into a grid, instead of one dimension being a database and the other an afterthought inside it.
The naming convention does the actual work. Every database follows <LAYER>_<ENV>; every schema underneath it groups by source system or business domain. Nothing needs a lookup table to know where an object belongs: the name says it:
CREATE DATABASE IF NOT EXISTS RAW_DEV -- raw layer
CREATE DATABASE IF NOT EXISTS REFINED_DEV -- refine layer
CREATE DATABASE IF NOT EXISTS CURATED_DEV -- curate layer
-- repeated once per environment: swap _DEV for _STG / _PROD
CREATE SCHEMA IF NOT EXISTS RAW_DEV.BILLING;
CREATE SCHEMA IF NOT EXISTS RAW_DEV.USAGE;
CREATE SCHEMA IF NOT EXISTS REFINED_DEV.BILLING;
CREATE SCHEMA IF NOT EXISTS REFINED_DEV.USAGE;
CREATE SCHEMA IF NOT EXISTS CURATED_DEV.SAAS;
-- repeated once per environment
A table's full path (RAW_DEV.BILLING.CUSTOMERS, say) tells you its environment, its layer, and its source system before you've run a single query against it. Whatever builds inside these schemas never has to compute or guess a name; it just connects to the database that matches its own environment.
Compute isolation
Why: A warehouse is pure compute; it has no bearing on which database a query touches. So isolating warehouses by workload, not by layer, stops an analyst's ad hoc query, an engineer's interactive debugging session, and a scheduled automated build from contending for the same cluster or landing on the same bill, even when all three are reading the exact same prod tables at the exact same moment.
CREATE WAREHOUSE IF NOT EXISTS TRANSFORMING_DEV_WH
WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE
-- automated build workloads only, never a human query;
CREATE WAREHOUSE IF NOT EXISTS DATA_ENGINEER_TEAM_WH
WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE
-- DATA_ENGINEER_TEAM_ROLE only, never shared with the transformer service;
Four warehouses in total: one per environment (dev/stg/prod), plus one for the human role below. AUTO_SUSPEND at 60–120s and INITIALLY_SUSPENDED keep every one of them billing at zero until something actually queries it.
Cost governance
Why: AUTO_SUSPEND only stops a warehouse from billing while it's idle; it does nothing once a warehouse is actually busy. A runaway query, a bad retry loop, or a misconfigured job firing every minute all bill at full rate indefinitely, and AUTO_SUSPEND never sees a reason to intervene because the warehouse is never idle. A resource monitor is the actual spending cap: attached to one warehouse, it tracks credit consumption against a quota and fires as usage approaches it, instead of a human noticing the bill days later. One monitor per warehouse, not one shared account-wide monitor across all four, for the same reason every other control in this document is scoped narrow: a shared monitor tells you the account overspent, a per-warehouse one tells you which one did it.
CREATE RESOURCE MONITOR IF NOT EXISTS TRANSFORMING_DEV_RM
WITH CREDIT_QUOTA = 50
FREQUENCY = DAILY
START_TIMESTAMP = IMMEDIATELY
TRIGGERS
ON 75 PERCENT DO NOTIFY
ON 100 PERCENT DO NOTIFY
ON 110 PERCENT DO NOTIFY;
ALTER WAREHOUSE TRANSFORMING_DEV_WH SET RESOURCE_MONITOR = TRANSFORMING_DEV_RM;
-- repeated once per environment, sized to that environment's expected load
CREATE RESOURCE MONITOR IF NOT EXISTS DATA_ENGINEER_TEAM_RM
WITH CREDIT_QUOTA = 20
FREQUENCY = DAILY
START_TIMESTAMP = IMMEDIATELY
TRIGGERS
ON 75 PERCENT DO NOTIFY
ON 100 PERCENT DO NOTIFY
ON 110 PERCENT DO NOTIFY;
ALTER WAREHOUSE DATA_ENGINEER_TEAM_WH SET RESOURCE_MONITOR = DATA_ENGINEER_TEAM_RM;
All three thresholds do the same thing here (NOTIFY, never SUSPEND or SUSPEND_IMMEDIATE), and the quota resets every day instead of every month. That's a deliberate trade: a forced suspend can cut off an in-flight production build or a human's active session, which is its own kind of blast radius, one this document isn't willing to risk automatically. A daily reset keeps any single bad day cheap even without an automatic kill switch, so three notifications (heads up, this is getting expensive, this is now past what a normal day should cost) are enough to get a person to look, with a hard 24-hour ceiling on how long it can go unnoticed.
RBAC
Schema-level roles
Why: "Can restructure this table," "can only add and change rows in it," and "can only read it" are three genuinely different blast radii, and a single blanket role collapses them into one grant. So every one of the 15 schemas (5 schemas × 3 environments) gets its own triad (_DDL_ROLE, _WRITER_ROLE, _READER_ROLE) instead of one role per environment doing everything.
I'll be straight about the cost of this: it's 45 roles for what is conceptually 5 schemas. WRITER and READER sit empty today, provisioned for a BI tool or an external loader that doesn't exist in this project; only the DDL tier actually gets handed to a consumer, in the next two subsections. That's a real bet: it's cheaper to create the seat now, while the schema list is small and stable, than to retrofit a new privilege tier under a live dependency graph later. For a smaller, faster-moving team I'd cut the unused tiers until a second consumer actually shows up. Least privilege is a discipline you can always add more of, not a rule that says provision every conceivable role on day one.
A tool like dbt, for example, issues DDL (CREATE TABLE, ALTER TABLE ... ADD COLUMN) as part of running its own transformations, not just DML. That's why a consumer needs the DDL tier at all, not just insert/update/delete.
CREATE ROLE IF NOT EXISTS RAW_DEV_BILLING_DDL_ROLE;
CREATE ROLE IF NOT EXISTS RAW_DEV_BILLING_WRITER_ROLE;
CREATE ROLE IF NOT EXISTS RAW_DEV_BILLING_READER_ROLE;
-- every custom role rolls up to SYSADMIN, so it stays manageable from
-- one place without ever granting a role directly to a person:
GRANT ROLE RAW_DEV_BILLING_DDL_ROLE TO ROLE SYSADMIN;
GRANT ROLE RAW_DEV_BILLING_WRITER_ROLE TO ROLE SYSADMIN;
GRANT ROLE RAW_DEV_BILLING_READER_ROLE TO ROLE SYSADMIN;
GRANT ALL ON SCHEMA RAW_DEV.BILLING TO ROLE RAW_DEV_BILLING_DDL_ROLE;
GRANT ALL ON FUTURE TABLES IN SCHEMA RAW_DEV.BILLING TO ROLE RAW_DEV_BILLING_DDL_ROLE;
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON FUTURE TABLES IN SCHEMA RAW_DEV.BILLING
TO ROLE RAW_DEV_BILLING_WRITER_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA RAW_DEV.BILLING TO ROLE RAW_DEV_BILLING_READER_ROLE;
What each tier can actually do:
-
_DDL_ROLE: create/alter/drop: yes · insert/update/delete: yes · select: yes -
_WRITER_ROLE: create/alter/drop: no · insert/update/delete: yes · select: yes -
_READER_ROLE: create/alter/drop: no · insert/update/delete: no · select: yes
None of these roles do anything by themselves yet; a role with no member is just a named bundle of privileges sitting unused. The next few subsections are where each one actually gets handed to a consumer: the tool role, the human team roles, the PII classification roles, and finally the user roles that tie all of it to an actual identity.
Tool role
Why: The transformer service needs an identity of its own: one role per environment, standing in for "whatever tool is doing the building here." It's scoped to exactly one warehouse and USAGE on that environment's three databases, then handed the schema-level DDL roles from above. Nothing about which specific tables it can touch gets decided here; that was already decided one subsection up.
CREATE ROLE IF NOT EXISTS TRANSFORMER_DEV_ROLE;
GRANT ROLE TRANSFORMER_DEV_ROLE TO ROLE SYSADMIN;
GRANT USAGE ON WAREHOUSE TRANSFORMING_DEV_WH TO ROLE TRANSFORMER_DEV_ROLE;
GRANT OPERATE ON WAREHOUSE TRANSFORMING_DEV_WH TO ROLE TRANSFORMER_DEV_ROLE;
GRANT USAGE ON DATABASE RAW_DEV TO ROLE TRANSFORMER_DEV_ROLE;
GRANT USAGE ON DATABASE REFINED_DEV TO ROLE TRANSFORMER_DEV_ROLE;
GRANT USAGE ON DATABASE CURATED_DEV TO ROLE TRANSFORMER_DEV_ROLE;
-- repeated once per environment
-- the transformer service itself only ever needs the DDL tier, already created above:
GRANT ROLE RAW_DEV_BILLING_DDL_ROLE TO ROLE TRANSFORMER_DEV_ROLE;
-- repeated once per schema, in every environment
USAGE on a database has to be granted directly to this role, in its own right; inheriting privileges on the schemas underneath isn't enough by itself to let a role traverse into the database that contains them. The last GRANT ROLE line is the one that actually matters: it's the only place in this role's setup where it gets handed real table access, and it only ever needed the DDL tier that already existed.
Team roles
Why: Human roles don't get their own privilege model; they compose from the exact same schema-level roles the transformer service does, just a different mix of them. DATA_ENGINEER_TEAM_ROLE is DDL in dev, read-only in stg and prod. Nothing new gets built for a human; they're just handed a different subset of what already exists.
CREATE ROLE IF NOT EXISTS DATA_ENGINEER_TEAM_ROLE;
GRANT ROLE DATA_ENGINEER_TEAM_ROLE TO ROLE SYSADMIN;
GRANT USAGE ON WAREHOUSE DATA_ENGINEER_TEAM_WH TO ROLE DATA_ENGINEER_TEAM_ROLE;
GRANT USAGE ON DATABASE RAW_DEV TO ROLE DATA_ENGINEER_TEAM_ROLE;
GRANT USAGE ON DATABASE REFINED_DEV TO ROLE DATA_ENGINEER_TEAM_ROLE;
GRANT USAGE ON DATABASE CURATED_DEV TO ROLE DATA_ENGINEER_TEAM_ROLE;
-- repeated for every database in stg and prod too -- this role spans all three environments
-- full DDL in dev, all 5 schemas:
GRANT ROLE