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.

Bytes are the unit of cost.Almost every speed-up in data engineering is the same move: touch fewer bytes, or touch the same bytes fewer times.
Distance sets the price of a byte.Memory is fast, local disk is slower, object storage over a network is slower again, another region is slower still. Each step is roughly 10 to 1,000 times slower than the one before (orders of magnitude, not benchmarks).
Many small things are slower than a few big things.Every file, task, request and query pays a fixed start-up price before it moves one byte. A million tiny items pay it a million times.
Workers must agree.Joins, group-bys and sorts need rows with the same key in the same place. Moving them there (the shuffle) is the most expensive thing a cluster does.
Doing nothing is also a cost.A job waiting for a cluster to start, a lock, a slow source or one straggler task still holds the clock, and often the bill.

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.

Performance

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.

Longer bar = slower to reach one piece of data (log scale: each tick is 10x)CPU cachenanosecondsMemory~100 nsLocal SSD / disk cache~100 µsObject storage, same region~10-50 ms to first byteAnother service or API callms to seconds, per callAnother region or cloud100+ ms and egress feesA human approving somethinghours to days

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 isRough time to reach itEveryday exampleWhat to remember
CPU cache / memorynanosecondsA column already loaded in an executorKeep hot data here: caches, broadcast tables
Local SSD / disk cacheabout 100 microsecondsSpark disk cache, shuffle filesRepeated reads of the same files get fast
Object storage in the same regiontens of milliseconds to first byte, then high throughputParquet or Delta files in S3 / ADLSGreat for big sequential reads, poor for many tiny reads
Another service over the networkmilliseconds to seconds per callJDBC source, REST API, a lookup per rowNever call it once per row
Another region or cloudtens to hundreds of milliseconds, plus egress feesReading a bucket in another regionMove compute to the data, not data to the compute
A humanhours to daysWaiting for approval, a fix, a definitionThe 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.

A slow jobwaitreadshufflecomputewrite630 unitsSame job after fixing the biggest bucket (read)waitshufflecomputewrite450 unitsFix the dominant bucket first. Polishing a small one barely moves the total.

Where the time goes: find the biggest bucket before tuning anything. Illustrative proportions.

BucketWhat it looks likeTypical causeFirst moveVerdict
ReadingStage reads far more bytes than the result needsNo partition or file skipping, wide rows, SELECT *Filter early on the layout key; read fewer columnsPursue first
ShufflingBig shuffle read/write, long exchange stagesJoins and group-bys on big data, wrong partition countBroadcast the small side; filter before joiningPursue
ComputingExecutors busy, CPU highPython UDFs, regex, JSON parsing, heavy window functionsUse built-in functions; parse oncePursue when CPU-bound
SpillingMemory spills to diskPartitions too big, skewed keysMore, smaller partitions; fix skewPursue
Skew / stragglersOne task runs 10x longer than the medianA few keys hold most rowsSalting, skew-join handling, split the hot keyPursue
WaitingWall-clock far above task timeCluster start, queue, locks, retries, slow sourceWarm pools, concurrency limits, tune the source sidePursue when gaps are large
WritingSlow commits, many tiny output filesToo many partitions at write, small files, merge rewritesCoalesce or auto-compact; avoid rewriting whole filesPursue

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.

Row storage: a row sits togetheriddatestatusamountColumn storage: a column sits togetheriddatestatusamountTotal of amount: reads every cellTotal of amount: reads one column

Why analytics uses column storage: a query about one column reads one column.

Random layoutfile 1Jan-Decfile 2Jan-Decfile 3Jan-Decfile 4Jan-DecQuery: WHERE month = FebClustered by datefile 1Jan-Marfile 2Apr-Junfile 3Jul-Sepfile 4Oct-DecQuery: WHERE month = FebAll 4 files could hold Feb: all opened.Only file 1 can hold Feb: 3 skipped.

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.

DecisionWhat it doesHelps whenHurts whenVerdict
Row-oriented storageStores each row's values togetherYou fetch whole rows by key (transactions)You scan a few columns across millions of rowsRight for OLTP, wrong for analytics
Column-oriented storageStores each column's values togetherAnalytics: read three columns of fiftyYou fetch single full rows all dayDefault for analytics
CompressionShrinks bytes on disk and on the wireAlmost always: less to read and moveVery high CPU codecs on data read oncePursue; pick a balanced codec
Encoding (dictionary, run-length)Stores repeated values as small codesLow-cardinality columns (status, country)Unique values like ids and free textFree win on suitable columns
PartitioningSplits data into folders by a column valueQueries filter on that column; low-to-medium cardinalityToo many values, or queries rarely filter on itUse sparingly; wrong key is costly
Sort / Z-order style co-locationPuts similar values near each other in filesFilters on several columns, so file min/max ranges are tightData is rewritten constantlyPursue for big tables
Liquid-style clusteringAdaptive co-location that changes without rewriting everythingQuery patterns change or keys are high-cardinalityTiny tablesPrefer over fixed partitions when available
File statistics (min/max per file)Lets the engine skip whole filesData is clustered so ranges do not overlapData is random, every file spans every valueFree if layout cooperates
Bloom filtersA tiny index answering 'is this value definitely not in this file?'Point lookups on high-cardinality columnsRange filters, low-cardinality columnsTargeted use
Right-sized filesFiles big enough to amortise open cost, small enough to paralleliseRoughly 128 MB to 1 GB per fileThousands of tiny files, or one giant filePursue

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.

ColumnKind of valuesHow well it compressesWhy
Order status (5 values)Low cardinalityExtremely well, often 50x or moreDictionary encoding stores a tiny code per row
Country codeLow cardinality, repeatedExtremely wellSame reason; sorted data adds run-length gains
Date / timestampOrdered, many repeatsWellDeltas and repeats are small
Integer measureNarrow rangeWellBit-packing stores only the bits needed
Customer id (unique)High cardinalityModestlyFew repeats to exploit
Free text / JSON stringUnique, longPoorly, and costly to parseLittle structure; parse once into typed columns
Random UUID stringUnique, no orderPoorlyStore 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.

Driverplans and schedulesExecutor 1tasktasktaskExecutor 2tasktasktaskExecutor 3tasktasktaskOne task per partition. Too few leave cores idle; too many drown in start-up cost.

Driver, executors and tasks. The driver coordinates; executors do the work in parallel, one task per partition per core.

ThingPlain meaningToo little looks likeToo much looks likeVerdict
Number of tasks / partitionsPieces of work run in parallelIdle cores, huge partitions, spillScheduler overhead, tiny files, long tail of tiny tasksAim for partitions of about 100 to 200 MB
Executors and coresThe parallel workersJob crawls, partitions queueCores idle, bill highSize to the work, enable autoscaling with a sensible floor
DriverPlans the job, collects small resultsDriver out of memory on collect or large broadcastOver-sized driver paid for nothingKeep data off the driver
Executor memoryRoom to hold a partition and its joinsSpill, out-of-memory errorsWasted money on memory-optimised nodesFix partition size before buying memory
Actions and job overheadEach action (count, collect, write, show) launches a job with planning and scheduling costNot the issueHundreds of tiny jobs in a loop; seconds of fixed cost eachBatch work into one job; this is the gap embedded engines like DuckDB close for small data
Python UDFsRow-by-row Python outside the engineNot the issueSerialisation cost on every row, no optimiser helpUse built-ins, or vectorised functions where unavoidable
Serverless vs classic computeWho manages start-up and sizingCold-start waits on classicPay for always-on serverless idleMatch 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.

Narrow step: stays inside a partitionWide step (shuffle): all to allFilter, select, mapJoin, group-by, sort: data crosses the network

Why a shuffle is the expensive step: all-to-all movement across the network. Filter before it, not after.

SituationWhat happensBetter approachVerdict
Join big to smallBoth sides are shuffled by keyBroadcast the small table to every worker; no shuffle of the big onePursue
Join big to bigBoth sides shuffled; expensive but sometimes unavoidableFilter and project first; pre-partition both by the join key if reusedPursue; accept the cost
Skewed keyOne task gets most of the rows and runs far longerSplit the hot key, salt, or use skew handlingPursue
Function wrapped around a join or filter columnThe engine cannot use file statistics or the join planCompare the raw column; fix types at write timePursue
Too many shuffle partitionsThousands of tiny tasks, tiny output filesLet adaptive execution coalesce themPursue
Too few shuffle partitionsHuge tasks, spill to diskRaise partitions or let adaptive execution splitPursue
Cartesian joinEvery row meets every rowAdd the missing join conditionAlways 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 patternWhy it hurtsFixVerdict
Processing data nobody usesEvery unused column, row and table is paid for on every runRead only needed columns and rows; retire unused outputsPursue first
Full reload when only changes arrivedReprocesses yesterday's data againIncremental loads with a watermark or change feedPursue
collect() inside a loopPulls data to the driver once per iteration, serial and slowDo it as one set-based queryAlways fix
Same query run in a loopPays planning and scan cost every timeRun once with a join or group-by; or read once and reuseAlways fix
Reading the same table repeatedlyEach read scans the files againRead once, reuse (cache only if it truly pays)Pursue
Recomputing an unchanged resultSame inputs, same answer, paid againCache, incremental, or materialisePursue
SELECT * and no filterReads every column and rowName columns, filter earlyPursue
Sorting without LIMITSorts everything to show ten rowsSort only what you needPursue
DISTINCT used to hide a bad joinFan-out then dedupe doubles the workFix the join grainPursue

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 waitWhat it isHow it shows upFixVerdict
Waiting for computeCluster start or a busy warehouse queueLong gap before the first taskWarm pools, right-sized concurrency, schedule off-peakPursue
Locks and write conflictsTwo writers touch the same files or rowsRetries, 'concurrent update' errors, serial slowdownsPartition writers by key range; separate maintenance windows; narrow merge conditionsPursue
Hidden rewritesA small change rewrites whole files (merge, update, delete)Writes far larger than the changeDeletion vectors / merge-on-read style, tight merge predicatesPursue on busy tables
Retry stormsFailures that retry and re-run workSame task runs several timesFix the root error; cap retriesPursue
Orchestrator gapsSerial steps that could run in parallel, polling loopsWall-clock far above compute timeRun independent tasks in parallel; use events instead of pollingPursue
Slow sourceThe database or API cannot hand over data quicklyReads are slow with idle clusterParallel reads on a key range, replicas, or change-based extractionPursue

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.

Without a gold objectWith a gold objectraw tablessame heavy querysame heavy querysame heavy querysame heavy queryEvery viewer pays the full scan and join cost.raw tablesoncegold tablecheap readcheap readcheap readcheap readPay once at load time; every read is small and fast.

Why gold has performance objects: spend write time once to save read time many times.

ObjectWhat it precomputesSaves on every readCostsVerdict
Fact table at a fixed grainJoined, cleaned, typed rows at one clear grainJoins and cleaningStorage and load timeCore gold object
Dimension tableSmall, descriptive lookup with stable keysRepeated lookups and text columnsMaintenance of historyCore gold object
Aggregate / summary tableDaily or monthly totalsScanning billions of rows for a chartMust stay in step with its basePursue for hot dashboards
Materialised viewA stored query result refreshed on changeRecomputing the queryRefresh cost, stalenessPursue when refresh is incremental
Clustered / sorted layoutCo-locates by the common filterBytes scannedRewrite costPursue for big gold tables
Semantic / metric layerDefinitions computed onceEvery tool recomputing differentlyGovernance effortPursue
Result and disk cachesRecent answers and filesRepeated identical queriesMemory, stalenessPursue, 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 ruleWhat it catches, in plain words
unpartitioned_large_tableLarge table with no layout to skip files
partition_filter_unusedPartitioned, but queries do not filter on it
uc_query_low_pruning_ratioQuery opens most files anyway
uc_query_full_scan_recurringThe same full scan, again and again
select_starReads every column
function_on_partition_columnA function hides the partition filter
not_equals_defeats_skippingA not-equals filter cannot skip files
or_across_columns_defeats_pruningOR across columns blocks pruning

Lay out well

CostSherlock ruleWhat it catches, in plain words
too_many_filesThousands of tiny files
mixed_file_sizesFile sizes vary wildly
target_file_size_too_smallTarget size set too low
over_partitioned_tableToo many partitions for the data volume
excessive_partition_depthNested partitions too deep
table_too_small_for_partitioningPartitioning a table that is too small to benefit
uc_zorder_misaligned_with_filtersCo-location columns differ from filter columns
using_unoptimised_formatNot a columnar, compressed format

Move less

CostSherlock ruleWhat it catches, in plain words
broadcast_join_missedA small side was shuffled instead of broadcast
uc_query_high_shuffle_ratioShuffle dwarfs input
partition_skewOne partition holds most rows
cross_joinEvery row meets every row
function_on_join_keyA function blocks an efficient join
implicit_type_cast_on_filterA hidden type cast disables skipping
excessive_shuffle_partitions_configShuffle partition setting is far off

Run well

CostSherlock ruleWhat it catches, in plain words
uc_cluster_io_wait_dominantCPUs waiting on disk or network
uc_query_spill_to_disk_recurringMemory spills every run
uc_cluster_memory_pressure_highMemory pressure on the cluster
udf_proliferationMany slow row-by-row functions
uc_query_excessive_task_countToo many tiny tasks
uc_query_underpartitionedToo few partitions to use the cluster
aqe_disabled_in_sqlAdaptive execution switched off

Do less

CostSherlock ruleWhat it catches, in plain words
collect_in_for_loopcollect() inside a loop
code_spark_action_inside_loopA Spark action inside a loop
loop_calling_spark_sqlA SQL query run per loop pass
loop_calling_spark_readA table read per loop pass
multi_read_same_tableThe same table read several times
repeated_table_scanRepeated scans of the same data
repeated_json_parsingJSON parsed again and again
ordering_without_limitSort with no limit
select_distinct_starDISTINCT over every column
union_instead_of_union_allUNION pays for a dedupe nobody needed
delta_full_refresh_patternWhole table rewritten each run
to_pandas_without_limitPulling a whole table into one machine

Wait less

CostSherlock ruleWhat it catches, in plain words
uc_query_waiting_for_compute_dominantMostly waiting for compute
uc_cluster_startup_latency_excessiveLong cluster start-up
uc_job_task_retry_stormTasks retry over and over
uc_orchestration_serial_when_parallel_possibleSerial steps that could run together
uc_orchestration_polling_loopPolling instead of reacting
uc_merge_thrash_tableMerges keep rewriting the same files
delta_merge_high_copy_ratioMerge copies far more than it changes

Precompute

CostSherlock ruleWhat it catches, in plain words
uc_dashboard_query_reads_bronze_instead_of_goldDashboard reads raw data instead of gold
uc_dashboard_heavy_query_recurringA heavy dashboard query runs again and again
uc_query_recompute_no_cachingThe same result is recomputed
uc_materialized_view_forced_full_refreshMaterialised 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.