Snowflake QPS Spike vs Ecom Order Rate, Snowflake

​

At a glance

A dual-axis chart overlaying Snowflake query rate (queries per interval across the account’s warehouses) against the storefront’s order rate over the trailing hour. In a healthy account these lines correlate loosely: more shopping drives more dashboard refreshes, more ad-hoc analysis, more scheduled reporting, so query volume rises with order volume across the day. The shape this card hunts for is a query spike with no matching order spike. When QPS jumps but orders stay flat, the extra queries are not being driven by genuine business activity. The usual causes are a dashboard storm (a popular Looker or Tableau report set to auto-refresh, or a workbook everyone opened at once), a runaway scheduled job stuck in a retry loop, or a bot or scraper hammering an exposed query endpoint. Every one of those burns credits and can starve the warehouse of capacity for the queries that actually matter, all while contributing nothing to revenue. The qps spike with no order spike alert is the divergence detector for exactly this pattern.

​

Calculation

qps(t)        = queries started in bucket t (from QUERY_HISTORY)
order_rate(t) = orders created in bucket t (from linked ecom connector)

qps_baseline  = rolling median of qps over the prior hour
alert fires when qps(t) >> qps_baseline
                 AND order_rate(t) ≈ order_rate_baseline (flat)

• Query rate normally tracks human and scheduled demand. BI dashboards, ad-hoc exploration, and scheduled reports all rise and fall with the business day, which itself tracks shopping activity. That loose correlation is what makes the order line a useful baseline: when QPS rises for a real reason, orders are usually rising too.
• A dashboard storm multiplies one action into hundreds of queries. A single Looker dashboard with twenty tiles set to auto-refresh every minute is twenty queries a minute, per open viewer. A board-meeting report opened by thirty people at 09:00, or an auto-refresh someone left running over a weekend, produces a query wall with zero new orders behind it.
• A runaway scheduled job retries silently. A dbt model or a Snowflake Task that errors and is configured to retry can re-fire on a tight loop, each attempt a fresh query. Query count climbs steeply; the work is the same failing statement over and over; orders are untouched.
• Bots and scrapers target exposed endpoints. Where a reverse ETL tool, an embedded analytics API, or a public data app sits in front of Snowflake, a scraper or a misbehaving integration can drive a flood of identical queries. This is the security-flavoured case and pairs with Failed Logins (24h) when the source is unauthenticated abuse.
• The spike costs twice. Beyond the direct credit burn, a query flood consumes the warehouse’s MAX_CONCURRENCY_LEVEL slots. Legitimate queries then queue behind the junk, so a dashboard storm on one warehouse can slow genuine analytics that shares it. This is why the card sits in Revenue at Risk, not just Cost.

​

Worked example

1. Find who or what is querying. Run a live query against INFORMATION_SCHEMA.QUERY_HISTORY grouped by user_name, role_name, and query_text over the last ten minutes. In this case a single Looker service account is responsible for 88% of the new queries, all variations of the same dashboard tile SQL.
2. Identify the trigger. The Looker dashboard in question had an auto-refresh interval accidentally set to 30 seconds and was left open on a wall-mounted office screen over the weekend, plus a marketing report someone scheduled to email hourly that fans out to forty tiles. Two sources, one signature.
3. Stop the bleed, then prevent recurrence. Short term: kill the runaway sessions if needed (SELECT SYSTEM$CANCEL_ALL_QUERIES(<session_id>) per offending session, or set a tighter STATEMENT_TIMEOUT_IN_SECONDS on the role). Lengthen the dashboard refresh interval to 15 minutes, enable Looker result caching, and put a Resource Monitor credit quota on BI_WH so a future storm self-suspends the warehouse before the bill runs away.

Cost and capacity framing of the spike (BI_WH is a Medium, 4 credits/hour, $3/credit):
  Excess queries over the hour: ~22,000 above baseline
  These kept BI_WH from auto-suspending all hour: ~4 credits = $12 in raw burn
  Worse: BI_WH hit its concurrency ceiling, so 9 genuine analyst queries
    queued for up to 40s each (see Warehouse Saturation and Queue Depth)
  Had the storm run the full weekend unspotted: ~$430 burn + two days of slow BI
Caught by the alert at 10:30: refresh-interval change + a resource monitor, minutes.

1. A query spike with flat orders is never demand; it is always a mechanism. Dashboard auto-refresh, a retry loop, or a scraper. The order line is what tells you it is not real business, so you can hunt the mechanism without second-guessing whether it is just a busy day.
2. The damage is concurrency, not just credits. A storm rarely bankrupts you in an hour, but it can quietly push a warehouse to its concurrency ceiling so that real analytics queues. Always read this card next to Warehouse Saturation %.
3. A resource monitor is the seatbelt. The durable fix for runaway query cost is a credit quota that suspends the warehouse automatically. The alert catches the event; the resource monitor caps the worst case while you respond.

​

Sibling cards to reference together

​

Reconciling against the source

Live query rate by source (low latency): SELECT user_name, role_name, COUNT(*) FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(END_TIME_RANGE_START=>DATEADD('minute', -15, CURRENT_TIMESTAMP()))) GROUP BY 1, 2 ORDER BY 3 DESC; localises a storm to a user or service account fast. Account-wide query counts (complete, some latency): SELECT DATE_TRUNC('minute', start_time) AS m, COUNT(*) FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY WHERE start_time >= DATEADD('hour', -1, CURRENT_TIMESTAMP()) GROUP BY 1 ORDER BY 1; Repeated identical queries (the dashboard / retry fingerprint): group QUERY_HISTORY by query_hash (or a LEFT(query_text, 80)) to surface the same statement firing hundreds of times. Managed console: Snowsight under Monitoring -> Query History offers filters by user, warehouse, and time, and Admin -> Warehouses shows live running and queued counts. Resource Monitors live under Admin -> Cost Management and are the native way to cap a runaway warehouse.

​

Known limitations / FAQs

​

Tracked live in Vortex IQ Nerve Centre