Skip to content

About

Roles, warehouses, resource monitors, databases, grants, and a key-pair service user for a new Snowflake account

Resources

Stars

0 stars

Watchers

0 watching

Forks

Repository files navigation

Snowflake Initial Setup Scripts

This folder is the account bootstrap I use before the first data load. It creates workload roles, four X-Small warehouses, two resource monitors, three databases, the core schemas, grants, and one key-pair service user.

It does not create loaders, dbt projects, streams, tasks, or application objects.

Replace these placeholders

Replace every placeholder before running any file:

Placeholder Replace with
<CLIENT> An identifier prefix that starts with a letter or underscore, followed by letters, digits, or underscores
<SOURCE_SCHEMA> The raw source schema name, such as APP or ERP
<MONTHLY_CREDIT_QUOTA> The account's monthly credit allowance
<DEV_DAILY_CREDIT_QUOTA> The daily credit allowance for development work
<BILLING_PERIOD_START> The first day of the contract or billing month at 00:00 UTC
<RAW_RETENTION_DAYS> The RAW retention period, usually 7 on Enterprise Edition or higher
<ANALYTICS_RETENTION_DAYS> The ANALYTICS retention period, usually 30 on Enterprise Edition or higher
<RSA_PUBLIC_KEY_BASE64> The 2048-bit or stronger RSA public key body with no PEM headers or line breaks

Retention of 7 and 30 days needs Enterprise Edition or higher. Standard Edition allows 0 or 1 day. Set the retention placeholders before running 04_databases_and_schemas.sql.

Do not put the private key in these files. Keep it outside the repository with restricted file permissions.

Run the files in order

  1. 01_roles.sql
  2. 02_warehouses.sql
  3. 03_resource_monitors.sql
  4. 04_databases_and_schemas.sql
  5. 05_grants.sql
  6. 06_service_user.sql
  7. 99_checks.sql

The files switch to the Snowflake system role needed for each step. Run them as an administrator who can use USERADMIN, SECURITYADMIN, SYSADMIN, and ACCOUNTADMIN.

Role hierarchy

The workload roles are siblings, not a chain:

  • <CLIENT>_LOADER writes to RAW and uses LOADING_WH.
  • <CLIENT>_TRANSFORMER reads RAW, builds in DEV and ANALYTICS, and uses DEV_WH and TRANSFORM_WH.
  • <CLIENT>_READER reads ANALYTICS.MARTS and uses REPORTING_WH.
  • SYSADMIN is the administrative parent of all three custom roles.

Keeping the workload roles separate makes each service activate only the access it needs. The scripts do not create a custom admin role because the source setups use Snowflake's system roles for account administration.

Warehouse defaults

Every new warehouse starts as X-Small, initially suspended, with auto-resume enabled and a 60-second auto-suspend. On rerun, 02_warehouses.sql restores the size, auto-suspend, and auto-resume settings. It does not suspend a running warehouse.

The four warehouses isolate loading, transformation, reporting, and development. A long transformation or ad hoc query cannot hold the reporting warehouse open.

Resource monitors

The account monitor warns at 50, 75, and 90 percent, then performs a graceful suspend at 100 percent. The development monitor uses the same warnings, suspends at 100 percent, and suspends immediately at 150 percent.

Set both quotas from the actual Snowflake contract and expected workload. The public template intentionally leaves them as placeholders.

After each CREATE RESOURCE MONITOR IF NOT EXISTS, the script runs ALTER RESOURCE MONITOR with the same quota, frequency, start timestamp, and complete trigger set. A rerun reconciles an existing monitor to those settings.

Resource monitor emails require verified user email addresses and the notification preference enabled in Snowsight. Account-level monitors cannot use an explicit NOTIFY_USERS list.

Grants and rebuilds

Future grants are declared at the schema level. Snowflake ignores a database-level future grant for an object type when a schema-level future grant for that object type exists in the schema, even if the grants target different roles.

Without COPY GRANTS, CREATE OR REPLACE TABLE drops the old table's explicit grants, but applicable future grants in the schema apply to the replacement. With COPY GRANTS, Snowflake copies the old table's explicit grants except OWNERSHIP, and future grants do not apply to the replacement.

Pick one. These scripts rely on schema-level future grants. Use COPY GRANTS when a table carries grants that the future grants do not cover.

The script grants current and future SELECT access so existing objects and later objects are both covered.

Rerun behavior

02_warehouses.sql converges warehouse size, auto-suspend, and auto-resume settings. 03_resource_monitors.sql converges monitor quotas, frequencies, start timestamps, and complete trigger sets.

The role, database, schema, and service user CREATE statements use IF NOT EXISTS, so they only skip creation. They do not reconcile existing roles, databases, or schemas. The service user script checks its type, then reapplies its service settings.

Service user

06_service_user.sql creates one TYPE = SERVICE user for ingestion. It uses RSA key-pair authentication, has no password, defaults to the loader role and warehouse, and sets DEFAULT_SECONDARY_ROLES = ().

That last setting matters. DEFAULT_SECONDARY_ROLES = ALL silently unions privileges from every granted secondary role into the session, even when Snowsight shows a restricted primary role.

If one process must both load and transform, decide whether it should receive both roles or whether those operations should be split into separate identities. This template grants only the loader role.

Verification

Run 99_checks.sql and read the output. Then open a new session as the service user and verify the actual operation it needs to perform. Grant listings alone do not prove that the consumer identity can use the intended warehouse and objects.

No generic rollback file is included. Dropping roles, schemas, or users can affect object ownership and active integrations. Review live dependencies before removing account objects.

About

Roles, warehouses, resource monitors, databases, grants, and a key-pair service user for a new Snowflake account

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors