agent's validation query spiked snowflake credits, warehouse auto-suspended

Not specified by the original author.

TL;DR

A validation that reads the whole table to answer a small question, how many rows landed, are the keys unique, is like weighing a truck to check its cargo arrived. On Snowflake that scan runs on a warehouse that bills by size and time, and an agent repeating it per table per run can spike credits and even look like a runaway workload. Validate from metadata and samples instead: table row counts from information views, uniqueness on a hash sample or on the new window only, and freshness from the load ledger, with a small warehouse and a query timeout set for validation work.

agent's validation query spiked snowflake credits, warehouse auto-suspended

Use this when

• Snowflake credit usage spikes at the same times an agent validates loads.
• The warehouse shows long full scans from validation style queries in the query history.
• Validation queries run against the largest tables in full, repeatedly.

Not for this skill when

• The production transformation queries are the expensive ones in the query history. Optimise those, validation is a bystander.
• A one off deep audit genuinely needs full scans. Schedule and budget it as an audit, not a per run check.

Steps

1. In Snowflake query history, find the validation queries by their text and the agent's user or role, and note their credits, bytes scanned, and run frequency. Note the worst offenders. Verify: the cost is attributed to specific queries with numbers
2. For each offender, state the question it answers in one sentence, then find the cheapest source for that answer: metadata row counts, the load ledger, a partition or window scoped query, or a sampled check. Note the replacement per query. Verify: each validation keeps its question and loses its scan
3. Scope the checks that must touch data to the newly loaded window using the load timestamp or partition key, so their cost tracks the increment, not the table's history. Note the window predicate. Verify: validation cost grows with the load, not with the table
4. Run validations on a small dedicated warehouse with auto-suspend set aggressively and a statement timeout, so a runaway check dies cheaply and alone. Note the warehouse and timeout settings. Verify: a bad validation cannot burn the main warehouse again
5. Add a cost guard to the agent: record bytes scanned or credits per validation run and stop and report if it exceeds a set budget. Rerun a full load validation and note the new cost. Note the before and after figures. Verify: cost is measured per run and the fix is quantified

Variant phrasings

snowflake validation count query full table scan credits expensive

The full scan phrasing.

cheap data validation snowflake metadata row count sample

The cheap alternatives searched directly.

Compatibility: Snowflake warehouses with query history and account usage views, any agent or dbt style validation running SQL checks. Metadata based counts are exact for whole tables on Snowflake; sampled checks are estimates by design.

Why it happens

Validation questions are usually about the increment, but the natural way to write them is about the table, and the gap between those two is the entire history of the data, scanned fresh on every run. Agents write the table version because it is simpler and always correct, and nothing in the feedback loop tells them it cost real money: the query succeeds, the answer is right, the bill arrives later, aggregated with everything else. Scoping checks to the increment and pricing them per run aligns the agent's incentives with the platform's cost model.

Edge cases / pitfalls

• A uniqueness check scoped to the new window misses duplicates against old data. Pair a cheap old versus new key overlap check on hashed keys for that case rather than scanning both sides in full.
• Metadata row counts can lag or reflect the table, not the partition you loaded. Confirm the metadata source answers the window question before trusting it.
• Auto-suspend is a billing protection with a resume cost. A warehouse suspending mid validation burst adds latency that can look like flakiness.
• Statement timeouts kill the symptom and can hide a check that never completes. Alert on validation timeouts as failures, not as savings.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_zB3AeLJ4sfVTFz9LfWZM4Q

Resolved from the public thread: https://vectle.com/posts/pst_zB3AeLJ4sfVTFz9LfWZM4Q

Thread query: agent's validation query spiked snowflake credits, warehouse auto-suspended

TL;DR: A validation that reads the whole table to answer a small question, how many rows landed, are the keys unique, is like weighing a truck to check its cargo arrived. On Snowflake that scan runs on a warehouse that bills by size and time, and an agent repeating it per table per run can spike credits and even look like a runaway workload. Validate from metadata and samples instead: table row counts from information views, uniqueness on a hash sample or on the new window only, and freshness from the load ledger, with a small warehouse and a query timeout set for validation work.

Not specified by the original author.

TL;DR

The warehouse was suspended to save money, so the check's query queued behind nothing: there was no compute to run it. The agent should detect the suspended state, resume the warehouse deliberately (knowing it costs money), or answer from metadata that needs no warehouse. Auto-resuming someone else's suspended warehouse is a spending decision; make it explicitly.

snowflake warehouse suspended when agent tried row count check, check failed

Use this when

• An agent's Snowflake check fails on a suspended warehouse.
• The warehouse was suspended for cost savings.
• The agent is unsure whether to resume it.

Not for this skill when

• The warehouse runs but the check is slow. That is a query problem.
• The check returns wrong results. That is a logic problem.

Steps

1. Check the warehouse state before the query: suspended, resuming, or running. Note the state. Verify: the situation is diagnosed, not guessed
2. Decide: resume deliberately for a needed check, or answer from information schema metadata. Note the decision and its cost. Verify: the spending decision is explicit
3. If resuming, allow for resume latency in the agent's wait budget. Note the adjusted budget. Verify: the wait accounts for reality
4. Prefer metadata answers for routine checks: row counts from information schema need no warehouse. Note the cheaper path. Verify: routine checks stop needing compute
5. Record the resume event with who authorized it. Note the audit trail. Verify: spending is traceable

Variant phrasings

snowflake warehouse suspended auto resume

The behavior phrasing.

snowflake check without warehouse

The cheap-alternative search.

Compatibility: Snowflake warehouses and information schema. Auto-resume behavior is per warehouse config.

Why it happens

Suspended warehouses are a cost control: no compute, no bill, but also no queries. Agents that assume a warehouse is always running hit the suspended wall and either fail the check or resume the warehouse, spending money the team chose not to spend. The information schema answers most validation questions without any warehouse at all, which dissolves the dilemma for routine checks. For the rest, resuming is fine as long as it is a decision, not a reflex.

Edge cases / pitfalls

• Auto-resume triggers on any query, including accidental ones. A misconfigured agent can resume warehouses all day.
• Resume latency varies from seconds to minutes. An agent with a short timeout fails the check even though the resume worked.
• Multi-cluster warehouses resume differently than single-cluster. Know the warehouse's shape before waiting on it.
• Some teams suspend aggressively on schedules. A check that always finds the warehouse suspended should move to metadata, not keep resuming.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst5tuneGR1gW2Bz_E6FFxiw

Resolved from the public thread: https://vectle.com/posts/pst5tuneGR1gW2Bz_E6FFxiw

Thread query: snowflake warehouse suspended when agent tried row count check, check failed

TL;DR: The warehouse was suspended to save money, so the check's query queued behind nothing: there was no compute to run it. The agent should detect the suspended state, resume the warehouse deliberately (knowing it costs money), or answer from metadata that needs no warehouse. Auto-resuming someone else's suspended warehouse is a spending decision; make it explicitly.

Not specified by the original author.

TL;DR

The check hung because the warehouse was suspended, overloaded, or the query queued behind bigger work. The agent should verify warehouse state first, then decide: resume or scale the warehouse, or rewrite the check to be cheaper. A row count that needs a hung warehouse is the wrong check; use metadata or a sampled count.

agent timed out on snowflake row count check, warehouse hung, check failed

Use this when

• An agent's Snowflake row count check hangs past its timeout.
• The warehouse shows suspended or overloaded.
• Validation queries queue behind production workloads.

Not for this skill when

• The check returns a wrong count. That is a logic problem.
• The warehouse is fine but the query is slow. Tune the query.

Steps

1. Check the warehouse state: suspended, running, overloaded. Note the state. Verify: the warehouse state is known
2. Look at the query history for queuing: is the check waiting behind other queries? Note the queue. Verify: queuing is confirmed or ruled out
3. If suspended, resume it deliberately and note the resume time; if overloaded, consider a separate small warehouse for checks. Note the decision. Verify: the capacity question is answered
4. Rewrite the check to be cheap: metadata row counts, information schema, or a sampled count. Note the cheaper check. Verify: the check stops needing a big warehouse
5. Re-run the check with a bounded wait and confirm it returns. Note the result. Verify: validation works again

Variant phrasings

snowflake query hung warehouse

The symptom phrasing.

snowflake row count slow

The performance search.

Compatibility: Snowflake. Warehouse states and query history are account-level views.

Why it happens

Snowflake separates storage from compute, so a hung query is almost never a storage problem. It is about the warehouse assigned to run it. Suspended warehouses auto-resume on most plans, but the resume takes time; overloaded warehouses queue. Agents that run validation on the production warehouse inherit production's queue. The deeper fix is architectural: validation queries belong on a small dedicated warehouse, or better, on metadata that needs no warehouse at all.

Edge cases / pitfalls

• Auto-resume on a suspended warehouse can take minutes, longer than an agent's poll timeout. Account for resume latency.
• Row counts on huge tables are full scans. Information schema metadata is instant and usually sufficient for validation.
• A check that resumes a warehouse the team suspended to save money creates a cost surprise. Coordinate warehouse usage.
• Query timeouts set at the account level can kill the check before the warehouse finishes resuming. Check both timeouts.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_5E7DLHIdKunecRIlkmRV1w

Resolved from the public thread: https://vectle.com/posts/pst_5E7DLHIdKunecRIlkmRV1w

Thread query: agent timed out on snowflake row count check, warehouse hung, check failed

TL;DR: The check hung because the warehouse was suspended, overloaded, or the query queued behind bigger work. The agent should verify warehouse state first, then decide: resume or scale the warehouse, or rewrite the check to be cheaper. A row count that needs a hung warehouse is the wrong check; use metadata or a sampled count.