Snowflake Multi-Snowflake Multi-Environment Account Architecture: RBAC, Cost Governance, Identity Isolation & PII Masking Design

sai krishna kumboju — Mon, 07 Sep 2026 10:17:16 +0000

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.

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 RAW_DEV_BILLING_DDL_ROLE TO ROLE DATA_ENGINEER_TEAM_ROLE;
-- repeated for the other 4 dev schemas

The same mechanism repeats once more for the read-only grants in stg and prod (all 5 schemas' _READER_ROLEs, in both environments). What this role is actually scoped to see, alongside the transformer service, is laid out next in Humans vs. machines.

PII roles

Why: A masking policy needs something to check against, and that check has to already be threaded through the grant graph before the policy itself exists, otherwise "turn on masking" also means "figure out who should still see the real value," under pressure, at the same time. So the classification roles get created and granted now, well ahead of the Enterprise Edition upgrade the masking policy itself depends on: DSC_CUSTOMER_EMAIL_ROLE goes to TRANSFORMER_DEV_ROLE (the transformer service genuinely needs to read real emails while moving them from raw into the refined layer) and nowhere else.

CREATE ROLE IF NOT EXISTS DSC_CUSTOMER_EMAIL_ROLE;
CREATE ROLE IF NOT EXISTS DSC_CUSTOMER_NAME_ROLE;
GRANT ROLE DSC_CUSTOMER_EMAIL_ROLE TO ROLE SYSADMIN;
GRANT ROLE DSC_CUSTOMER_NAME_ROLE  TO ROLE SYSADMIN;

GRANT ROLE DSC_CUSTOMER_EMAIL_ROLE TO ROLE TRANSFORMER_DEV_ROLE;
GRANT ROLE DSC_CUSTOMER_NAME_ROLE  TO ROLE TRANSFORMER_DEV_ROLE;
-- repeated once per environment; deliberately never granted to any read-only role

On Standard Edition this grant has no visible effect at all; there's no masking policy yet to check it. The day this account moves to Enterprise Edition and the masking policy in PII masking actually gets applied, every role that was never added here starts seeing a masked value automatically, with no other change required.

User roles

Why: Every role built so far is an access role: it exists to describe a set of privileges, not a person or a service. Granting those directly to a user works, but it means a user's real privilege footprint is whatever the union of every GRANT ROLE ... TO USER statement ever run against them adds up to, with nowhere to look but the account's grant history to answer "what can this identity actually do." So one more role sits between the access roles and the identity: a user role, one per user, that exists only to collect every access role that user needs and hand them to the user as a single grant. Auditing a user becomes "what's granted to their one user role," and revoking a user's access is one REVOKE, not a hunt through every role they were ever added to.

CREATE ROLE IF NOT EXISTS SERVICE_DEV_USER_ROLE;
GRANT ROLE SERVICE_DEV_USER_ROLE TO ROLE SYSADMIN;
GRANT ROLE TRANSFORMER_DEV_ROLE  TO ROLE SERVICE_DEV_USER_ROLE;
-- repeated once each for SERVICE_STG_USER_ROLE / TRANSFORMER_STG_ROLE,
-- SERVICE_PROD_USER_ROLE / TRANSFORMER_PROD_ROLE

CREATE ROLE IF NOT EXISTS HUMAN_ENGINEER_USER_ROLE;
GRANT ROLE HUMAN_ENGINEER_USER_ROLE TO ROLE SYSADMIN;
GRANT ROLE DATA_ENGINEER_TEAM_ROLE  TO ROLE HUMAN_ENGINEER_USER_ROLE;

This is the only role that ever gets granted to a user directly, next in Identity isolation: every access role above reaches a person or a service exclusively through the one user role built for them.

Identity isolation

Why: Each environment gets its own service account (SERVICE_DEV_USER, SERVICE_STG_USER, SERVICE_PROD_USER) and each one is granted only its own environment's user role, built back in RBAC to hold exactly one environment's worth of access and nothing else. Not one shared identity that can request any role by name: a separate account per environment, scoped so it structurally cannot reach beyond the one it belongs to. If SERVICE_DEV_USER's key ever leaks, there's no USE ROLE TRANSFORMER_PROD_ROLE for it to fall back on; that grant was never made, not to the user directly and not to its user role either. The isolation doesn't depend on a connection asking for the right role and getting it right every time; it depends on the wrong role not existing for that user at all.

Every identity in this account is one of two kinds: a service account, authenticating unattended with a key pair, or a human account, authenticating interactively with a password and MFA (the next section, Humans vs. machines, covers those). This section is specifically about the service accounts: the ones a leaked credential actually threatens, because nothing has to go wrong except a file ending up somewhere it shouldn't.

CREATE USER IF NOT EXISTS SERVICE_DEV_USER
    LOGIN_NAME = 'SERVICE_DEV_USER'
    DISPLAY_NAME = 'Transformer service - dev'
    TYPE = SERVICE
    DEFAULT_ROLE = 'SERVICE_DEV_USER_ROLE'
    DEFAULT_WAREHOUSE = 'TRANSFORMING_DEV_WH'
    MUST_CHANGE_PASSWORD = FALSE
    COMMENT = 'Key-pair auth only. Scoped to SERVICE_DEV_USER_ROLE -- cannot assume any other environment''s role.';
GRANT ROLE SERVICE_DEV_USER_ROLE TO USER SERVICE_DEV_USER;
-- repeated once each for SERVICE_STG_USER, SERVICE_PROD_USER

TYPE = SERVICE is doing real work, not just documenting intent: it tells Snowflake this identity is structurally incapable of password or MFA login at all, only key-pair or OAuth. It's the same idea as the rest of this section applied one level deeper: don't rely on nobody ever trying a password login for this account, remove the option entirely. MUST_CHANGE_PASSWORD = FALSE matters for the same reason: there's no interactive session that could ever act on a change-password prompt.

That's the entire access mechanism: one CREATE USER and one GRANT ROLE per environment, repeated three times. No cross-environment grant is ever created in the first place, so there's nothing to remember to revoke, and nothing a compromised credential can escalate into later. But a user with no key attached can't authenticate at all yet: that's the other half:

Generate the key pair on whatever machine will hold the private half; it never leaves that machine, or a secrets manager, or gets pasted anywhere:

# private key (keep this one secret, never commit it)
openssl genrsa -out tmp_dev.pem 2048
openssl pkcs8 -topk8 -inform PEM -in tmp_dev.pem -out rsa_key_dev.p8 -nocrypt

# public key (the only half that ever goes near Snowflake)
openssl rsa -in rsa_key_dev.p8 -pubout -out rsa_key_dev.pub

Then attach the public half to the account: paste in the contents of rsa_key_dev.pub, minus the BEGIN/END lines:

ALTER USER SERVICE_DEV_USER SET RSA_PUBLIC_KEY = '<contents of rsa_key_dev.pub, no BEGIN/END lines>';

The connection config then points at the private key file's path, not the key itself; SERVICE_DEV_USER authenticates by proving it holds the private half that matches what Snowflake has on file.

For contrast, here's the human equivalent: a real person, not a service:

CREATE USER IF NOT EXISTS HUMAN_ENGINEER_USER
    LOGIN_NAME = 'HUMAN_ENGINEER_USER'
    DISPLAY_NAME = 'Jane Doe - Data Engineer'
    TYPE = PERSON
    DEFAULT_ROLE = 'HUMAN_ENGINEER_USER_ROLE'
    DEFAULT_WAREHOUSE = 'DATA_ENGINEER_TEAM_WH'
    MUST_CHANGE_PASSWORD = TRUE
    COMMENT = 'Real person -- password + MFA only, never key-pair auth.';
GRANT ROLE HUMAN_ENGINEER_USER_ROLE TO USER HUMAN_ENGINEER_USER;

Same shape, opposite substance: TYPE = PERSON instead of SERVICE; a password instead of a key pair, set once through Snowsight's reset-password flow, never as a literal in committed SQL; and MUST_CHANGE_PASSWORD = TRUE, so that first password gets replaced the moment the person logs in. Enforcing MFA on top of that password is covered next, in Hardening identities; it needs an authentication policy that doesn't exist yet. None of this applies to a service account: there's no one present to type a code or choose a new password, so none of these controls would do anything for one.

Humans vs. machines

Why: An automated transformer service is the only machine consumer in this account, and the human engineer working alongside it needs a genuinely different shape of access, not just a smaller version of the service's own role. The engineer needs to iterate: same DDL surface the transformer service gets, but only in dev, so they can verify a deploy landed in stg/prod by reading it without also being able to write over what the transformer service just built.

TRANSFORMER_DEV_ROLE: dev: DDL · stg: none · prod: none · warehouse: TRANSFORMING_DEV_WH
DATA_ENGINEER_TEAM_ROLE: dev: DDL · stg: read-only · prod: read-only · warehouse: DATA_ENGINEER_TEAM_WH

Each gets its own dedicated warehouse (never a TRANSFORMING_<ENV>_WH, never each other's), so a human's ad hoc query can't contend for capacity with an automated build, or with the other role's workload.

Hardening identities

Why: Machines and humans need opposite controls on authentication, but the same short idle timeout works for both: a single automated build finishes well inside 15 minutes, so it never gets cut off mid-run, and it keeps a stale session from lingering either way. A service account authenticates unattended (there's no one present to approve an MFA prompt), so it skips that control entirely. A human's interactive login is exactly where a second factor earns its keep against a leaked or guessed password, so it gets enforced MFA on top of the same timeout, since an unattended, unlocked laptop shouldn't stay authenticated indefinitely either.

CREATE DATABASE IF NOT EXISTS GOVERNANCE;
CREATE SCHEMA IF NOT EXISTS GOVERNANCE.SECURITY;

CREATE AUTHENTICATION POLICY IF NOT EXISTS GOVERNANCE.SECURITY.HUMAN_MFA_REQUIRED_POLICY
    MFA_ENROLLMENT = REQUIRED;
ALTER USER HUMAN_ENGINEER_USER SET AUTHENTICATION POLICY GOVERNANCE.SECURITY.HUMAN_MFA_REQUIRED_POLICY;

CREATE SESSION POLICY IF NOT EXISTS GOVERNANCE.SECURITY.HUMAN_SESSION_POLICY
    SESSION_IDLE_TIMEOUT_MINS = 15;   -- interactive humans
ALTER USER HUMAN_ENGINEER_USER SET SESSION POLICY GOVERNANCE.SECURITY.HUMAN_SESSION_POLICY;

CREATE SESSION POLICY IF NOT EXISTS GOVERNANCE.SECURITY.SERVICE_ACCOUNT_SESSION_POLICY
    SESSION_IDLE_TIMEOUT_MINS = 15;   -- a real build finishes well inside this
ALTER USER SERVICE_DEV_USER SET SESSION POLICY GOVERNANCE.SECURITY.SERVICE_ACCOUNT_SESSION_POLICY;
-- repeated once each for SERVICE_STG_USER, SERVICE_PROD_USER

For the three service accounts, rotation is a two-slot mechanism (RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2) so a key can be replaced without downtime:

Generate a new key pair; never overwrite the old file yet.
Load the new public key into whichever slot is unused; both keys now work.
Point the connection config at the new private key, and confirm a real run succeeds against it.
Only once that's confirmed, retire the old key from its slot.
Optionally consolidate the new key back into slot 1 before the next rotation.

Skipping the live check in step 3 before retiring the old key in step 4 is exactly how a routine rotation turns into an outage.

Network policy

Status: commented out, fill in first

Why: A network policy restricts which IP addresses can even attempt to authenticate as a given user; without one, a leaked credential can be used from anywhere.

What it takes: a real, fixed IP or CIDR range to allowlist for the account or user connecting. One policy per user, not one shared account-wide policy, so a bad IP list only ever locks out that one user instead of everyone, ACCOUNTADMIN included:

CREATE NETWORK POLICY IF NOT EXISTS SERVICE_DEV_NETWORK_POLICY
    ALLOWED_IP_LIST = ('<dev runner IP or CIDR>');
ALTER USER SERVICE_DEV_USER SET NETWORK_POLICY = SERVICE_DEV_NETWORK_POLICY;

PII masking

Status: Enterprise Edition and above

Why: PII masking hides sensitive columns from anyone without the right classification role; without it, every reader with SELECT access sees raw values, no matter how carefully everything else in this account is scoped.

What it takes: Snowflake Enterprise Edition. CREATE MASKING POLICY doesn't exist on Standard Edition; every statement below fails outright until the account is upgraded. Once it is, a masking policy checks which role is active in the session and decides whether to reveal the real value:

CREATE SCHEMA IF NOT EXISTS GOVERNANCE.PII;

CREATE MASKING POLICY IF NOT EXISTS GOVERNANCE.PII.MASK_CUSTOMER_EMAIL AS
    (val STRING) RETURNS STRING ->
    CASE WHEN IS_ROLE_IN_SESSION('DSC_CUSTOMER_EMAIL_ROLE')
         THEN val ELSE '***MASKED***' END;

The classification roles the policy keys off of aren't new here: DSC_CUSTOMER_EMAIL_ROLE and DSC_CUSTOMER_NAME_ROLE were already created and granted back in RBAC → PII roles, well ahead of this edition upgrade. On Standard Edition that grant has no technical effect yet; the day this account moves to Enterprise Edition and the masking policy above actually gets applied, every role that was never added to that grant starts seeing ***MASKED*** with no other change required.

Honesty as architecture

The last risk this account carries isn't a bad grant; it's false confidence in a script that was never actually checked. A file full of CREATE ROLE and GRANT statements looks like proof the access model works, but SQL that reads correctly isn't the same as SQL that ran correctly against a real account. So the script shouldn't just assert it works; it should come with a way to check that claim:

SHOW GRANTS TO ROLE DATA_ENGINEER_TEAM_ROLE;
DESCRIBE USER HUMAN_ENGINEER_USER;

-- then log in as the role itself, not as an admin who can see everything:
USE ROLE DATA_ENGINEER_TEAM_ROLE;
SELECT CURRENT_ROLE(), CURRENT_WAREHOUSE();

SHOW GRANTS TO ROLE lists exactly what that role can do right now, not what a GRANT statement further up claimed it would do. DESCRIBE USER confirms a user's real default role and warehouse actually took. And connecting as the role, not as an admin, is the only way to catch a grant that looks right on paper but doesn't actually work.

A design that tells you how to check it is more trustworthy than one that only asserts it works.

That same honesty applies to what's already been laid out above:

✕ WRITER_ROLE and READER_ROLE exist on every schema but are granted to no one yet: not an oversight, a seat held open for a BI tool or external loader that doesn't exist in this project yet. Nothing to grant them to means nothing to grant.
✕ No network policy is active yet: the SQL is ready, but <dev runner IP or CIDR> is a placeholder, not a real value. Whoever runs this has to fill in the actual fixed IP or CIDR range for each environment before it does anything.
✕ Masking isn't live yet because this account is still on Standard Edition, which doesn't support CREATE MASKING POLICY at all: an edition gap, not a design gap. Anyone with SELECT access but no classification role sees real, unmasked customer emails today, exactly the exposure the classification roles were built ahead of.
✕ No SSO or SCIM: a scope decision, not a technical one. Federating through an external identity provider is an account-wide choice made once, not once per role, and this document deliberately stops at the native primitives (password + MFA) rather than assume a specific IdP the reader may not have.

None of that undermines the design; it's the other half of it. Isolation at the credential layer, the role layer, and the compute layer only means something if you're also honest about which layers are still unverified or not yet switched on. That list is as load-bearing as any GRANT statement above it.

Snowflake account architecture · 15-section provisioning script

DEV Community: sai krishna kumboju