Snowflake Cost Optimization — Automatic Warehouse Suspension and Credit Analytics

Snowflake spend is driven by credits, and credits are driven by warehouses that keep running when nobody is querying them. ZopNight connects to your Snowflake account as a dedicated service user, discovers every warehouse, database, table, materialized view, stage and pipe, and meters credits from the same ACCOUNT_USAGE views Snowflake’s own billing UI reads, so credit counts match; dollar cost uses your contract rate from RATE_SHEET_DAILY when ZopNight can read it, otherwise the public list price for your edition. Its scheduler suspends idle warehouses safely: before suspending it checks the last 5 minutes of warehouse load and skips busy warehouses, and Snowflake’s SUSPEND lets running queries finish, so nothing is killed. The result is warehouse bills that track actual usage, not the calendar.

The bootstrap script: two roles, a user, an ops warehouse

You run a single bootstrap SQL script as ACCOUNTADMIN. It creates two roles (ZOPNIGHT_READ_ROLE and ZOPNIGHT_WRITE_ROLE), a dedicated extra-small operations warehouse (ZOPNIGHT_OPS_WH, auto-suspending after 60 seconds), and a SERVICE-type user ZOPNIGHT_SVC. Grants are minimal and documented: imported privileges on the SNOWFLAKE database for usage views, MONITOR USAGE on the account, USAGE and MONITOR on warehouses, and OPERATE on warehouses only for the write role that powers scheduling. ZopNight authenticates with RSA key-pair authentication (recommended) or a Programmatic Access Token, and verifies its own grants with SHOW GRANTS, including inherited roles. Warehouse costs are computed as credits from WAREHOUSE_METERING_HISTORY multiplied by your contract credit rate from RATE_SHEET_DAILY, or your edition’s list rate when that view is not readable; storage is tracked per table across active, Time Travel and Fail-safe bytes; serverless features (Snowpipe, auto-clustering, materialized view refresh, search optimization) are metered separately.

Key pair, bootstrap script, account identifier

1. Generate an RSA key pair (openssl genrsa 2048, convert to PKCS8).
2. Run the bootstrap SQL script as ACCOUNTADMIN (read-write or read-only variant).
3. Find your account identifier with the provided SQL one-liner (format: locator.region.cloud).
4. In ZopNight enter account, user ZOPNIGHT_SVC, and the private key. PAT alternative: create a network policy allow-listing ZopNight’s egress IP, generate a token restricted to the ZopNight role, and paste it instead.

Credit-accurate costing and the rightsizing rules

Discovery of warehouses, databases, tables, materialized views, stages, pipes, users and resource monitors, with cost-allocation tags imported from TAG_REFERENCES. Credit-accurate warehouse cost (credits match the Snowflake billing UI; dollars use your contract rate when ZopNight can read it, otherwise list price), per-table storage including Time Travel and Fail-safe, and serverless credit tracking. Query-safe warehouse auto-suspend scheduling. Cost-focused recommendations: suspend idle warehouses, right-size up or down (based on load and remote spillage), tune AUTO_SUSPEND and multi-cluster settings, add statement timeouts, attach resource monitors, consolidate under-used warehouses, and apply cost-allocation tags.