Databricks & Snowflake · Chapter 7 of 16
Making queries fast on each
The specific moves that matter, and the diagnostic that tells you which one you need.
Performance work on both platforms is the same discipline: find out what the engine is actually doing before changing anything. The tools differ and the reasoning does not.
Diagnose first
| Databricks | Snowflake | |
|---|---|---|
| Where you look | Spark UI — stages, tasks, shuffle read/write | Query Profile — the operator tree with time and bytes |
| Reading too much | Files scanned versus files pruned | Partitions scanned versus total |
| Skew | One task far longer than its peers in a stage | Uneven partition scan, or a heavy join operator |
| Memory pressure | Spill to disk in stage metrics | 'Bytes spilled to local/remote storage' |
| Wrong join | ShuffleHashJoin where Broadcast would fit | Join operator showing a very large intermediate |
Databricks moves, in order of usual payoff
- Make the filter prunable — partition or Z-order/liquid-cluster on the column you filter. Reading less always beats computing faster.
- Get the join broadcast when one side is small. Frequently a ten-times change.
- Fix skew — salt the hot key, or enable adaptive execution's skew handling.
- Compact small files. Thousands of tiny files means planning overhead dominates.
- Cache deliberately, and only what is reused; caching everything wastes memory and causes spill.
- Right-size rather than upsize. A job bottlenecked on a shuffle does not get faster with more cores.
Snowflake moves, in order of usual payoff
- Improve pruning — cluster on the selective column you filter, or load in an order that clusters naturally.
- Filter earlier and project less. Selecting fewer columns genuinely reduces bytes read.
- Avoid functions on the filtered column.
WHERE DATE(ts) = …can defeat pruning whereWHERE ts >= … AND ts < …does not. - Size up if spilling; add clusters if queuing. Those are different problems.
- Materialise repeated expensive work rather than paying for it per dashboard load.
The measurement discipline that applies to both
Run the change on a cold warehouse or cluster, or you are measuring cache. On Snowflake, an identical query returns from the result cache for 24 hours — so a ‘huge improvement’ measured by re-running the same SQL is usually nothing at all.
And record the before number with a date. Optimisation claims without a recorded baseline are the same problem as everywhere else on this site.
The four things that actually make a query slow
Query tuning on a modern warehouse is not index selection — there usually are no indexes. It is almost entirely about how much data the engine has to read, and how much it has to move between machines. Four causes account for nearly everything.
| Cause | Symptom | What to do |
|---|---|---|
| Reading too much | Scan bytes far larger than the result | Filter on the partition or clustering column directly. Wrapping it in a function defeats pruning silently — the query still works, it just reads everything |
| Shuffling too much | A join stage dominating the plan | Reduce before you join. Filter and aggregate first; join the small result, not the large source |
| Skew | One task running long after all others finish | A join key with a dominant value — often a null or a default. Salt it, or handle the hot key separately. This is the single most common cause of a job that is 'nearly done' for an hour |
| Small files | Planning time exceeding execution time | Compact. Thousands of tiny files mean thousands of open operations before any work begins |
Skew deserves the most attention because it is the least obvious. The dashboard says 99% of tasks complete; the job runs for another forty minutes. That is one partition holding most of the data, and no amount of extra compute fixes it — you are adding machines that have nothing to do. More cluster is the intuitive response and it is money spent on idle workers.
Read the plan before changing anything. It is the fastest habit to acquire and the least practised.
Three things to look for, in order: how many bytes are actually scanned versus how many the table holds — if pruning is working this is a small fraction; whether the largest stage is a scan or an exchange, which tells you immediately whether your problem is reading or moving; and whether task durations within a stage are uniform, which is how you spot skew without guessing.
The most common self-inflicted wound is defeating partition pruning by applying a function to the partition column in the filter. It is invisible — the query returns correct results, it just reads the entire table to do it, and the only evidence is a scan-bytes number nobody looked at.
And the second most common is scaling up to fix skew. Check task duration uniformity before you resize anything; if one task is the problem, more workers do nothing except cost.
Check yourself