Slow Analytics Queries During Checkout Window, Snowflake

​

At a glance

A live table that lists individual slow Snowflake queries running during an active checkout window, time-aligned against a dip in storefront conversion. Most Snowflake performance cards look at the warehouse in isolation. This one asks a sharper, revenue-shaped question: are heavy analytics queries running right now, on the same compute path, at the exact moment shoppers are trying to check out, and is checkout suffering because of it? Each row is one slow query (its text, warehouse, duration, queue time, and the user or job that ran it), surfaced only when slow queries cluster inside a checkout-conversion drop. The classic culprit is a heavy ad-hoc analytics query or a scheduled report sharing a warehouse (or a real-time feature lookup) that the live storefront also depends on: the report queues the lookups, the lookups time out, the checkout page stalls, conversion falls. The >5 slow queries co-occur with checkout drop alert fires when at least five slow queries land inside a checkout-conversion dip in the same five-minute window, the signature of analytics load directly stealing from sales.

​

Calculation

slow_queries(window) = queries where total_elapsed_time > slow_threshold
                       in the trailing 5 minutes
checkout_drop        = current conversion_rate < baseline conversion_rate
alert fires when COUNT(slow_queries) > 5 AND checkout_drop is true

• Slow is measured by total elapsed time, and queue time is part of it. A query’s total_elapsed_time includes compilation, queueing, and execution. A query that is fast to execute but spent thirty seconds queued behind a heavy report still counts as slow here, because from the storefront’s point of view the wait is what hurts. The card reads queued_overload_time and queued_provisioning_time so you can see when the delay is contention, not the query itself.
• Shared warehouses are where checkout and analytics collide. If a real-time feature lookup, a personalisation call, or a checkout-time inventory check runs on the same warehouse as ad-hoc BI, a heavy analyst query consumes the warehouse’s concurrency slots and the live lookups queue behind it. The fix is architectural: isolate the live path on its own warehouse.
• Snowflake is built for analytics, not single-digit-millisecond OLTP. Using a Snowflake warehouse directly in a synchronous checkout path is risky by design. This card exists partly to catch accounts that have drifted into that pattern, where a perfectly normal analytics query becomes a checkout hazard.
• The five-minute window is deliberate. Checkout contention is bursty: a report kicks off, the queue forms, conversion dips, the report finishes, recovery follows, often inside ten minutes. A 30-day or even 1-hour view would smooth this into invisibility. The short window keeps each contention event sharp and individually inspectable.
• The conversion join is what turns a perf signal into a revenue signal. Five slow queries on their own are an ops curiosity. Five slow queries during a measured conversion dip are money leaving. The card only escalates when both are true, which keeps it quiet during heavy-but-harmless overnight reporting.

​

Worked example

1. Confirm the contention, not a slow query. The queue-time column is the tell: the checkout lookups spent ~10s queued and milliseconds executing. That is concurrency starvation on a shared warehouse, not bad SQL. Cross-check Warehouse Saturation % and Avg Query Queue Depth per Warehouse for LIVE_WH, both will be pinned.
2. Clear the immediate contention. Cancel the analyst query if it is non-urgent (SELECT SYSTEM$CANCEL_QUERY('01b4f2')). The checkout lookups drain their queue within seconds and conversion recovers.
3. Fix the architecture so it cannot recur. Move the analyst workload onto a separate BI_WH, and protect the live path: enable multi-cluster (scale-out) on LIVE_WH so concurrency bursts get extra clusters instead of a queue, and set a STATEMENT_QUEUED_TIMEOUT_IN_SECONDS on the checkout role so a lookup fails fast and falls back rather than spinning for ten seconds. The real lesson is that a synchronous checkout dependency should never share a warehouse with ad-hoc analytics.

Revenue framing of the five-minute event:
  Conversion baseline 3.1%, observed 1.9% during the window
  Checkout sessions in the window: ~260
  Orders expected at baseline: ~8.1; orders observed: ~4.9
  Lost orders: ~3.2; average order value £74 -> ~£237 lost in five minutes
  If the analyst's report ran 30 minutes unspotted: ~£1,400 lost
Caught by the alert at 13:05: one CANCEL_QUERY, conversion recovers in under a minute.

1. Read the queue-time column first. It separates a genuinely slow query from a fast query starved of capacity. When the live lookups are slow purely because of queue time, the problem is the warehouse it shares, not the query itself.
2. A synchronous checkout dependency on a shared analytics warehouse is the root cause, not the symptom. Cancelling the heavy query buys time; isolating the live path on its own (ideally multi-cluster) warehouse is the fix that stops this card ever firing again.
3. The conversion join is what makes this urgent. The same seven slow queries at 02:00 with no shoppers present would be a non-event. The card escalates only when slow queries and a real conversion dip coincide, which is exactly the moment that costs orders.

​

Sibling cards to reference together

​

Reconciling against the source

Slow queries with queue breakdown (live, low latency): SELECT query_id, warehouse_name, user_name, total_elapsed_time/1000 AS secs, (queued_overload_time + queued_provisioning_time)/1000 AS queue_secs, LEFT(query_text, 80) FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(END_TIME_RANGE_START=>DATEADD('minute', -5, CURRENT_TIMESTAMP()))) WHERE total_elapsed_time > 5000 ORDER BY secs DESC; Account-wide slow-query history (complete, some latency): the same fields from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY filtered on total_elapsed_time and start_time. Cancel a runaway: SELECT SYSTEM$CANCEL_QUERY('<query_id>'); or SYSTEM$CANCEL_ALL_QUERIES(<session_id>) for a whole session. Managed console: Snowsight under Monitoring -> Query History lets you filter by warehouse, duration, and time, and each query’s profile shows the queue-versus-execution split visually. Admin -> Warehouses shows live running and queued counts per warehouse. The checkout-side conversion dip is confirmed in the commerce platform’s own analytics, not in Snowflake.

​

Known limitations / FAQs

​

Tracked live in Vortex IQ Nerve Centre