Snowflake Production Errors & Observability: Query Timeouts & Credit Governance

[!NOTE] Unconstrained compute in cloud data platforms can lead to runaway cloud bills. Enterprise Snowflake environments deploy Resource Monitors to automatically pause warehouses when credit thresholds are breached.

1. Credit Governance: Resource Monitors

• Assign Resource Monitors at Account or Warehouse level.
• Triggers:
  • At 75%: Send notification email to DataOps.
  • At 90%: Send notification email and suspend warehouse after in-flight queries complete.
  • At 100%: Suspend immediately (cancels running queries) to cap billing.

Staff Triage: Lock Contention, Statement Timeouts, 16MB VARIANT Limit & Micro-Partition Blowups

• DML Lock Contention (Statement reached its lock wait timeout): Caused when dozens of parallel Airflow/dbt jobs run simultaneous UPDATE/MERGE statements against one table (or an open uncommitted transaction hangs). Fix by batching mutations, ensuring AUTOCOMMIT = TRUE, or consolidating feeds via Snowpipe Streaming + Dynamic Tables.
• Max LOB size (16777216) exceeded (VARIANT JSON Ceiling): Snowflake VARIANT/VARCHAR columns max out at 16 MB uncompressed per row. Always STRIP_OUTER_ARRAY = TRUE when loading giant API JSON response arrays via COPY INTO, or split oversized blobs into chunked Bronze rows before parsing.
• Queued (overload) vs Execution Diagnosis: If Query Profile shows 90% spent in Initialization/Queued or Metadata pruning, inspect WAREHOUSE_LOAD_HISTORY (AVG_QUEUED_LOAD), unpartitioned multi-statement transactions, or overly fragmented micro-partitions from tiny row-by-row inserts (1 row per micro-partition).