Credit Growth on a Mixed ELT and BI Warehouse
A retail account runs a nightly ELT chain of 1,400 tasks on a single 2XL warehouse with AUTO_SUSPEND = 600 seconds and MAX_CLUSTERS = 5 on the STANDARD scaling policy. Between 06:00 and 09:00, BI queries queue for 4-9 minutes and analysts complain. The rest of the day the same warehouse serves analyst dashboards that re-run the same 12 queries every 30 seconds. The task DAG fires every 2 minutes around the clock. Finance reports credits up 38% month over month with flat data volume. Last month the resource monitor suspended the warehouse at 100% of quota and production loads failed mid-transaction; the team's response was to raise the quota by 30% for next quarter.
1. Explain the 38% credit increase using the settings described, with arithmetic.
Read the worked answer
The increase is driven by the two settings that decide when compute stops, not by data volume.
AUTO_SUSPEND vs task cadence. A 2XL warehouse burns 32 credits per hour. AUTO_SUSPEND = 600s means the warehouse must be idle for ten minutes before it shuts down. The task DAG fires every two minutes, so the warehouse is never idle for ten minutes - it effectively runs 24x7. That is 32 x 24 = 768 credits per day for the ETL warehouse alone, whether or not there is work.
STANDARD multi-cluster. STANDARD keeps clusters alive after demand drops so they are warm for the next spike. With MAX_CLUSTERS = 5, a burst at 06:00-09:00 can leave up to 5 clusters (160 credits/hour) running into the late morning before they retire, so the morning spike costs far more than three hours of a single cluster.
Coupling. ETL and BI share one warehouse, so BI's concurrency needs inflate MAX_CLUSTERS for the load path too, and the load path's long sessions keep BI queries queued behind them. Queuing is a symptom of contention, and the market response to contention - more clusters - is the most expensive possible answer.
Fixes, in order of leverage: split into separate ETL and BI warehouses; drop the ETL warehouse to a size justified by the actual spill data in the profile; reduce its AUTO_SUSPEND to the 60-second minimum, or better, consolidate the 2-minute DAG into fewer, larger runs so the warehouse can genuinely suspend between batches; and set the BI warehouse to ECONOMY with MAX_CLUSTERS sized to observed concurrency (usually 2-3).
2. The dashboards re-run the same 12 queries every 30 seconds. How much help can the result cache give, and what would you change?
Read the worked answer
The result cache returns a prior result without consuming compute credits when the query text is identical, the role has the same privileges, and no DML has touched the referenced tables since the result was cached. Cached results persist for 24 hours, but they are invalidated the moment the underlying micro-partitions change.
That invalidation rule is the whole story here. The nightly and continuous loads rewrite the fact tables, so:
- During steady state, the 12 queries can hit cache repeatedly and cost effectively nothing - provided the SQL text is byte-identical. Any generated timestamp literal, changing
INlist, session parameter difference, or ORM-added whitespace produces a miss on every run. Audit the actual executed SQL inQUERY_HISTORYfirst; this is where most 'the cache does not work' cases die. - Right after each load, all 12 are cold and hit compute simultaneously, which is exactly when queues form.
Because the workload is a fixed set of aggregates over a slowly changing base, the durable fix is to pre-compute. Materialize the 12 aggregates into a summary table refreshed by a task after each load, or use a materialized view if the aggregates qualify and the base table is not so volatile that refresh cost exceeds the savings. Then point the dashboards at the summary, whose rows change far less often and which is dramatically cheaper to scan. Keep the cache as a bonus, not as the design.
3. The resource monitor failed a production load. How should monitors and guardrails be restructured?
Read the worked answer
A resource monitor is a financial circuit breaker, and someone wired it into the production critical path. At 100% of quota a monitor with a SUSPEND action suspends the warehouse, which aborts in-flight statements - including a load transaction. Raising the quota by 30% treats the symptom and pushes the same cliff further out.
Restructure along three axes:
- Scope. Put a monitor on each workload group (ETL, BI, ad-hoc), not one monitor over everything. An analyst running an accidental cartesian join on the BI warehouse must not be able to kill the nightly load. Account-level monitors are a backstop for total spend, not a workload control.
- Thresholds and actions. NOTIFY at 50/75/90 percent to give humans time to react, and place SUSPEND well above expected consumption with headroom. Where supported, let the suspend action wait for running statements rather than aborting immediately.
- Complementary controls that shape rather than kill.
STATEMENT_TIMEOUT_IN_SECONDSon the ad-hoc warehouse,STATEMENT_QUEUED_TIMEOUT_IN_SECONDSto shed rather than queue, and query tagging soQUERY_ATTRIBUTION_HISTORYshows which team drove the spend. Right-size the warehouses as described earlier - most of the 38% is structural waste, and removing it is what makes any quota realistic.
Finally, forecast from METERING_DAILY_HISTORY and set next quarter's quota from a trend line with an agreed buffer, rather than a reactive percentage increase.