Home › Data engineering › Fundamentals › Storage

Data engineering fundamentals · free guide

Storage fundamentals: how data is kept and why layout matters

Storage is where performance, cost and correctness begin. A table is just files in a folder plus a record of which files belong to it. This page explains what that means, how layout decisions affect every read, and what maintenance keeps a table healthy, organised as a mind map.

Start here

The data physics

Five plain facts that explain almost everything below. Read these and the rest follows.

A table is files plus a ledger.Modern lake tables are many immutable files and a log that says which files are current. Updates write new files; they do not edit old ones.
Files are never edited, only replaced.That makes writes safe and readers consistent, but a small change can rewrite a large file.
Layout is a bet on how the data will be read.Sort or group by what people filter on and reads skip most files. Guess wrong and every read pays.
Object storage is big and patient.It handles huge sequential reads well and tiny random ones badly. Design for fewer, larger files.
Old data accumulates.Versions, logs and small files pile up unless something tidies them.

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.

Storage

1. Where it lives

What kind of store?

Object storage, block, file system, database; regions; tiers

First check: Access pattern vs store type

2. How it is encoded

What is in the file?

Row vs column formats, compression, encoding, data types

First check: Format and codec per layer

3. How it is laid out

How are files grouped?

Partitioning, clustering, sort order, file size

First check: Files opened vs needed

4. How it is a table

What makes files a table?

Table formats, transaction log, schema, constraints, versions

First check: Can two writers collide?

5. How it changes

What does an update do?

Append, overwrite, merge, deletes, deletion markers, change feed

First check: Bytes written vs bytes changed

6. How it stays healthy

What tidies it?

Compaction, clean-up, statistics, retention, automatic maintenance

First check: Small-file count, age of last compaction

7. How it is priced

What does keeping it cost?

Tiers, lifecycle rules, replication, egress

First check: Cold data on hot tiers

Where it lives

Choosing the kind of store

Analytics data lives in object storage because it is cheap, durable and shared. Other stores suit other access patterns.

StoreStrengthWeaknessTypical useVerdict
Object storageCheap, huge, durable, shared by many enginesHigher latency per request; no in-place editsLakehouse files and tablesDefault for analytics
Relational databaseFast single-row reads and writes, constraintsCostly to scale for big scansOperational systems, small lookupsSource, not the lake
Block / local diskLow latencyTied to a machineCaches, shuffle, scratchTemporary
Network file systemFamiliar pathsThroughput limitsLegacy and shared work areasAvoid for big tables
Key-value / search storesInstant lookups or text searchPoor for scans and joinsServing layersServing only

How it is encoded

Row, column and compression

The format decides how many bytes a query must read to answer. Columnar formats store each column together so a query reads only the columns it names.

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.

Format ideaHow data is storedGood forNot forVerdict
Row-based (CSV, JSON, Avro)One record after anotherWriting fast, streaming, whole-record readsAnalytics scans; CSV loses typesLanding and exchange
Column-based (Parquet, ORC)Column by column, with per-file statisticsAnalytics, skipping files and columnsSingle-record updatesDefault for tables
CompressionShrinks bytesEverythingNothing, if the codec is balancedAlways on
Dictionary and run-length encodingRepeated values become tiny codesLow-cardinality columnsUnique valuesAutomatic in columnar formats
Right data typesNumbers as numbers, dates as datesCompression, comparison, pruningEverything as stringsFix at silver
Nested / semi-structured columnsStructures inside a columnVariable data from APIsHeavy filtering on nested fieldsFlatten what you filter on

How it is laid out

Partitioning, clustering and file sizing

Layout is how rows are grouped into files. The engine uses each file's minimum and maximum values to skip files, so grouped data skips well and scattered data does not.

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.

Too many partitionsRight-sized partitions1 MB1 MB1 MB1 MB1 MB1 MB1 MB1 MB1 MB1 MB1 MB1 MBTiny files: slow to list, little skipping.month 1~500 MBmonth 2~500 MBmonth 3~500 MBHealthy sizes: cheap to list, easy to skip.

Partition only on a column people filter on, and only so each partition stays large.

TechniqueIdeaGood whenBad whenVerdict
Partition by a columnOne folder per valueCommon filter, few to hundreds of values, big dataHigh cardinality, small tables, or filter on other columnsUse carefully
Sort / cluster within filesSimilar values land in the same filesFilters on one or several columnsNever filteredPursue for large tables
Multi-column co-location (Z-order style)Interleaves several columns' orderFilters vary across columnsA single dominant filter (sort on it)Pursue when filters vary
Adaptive clustering (liquid style)Keys can change without rewriting the worldPatterns change; high-cardinality keysTiny tablesPrefer where available
Right-sized filesLarge enough to amortise cost100 MB to 1 GB filesThousands of tiny filesAlways aim for it
Over-partitioningToo many folders, each tinyNeverAlwaysAvoid

How it is a table

Table formats and the transaction log

A table format keeps a log of which files are current, so readers see a consistent snapshot and writers can commit atomically.

A table is files plus a ledgerTransaction log (the ledger)v1: add file A, file Bv2: add file Cv3: remove A, add A2current = B, C, A2file Afile Bfile Cfile A2files are never edited, only added or replacedReaders follow the ledger: always a consistent snapshot

How lake tables work: update = write new files and record it in the log.

FeatureWhat it givesWhy you care
Transaction logAtomic commits, consistent readsNo half-written tables
Schema enforcement and evolutionRejects bad writes; allows controlled changePrevents silent corruption
Time travel / versionsRead an earlier snapshotDebugging and recovery; storage cost if kept long
Optimistic concurrencyWriters check for conflict at commitOverlapping writes may fail and retry
Statistics in the logFile-level min/max, countsSkipping without opening files
ConstraintsNot-null, check rulesCheap correctness at write time

How it changes

What an update really does

Because files are immutable, a row-level change must write new files. How much it writes decides cost and conflict risk.

OperationWhat is writtenCost shapeVerdict
AppendNew files onlyCheapestPrefer when possible
Overwrite partitionReplaces the files of one partitionPredictableGood for idempotent loads
Merge / upsertRewrites every file that holds a matching rowLarge if matches are scatteredNarrow the condition; cluster by the key
Delete / updateRewrites files or marks rows deletedDepends on deletion markersUse markers on busy tables
Change feedExtra record of changesExtra storageOnly with a consumer

How it stays healthy

Maintenance

Tables decay: small files accumulate, old files linger, statistics go stale. Maintenance is part of the design, not an afterthought.

TaskWhat it doesRun whenVerdict
CompactionMerges small files into larger onesFile count or size driftsAutomate where the platform can
Clean-up of old filesPhysically removes unreferenced filesAfter retention passesSchedule
Statistics refreshKeeps skipping accurateAfter big changesAutomatic if possible
Re-clusteringRestores layout after many writesLayout quality dropsOn need
Retention policyLimits history keptAlways definedDefine it
Who runs itPlatform or youDecide explicitlySee who maintains your tables

How it is priced

Tiers and lifecycle

Cold data on cold tiers is cheap to keep and costly to read. Hot data on cold tiers is a slow, expensive mistake.

ChoiceEffectVerdict
Hot tier for active tablesFast, higher price per byteActive data
Cool or archive tier for historyCheap to keep, slow or costly to readData read rarely
Lifecycle rulesMove or delete by age automaticallyPursue
Replication across regionsDurability and latency, plus costOnly where needed
Duplicate copiesPays twice and driftsAvoid; share instead

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.

How it is laid out

CostSherlock ruleWhat it catches, in plain words
unpartitioned_large_tableLarge table with no layout
over_partitioned_tableToo many partitions
table_too_small_for_partitioningPartitioning on a small table
excessive_files_per_partitionToo many files in a partition
uc_partition_columns_never_filteredPartition columns no query uses
unclustered_filter_dominantFilters dominate but no clustering
unclustered_large_tableLarge table with no clustering
clustering_recommendedClustering advised
uc_zorder_misaligned_with_filtersCo-location columns differ from filters
uc_liquid_clustering_unusedAdaptive clustering available but unused

How it is encoded

CostSherlock ruleWhat it catches, in plain words
using_unoptimised_formatNot a columnar, compressed format
implicit_type_cast_on_filterType mismatch forces casts
repeated_json_parsingJSON left as strings and re-parsed

File sizing

CostSherlock ruleWhat it catches, in plain words
too_many_filesToo many small files
mixed_file_sizesUneven file sizes
target_file_size_too_smallTarget too small
target_file_size_too_largeTarget too large
uc_auto_compact_with_large_filesAuto-compact on already large files
uc_optimize_write_with_large_writesWrite optimisation on large writes

How it changes

CostSherlock ruleWhat it catches, in plain words
delta_merge_high_copy_ratioMerge copies much more than it changes
merge_without_partition_predicateMERGE with no partition limit
uc_merge_thrash_tableMerges rewriting the same data
uc_deletion_vectors_enabled_no_dmlDeletion markers on with no updates
delta_no_deletion_vectors_on_active_rewritesActive rewrites without deletion markers
delta_schema_thrashSchema changing repeatedly

How it stays healthy

CostSherlock ruleWhat it catches, in plain words
missing_optimizeNo compaction
delta_optimize_overdueCompaction overdue
delta_optimize_thrashCompaction too frequent
delta_vacuum_overdueClean-up overdue
delta_vacuum_retention_driftRetention drifting from policy
delta_log_growth_without_checkpointLog growing without checkpoints
uc_predictive_optimize_failed_recurringAutomatic maintenance keeps failing
uc_predictive_optimize_skipped_dominantAutomatic maintenance mostly skipped
uc_maintenance_history_staleNo recent maintenance

FAQ

Questions people ask

Why is partitioning not always good?

Each partition is a separate folder of files. Partition on a column with too many values, or one nobody filters on, and you get thousands of tiny files and no skipping.

What is the difference between partitioning and clustering?

Partitioning splits data into folders by value. Clustering orders rows within and across files so similar values sit together. Clustering is more flexible and works with high-cardinality columns.

Why do updates seem slow on a data lake?

Files are immutable, so a changed row means rewriting the file that holds it. Scattered matches rewrite many files. Cluster by the match key and narrow the merge condition.

How big should files be?

Roughly 100 MB to 1 GB each is a common starting point. Too small wastes overhead; too large limits parallelism.

Do I need to keep time travel forever?

No. Keep what you use for recovery and debugging. Longer history costs storage and slows clean-up.

Are the file sizes here fixed rules?

They are starting points. Check your engine's current guidance and measure.

Related

Where it connects

Performance fundamentals · Cost fundamentals · Processing fundamentals · Security 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.