Home › Learn › How reads and writes work › 2 · Read path
Delta Lake and Spark · from zero to advanced
How a read works: one big table, six kinds of query
Follow a query from SQL to bytes. Six queries on one table (select star, two columns, a sorted column, a random column, a column beyond the 32 statistics columns, a Z-ordered column) with measured files read and bytes read, how the driver scans the log, and why count star never touches the data.
Who does what
The driver plans, the executors read
Before any data moves, one process, the driver, works out which files are needed. Only then do the executors, the worker processes, read them in parallel. This split is why metadata work (the log) and data work (the files) behave so differently.
Metadata work on the driver, data work on executors.
The steps
Seven steps from SQL to bytes
Steps 1 to 5 never touch a data file.
- Steps 2 to 4 are Delta's contribution. The driver loads the live file list and the statistics for each file from the log, then drops every file whose min and max prove it cannot match. This is called data skipping.
- Step 3, partition pruning, only applies when the table is partitioned: whole folders are dropped by name.
- Steps 6 and 7 are Parquet's contribution. Inside each chosen file the reader checks the footer's per-chunk min and max (skipping row groups) and then reads only the column chunks the query names.
Six kinds of query
One table, six queries, measured
The table has 20 files of 10,000 rows: event_id and event_date increase with the load order, user_id is random, and 40 columns in all. Each query was run to a no-op sink so nothing was printed, and repeated three times after a warm-up.
Files opened and bytes read for each query. Red is bad, green is good.
| Query | Files opened | Bytes read | Rows decoded | Why |
|---|---|---|---|---|
SELECT * | 20 of 20 | 11.6 MB | 200,000 | No filter, every column: the full cost. |
SELECT event_id, amount | 20 of 20 | 2.9 MB (25% of select *) | 200,000 | Every file opened, but only 2 of 40 column chunks read. |
WHERE event_date = one day | 1 of 20 | 670 KB (6%) | 10,000 | Sorted column: each file holds one day, so 19 files are skipped by the log alone. |
WHERE user_id = 12345 | 20 of 20 | 12.0 MB | 200,000 | Random column: every file's range contains 12,345, so nothing can be skipped. |
WHERE late_code = 7 (column 40) | 20 of 20 | 900 KB | 10,000 | No log statistics, so all files opened. The Parquet footer then skipped most data. |
| same, statistics on all 40 columns | 1 of 20 | 597 KB | 10,000 | The log can now skip 19 files without opening them. |
WHERE user_id = 12345 after Z-ORDER | 1 of 28 | 607 KB | 6,712 | Z-ordering clustered similar user_ids together, so one file matches. |
ReadSchema: struct<event_id:bigint,amount:double>. For the one-day query: PushedFilters: [IsNotNull(event_date), EqualTo(event_date,2026-01-10)]. Timings were all between 0.10 s and 0.34 s here, so judge by files and bytes, not seconds.Why min and max work
A filter helps only when the data's order cooperates
Data skipping is a yes-or-no test on each file: can this value possibly be inside [min, max]? If the answer is no, the file is skipped. That works well on a column whose values arrive in order, and not at all on one that is scrambled.
Real min and max of all 20 files. Sorted column on top, random column below.
- event_date arrived in order, so every file has min = max = one day. A one-day filter excludes 19 of 20 files for free.
- user_id is random, so every file spans almost the whole range. No file can be excluded, however selective the filter looks.
- The lesson: a column is only useful for skipping if the layout of the files makes ranges narrow. Section 3 shows how to make that happen on purpose.
The 32-column limit
Statistics are not collected for every column
Where statistics stop, skipping stops.
By default Delta collects min, max and null counts only for the first 32 columns (the Databricks docs say the same). The real add action for the main table lists 32 statistics columns and does not list late_code. A second copy of the table with delta.dataSkippingNumIndexedCols = 40 had 40 and could skip files on it.
- Settings (from the Databricks docs):
delta.dataSkippingNumIndexedColschanges how many leading columns get statistics;delta.dataSkippingStatsColumns(Databricks Runtime 13.3 LTS and later) names the columns and takes precedence. Neither recomputes statistics for files already written. - To recompute on existing data:
ANALYZE TABLE t COMPUTE DELTA STATISTICS(Databricks Runtime 14.3 LTS and later). - Put columns you filter or join on early in the schema, or list them explicitly. Long strings are truncated in statistics; the docs do not say at how many characters.
- A second chance: even with no log statistics the Parquet footer has its own min and max per column chunk. That rescued part of the read above (0.9 MB instead of 11.6 MB), but only after opening all 20 files. With thousands of files, that opening can become the expensive part.
Reading nothing
Some queries never open a data file
The log holds numRecords, and min and max, for every file. For a few query shapes the engine answers from those numbers alone.
| Query | Plan starts with | Parquet read? |
|---|---|---|
SELECT count(*) | LocalTableScan [count#169L] | No |
SELECT min(event_id), max(event_id) | LocalTableScan [min(event_id)#257L, max(even | No |
count(*) with a filter | HashAggregate over FileScan parquet | Yes |
SUM(amount) | HashAggregate over FileScan parquet | Yes |
count(*) on the whole table became a LocalTableScan: the answer came from the log with no Parquet file opened. This was seen on open-source Delta Lake 3.2.0; I did not check how Databricks behaves, so confirm it before relying on it there.Scaling up
The same picture at 1 TB
These are arithmetic, not measurements, but they follow from the ratios above and from one Databricks docs figure: a 1 TB table is tuned to about 256 MB files, which is about 4,096 files.
| Query on 1 TB | Reads about | How |
|---|---|---|
SELECT * | 1,000 GB | Everything |
| two of 40 columns | 254 GB | Same 25% ratio as measured |
| one day of 365, sorted by date | 2.7 GB (about 11 of 4,096 files) | Skipped by the log |
| a random column | 1,000 GB | Nothing can be skipped, 4,096 files opened |
Spark normally splits large files so one task handles at most about 128 MB (a general default, not re-checked here). So 256 MB files give roughly 8,000 tasks, which a cluster with 64 task slots works through in about 130 waves.
What to do
Rules that follow from this
| Do | Because | Verdict |
|---|---|---|
| Select only the columns you need | Column chunks you do not name are never read | Pursue |
| Filter on columns that are sorted or clustered | Narrow min/max ranges let the log skip files | Pursue |
| Put filter and join columns in the first 32, or list them | Statistics exist only for those | Pursue |
Use SELECT * on wide tables | Pays for every column | Drop |
| Expect a filter on a random column to help | Every file's range contains the value | Drop |
Count rows with count(*) on the whole table | Can be answered from the log (confirmed locally) | Pursue |
FAQ
Questions people ask
Does Delta read every file for every query?
No. The driver uses the min and max values in the log to skip files that cannot match the filter, and only then are data files read. If the layout is random, though, nothing can be skipped.
Why is select * so expensive on a wide table?
A Parquet file stores each column as its own chunk. A query that names two of forty columns reads two chunks; select * reads all forty.
Why did a filter on the 40th column still read fewer bytes?
Delta had no statistics for it, so it opened every file, but each Parquet footer has its own min and max per column chunk, so most row groups could still be skipped. Collecting statistics on that column avoids opening the files at all.
Can a query be answered without reading data files?
Yes in some cases: an unfiltered count(*) or min/max on a Delta table was answered from the log's statistics in the local experiments.