Databricks & Snowflake · Chapter 13 of 16
Performance engineering with the actual numbers
File sizing, the three layout strategies and why only one is reversible, clustering economics, and the caching layer that silently controls your bill.
This chapter is deliberately full of specific numbers, because performance engineering on these platforms is mostly a matter of knowing thresholds that are documented and rarely read.
File sizing, and the auto-tune curve
The small-file problem is the most common performance pathology in a lakehouse: every file costs a metadata entry, an object-storage request and a task, so a table of ten million tiny files spends all its time on overhead. The auto-tuning curve is worth internalising because it tells you what ‘correct’ looks like:
| Table size | Target file size | Approximate file count |
|---|---|---|
| 10 GB | 256 MB | ~40 |
| 1 TB | 256 MB | ~4,096 |
| 3 TB | 307 MB | ~12,100 |
| 5 TB | 512 MB | ~17,300 |
| 10 TB | 1 GB | ~24,400 |
| 100 TB | 1 GB | ~114,400 |
- Note the shape: the target grows with table size and then caps at 1 GB, so beyond about 10 TB the file count grows linearly. That cap is a deliberate trade — larger files prune less finely and cost more to rewrite.
- Optimised writes target 128 MB and are on by default for MERGE, UPDATE and DELETE — which is why those operations are slower than a plain append and produce better-shaped output.
- Bin-packing compaction is idempotent. Running it twice on the same data has no effect the second time, and on a partitioned table it compacts within partitions — so a table partitioned too finely can never reach the target file size no matter how often you optimise it.
- Iceberg's own default target is 512 MB, and its delete-file target is 64 MB. Same problem, a different opinion, and worth knowing when you are reading someone else's table.
The three layout strategies, and the one that is reversible
Partitioning is a directory-level physical commitment. It pays off only when the partition count is modest and essentially every query filters on the partition column. It hurts in three ways: too fine and you create small files that compaction cannot fix; queries filtering on anything else get no benefit at all; and on Delta it disables row-level concurrency, as covered in the internals chapter.
Z-ordering is a multi-dimensional sort applied during optimisation. It is not incremental, so re-running redoes the work, and it generates the write-versus-optimise conflict class.
Liquid clustering is the successor to both, and the reason is one sentence from the documentation: you can redefine clustering keys without rewriting existing data. The keys live in table metadata rather than in the schema or the directory structure, so changing your mind is a metadata operation followed by gradual reorganisation rather than a full rewrite.
The clustering-on-write minimums are the number nobody knows, and they explain most ‘why isn't my table clustering’ reports.
One clustering column needs a 64 MB transaction on a managed table, or 256 MB otherwise. Four columns need 1 GB, or 4 GB otherwise.
Write smaller transactions than that and data lands unclustered, silently. A streaming job writing 10 MB micro-batches into a four-key clustered table clusters nothing on write — it relies entirely on the background optimisation to fix it later.
Two more operational facts. Optimisation is incremental by default and will not rewrite already-well-clustered files, so a full reclustering is required when you first enable clustering or change keys — and it can take hours on a large table. And enabling clustering upgrades the table protocol, turning on deletion vectors, row tracking and V2 checkpoints, which is how you get row-level concurrency as a side effect.
The honest 2026 advice for a new Delta table: do not partition, do not Z-order, cluster by the columns you filter on — or let it choose automatically, which only selects keys when the modelled saving exceeds the clustering overhead.
Snowflake clustering, and a billing change worth watching
Automatic clustering is not continuous — it triggers only when the table would benefit — and it does not block DML. The interesting development is that two generations now coexist, with a documented cutover date, and they bill on completely different principles.
- The classic mechanism bills serverless compute at a 2× multiplier. Its cost is proportional to how much churn the reclusterer decides to fix, which makes it unpredictable — historically the single most common surprise line on a Snowflake bill.
- The newer mechanism bills on volume: credits per uncompressed gigabyte ingested, multiplied by an overlap factor between zero and one. Append-only workloads pay close to nothing; update-heavy ones pay more.
- Why that matters conceptually: the cost is now proportional to a quantity the data engineer already knows and controls, rather than to an internal scheduling decision. That is the interesting part, not the rate.
Alongside it sits the search optimization service, which builds a persistent search access path tracking which values might be found in each micro-partition. The clean framing: clustering makes ranges prunable; search optimization makes needles findable. They solve different query shapes, they both cost extra serverless compute, and neither helps an analytical query that scans most of the table.
Caching, which quietly controls the bill
Both platforms have caching layers, and on Snowflake in particular the caching behaviour explains most unexplained cost movements.
| Layer | What it caches | How you lose it |
|---|---|---|
| Databricks disk cache | Remote Parquet data files only, as local NVMe in a fast intermediate format | Worker decommissioned by autoscaling. Also: it does nothing for JSON, CSV or Avro sources |
| Snowflake metadata cache | Micro-partition metadata — answers COUNT(*) and MIN/MAX with no warehouse running | A row access policy, which makes statistics inaccessible |
| Snowflake warehouse SSD cache | Table data on the warehouse's local disk | Dropped entirely on suspend. This is why auto-suspend is a performance setting, not just a cost one |
| Snowflake result cache | Whole query results for 24 hours, extended on reuse up to a 31-day maximum | Any difference in query text — including case and table aliases. Non-deterministic functions. Changed micro-partitions. Different privileges |
Two Snowflake settings that pull in opposite directions
Auto-suspend too long and you pay for idle warehouses at full size, multiplied by cluster count on a multi-cluster warehouse.
Auto-suspend too short and you drop the local SSD cache constantly, so every query re-scans from remote storage — slower and more expensive, because you burn more credits doing the same work.
The documented compromise for interactive BI is around ten minutes. The point worth making in an interview is that these two failure modes point in opposite directions, so ‘set auto-suspend as low as possible’ is confidently wrong advice.
And the result cache's exact-text-match requirement is more fragile than people expect: a BI tool that injects a timestamp comment, or reorders a select list, defeats it entirely. Those two facts — the cache dropping on suspend, and the result cache being text-exact — explain most ‘why did our bill jump’ stories.
Skew
Detection: on Databricks, the task duration distribution in the query profile, plus the knowledge that adaptive execution's own threshold requires both a large absolute size and five times the median. On Snowflake, an operator dominating the expensive-nodes panel with disproportionate spill.
Remedies, in order of preference: salt the join key; broadcast the small side deliberately by raising the threshold rather than blindly; split the skewed key out and union the results. On Snowflake you cannot hand-partition, so sizing up — more memory per node — is the blunt but effective fix, and being able to say why that is the available lever rather than a lazy answer is the distinction worth drawing.
Check yourself