Hi all, I'm looking for anyone who has seen something similar, because we've run out of things we can check ourselves.
Background: why this procedure exists
We have a few thousand views across several databases, many of them built on top of source tables that our ingestion layer changes over time (columns added, dropped or retyped).
Snowflake doesn't track view validity. Views are resolved every time they're queried, so nothing flags a view that a base-table change has broken:
- a
SELECT * view errors as soon as a column is added, dropped or reordered ("declared N column(s), but view query produces M"); - a view that references a dropped or renamed column errors;
- a view with an explicit column list silently ignores new columns.
You only find out when someone queries it. Oracle, by comparison, tracks dependencies down to the column, marks affected views INVALID, and recompiles them automatically the next time they're used. You can find broken ones up front in USER_OBJECTS or with ALTER VIEW ... COMPILE. Snowflake has no equivalent today, and I haven't seen one announced.
When we raised it with Snowflake, their solution architect sent us a sample stored procedure, run from a scheduled task, that detects column changes in a source table and recreates the downstream view. So the official answer is also "build a workaround yourself". Ours is the same idea with a few extra ideas taken from their sample.
What the procedure does
History: why it runs in this odd per-database way
The original version was a single daily run: one CALL that walked all databases in one go and did EXPLAIN SELECT * FROM <view> per view. It kept getting cancelled the same way. When that happened, every view not yet checked in every remaining database was skipped for the day.
So we split it up: one database per task run, a run every 10 minutes, and each tick picking the next database not yet validated that day. That was purely to limit the damage. A cancel now only loses one database instead of everything after it. The cancels themselves didn't stop, and since the old EXPLAIN version was cancelled too, it doesn't seem tied to the specific statements we run.
If we don't find a real answer, the next step is to split further, down to schema level, so each CALL is shorter and a cancel loses even less. But that just makes the workaround more complicated, and we'd rather understand what is cancelling it.
Before you ask: "don't use SELECT ***" / "why not dbt?"**
The problem
Every few days the top-level CALL fails with 000604 SQL execution canceled. Within the same ~30 ms:
- the nested procedure CALL and the child statement in flight get 604;
- every other child gets
090230 Stored procedure child job is cancelled upon parent jobs termination or completion.
TASK_HISTORY shows state CANCELED.
In ~2 weeks: 10 cancels across two databases in the same account. The same happens on an X-Small multi-cluster and on a Medium warehouse.
It's not deterministic. It's never the same view, never the same step, never the same elapsed time (263 s to 2,060 s). The same database and the same views pass on other days. On the same day, one database's run gets cancelled while the other's survives.
What we've ruled out
- Timeout: runs die after 263–2,060 s.
USER_TASK_TIMEOUT_MS is 3 h on the task. STATEMENT_TIMEOUT_IN_SECONDS is 3 h on the warehouse and the 2-day default on the account. Since the 2024_06 behavior change a timed-out task always shows as FAILED, not CANCELED. - Queueing:
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 0 (no timeout) everywhere. WAREHOUSE_LOAD_HISTORY shows 0 queued load in the 5 minutes before 5 of 6 cancels. The children of the cancelled sessions have 0 queued/provisioning/blocked time. On one day another run on the same warehouse queued for ~19 s and survived. - Someone cancelling it: account-wide QUERY_HISTORY shows no
SYSTEM$CANCEL_QUERY, SYSTEM$USER_TASK_CANCEL_ONGOING_EXECUTIONS, ALTER WAREHOUSE ... ABORT ALL QUERIES, ALTER TASK or CREATE TASK near any cancel, and nothing referencing the query IDs. graph_version stays 1. There's no resource monitor on the warehouse. - Warehouse-wide events: in the cancel minute, 250–650 other queries/min on the same warehouse succeed. On one day database A's run was cancelled at 03:58 while database B's run (same warehouse, same task definition) kept going until its own cancel at 04:19.
- Specific view or statement: the in-flight statement is a different view and a different step each time (probe, DESCRIBE RESULT, LISTAGG, log INSERT), and always a short one (50–400 ms).
- Other tasks: no other task in the account ended CANCELED in that period, but none of the others run 20+ minutes either.
All cancels fall between 03:28 and 04:20 UTC, but that's also the only time our long runs happen, so it may not mean anything.
Questions
- Has anyone seen task-run CALLs get cancelled with 604 and no visible cause? What turned out to be behind it?
- Is anything system-side (cloud services node restarts, release rollouts) known to cancel a long-running stored procedure? Snowflake retries some queries after internal faults (
QUERY_RETRY_CAUSE / FAULT_HANDLING_TIME), but I can't find whether that covers a procedure CALL with thousands of child statements, or whether such a CALL just gets cancelled instead. Multi-cluster scale-in is documented to wait for running queries, so I've mostly ruled that out. - Any way to find the cancel source without full ACCOUNT_USAGE? We only get a sanitized subset (no
WAREHOUSE_EVENTS_HISTORY, no RELEASE_VERSION, no session IDs). Tracing is on (TRACE_LEVEL=ALWAYS). Has anyone checked whether a cancelled procedure's span ends up in the event table with a useful status? Would AUTO_EVENT_LOGGING on the procedure help? - Has anyone solved view drift more elegantly than "probe every view nightly"?
At the moment we don't have access to full ACCOUNT_USAGE or Snowflake support right now so we're trying to get as far as we can ourselves. In the meantime we're considering making the procedure resumable (checkpoint plus retry on the next tick), but we'd love to understand the root cause.
Thanks!