Every query you run has a life: it is born in a SELECT, travels down through catalog, log, files, and CPU, and comes home as rows on a dashboard. The fastest queries are the ones that touch almost nothing along the way, because the whole stack is built around one economy: rule things out with small, cheap metadata before touching anything large and expensive. Move decisions and summaries, not data.
When the platform is healthy, that economy works almost silently: maps rule out most of the table unread, a cache remembers what the network already paid for, and the CPU meets the data in exactly the shape it likes. But every stretch of the route has an obstacle a well-meaning human can leave behind: a stale config, a schedule nobody remembers, one line of Python in the wrong place. When a query is slow, one of the layers has gone blind, almost always because of something the table's owners did, or stopped doing, at write time. Know the route, and you can diagnose a slow query by asking one question per layer instead of reaching for a bigger cluster.
So let's follow one query all the way down and help it home:
SELECT customer_id, SUM(amount)
FROM sales.orders
WHERE order_date = '2026-08-01'
GROUP BY customer_id;Meet orders, the table it must cross: three years of history, 2.1 billion rows, 50 columns, 480 GB of Parquet spread over 8,400 files, clustered by order_date. The query wants two columns, filters on a third, and cares about one day out of roughly a thousand. A naive engine reads 480 GB and filters in memory. Follow the scoreboard after each layer to see what the real one reads.
In play: 8,400 files · 480 GB · 50 columns · the query wants three columns and one day
Layer 0: The refusal
The journey has a step before any engine gets involved, and it is the only one with a perfect score: a query that never runs reads nothing at all. Before optimizing how the platform executes this query, spend one honest look on whether it should execute. Some calls deserve to be refused.
Most expensive queries in a workspace are not slow, they are frequent. The classic case is a schedule decoupled from the data it reads: a dashboard refreshing every ten minutes on a table that a nightly pipeline updates once. Of its 144 daily runs, 143 recompute yesterday's answer to the byte. Nobody decided that; someone accepted a default refresh interval two years ago, the schedule outlived its author, and the platform faithfully burns compute on questions whose answers cannot have changed.
So layer 0 is a question, asked once at design time: how often does the answer actually change, and who is waiting for it? The refresh cadence of a query should derive from the arrival cadence of its data, not from a dropdown default. A daily table earns a daily refresh, triggered by the pipeline's completion rather than a clock, so the two can never drift apart.
How it goes blind
Schedules are set once and never revisited, while the pipelines beneath them change cadence, get consolidated, or die. A quarterly look at your most-run queries against the update frequency of the tables they read is the highest-leverage performance review there is, because deleting a schedule beats accelerating it by exactly one hundred percent.
The smoking gun: a refresh counter racing a version counter. DESCRIBE HISTORY shows the table advanced one version yesterday; the dashboard's run history shows 144 refreshes in the same window. The other 143 recomputed an answer that could not have changed.Who tends it
The platform catches some of this for you. The result cache returns a repeated query over unchanged data without executing anything, and a materialized view serves a precomputed result, refreshed incrementally on the schedule or trigger you give it instead of recomputed from scratch. Both encode "the answer has not changed" into the platform. But neither can cancel a schedule; the decision that a question does not need asking remains human.
In play: everything · the only cut that costs nothing is not running
Layer 1: Pruning
The query passed layer 0; it deserves to run. What happens next is one idea applied three times at shrinking scales: before reading anything large, consult a small map of it, and rule out everything the map can exclude.
The first map is the transaction log, which records which of our 8,400 files constitute the table right now and, alongside every file reference, that file's min/max values for the leading columns. That last part is the weapon. Because the table is clustered by order_date, each file covers a narrow slice of dates, so the planner walks the log (cached on the cluster, no storage touched) and asks 8,400 times: could this file contain August 1st? For all but about 130, the answer is provably no. The biggest cut of the whole journey just happened, and not a single file has been opened.
In play: 8,400 files → 130 files · 480 GB → ~7.4 GB
Now the same trick, one level down. The engine holds a shortlist of 130 files and knows nothing about their insides, so it fetches the next map: 130 parallel range requests for just each file's tail, where the footer keeps a grid of the file's contents, one byte offset and min/max entry per row group per column. That grid answers two questions at once, and neither answer moves any data. The first is which columns matter: the query names three of fifty, so only those three will ever be requested, and the other 47, including the wide shipping_address and line_items blobs, never become network traffic at all. The second is which stretches of those three columns matter: the min/max entries rule out the corners of each file that lie outside August 1st, and a third and final map, the page index, repeats the ruling at the scale of pages. The first answer is called projection, the second skipping; together they leave a few hundred megabytes standing.
The engine is now staring at a precise shopping list of byte ranges, having read nothing but maps. And the maps all follow one rule: each sits above the territory it describes, readable without touching it. The log maps files, the footer maps row groups, the page index maps pages. Learn the trick once and you have understood all three.
In play: 7.4 GB → ~310 MB, spread over 3 columns of 130 files
How it goes blind
Pruning has four enemies, all self-inflicted.
Scatter. Statistics can only exclude what sits together. Written unclustered, every file spans January to December, and all three maps go blind at once.
Fragmentation. Thousands of tiny files, each costing map-reading work before its data is even considered. Streaming ingest and frequent small writes manufacture this one continuously.
Asking for everything. SELECT * disables projection by definition, and a timestamp stored as a string can barely be range-pruned.
The undead. Under deletion vectors, a DELETE only marks rows dead. No map can exclude a marked row, so every scan keeps reading and discarding them until OPTIMIZE physically rewrites the files. Many teams never do.
The smoking gun: the query profile's files read versus files pruned. A selective query that reads nearly every file is a blinded layer 1, and DESCRIBE DETAIL showing thousands of files is the fragmentation variant.Who tends it
Liquid clustering maintains co-location on your chosen keys (CLUSTER BY, or CLUSTER BY AUTO to learn keys from query history on managed tables with predictive optimization enabled), OPTIMIZE compacts small files, and predictive optimization schedules both. Choosing the clustering key remains a human decision: the column your queries actually filter and join on, which is knowledge about your workload, not your data. The schema and the SELECT list are entirely yours.
Layer 2: Moving bytes
Only now does anything heavy move. The workers execute the shopping list: one ranged GET per surviving byte range, hundreds in flight at once, decompressing one page at a time. Count what depends on what, rather than the requests themselves, and you see the architectural signature of a lakehouse: three sequential volleys, each a swarm of parallel requests that cannot fire until the previous swarm has answered. Log first (which files), footers second (which byte ranges), data third. Three stacked round-trips to building-scale storage, whether the table has ten files or ten thousand.
That is the full price, and here is the layer's real mechanism: it works to never pay it twice. Everything the volleys produced is kept hot, for as long as the cluster lives and the cache has room, which is exactly the fine print the blind spot below is about. The log and the footers stay resident in memory on the cluster, and the 310 MB of data pages land in the disk cache, the cluster's own local storage, microseconds away instead of milliseconds. When the dashboard refreshes this query in five minutes, volley one and two are answered from memory, volley three from local storage, and the network is not consulted at all. The distinction that matters here is not fast versus slow queries; it is cold versus warm reads, and the entire job of this layer is to make sure every byte is fetched the expensive way at most once.
The disk cache also stacks with layer 0's result cache into a hierarchy of laziness: the result cache skips the query, the disk cache skips the network, and pruning shrinks whatever neither could skip. The fastest byte is one you don't fetch; the second fastest is one you already fetched.
Fetched: ~310 MB · once over the network, ms · re-reads from local storage, µs
How it goes blind
A cache starved of lifetime: clusters sized so small, or autoterminated so aggressively, that the cache never survives long enough to be warm for anyone. A cache is an investment, and a cluster that dies every ten minutes keeps writing it off. The other classic is the reflexive .persist(): a manual override of caching the platform already does better, storing a second copy of what the disk cache already holds in the memory the query needs for execution. The one thing it can cache that the disk cache cannot is an expensive computed intermediate reused within a job, and even that is usually better written to a temp table.
The smoking gun: the query profile's bytes-read-from-cache sitting near zero for a query that runs all day.
Who tends it
The disk cache is automatic; nobody tunes it, and that is the point. Your levers are indirect: compute from a "Delta cache accelerated" instance family for classic clusters (the picker's label for hardware with the local storage the cache needs), autotermination windows that match how often the data is actually re-read, and letting serverless make both choices for you.
Layer 3: Execution
The pages decompress into columnar vectors: contiguous arrays of one column's values. Because the page index could only exclude pages that provably contain no August 1st rows, the surviving pages still carry some neighbors from adjacent dates, call it 25 million rows scanned to find the 2 million that match. Everything up to here was about not touching data; this layer finally has to touch 25 million values, and its entire craft fits in one sentence: keep the CPU fed, and keep it doing real work.
Keeping it fed is the layout's half of the bargain. A modern CPU can compare values far faster than RAM can deliver them, so the bottleneck is supply, not arithmetic. Because a vector is one contiguous block, every fetched cache line carries the next fifteen values along for free, and the prefetcher, seeing a perfectly predictable stride, fetches ahead so the data is already waiting when the CPU asks for it. No stalls on memory, the fate that kills row-based engines chasing pointers from object to object.
Photon is the other half: an engine built to consume that supply without wasting a cycle on anything but the work. Its loops compile to SIMD instructions that compare eight or sixteen dates per cycle with no branches to mispredict; where a column is dictionary-encoded, equality checks run on the small integer codes without ever decoding a string; and the administration a classic engine pays per value (function calls, type checks, null checks) is paid once per batch of thousands. The layout supplies, Photon consumes, and together they make our filter, the thing the SQL is nominally about, nearly the cheapest step in the query's life: milliseconds per core. Each worker then sums amount per customer locally, and the query is one step from done.
In flight: 25 M rows scanned → 2 M match → partial sums per customer, per worker
How it goes blind
One line of Python in the hot path undoes all of it. Photon cannot run your function, so vectors are broken back into rows and shipped to a separate Python process, where an interpreter calls it once per row (newer runtimes batch the shipping through Arrow, but the per-row interpretation remains): every cost the vectorized loop existed to avoid, restored at a stroke. Worse, the optimizer cannot see inside the function, so WHERE my_udf(order_date) = ... can never become min/max pruning, and the UDF blinds layer 1's maps too: the 480 GB table becomes a full scan feeding a Python loop. The quieter version of the same wound: an expression Photon does not support silently falls back to classic JVM Spark, correct but unvectorized. The order of preference is fixed: SQL expressions and built-ins first, pandas UDFs second, row-by-row Python never.
The smoking gun: the query profile's Photon coverage, and wall time concentrated in operators outside it.
Who tends it
Photon is on by default on SQL warehouses and serverless. There is nothing to tune, only things to avoid putting in its way.
Layer 4: Leaving memory
Everything so far happened without a single row leaving the worker that read it: pruning chose the bytes, the fetch brought them into local RAM, and Photon filtered and summed them right there. GROUP BY customer_id ends that comfort. Customer 42's August 1st purchases sit in whichever files the writes happened to land in, which means in different workers' memory, and a total can only be computed where the rows meet. So rows must leave memory, and the cheap part of the query is over. The planned exit is the shuffle: every worker exchanges rows with every other so that each key lands in exactly one place. What used to be a memory copy inside one machine becomes a network transfer between machines, 10 to 100 times slower than RAM.
Narrow operations keep every worker independent; wide operations repartition rows by key, and the crossing edges are actual data on the wire. Most query tuning is an attempt to shrink or eliminate the right-hand panel.
The engine works hard to shrink the exit, and for our query it succeeds spectacularly: because each worker pre-aggregated in layer 3, what crosses the network is partial sums per customer, a couple of megabytes, rather than 2 million raw rows. The same instinct drives the other big mitigations: a join against a small table gets broadcast so the large side never moves, and AQE re-plans mid-query using observed sizes instead of estimates.
On the wire: ~2 MB of partial sums, not 2 M raw rows → 20 K result rows to the client
There is also a second, unplanned exit from memory: spill, when a worker's working set outgrows RAM and overflows to local disk. It usually strikes right here, because the shuffle is what concentrates working sets: all of customer NULL's half a million rows landing on one unlucky worker. The two exits blur together in the Spark UI, but keep the diagnosis apart: shuffle is the network exit, shrunk with broadcast hints, pre-aggregation, and layout; spill is the disk exit, cured with memory per task or by fixing the skewed key.
How it goes blind
Two classics. A hand-set spark.sql.shuffle.partitions from 2021 encodes that year's data volumes and constrains AQE with stale numbers. And the skewed key you just met: the query is as slow as its worst key.
The smoking gun: shuffle and spill bytes in the Spark UI, and a stage whose slowest task runs many times longer than its median; that gap is the skewed key.
Who tends it
AQE, on by default, handles partition sizing and skew splitting for most cases; serverless removes nearly all of the memory-sizing knobs. Your remaining levers are the ones requiring workload knowledge: broadcast hints when the planner misjudges, pre-aggregating before joins, fixing NULL-heavy keys at the source, and laying tables out on the keys they habitually join on, which shrinks the shuffle before it starts.
Home
The query is home: twenty thousand result rows on the dashboard, seconds after SELECT, having read 0.06% of the table, shuffled a couple of megabytes, and paid the network for every byte exactly once. None of that was luck: every fast step was a mechanism somebody armed.
Journey: 480 GB in play → 310 MB fetched → ~2 MB on the wire → 20 K rows home
The human layer
It will not stay this way on its own. Every layer the query just traveled is kept sharp between queries, or dulled there: files fragment as writes land, deleted rows linger in place, and none of it ever shows up as an error. Every query keeps returning the right answer, just a little slower each month.
Maintenance is the platform's share. On Unity Catalog managed tables, predictive optimization schedules OPTIMIZE, VACUUM, and statistics collection from observed usage, the single best argument for managed tables; on external tables that calendar is yours, and DESCRIBE HISTORY shows when they last actually ran (VACUUM appears only where vacuum logging is enabled), which on a neglected table is the whole diagnosis. And the platform's share has been growing: the manual partitioning schemes, the persist calls, the tuned shuffle configs, it turns those knobs better than we do and re-turns them as the data drifts, which we never did.
What it cannot do is know your workload. The decisions that still deserve human hands are exactly the ones requiring knowledge the optimizer does not have:
- The query itself. Before touching any infrastructure, reread the SQL or PySpark. Name the columns instead of
SELECT *, filter on the clustered keys as early as possible, replace UDFs with built-in expressions, pre-aggregate before joining. A rewrite costs nothing, ships instantly, and fixes more slow queries than any cluster change, because much of the blindness in this article was inflicted by the query text in the first place. - Layout. Which columns your queries filter and join on (clustering keys), what one row should mean (grain), whether that hot dimension should be denormalized into the fact table. Write-time decisions that arm, or blind, every pruning layer at once.
- Join strategy. Recognizing the plan that should have broadcast, the key that skews, the aggregation that should happen before the join.
- Diagnosis. Reading the Spark UI well enough to prove which layer a slow query is stuck in before touching anything: bytes scanned points at layer 1, spill points at layer 4, a hot Python process points at layer 3, and each layer's smoking gun above tells you where to look before you change anything.
The platform will keep taking work off your hands, and it should: it turns the knobs better than we do. But it cannot take the judgment. The hardest pain points are not settings but choices: what to cluster on, what a row should mean, whether a question needs asking at all. Whether a query gets a good life is decided by the people who own the tables it travels. Now you know the route.
The vocabulary
The anatomy behind the story, in alphabetical order. Each entry: what it is, and why the journey cares.
AQE (adaptive query execution)
The engine re-plans a query mid-flight using the sizes it has actually observed, picking shuffle partition counts and join strategies from reality instead of estimates. It is why hand-set shuffle configs are usually worse than no config at all.
Broadcast join
When one side of a join is small, the engine copies it whole to every worker so the large side never crosses the network, turning a full shuffle into a local lookup. A hint (/*+ BROADCAST(t) */) forces it when the planner misjudges.
Column chunk
All of one column's values for one row group, stored contiguously. The unit projection operates on: a column the query does not name is a chunk that is never fetched.
Delta table / transaction log
A Delta table is a directory of ordinary, immutable Parquet files plus the transaction log: a sequence of small JSON commits ("add these files, remove those"), compacted periodically into checkpoints, whose replay defines the current table. The log also stores per-file min/max statistics, which is what makes file pruning nearly free.
Deletion vector
A small bitmap marking rows in a file as deleted, without rewriting the file. Scans keep reading the dead rows and filtering them out until OPTIMIZE or REORG physically rewrites the files, and the bytes only leave storage after a later VACUUM.
Disk cache
Workers keep copies of the Parquet data they fetch from cloud storage on their local SSDs, automatically, in a decoded format faster to re-read than the original. Repeat reads of warm data skip the network entirely; it is why the second run of a query is routinely several times faster than the first, and why .persist() on raw table data is usually redundant.
Footer
The directory Parquet keeps at the end of each file: the schema plus, per row group per column, byte offsets, sizes, and min/max statistics. The file ends with the footer's length followed by the format's 4-byte magic number, so readers bootstrap from the tail; engines speculatively fetch the final few tens of kilobytes (Arrow's default is 64 KB) in one request because on cloud storage round-trips cost more than bytes.
Liquid clustering
Delta's mechanism for keeping rows with similar key values physically co-located, maintained incrementally as data arrives, with keys changeable later. Clustering fires on three triggers: eagerly during large writes, incrementally when OPTIMIZE runs (by hand or scheduled by predictive optimization), and wholesale with OPTIMIZE FULL after a key change. Co-location is what gives every min/max statistic its power to exclude, at all three map levels at once.
Materialized view
A precomputed query result stored as a table, refreshed on demand, on a schedule, or (opting in with TRIGGER ON UPDATE) when its sources change, incrementally where the engine can manage it. Consumers read the answer instead of recomputing it.
OPTIMIZE
The maintenance rewrite. It selects files whose physical shape has degraded and writes them anew in one atomic log commit; a file qualifies three ways: too small (compacted with its neighbors), unclustered (rows re-ordered onto the table's clustering keys), or carrying enough deletion-vector-marked rows to be worth purging. One pass can fix all three at once, and files already healthy are skipped, which keeps routine runs cheap. Old files are removed only logically; the bytes wait for VACUUM.
Page / page index
A page (about a megabyte) is a column chunk's internal segment and the atom of compression: reading one value means decompressing its whole page, and never more. The page index duplicates page-level min/max statistics near the footer, so pages can be skipped without being touched.
pandas UDF
A Python UDF that receives whole columns as pandas/Arrow arrays instead of one row at a time, so data crosses the process boundary in columnar batches and the function body can run vectorized NumPy operations. That heals two of a plain UDF's three wounds, the serialization and the per-row interpretation, but not the third: execution still leaves Photon, and the optimizer still cannot see inside the function, so pruning stays blind. The right tool when Python is genuinely unavoidable.
Photon
Databricks' vectorized execution engine. Its goal is maximum useful work per CPU cycle, pursued from two sides at once: it generates the instructions (SIMD comparisons of many values per cycle, in tight branch-light loops, with bookkeeping paid once per batch instead of once per row) and it shapes the feeding (data stays in contiguous columnar vectors from decompression through aggregation, the one memory pattern a CPU prefetcher can fully exploit). The two sides depend on each other, which is why leaving Photon, through a Python UDF above all, loses both at once.
Predictive optimization
Databricks scheduling OPTIMIZE, VACUUM, and ANALYZE automatically for Unity Catalog managed tables, based on observed usage. On external tables, that calendar is yours to keep.
Result cache
Databricks SQL returns the stored result of a previously executed query when it repeats over unchanged data, executing nothing. On serverless warehouses this cache lives at the workspace and survives restarts.
Row group
A horizontal slice of a Parquet file, sized by bytes rather than rows (128 MB by default, from a few hundred thousand to a few million rows depending on row width), inside which data is stored column by column. The unit of parallelism across workers and of stats-based skipping within a file.
Shuffle
The all-to-all exchange that repartitions rows across workers by key, so a join or aggregation finds all rows for a key in one place. Each worker buckets its output into local shuffle files; the next stage fetches each bucket from every worker. The single most expensive movement in a distributed query.
Spill
A worker's in-memory working set (sort buffers, aggregation hash tables) outgrowing its allotment and overflowing to local disk, deliberately and per operator, and reported in the Spark UI rather than happening silently. The classic symptom of skewed keys or undersized memory.
Vector
An engine's unit of processing: a contiguous array of one column's values, typically a few thousand rows' worth, plus a validity bitmap for nulls. The word carries two scales in this field: these engine vectors, and the register-sized hardware vectors (4 to 16 values) that a single SIMD instruction consumes; the engine streams its big vectors through the CPU's small ones. This article uses the engine sense throughout.