Slow SQL Queries During Checkout Window, Databricks

​

At a glance

A row-level table of Databricks SQL queries that ran slow (above the warehouse latency threshold) during the active checkout window, correlated against a live drop in storefront orders. For a platform team, this answers one question: “is my data layer the reason checkout is stuttering right now?” Each row is one slow statement: the warehouse it ran on, the user or service principal that issued it, the elapsed time, and whether it overlapped a checkout conversion dip. When five or more slow queries co-occur with a measurable checkout drop in the same five-minute window, the card fires. This is the card you open at 14:00 on a flash-sale day when the orders graph wobbles and nobody knows why.

​

Calculation

1. Pull slow statements. The engine queries system.query.history (or the Query History API where the system table is not enabled) for the last five minutes, keeping rows where total_duration_ms is greater than the warehouse slow threshold. Each surviving row becomes a table entry: statement id, warehouse, executing identity, total_duration_ms, rows_produced, and the start_time to end_time span.
2. Read the checkout signal. In parallel, the connected storefront connector reports orders per minute for the same window. Vortex IQ compares that figure to the same-weekday, same-hour baseline (a rolling four-week median). A “checkout drop” is recorded when current order rate falls a configurable percentage below baseline (default 20%).
3. Correlate and count. The two streams are joined on the shared UTC window. The headline count is the number of slow statements; the alert state is slow_query_count > 5 AND checkout_drop = true. The detail (Slow SQL Queries During Checkout Window, broken down by row) drives the per-row table so an engineer can jump straight from “checkout dipped” to “these are the five statements that were blocking the warehouse when it dipped”.

​

Worked example

1. Five of the six slow statements are storefront serving queries on wh-serving-prod, all single-row lookups taking 8 to 11 seconds. A single-row lookup should return in tens of milliseconds. Eight seconds means the warehouse is queueing, not computing. The serving warehouse is saturated. Cross-check with SQL Warehouse Saturation %, which reads 96%.
2. The sixth row is the smoking gun. A merchandising analyst kicked off a 14-second, 38,901-row scan on wh-analytics, but the workspace had a shared cluster pool and wh-serving-prod had been configured to burst into the same pool. The analytics query starved the serving warehouse of slots. This is a noisy-neighbour incident, not a code regression.
3. The checkout drop is real and it is costing money. Orders fell from 38/min to 27/min, an 11/min shortfall. At an average order value of £62, that is roughly £680 of orders per minute not landing while the warehouse is jammed.

Cost framing while the warehouse is saturated:
  - Baseline orders/min: 38   Current: 27   Shortfall: 11/min
  - Average order value: £62
  - Revenue shortfall/min: 11 × £62 = £682
  - Window so far: 5 minutes  ->  ~£3,410 of delayed/lost orders
  - If it runs another 15 minutes before mitigation: a further ~£10,230

1. Single-row lookups taking seconds is the tell. When the duration is high but rows_produced is tiny, the warehouse is queueing, not crunching. That points at saturation or a noisy neighbour, not query SQL.
2. The cross-channel join is what makes this actionable. Five slow queries with healthy checkout is a yawn; five slow queries with a live order dip is a revenue incident. The card only pages on the combination.
3. Isolate serving from analytics. The most common cause of this card firing is a heavy BI or pipeline query stealing slots from the storefront-serving warehouse. Pair with DBU by Cluster (7d) to confirm which workloads share compute.

​

Sibling cards merchants should reference together

​

Reconciling against the source

Query History (in the SQL workspace, Query History tab) for the per-statement list with duration, status, and the user who ran it. Filter by warehouse and time range to match the five-minute window. system.query.history (Unity Catalog system table) to run the same filter as SQL: SELECT statement_id, compute.warehouse_id, executed_by, total_duration_ms FROM system.query.history WHERE start_time >= now() - INTERVAL 5 MINUTES AND total_duration_ms > 5000 ORDER BY total_duration_ms DESC. SQL Warehouse monitoring (the warehouse’s Monitoring tab) for queued vs running query counts, which confirm whether slowness is queueing or execution.

​

Known limitations / merchant FAQs

​

Tracked live in Vortex IQ Nerve Centre