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 consumption from the same ACCOUNT_USAGE views Snowflake’s own billing UI reads, so the numbers always match. Its scheduler suspends idle warehouses safely: before every suspend it checks for in-flight queries and skips rather than kills. 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 edition’s credit rate; 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 (matches the Snowflake billing UI), 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.