Analyzing Queries
Where to look
Query History lists every statement with duration, status, warehouse, user, and rows/bytes read. Clicking a query opens Query Profile, a visual plan showing operators, rows, and time per stage. For Spark-level work, the Spark UI shows stages, tasks, shuffle, and skew.
The metrics that matter
- Queue time vs. execution time vs. fetch time. Total duration is usually dominated by queue time on a busy warehouse, not by slow SQL.
- Bytes/rows scanned. Large scans usually mean missing partition or clustering filters, or
SELECT *on wide tables. - Spill to disk / shuffle size. Indicates insufficient memory or a poorly chosen join strategy.
- Skew. One task taking far longer than its siblings means a hot key in a join or
GROUP BY.
Diagnostic toolkit
EXPLAIN shows the logical and physical plan. Filtering early, joining on selective keys, avoiding SELECT *, and using OPTIMIZE/clustering to enable file pruning all reduce scanned bytes. Broadcast joins help when one side is small.
Traps
- Reading only "duration" and blaming the query when the time was spent queued.
- Assuming more warehouse size fixes a bad plan - it often just makes the bad plan faster but still expensive.
- Ignoring result caching when comparing before/after timings.
- Confusing bytes read from storage with bytes scanned after pruning.
Rule of thumb
Read the plan before resizing anything. First check queue time (operational fix: more clusters). If execution time dominates, check bytes scanned and shuffle/spill (query fix: filters, pruning, join strategy).
Worked example
A report takes 90 seconds. Query Profile shows 70 seconds queued and 20 seconds executing with 4 GB scanned. The fix is concurrency - raise min/max clusters - not rewriting the SQL or upsizing.
Operational extras
Use query history filters to find the top consumers per warehouse, and schedule alerting on long-running queries rather than waiting for complaints.