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.
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. 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.
| Store | Strength | Weakness | Typical use | Verdict |
|---|---|---|---|---|
| Object storage | Cheap, huge, durable, shared by many engines | Higher latency per request; no in-place edits | Lakehouse files and tables | Default for analytics |
| Relational database | Fast single-row reads and writes, constraints | Costly to scale for big scans | Operational systems, small lookups | Source, not the lake |
| Block / local disk | Low latency | Tied to a machine | Caches, shuffle, scratch | Temporary |
| Network file system | Familiar paths | Throughput limits | Legacy and shared work areas | Avoid for big tables |
| Key-value / search stores | Instant lookups or text search | Poor for scans and joins | Serving layers | Serving 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.
Why analytics uses column storage: a query about one column reads one column.
| Format idea | How data is stored | Good for | Not for | Verdict |
|---|---|---|---|---|
| Row-based (CSV, JSON, Avro) | One record after another | Writing fast, streaming, whole-record reads | Analytics scans; CSV loses types | Landing and exchange |
| Column-based (Parquet, ORC) | Column by column, with per-file statistics | Analytics, skipping files and columns | Single-record updates | Default for tables |
| Compression | Shrinks bytes | Everything | Nothing, if the codec is balanced | Always on |
| Dictionary and run-length encoding | Repeated values become tiny codes | Low-cardinality columns | Unique values | Automatic in columnar formats |
| Right data types | Numbers as numbers, dates as dates | Compression, comparison, pruning | Everything as strings | Fix at silver |
| Nested / semi-structured columns | Structures inside a column | Variable data from APIs | Heavy filtering on nested fields | Flatten 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.
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.
Partition only on a column people filter on, and only so each partition stays large.
| Technique | Idea | Good when | Bad when | Verdict |
|---|---|---|---|---|
| Partition by a column | One folder per value | Common filter, few to hundreds of values, big data | High cardinality, small tables, or filter on other columns | Use carefully |
| Sort / cluster within files | Similar values land in the same files | Filters on one or several columns | Never filtered | Pursue for large tables |
| Multi-column co-location (Z-order style) | Interleaves several columns' order | Filters vary across columns | A single dominant filter (sort on it) | Pursue when filters vary |
| Adaptive clustering (liquid style) | Keys can change without rewriting the world | Patterns change; high-cardinality keys | Tiny tables | Prefer where available |
| Right-sized files | Large enough to amortise cost | 100 MB to 1 GB files | Thousands of tiny files | Always aim for it |
| Over-partitioning | Too many folders, each tiny | Never | Always | Avoid |
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.
How lake tables work: update = write new files and record it in the log.
| Feature | What it gives | Why you care |
|---|---|---|
| Transaction log | Atomic commits, consistent reads | No half-written tables |
| Schema enforcement and evolution | Rejects bad writes; allows controlled change | Prevents silent corruption |
| Time travel / versions | Read an earlier snapshot | Debugging and recovery; storage cost if kept long |
| Optimistic concurrency | Writers check for conflict at commit | Overlapping writes may fail and retry |
| Statistics in the log | File-level min/max, counts | Skipping without opening files |
| Constraints | Not-null, check rules | Cheap 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.
| Operation | What is written | Cost shape | Verdict |
|---|---|---|---|
| Append | New files only | Cheapest | Prefer when possible |
| Overwrite partition | Replaces the files of one partition | Predictable | Good for idempotent loads |
| Merge / upsert | Rewrites every file that holds a matching row | Large if matches are scattered | Narrow the condition; cluster by the key |
| Delete / update | Rewrites files or marks rows deleted | Depends on deletion markers | Use markers on busy tables |
| Change feed | Extra record of changes | Extra storage | Only 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.
| Task | What it does | Run when | Verdict |
|---|---|---|---|
| Compaction | Merges small files into larger ones | File count or size drifts | Automate where the platform can |
| Clean-up of old files | Physically removes unreferenced files | After retention passes | Schedule |
| Statistics refresh | Keeps skipping accurate | After big changes | Automatic if possible |
| Re-clustering | Restores layout after many writes | Layout quality drops | On need |
| Retention policy | Limits history kept | Always defined | Define it |
| Who runs it | Platform or you | Decide explicitly | See 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.
| Choice | Effect | Verdict |
|---|---|---|
| Hot tier for active tables | Fast, higher price per byte | Active data |
| Cool or archive tier for history | Cheap to keep, slow or costly to read | Data read rarely |
| Lifecycle rules | Move or delete by age automatically | Pursue |
| Replication across regions | Durability and latency, plus cost | Only where needed |
| Duplicate copies | Pays twice and drifts | Avoid; 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 rule | What it catches, in plain words |
|---|---|
unpartitioned_large_table | Large table with no layout |
over_partitioned_table | Too many partitions |
table_too_small_for_partitioning | Partitioning on a small table |
excessive_files_per_partition | Too many files in a partition |
uc_partition_columns_never_filtered | Partition columns no query uses |
unclustered_filter_dominant | Filters dominate but no clustering |
unclustered_large_table | Large table with no clustering |
clustering_recommended | Clustering advised |
uc_zorder_misaligned_with_filters | Co-location columns differ from filters |
uc_liquid_clustering_unused | Adaptive clustering available but unused |
How it is encoded
| CostSherlock rule | What it catches, in plain words |
|---|---|
using_unoptimised_format | Not a columnar, compressed format |
implicit_type_cast_on_filter | Type mismatch forces casts |
repeated_json_parsing | JSON left as strings and re-parsed |
File sizing
| CostSherlock rule | What it catches, in plain words |
|---|---|
too_many_files | Too many small files |
mixed_file_sizes | Uneven file sizes |
target_file_size_too_small | Target too small |
target_file_size_too_large | Target too large |
uc_auto_compact_with_large_files | Auto-compact on already large files |
uc_optimize_write_with_large_writes | Write optimisation on large writes |
How it changes
| CostSherlock rule | What it catches, in plain words |
|---|---|
delta_merge_high_copy_ratio | Merge copies much more than it changes |
merge_without_partition_predicate | MERGE with no partition limit |
uc_merge_thrash_table | Merges rewriting the same data |
uc_deletion_vectors_enabled_no_dml | Deletion markers on with no updates |
delta_no_deletion_vectors_on_active_rewrites | Active rewrites without deletion markers |
delta_schema_thrash | Schema changing repeatedly |
How it stays healthy
| CostSherlock rule | What it catches, in plain words |
|---|---|
missing_optimize | No compaction |
delta_optimize_overdue | Compaction overdue |
delta_optimize_thrash | Compaction too frequent |
delta_vacuum_overdue | Clean-up overdue |
delta_vacuum_retention_drift | Retention drifting from policy |
delta_log_growth_without_checkpoint | Log growing without checkpoints |
uc_predictive_optimize_failed_recurring | Automatic maintenance keeps failing |
uc_predictive_optimize_skipped_dominant | Automatic maintenance mostly skipped |
uc_maintenance_history_stale | No 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.