The first Snowflake invoice is often a surprise. Not because Snowflake is expensive, but because consumption pricing behaves very differently from a SQL Server licence. On-premises, a badly written query costs you nothing but time. In Snowflake it costs credits, and a warehouse left running over a weekend costs a lot of credits.
Delegated authority workloads have a particular shape: quiet for most of the month, then a rush in the first working days when coverholders' risk, premium and claims bordereaux are loaded, validated, and reported to underwriters and oversight teams. That pattern is a good fit for Snowflake's pricing, but only if the account is set up for it. Here's the checklist I go through, with the queries I use to find where the money goes.
How you're billed, in one paragraph
Compute is billed in credits per hour per running virtual warehouse. An X-Small uses 1 credit/hour, and each size up doubles that. Billing is per second, with a 60-second minimum each time a warehouse resumes. Storage is billed separately per TB per month, and serverless features (Snowpipe, automatic clustering, search optimisation, serverless tasks, and so on) have their own credit usage. In most accounts, warehouses are 70–90% of the bill, so start there.
1. Auto-suspend and auto-resume on every warehouse
1: SHOW WAREHOUSES; 2: -- check the auto_suspend column: anything NULL or > 300 deserves a question 3: 4: ALTER WAREHOUSE bdx_reporting_wh SET AUTO_SUSPEND = 60 AUTO_RESUME = TRUE; 5: ALTER WAREHOUSE bdx_etl_wh SET AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;
60 seconds is a good default. Going lower rarely helps because of the 60-second minimum on resume. The exception is a warehouse serving a busy Power BI model, where a slightly longer suspend (2–5 minutes) keeps the local cache warm and can actually save credits by avoiding repeated cold starts.
2. Separate warehouses by workload
One giant warehouse for everything makes it impossible to see who is spending what. I split by workload:
bdx_etl_wh: bordereaux ingestion, validation and transformation into the risk, premium and claims factsbdx_reporting_wh: Power BI, including GWP, GPI utilisation and loss ratio dashboardsactuarial_adhoc_wh: actuaries and analysts querying historydev_wh: development, kept small
Then grant USAGE per role, so people can only use the warehouse meant for them.
3. Resource monitors as a safety net
1: USE ROLE ACCOUNTADMIN; 2: 3: CREATE OR REPLACE RESOURCE MONITOR rm_actuarial_adhoc 4: WITH CREDIT_QUOTA = 200 5: FREQUENCY = MONTHLY 6: START_TIMESTAMP = IMMEDIATELY 7: TRIGGERS 8: ON 75 PERCENT DO NOTIFY 9: ON 90 PERCENT DO NOTIFY 10: ON 100 PERCENT DO SUSPEND 11: ON 110 PERCENT DO SUSPEND_IMMEDIATE; 12: 13: ALTER WAREHOUSE actuarial_adhoc_wh SET RESOURCE_MONITOR = rm_actuarial_adhoc;
SUSPEND lets running queries finish. SUSPEND_IMMEDIATE cancels them. For bordereaux ETL, I set monitors to notify only. Suspending the warehouse halfway through month-end processing just delays the reports underwriters are waiting for. For ad-hoc and dev, hard limits are fine. Make sure notifications are enabled for the account admins in their user preferences, or the alerts go nowhere.
4. Statement timeouts
The default statement timeout is 2 days. That's a long time for an accidental cartesian join between the risk and claims facts to run on a Large warehouse.
1: ALTER WAREHOUSE actuarial_adhoc_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 1800; -- 30 min 2: ALTER WAREHOUSE bdx_reporting_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 600; -- 10 min 3: ALTER WAREHOUSE bdx_etl_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 7200; -- 2 h 4: 5: ALTER WAREHOUSE actuarial_adhoc_wh SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 600;
5. Find where the credits go
These views live in SNOWFLAKE.ACCOUNT_USAGE and lag behind real time by up to a few hours.
Credits by warehouse, last 30 days:
1: SELECT warehouse_name, 2: ROUND(SUM(credits_used), 1) AS credits, 3: ROUND(SUM(credits_used_cloud_services), 1) AS cloud_services 4: FROM snowflake.account_usage.warehouse_metering_history 5: WHERE start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP()) 6: GROUP BY warehouse_name 7: ORDER BY credits DESC;
Credits by day of month, which shows the month-end bordereaux peak clearly and helps decide whether a bigger warehouse for those days is worth it:
1: SELECT warehouse_name, 2: DAY(start_time) AS day_of_month, 3: ROUND(AVG(credits_used), 2) AS avg_credits_per_hour 4: FROM snowflake.account_usage.warehouse_metering_history 5: WHERE start_time >= DATEADD(month, -3, CURRENT_TIMESTAMP()) 6: GROUP BY 1, 2 7: ORDER BY 1, 2;
Run the same query with HOUR(start_time) to spot warehouses that never sleep. If a warehouse shows steady usage at 3am every night and nothing is scheduled then, something (often a BI tool's keep-alive or a forgotten refresh) is keeping it awake.
The most expensive query patterns, grouped by query hash so the same query with different literals counts once:
1: SELECT query_parameterized_hash, 2: ANY_VALUE(query_text) AS sample_query, 3: ANY_VALUE(warehouse_name) AS warehouse, 4: COUNT(*) AS executions, 5: ROUND(SUM(total_elapsed_time) / 1000 / 3600, 2) AS total_hours, 6: ROUND(AVG(bytes_scanned) / POWER(1024, 3), 2) AS avg_gb_scanned 7: FROM snowflake.account_usage.query_history 8: WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP()) 9: AND warehouse_name IS NOT NULL 10: GROUP BY query_parameterized_hash 11: ORDER BY total_hours DESC 12: LIMIT 25;
Elapsed time isn't the same as credits, since several queries share a running warehouse. But as a ranking it reliably points at the right places. In DA accounts the top of this list is often a Power BI query recomputing GWP or incurred claims across every year of account on every refresh. A pre-aggregated mart by coverholder, contract section and bordereau month usually fixes it. Snowflake also provides QUERY_ATTRIBUTION_HISTORY for per-query compute cost, which is worth using if your account has it.
6. Right-size warehouses
Doubling a warehouse doubles the cost per second. If the query runs twice as fast, the total cost is the same and the result arrives sooner. If it doesn't speed up, you're wasting credits. In the query profile, look for:
- "Bytes spilled to local/remote storage": the warehouse is too small for the query's memory needs. Going up a size can be cheaper.
- Queuing on the reporting warehouse during month-end, when every underwriter opens the dashboards at once: use a multi-cluster warehouse (Enterprise edition) with
SCALING_POLICY = ECONOMYrather than a bigger size. - Small queries on a big warehouse: scale down. Looking up one coverholder's contract sections doesn't need a Large.
For the month-end peak, a scheduled task can resize the ETL warehouse up on working day 1 and back down on day 6. It's simple, and it beats running a Large all month.
7. Look at storage too
1: SELECT table_catalog, table_schema, table_name, 2: ROUND(active_bytes / POWER(1024,4), 3) AS active_tb, 3: ROUND(time_travel_bytes / POWER(1024,4), 3) AS time_travel_tb, 4: ROUND(failsafe_bytes / POWER(1024,4), 3) AS failsafe_tb 5: FROM snowflake.account_usage.table_storage_metrics 6: WHERE deleted = FALSE 7: ORDER BY (active_bytes + time_travel_bytes + failsafe_bytes) DESC 8: LIMIT 20;
Tables where Time Travel and Fail-safe are much larger than active storage are usually bordereaux staging tables that get fully reloaded each month. Make them TRANSIENT with short retention.
Make it a habit
None of this is a one-off. Put the queries above into a Snowsight dashboard, review it every Monday (and straight after each month-end), and set a budget alert with Snowflake's Budgets feature. A Snowflake account that's watched weekly rarely produces a bad surprise. One that's set up and forgotten nearly always does.
No comments:
Post a Comment