Snowflake Initial Setup: Roles, Warehouses and Grants

The order matters when I set up a new Snowflake account.

If I create users before roles, or tables before future grants, I end up fixing access one object at a time. If I create warehouses without cost controls, the account can start spending before the first useful dataset exists.

I run a handful of Snowflake accounts for clients, and this is the setup I start from before data lands.

1. Create the workload roles

I start with three custom roles:

• <CLIENT>_LOADER writes source data into RAW.
• <CLIENT>_TRANSFORMER reads RAW and builds models in DEV and ANALYTICS.
• <CLIENT>_READER reads approved tables in ANALYTICS.MARTS.

I create them with USERADMIN and grant each one to SYSADMIN with statements such as GRANT ROLE <CLIENT>_LOADER TO ROLE SYSADMIN;.

I keep the workload roles separate. The transformer doesn't inherit the loader role. The reader doesn't inherit either one. SYSADMIN can still manage the objects below them.

I don't add another custom admin role in the base script. For this setup, Snowflake's built-in admin roles are enough.

2. Create one warehouse per workload

Next I create four warehouses:

• LOADING_WH
• TRANSFORM_WH
• REPORTING_WH
• DEV_WH

They all start at X-Small. The important settings are AUTO_SUSPEND = 60, AUTO_RESUME = TRUE, and INITIALLY_SUSPENDED = TRUE.

A typical statement is:

CREATE WAREHOUSE IF NOT EXISTS LOADING_WH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

I separate the warehouses because the workloads behave differently. A load shouldn't compete with reporting. A development query shouldn't hold the scheduled transformation warehouse open.

After each CREATE WAREHOUSE IF NOT EXISTS, I run an ALTER WAREHOUSE with the same size and auto-suspend settings. IF NOT EXISTS avoids an error. It doesn't fix a warehouse that has drifted.

3. Add resource monitors before anyone runs queries

I create an account-level monthly monitor and a warehouse-level daily monitor for DEV_WH.

The monthly monitor warns at 50, 75, and 90 percent, then uses ON 100 PERCENT DO SUSPEND. The public script uses <MONTHLY_CREDIT_QUOTA> because the quota has to come from the actual contract. I set its start timestamp to the first of the billing month. Starting it immediately makes the quota reset on the creation day instead.

The script reruns an ALTER RESOURCE MONITOR after the create so a rerun updates an existing monitor's quota and triggers.

The development monitor uses <DEV_DAILY_CREDIT_QUOTA>. It warns at the same early thresholds, suspends at 100 percent, and uses SUSPEND_IMMEDIATE at 150 percent.

An account monitor and a warehouse monitor can both cover the same warehouse. If either one reaches a suspend threshold, Snowflake suspends the warehouse.

Resource monitor emails still need manual setup. The recipient has to verify their email and turn on resource monitor notifications in Snowsight. I also leave NOTIFY_USERS off the account-level monitor because Snowflake doesn't support it there.

4. Create the databases and schemas

The base layout is:

RAW
  <SOURCE_SCHEMA>
  META

DEV
  STG
  INT
  MARTS

ANALYTICS
  STG
  INT
  MARTS

RAW holds source-aligned tables and load metadata. DEV gives transformation work an isolated target. ANALYTICS.MARTS is the only production schema exposed to the reader role.

I use CREATE DATABASE IF NOT EXISTS and CREATE SCHEMA IF NOT EXISTS throughout. The script sets seven days of Time Travel retention on RAW, one day on DEV, and 30 days on ANALYTICS.

Those values need Enterprise Edition or higher. Standard Edition caps Time Travel at one day, and the script fails on the first CREATE DATABASE if you leave 7 in. The scripts take the days as placeholders for that reason.

These are my starting values, not Snowflake defaults. I check them against the account edition, recovery needs, and storage budget before I use them.

5. Grant current access and future access

This is the longest script because I keep the grants explicit.

The loader gets USAGE on LOADING_WH, RAW, and the two raw schemas. It gets the schema privileges needed to create tables, views, stages, and file formats.

The transformer gets USAGE on TRANSFORM_WH and DEV_WH, read access to RAW, and create access in the DEV and ANALYTICS transformation schemas.

The reader gets only USAGE on REPORTING_WH, ANALYTICS, and ANALYTICS.MARTS, plus SELECT on current and future tables and views in that schema.

I grant both current and future objects:

GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.MARTS
  TO ROLE <CLIENT>_READER;

GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS.MARTS
  TO ROLE <CLIENT>_READER;

I keep future grants at the schema level. Snowflake gives schema-level future grants precedence over database-level future grants for the same object type. If both exist, the database-level grant doesn't apply in that schema.

This cost me real time. A loader rebuilt raw tables with CREATE OR REPLACE TABLE, which removed the transform role's explicit SELECT grants. I had database-level future grants, but a schema-level future ownership grant took precedence. I moved the future grants to the schema and kept them in the setup script.

With COPY GRANTS, the old table's explicit grants are copied and future grants don't apply to the replacement. Without it, future grants apply. I use future grants at the schema level and reach for COPY GRANTS only when a table carries grants the future grants don't cover.

6. Create one service user with key-pair authentication

The example service user is <CLIENT>_SVC_INGEST.

I create it with TYPE = SERVICE, set RSA_PUBLIC_KEY = '<RSA_PUBLIC_KEY_BASE64>', and default it to the loader role, LOADING_WH, and RAW.<SOURCE_SCHEMA>. The private key never goes into Snowflake or the repository.

I also set DEFAULT_SECONDARY_ROLES = ().

I missed this once. The primary role looked restricted, but the session was still using privileges from secondary roles. I set the empty value, reconnect, and test again from a new session.

Setting DEFAULT_ROLE doesn't grant the role. The script still runs GRANT ROLE <CLIENT>_LOADER TO USER <CLIENT>_SVC_INGEST; under SECURITYADMIN.

7. Read everything back

The last script runs SHOW ROLES, SHOW WAREHOUSES, SHOW RESOURCE MONITORS, SHOW DATABASES, SHOW SCHEMAS, SHOW GRANTS TO ROLE, SHOW GRANTS OF ROLE, and DESCRIBE USER. SHOW GRANTS OF ROLE proves each workload role landed under SYSADMIN.

I still test from a fresh session as the service identity. Grant output can look correct while the real connection uses the wrong default role, a secondary role, or an old session.

AUTO_SUSPEND = 60 doesn't mean the warehouse always stops after 60 seconds. The timer starts after the last statement finishes.

I've watched small queries arrive in a trickle and keep an X-Small warehouse awake for hours. I've also watched a query spend much longer compiling than executing. Auto-suspend was working. The warehouse was never idle.

The full scripts are on GitHub: https://github.com/linerss/snowflake-initial-setup