Dinesh/ Blog
data engineering

Designing for Scale · The Data Lakehouse

Explain atomic table snapshots over immutable files, writer conflicts, row deletes, schema identity, maintenance and measured architecture trade-offs.

12 min read
Checking device speech…
Lesson preparation & details

Level: advanced

Designing for scale · Lesson 11 of 20 ↗
Loading this browser’s progress…

By the end, you should be able to

  • Distinguish object atomicity from a multi-file table commit
  • Explain snapshot visibility and optimistic writer validation
  • Compare copy-on-write and merge-on-read semantics
  • Plan field evolution, compaction, retention and total-cost evaluation

Bring with you

Sources checked: . A source check does not change the publication or update date.

Editorial review: · What review means

In this article · 12 sections

The problem this post solves

A directory of Parquet files is not a transactional table. One job may be rewriting several files while another lists the directory; a failed writer can leave only part of its intended output. A table format adds a committed metadata view so readers agree which files and row-level changes belong to a snapshot.

Lakehouse designs combine object storage with table metadata, query engines and operational services. Managed warehouses can also separate storage from compute and support open tables. The useful comparison is workload, interoperability and total cost—not “warehouse always fast and expensive, lake always slow and cheap.”

First principles

Object-level atomicity and consistency do not supply a transaction over several objects. Modern S3 provides strong read-after-write consistency for object PUT/DELETE and listing; that does not turn five independently uploaded files into one atomic table update.

A writer first prepares new data files without advertising them as committed. It then publishes a new table state using the format/catalog's atomic commit protocol. Readers pin a committed snapshot and follow its references rather than inferring table membership from a directory listing. If a writer fails before the metadata commit, the prepared files can remain unreferenced; readers continue with the old snapshot. If a commit acknowledgment is lost, the writer must determine whether the commit succeeded before blindly retrying.

Thus “object storage cannot support ACID” is too broad: the missing property is table-level multi-file coordination, supplied by the protocol plus correctly behaving storage/catalog and engines. Durability, isolation level and transaction scope still need inspection; one table's atomic commit does not automatically make a multi-table job atomic.

Architecture: The Lakehouse and Open Table Formats

An Open Table Format (like Apache Iceberg, Apache Hudi, or Delta Lake) supplies the metadata layer that the immutable files are missing. In the diagram, different compute engines consult that layer to determine which files belong to the table. This is how the lakehouse separates compute from storage without asking each engine to invent its own view of the data.

Architecture: The Lakehouse and Open Table Formats
Figure text equivalent — labels and relationships
  • Node “Spark Cluster” (square).
  • Node “Metadata Layer Iceberg / Delta / Hudi” (square).
  • Node “Trino / Presto” (square).
  • Node “Snowflake / Athena” (square).
  • Node “Object Storage S3 / Parquet Files” (cylinder).
  • Group “Lakehouse” contains “Metadata Layer Iceberg / Delta / Hudi”, “Object Storage S3 / Parquet Files”.
  • “Spark Cluster” leads to “Metadata Layer Iceberg / Delta / Hudi”: Writes. Line: normal; ends: arrow point.
  • “Trino / Presto” leads to “Metadata Layer Iceberg / Delta / Hudi”: Reads. Line: normal; ends: arrow point.
  • “Snowflake / Athena” leads to “Metadata Layer Iceberg / Delta / Hudi”: Reads. Line: normal; ends: arrow point.
  • “Metadata Layer Iceberg / Delta / Hudi” leads to “Object Storage S3 / Parquet Files”: Tracks active files. Line: dotted; ends: arrow point.

The Metadata Layer

For Iceberg, a table's current metadata identifies its schema, partition specification and snapshots; snapshots refer through manifest lists/manifests to data and delete files. The catalog's atomic metadata replacement is the publication point. Writers use optimistic concurrency: read a base state, prepare a change, validate assumptions, then attempt an atomic replacement. If another writer won first, retry/rebase only when the operation remains valid; conflicting row changes may fail validation. A file upload succeeding does not mean the table transaction committed.

Delta's transaction log and Hudi's timeline implement related table-management goals through different protocols. Do not assume an Iceberg catalog pointer operation, a Delta log commit and a Hudi timeline action are interchangeable, or that every engine supports every format feature.

The library analogy remains useful: data files are books, and a snapshot is the ledger listing the books for one edition. Reader R starts on snapshot 10 referencing {A,B}. Writer W creates C, then atomically publishes snapshot 11 referencing {B,C}. R may continue reading A and B; a new reader uses B and C. Deleting A immediately would break R and historical reads even though the newest snapshot no longer references it.

Safety: a conforming reader does not observe half of the committed table update. Liveness: the writer eventually commits only if storage/catalog are reachable and conflicts permit progress. An unavailable catalog may stop new commits while existing pinned readers can still read reachable referenced files; this is not universal always-available transactional storage.

How it solves the Update Problem

Updating a row in an immutable Parquet object generally means writing replacement data or writing a supported row-level change representation. It does not necessarily mean scanning petabytes: partition pruning, file statistics and indexes can identify a smaller candidate set, with effectiveness depending on layout and predicates.

Copy-on-write (COW): rewrite affected data files and atomically replace their references. If one row changes in a 128 MB file, a simple implementation may rewrite that file, even though the changed row is tiny. Reads avoid merging separate row-change structures. Multiple changes can be batched into one rewrite; “COW is always slow” ignores workload and pruning.

Merge-on-read (MOR): record updates/deletes separately and merge them when reading, according to the format and engine. Iceberg v2 supports positional deletes (file path plus row position) and equality deletes (key-field values), not a universal “row ID 42” format. Hudi and Delta have their own mechanisms/features. Separate change records can reduce immediate write amplification but increase read/maintenance work; read amplification can become substantial, not always “slightly more.” An engine lacking the required delete support must not silently read only the base files.

Logical deletion is not physical erasure. A delete can hide a row from current queries while older snapshots, object versions, backups or replicas retain it. Privacy/compliance workflows must inventory those copies and retention obligations separately; an SQL delete is not a complete legal-compliance procedure.

Choosing the storage architecture

DimensionManaged warehouse candidateOpen-table/lakehouse candidate
OperationsOften integrated query service, workload management and governance; still needs modeling and tuningCan be managed or self-operated; catalog, engine compatibility, maintenance and permissions remain responsibilities
PortabilityCheck export, open-table support and proprietary feature dependenceOpen formats reduce one form of lock-in; catalogs, identity, protocol features and SQL dialects can still constrain movement
CostMeasure storage, scan/compute, concurrency, reservations and operationsMeasure object requests, compaction, storage, engine compute, egress and staff time—not just object bytes
PerformanceDepends on layout, workload, caching and provisioningAlso depends on layout, metadata pruning, file sizes, delete handling and engine support; not automatically warehouse-speed

Illustrative monthly budget: suppose object storage is 400,querycompute400, query compute 1,200, compaction 300,catalog/requests300, catalog/requests 100 and operations allocation 1,000.Totalis1,000. Total is 3,000, not 400.A400. A 2,500 managed alternative would be cheaper under those assumed inputs even if its storage line item were higher. Change the workload or staffing assumptions and the conclusion can reverse. These are synthetic costs, not vendor prices.

Choose open tables when multi-engine access, control and workload economics justify them. Choose a managed warehouse when its capabilities and total operational cost fit. There is no universal enterprise default or zero-lock-in guarantee.

Time Travel and Schema Evolution

Time travel selects an earlier committed table snapshot. It works only while the necessary metadata and data/delete files remain available and the engine supports that snapshot/protocol. SQL syntax is engine-specific; a generic FOR SYSTEM_TIME AS OF fragment is not portable across Spark, Trino, Delta and Iceberg.

For Iceberg, schemas use stable field IDs rather than just names or positions. Renaming a field preserves its identity; adding a new field assigns a new identity. Reusing an old display name must not cause a dropped field's old data to be read as the new field. Supported type promotions and partition evolution have explicit rules; arbitrary type changes or engines that ignore IDs are not automatically safe. Delta column mapping and other formats' evolution rules differ.

A reproducible ML result requires more than selecting a snapshot: record snapshot ID, code, feature definitions, dependency versions, seeds and relevant model artifacts. Snapshot expiry or a changed external join source can break replay. Pinning a table snapshot is an important ingredient, not “trivial reproducibility.”

Snapshot visibility model

This bounded in-memory example teaches the publication point. It does not implement Iceberg's manifest hierarchy or catalog concurrency, and it does not test an object store. Its commit stands for an atomic compare-and-swap, implemented sequentially here.

files = {'A': [1, 2], 'B': [3]}
snapshots = {10: ('A', 'B')}
current = 10
 
def read(snapshot):
    return [row for name in snapshots[snapshot] for row in files[name]]
 
def commit(expected, new_files):
    global current
    if current != expected:
        return None
    current += 1
    snapshots[current] = tuple(new_files)
    return current
 
reader_snapshot = current
files['C'] = [2]  # remove row 1 by rewriting A; not visible yet
files['orphan'] = [999]  # crashed writer never commits this file
assert read(current) == [1, 2, 3]
assert commit(10, ('B', 'C')) == 11
assert read(11) == [3, 2]
assert read(reader_snapshot) == [1, 2, 3]
assert commit(10, ('orphan',)) is None  # stale writer must revalidate
referenced = {name for refs in snapshots.values() for name in refs}
assert 'A' in referenced and 'orphan' not in referenced
assert 'A' not in snapshots[current]  # not current is NOT the same as unreferenced
print('snapshot 10 stays readable; snapshot 11 is atomic; stale commit rejected')

Exercise: writer X and writer Y both start from snapshot 10. X appends a file and commits 11. Can Y blindly publish its own complete file list based on 10? Answer: no; it could erase X's append from the visible table. Y must reload/rebase and validate, or fail. Two independent appends may be retryable; a row-level rewrite must revalidate which files/rows it depended on.

Failure modes

Small files. Suppose one day produces 86,400 files of 1 MB each: 86.4 GB of data. Compaction targeting roughly 256 MB can reduce this to about 338 files before partition boundaries and other layout limits. At a hypothetical serial 5 ms planning/open overhead per file, that component is 432 seconds versus about 1.69 seconds. Real engines parallelize and cache, and larger files can reduce pruning/parallelism, so this arithmetic is a planning illustration, not a speedup benchmark. Compact when measured file-count/read costs justify it; constant compaction is not mandatory for every table.

Different maintenance jobs. Rewriting small data files improves size/layout; rewriting manifests changes planning metadata; expiring snapshots removes historical references; orphan cleanup removes files not referenced by live metadata after a safe age. These jobs are not one generic “vacuum metadata” command. Delta VACUUM removes eligible data files and can make older versions unreadable; transaction-log retention is a separate setting/process.

Retention and slow readers. If a query pins snapshot 10 for two hours, deleting A after one hour because only snapshot 11 is current can break that query. Retention must accommodate the longest reader, concurrent writer, stream lag, replay and rollback requirements plus margin. Iceberg's maintenance documentation warns that too-short orphan retention can delete files still being written. Delta documents default data-file retention of seven days, but a default is not proof it fits a ten-day replay requirement. Do not disable retention safeguards merely to reclaim space quickly.

Protocol/engine mismatch. Test whether each engine supports the table version, catalog, delete representations, field IDs and chosen write operations. Reading raw Parquet under the table directory bypasses the transaction/deletion view. Open format is not universal feature compatibility.

Commit uncertainty and orphan cleanup. A writer can time out after the metadata commit actually succeeded. Check commit status and operation identity before retrying; blind duplication can create a second append. Orphan files after a pre-commit crash do not force partial reads if readers use snapshots. Cleanup needs a safe grace period and proof the files are unreferenced by retained snapshots, not merely absent from the newest one.

Worked answers

  • A crashed writer leaves C uncommitted; must readers see it? No. Snapshot readers follow committed references, not directory listings.
  • Does deleting row 42 erase every copy immediately? No. Current queries can hide it while history/backups retain it; handle those retention/compliance boundaries separately.
  • Why can a column rename preserve old files? Iceberg tracks stable field identity; the engine maps the new name to the same field ID. This is not arbitrary name-based schema guessing.
  • Is compaction compulsory after every streaming batch? No. Measure file counts, planning overhead and write/read amplification, then choose an appropriate schedule/target size and respect partition boundaries.
  • Does the oldest snapshot remaining in metadata guarantee time travel? No. Its referenced files must still exist, permissions must allow access, and the engine must support the snapshot's features.

What to carry forward

A lakehouse transaction is a protocol over data files, metadata and a catalog/commit mechanism—not a property of a folder called a table. Teach the atomic visibility point, writer conflict handling, pinned readers, COW/MOR trade-offs, field identity, retention and maintenance independently. Compare portability and total cost using a real workload rather than assuming open storage is always cheapest or fastest.

Sources and review boundary

Primary passages checked 2026-10-07; mutable documentation is bound to saved source hashes in the accompanying claim evidence. Numerical examples are explicit teaching assumptions. The bounded Python examples were run locally; no cloud/distributed deployment, PostgreSQL cluster, broker, object store, load benchmark or regional drill was executed. Compilation and diagram parsing do not establish deployed-system correctness.

  • S3 — S3 object-level atomicity does not itself supply a multi-file table transaction.; S3 successful object PUT is visible to subsequent GET/LIST under its documented model.
  • Apache Iceberg reliability — Iceberg publishes table changes through an atomic metadata update.; Iceberg optimistic retries validate operation assumptions.
  • Iceberg Evolve — Iceberg field identity uses stable IDs rather than names alone.
  • Iceberg Spec — Iceberg v2 has positional and equality delete representations.
  • Iceberg Maint — Compaction and manifest rewriting address different measured costs.; Orphan cleanup retention must not overlap in-progress writes.
  • Delta Lake vacuum utility — Delta vacuum and log retention are separate processes.; Deleting retained data files can remove time-travel capability.
  • Hudi — Hudi COW rewrites base files for updates/deletes.; Hudi MOR shifts some update work to read-time merge/compaction.

Pause / Recall / Apply

Can you explain it without the page?

Close the example. Reconstruct the core idea, then change one assumption. Mark complete when you’re ready; you can always undo it.

Stored in this browser only. No account, no sync. Clearing browser data removes your record.