Databricks & Snowflake · Chapter 7 of 10
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.