Home › Data engineering › Fundamentals › Performance
Data engineering fundamentals · free guide
Performance fundamentals: where the time goes in a data job
A slow job is almost never slow for a mysterious reason. Time goes to a short list of places: reading bytes, moving bytes, doing work on bytes, waiting, or doing the same thing twice. This page is that list, organised as a mind map so you can walk it when a job is slow. Storage choices are explained in the abstract; the engine is not the point.
Start here
The data physics
Five plain facts that explain almost everything below. Read these and the rest follows.
The mind map
The whole topic on one screen
Each card is a branch. Walk them in order when you are stuck: the question tells you what the branch is about, and the first check tells you where to look.
1. Read less
How many bytes leave storage?
Partitioning, clustering, file statistics, column pruning, predicate pushdown, bloom filters, compression
First check: Compare bytes scanned with bytes needed
2. Lay out well
Is the data shaped for how it is read?
Row vs column, file size, data types, sort order, small files
First check: Average file size and how many files a query opens
3. Move less
How much crosses the network?
Shuffles, joins, broadcast, skew, repartitioning, wide vs narrow steps
First check: Shuffle bytes vs input bytes
4. Run well
Is the cluster used well?
Driver vs executors, task count, parallelism, memory, spill, serialization, UDFs
First check: Idle cores, spill, one slow task
5. Do less
Are we doing unnecessary work?
Needless data, loops with actions, repeated queries, recompute, full refresh, SELECT *
First check: Look for the same read or query twice
6. Wait less
Where does the clock run while nothing computes?
Cluster start, queueing, locks, slow sources, retries, orchestration gaps
First check: Compare wall-clock time with task time
7. Precompute
Can the answer be ready before the question?
Gold tables, aggregates, materialised views, caches, serving layers
First check: Same expensive question asked many times
The ladder
Where a byte lives decides how long it takes
Pick any job and ask: where is each byte at the start, where does it need to be, and how many times does it travel? The ladder below is the whole story. The figures are rough orders of magnitude to build intuition.
The data physics ladder. Moving down a row is roughly 10 to 1,000 times slower. Orders of magnitude to build intuition, not benchmarks.
| Where the byte is | Rough time to reach it | Everyday example | What to remember |
|---|---|---|---|
| CPU cache / memory | nanoseconds | A column already loaded in an executor | Keep hot data here: caches, broadcast tables |
| Local SSD / disk cache | about 100 microseconds | Spark disk cache, shuffle files | Repeated reads of the same files get fast |
| Object storage in the same region | tens of milliseconds to first byte, then high throughput | Parquet or Delta files in S3 / ADLS | Great for big sequential reads, poor for many tiny reads |
| Another service over the network | milliseconds to seconds per call | JDBC source, REST API, a lookup per row | Never call it once per row |
| Another region or cloud | tens to hundreds of milliseconds, plus egress fees | Reading a bucket in another region | Move compute to the data, not data to the compute |
| A human | hours to days | Waiting for approval, a fix, a definition | The slowest hop in any pipeline |
Where the time goes
Seven buckets for a slow job
When someone says 'the job is slow', find the bucket first. Most jobs have one dominant bucket; fixing a small one first is the usual mistake.
Where the time goes: find the biggest bucket before tuning anything. Illustrative proportions.
| Bucket | What it looks like | Typical cause | First move | Verdict |
|---|---|---|---|---|
| Reading | Stage reads far more bytes than the result needs | No partition or file skipping, wide rows, SELECT * | Filter early on the layout key; read fewer columns | Pursue first |
| Shuffling | Big shuffle read/write, long exchange stages | Joins and group-bys on big data, wrong partition count | Broadcast the small side; filter before joining | Pursue |
| Computing | Executors busy, CPU high | Python UDFs, regex, JSON parsing, heavy window functions | Use built-in functions; parse once | Pursue when CPU-bound |
| Spilling | Memory spills to disk | Partitions too big, skewed keys | More, smaller partitions; fix skew | Pursue |
| Skew / stragglers | One task runs 10x longer than the median | A few keys hold most rows | Salting, skew-join handling, split the hot key | Pursue |
| Waiting | Wall-clock far above task time | Cluster start, queue, locks, retries, slow source | Warm pools, concurrency limits, tune the source side | Pursue when gaps are large |
| Writing | Slow commits, many tiny output files | Too many partitions at write, small files, merge rewrites | Coalesce or auto-compact; avoid rewriting whole files | Pursue |
Read less
Storage model decisions, in the abstract
These choices are made once, when a table is designed, and then pay or cost on every read. Names differ by engine; the ideas do not.
Why analytics uses column storage: a query about one column reads one column.
File skipping: each file records its min and max. Clustering makes those ranges tight so the engine can skip files. Partitioning, Z-order and liquid clustering are different ways to get this.
| Decision | What it does | Helps when | Hurts when | Verdict |
|---|---|---|---|---|
| Row-oriented storage | Stores each row's values together | You fetch whole rows by key (transactions) | You scan a few columns across millions of rows | Right for OLTP, wrong for analytics |
| Column-oriented storage | Stores each column's values together | Analytics: read three columns of fifty | You fetch single full rows all day | Default for analytics |
| Compression | Shrinks bytes on disk and on the wire | Almost always: less to read and move | Very high CPU codecs on data read once | Pursue; pick a balanced codec |
| Encoding (dictionary, run-length) | Stores repeated values as small codes | Low-cardinality columns (status, country) | Unique values like ids and free text | Free win on suitable columns |
| Partitioning | Splits data into folders by a column value | Queries filter on that column; low-to-medium cardinality | Too many values, or queries rarely filter on it | Use sparingly; wrong key is costly |
| Sort / Z-order style co-location | Puts similar values near each other in files | Filters on several columns, so file min/max ranges are tight | Data is rewritten constantly | Pursue for big tables |
| Liquid-style clustering | Adaptive co-location that changes without rewriting everything | Query patterns change or keys are high-cardinality | Tiny tables | Prefer over fixed partitions when available |
| File statistics (min/max per file) | Lets the engine skip whole files | Data is clustered so ranges do not overlap | Data is random, every file spans every value | Free if layout cooperates |
| Bloom filters | A tiny index answering 'is this value definitely not in this file?' | Point lookups on high-cardinality columns | Range filters, low-cardinality columns | Targeted use |
| Right-sized files | Files big enough to amortise open cost, small enough to parallelise | Roughly 128 MB to 1 GB per file | Thousands of tiny files, or one giant file | Pursue |
Why compression matters so much
Fewer bytes is the cheapest speed-up there is
Compression is not a space-saving afterthought. Smaller files mean less disk read, less network, more data per memory page and faster scans. Columnar storage compresses far better than rows because each column holds similar values.
| Column | Kind of values | How well it compresses | Why |
|---|---|---|---|
| Order status (5 values) | Low cardinality | Extremely well, often 50x or more | Dictionary encoding stores a tiny code per row |
| Country code | Low cardinality, repeated | Extremely well | Same reason; sorted data adds run-length gains |
| Date / timestamp | Ordered, many repeats | Well | Deltas and repeats are small |
| Integer measure | Narrow range | Well | Bit-packing stores only the bits needed |
| Customer id (unique) | High cardinality | Modestly | Few repeats to exploit |
| Free text / JSON string | Unique, long | Poorly, and costly to parse | Little structure; parse once into typed columns |
| Random UUID string | Unique, no order | Poorly | Store as a binary or numeric key if you can |
Run well
Driver, executors, tasks: how a cluster spends your job
The driver plans and coordinates; executors do the work in parallel, one task per partition per core. Most tuning is picking the right number of tasks and not overloading the driver.
Driver, executors and tasks. The driver coordinates; executors do the work in parallel, one task per partition per core.
| Thing | Plain meaning | Too little looks like | Too much looks like | Verdict |
|---|---|---|---|---|
| Number of tasks / partitions | Pieces of work run in parallel | Idle cores, huge partitions, spill | Scheduler overhead, tiny files, long tail of tiny tasks | Aim for partitions of about 100 to 200 MB |
| Executors and cores | The parallel workers | Job crawls, partitions queue | Cores idle, bill high | Size to the work, enable autoscaling with a sensible floor |
| Driver | Plans the job, collects small results | Driver out of memory on collect or large broadcast | Over-sized driver paid for nothing | Keep data off the driver |
| Executor memory | Room to hold a partition and its joins | Spill, out-of-memory errors | Wasted money on memory-optimised nodes | Fix partition size before buying memory |
| Actions and job overhead | Each action (count, collect, write, show) launches a job with planning and scheduling cost | Not the issue | Hundreds of tiny jobs in a loop; seconds of fixed cost each | Batch work into one job; this is the gap embedded engines like DuckDB close for small data |
| Python UDFs | Row-by-row Python outside the engine | Not the issue | Serialisation cost on every row, no optimiser help | Use built-ins, or vectorised functions where unavoidable |
| Serverless vs classic compute | Who manages start-up and sizing | Cold-start waits on classic | Pay for always-on serverless idle | Match to the job's shape |
Move less
Shuffles, joins and skew
A shuffle sends data across the network so rows with the same key meet. It writes to disk, reads from every other worker and waits for the slowest one. Reduce what you shuffle, shuffle less often, or avoid it entirely.
Why a shuffle is the expensive step: all-to-all movement across the network. Filter before it, not after.
| Situation | What happens | Better approach | Verdict |
|---|---|---|---|
| Join big to small | Both sides are shuffled by key | Broadcast the small table to every worker; no shuffle of the big one | Pursue |
| Join big to big | Both sides shuffled; expensive but sometimes unavoidable | Filter and project first; pre-partition both by the join key if reused | Pursue; accept the cost |
| Skewed key | One task gets most of the rows and runs far longer | Split the hot key, salt, or use skew handling | Pursue |
| Function wrapped around a join or filter column | The engine cannot use file statistics or the join plan | Compare the raw column; fix types at write time | Pursue |
| Too many shuffle partitions | Thousands of tiny tasks, tiny output files | Let adaptive execution coalesce them | Pursue |
| Too few shuffle partitions | Huge tasks, spill to disk | Raise partitions or let adaptive execution split | Pursue |
| Cartesian join | Every row meets every row | Add the missing join condition | Always fix |
Do less
The unnecessary work list
The biggest wins are often not tuning at all, but stopping the job from doing things nobody needed. These patterns are the common ones.
| Wasteful pattern | Why it hurts | Fix | Verdict |
|---|---|---|---|
| Processing data nobody uses | Every unused column, row and table is paid for on every run | Read only needed columns and rows; retire unused outputs | Pursue first |
| Full reload when only changes arrived | Reprocesses yesterday's data again | Incremental loads with a watermark or change feed | Pursue |
| collect() inside a loop | Pulls data to the driver once per iteration, serial and slow | Do it as one set-based query | Always fix |
| Same query run in a loop | Pays planning and scan cost every time | Run once with a join or group-by; or read once and reuse | Always fix |
| Reading the same table repeatedly | Each read scans the files again | Read once, reuse (cache only if it truly pays) | Pursue |
| Recomputing an unchanged result | Same inputs, same answer, paid again | Cache, incremental, or materialise | Pursue |
| SELECT * and no filter | Reads every column and row | Name columns, filter early | Pursue |
| Sorting without LIMIT | Sorts everything to show ten rows | Sort only what you need | Pursue |
| DISTINCT used to hide a bad join | Fan-out then dedupe doubles the work | Fix the join grain | Pursue |
Wait less
Waiting, locking and internal rewrites
Some time is not compute at all. It is the clock running while something else finishes.
| Kind of wait | What it is | How it shows up | Fix | Verdict |
|---|---|---|---|---|
| Waiting for compute | Cluster start or a busy warehouse queue | Long gap before the first task | Warm pools, right-sized concurrency, schedule off-peak | Pursue |
| Locks and write conflicts | Two writers touch the same files or rows | Retries, 'concurrent update' errors, serial slowdowns | Partition writers by key range; separate maintenance windows; narrow merge conditions | Pursue |
| Hidden rewrites | A small change rewrites whole files (merge, update, delete) | Writes far larger than the change | Deletion vectors / merge-on-read style, tight merge predicates | Pursue on busy tables |
| Retry storms | Failures that retry and re-run work | Same task runs several times | Fix the root error; cap retries | Pursue |
| Orchestrator gaps | Serial steps that could run in parallel, polling loops | Wall-clock far above compute time | Run independent tasks in parallel; use events instead of polling | Pursue |
| Slow source | The database or API cannot hand over data quickly | Reads are slow with idle cluster | Parallel reads on a key range, replicas, or change-based extraction | Pursue |
Precompute
Why we build performance objects in the gold layer
Raw tables are shaped for capture; business questions need a different shape. Gold objects pay the cost once, at load time, so thousands of reads pay almost nothing. This is the same trade as indexing in a database: spend write time to save read time.
Why gold has performance objects: spend write time once to save read time many times.
| Object | What it precomputes | Saves on every read | Costs | Verdict |
|---|---|---|---|---|
| Fact table at a fixed grain | Joined, cleaned, typed rows at one clear grain | Joins and cleaning | Storage and load time | Core gold object |
| Dimension table | Small, descriptive lookup with stable keys | Repeated lookups and text columns | Maintenance of history | Core gold object |
| Aggregate / summary table | Daily or monthly totals | Scanning billions of rows for a chart | Must stay in step with its base | Pursue for hot dashboards |
| Materialised view | A stored query result refreshed on change | Recomputing the query | Refresh cost, staleness | Pursue when refresh is incremental |
| Clustered / sorted layout | Co-locates by the common filter | Bytes scanned | Rewrite cost | Pursue for big gold tables |
| Semantic / metric layer | Definitions computed once | Every tool recomputing differently | Governance effort | Pursue |
| Result and disk caches | Recent answers and files | Repeated identical queries | Memory, staleness | Pursue, but do not rely on |
In practice
CostSherlock rules for this topic
CostSherlock is our open set of cost and performance rules. Each rule below has a plain meaning here; the rule id is its name in CostSherlock. They are grouped by the same branches as the mind map, so a finding tells you which branch to open.
Read less
| CostSherlock rule | What it catches, in plain words |
|---|---|
unpartitioned_large_table | Large table with no layout to skip files |
partition_filter_unused | Partitioned, but queries do not filter on it |
uc_query_low_pruning_ratio | Query opens most files anyway |
uc_query_full_scan_recurring | The same full scan, again and again |
select_star | Reads every column |
function_on_partition_column | A function hides the partition filter |
not_equals_defeats_skipping | A not-equals filter cannot skip files |
or_across_columns_defeats_pruning | OR across columns blocks pruning |
Lay out well
| CostSherlock rule | What it catches, in plain words |
|---|---|
too_many_files | Thousands of tiny files |
mixed_file_sizes | File sizes vary wildly |
target_file_size_too_small | Target size set too low |
over_partitioned_table | Too many partitions for the data volume |
excessive_partition_depth | Nested partitions too deep |
table_too_small_for_partitioning | Partitioning a table that is too small to benefit |
uc_zorder_misaligned_with_filters | Co-location columns differ from filter columns |
using_unoptimised_format | Not a columnar, compressed format |
Move less
| CostSherlock rule | What it catches, in plain words |
|---|---|
broadcast_join_missed | A small side was shuffled instead of broadcast |
uc_query_high_shuffle_ratio | Shuffle dwarfs input |
partition_skew | One partition holds most rows |
cross_join | Every row meets every row |
function_on_join_key | A function blocks an efficient join |
implicit_type_cast_on_filter | A hidden type cast disables skipping |
excessive_shuffle_partitions_config | Shuffle partition setting is far off |
Run well
| CostSherlock rule | What it catches, in plain words |
|---|---|
uc_cluster_io_wait_dominant | CPUs waiting on disk or network |
uc_query_spill_to_disk_recurring | Memory spills every run |
uc_cluster_memory_pressure_high | Memory pressure on the cluster |
udf_proliferation | Many slow row-by-row functions |
uc_query_excessive_task_count | Too many tiny tasks |
uc_query_underpartitioned | Too few partitions to use the cluster |
aqe_disabled_in_sql | Adaptive execution switched off |
Do less
| CostSherlock rule | What it catches, in plain words |
|---|---|
collect_in_for_loop | collect() inside a loop |
code_spark_action_inside_loop | A Spark action inside a loop |
loop_calling_spark_sql | A SQL query run per loop pass |
loop_calling_spark_read | A table read per loop pass |
multi_read_same_table | The same table read several times |
repeated_table_scan | Repeated scans of the same data |
repeated_json_parsing | JSON parsed again and again |
ordering_without_limit | Sort with no limit |
select_distinct_star | DISTINCT over every column |
union_instead_of_union_all | UNION pays for a dedupe nobody needed |
delta_full_refresh_pattern | Whole table rewritten each run |
to_pandas_without_limit | Pulling a whole table into one machine |
Wait less
| CostSherlock rule | What it catches, in plain words |
|---|---|
uc_query_waiting_for_compute_dominant | Mostly waiting for compute |
uc_cluster_startup_latency_excessive | Long cluster start-up |
uc_job_task_retry_storm | Tasks retry over and over |
uc_orchestration_serial_when_parallel_possible | Serial steps that could run together |
uc_orchestration_polling_loop | Polling instead of reacting |
uc_merge_thrash_table | Merges keep rewriting the same files |
delta_merge_high_copy_ratio | Merge copies far more than it changes |
Precompute
| CostSherlock rule | What it catches, in plain words |
|---|---|
uc_dashboard_query_reads_bronze_instead_of_gold | Dashboard reads raw data instead of gold |
uc_dashboard_heavy_query_recurring | A heavy dashboard query runs again and again |
uc_query_recompute_no_caching | The same result is recomputed |
uc_materialized_view_forced_full_refresh | Materialised view refreshes in full |
FAQ
Questions people ask
Where should I look first when a Spark job is slow?
Open the slowest stage and ask which bucket it is: reading too much, shuffling, computing, spilling, one straggler task, or waiting. Fix the dominant bucket. Tuning a small bucket first rarely changes the total.
Is more parallelism always faster?
No. Parallelism helps until each piece is so small that starting it costs more than doing it. Aim for partitions of roughly 100 to 200 MB, and watch for tiny tasks and tiny output files.
Why do small files hurt so much?
Every file costs an open, a metadata lookup and a task slot before any data moves. Ten thousand 1 MB files do far more overhead than ten 1 GB files holding the same data.
Why does a low-cardinality column compress so well?
A column with five distinct values can be stored as a short code per row plus a five-entry dictionary, then run-length encoded. Unique values like ids have no repeats to exploit.
When does an embedded engine like DuckDB beat a cluster?
When data fits on one machine. A cluster pays job start-up, planning and shuffle cost on every action. For small and medium data on one node, that fixed overhead dominates, and a single-process engine avoids it.
Are the time and size numbers on this page measured?
No. They are orders of magnitude to build intuition. Measure your own job with the engine's query profile before deciding.
Related
Where it connects
Cost fundamentals · Processing fundamentals · Security fundamentals · Storage fundamentals · Database families · Where data engineering time goes · Who maintains your tables · All data engineering
The numbers and thresholds on this page are rules of thumb and orders of magnitude to build intuition, not benchmarks. Check your platform's current documentation and measure your own workload.