Diagnosing spill and queueing before adding more warehouse

Overview

An insurance group's nightly transformation pipeline started overrunning its window and colliding with the business day. The instinctive fix was a larger warehouse, which had already been tried once without much effect. AceMQ was engaged to find what was actually consuming the time.

Challenge

Two distinct problems were producing the same symptom. A small number of transformation queries were spilling to remote storage, which is orders of magnitude slower than local, and those long-running queries were occupying the warehouse while everything else queued behind them. Increasing warehouse size had helped the spilling queries slightly while making the queueing worse per credit spent.

Environment

Snowflake on Azure running dbt transformations orchestrated by Airflow across policy and claims data.

Approach

AceMQ used query history to separate execution time, queueing time, and spill volume per query so the two problems could be addressed independently. Query-level fixes came first because a query that spills will spill on any warehouse size that does not fit its working set.

Solution

• Separated execution time, queue time, and remote spill volume per query from query history
• Rewrote the joins and window functions in the worst-spilling queries to reduce intermediate result size
• Split the pipeline across separate warehouses so long transformations no longer blocked short jobs
• Configured multi-cluster scaling on the warehouse serving concurrent short queries
• Corrected clustering and filtering on the largest source tables to cut scanned micro-partitions
• Added spill volume and queue depth to pipeline monitoring as leading indicators of window overrun

Outcome

Technologies