11. Operate an hourly updated ten-billion-row lakehouse table.
Choose table format, partition and sort strategy, merge mechanism, snapshot retention, compaction, metadata cleanup, and compute isolation. Explain how concurrent readers obtain stable snapshots while late corrections and hourly writes commit atomically, and how the platform recovers from an interrupted rewrite.
I would use Apache Iceberg format v2 on object storage, with separate Spark writer, reader, and maintenance compute. Hourly MERGE INTO jobs publish atomic snapshots, so readers keep stable views during corrections. The trade-off is added metadata, retention, and compaction work for stronger operational correctness.
At ten billion rows, the hard part is not simply loading another hourly batch. Producer systems keep sending new records and late corrections while analysts, scientists, applications, and downstream data products may be reading the table at the same time. A one-off pipeline does not solve atomic publication, concurrent reads, file layout, snapshot retention, compaction, metadata growth, or failed rewrites. I would operate the table as a reusable Apache Iceberg lakehouse service with shared object storage and catalog metadata, while separating writer, reader, and maintenance compute so each workload can run without starving the others.
- What are the dominant read patterns: recent-time scans, entity lookups, broad historical analytics, or a mixture?
- How late can corrections arrive, and can the same entity be corrected repeatedly?
- How much snapshot history must remain available for rollback or time travel?
- Which Spark or SQL engines must read the Iceberg table?
- Do writer, reader, and maintenance workloads need separate quotas or scheduling priorities?
The logical table is an Apache Iceberg format-v2 table. Production records are stored in immutable data files on object storage. The catalog side stores table metadata, snapshots, and manifests rather than the production rows themselves.
That separation gives the platform a clean publication boundary. Writers can create new or replacement data files first, but readers do not treat those files as part of the table until a successful metadata commit publishes a new snapshot. A failed operation before that point does not expose a partially updated logical table.
The trade-off is that a table at this scale can accumulate many files, manifests, snapshots, and metadata objects, so lifecycle maintenance must be part of normal platform operation.
The selected table layout partitions by day(event_ts). This gives useful pruning for time-based work while avoiding a finer hourly partition scheme that could create excessive partition management overhead.
Inside that layout, the selected sort order is (entity_id, event_ts). This improves locality for records belonging to the same entity while still retaining useful event-time ordering. The partition strategy and sort strategy solve different problems: partitioning reduces the file groups considered for relevant predicates, while sorting improves locality within the physical data layout.
Repeated hourly merges can still create small or uneven files, so this layout does not eliminate the need for compaction.
Transactional systems, application events, event streams, and explicit late corrections feed the writer side as an hourly batch. Isolated Spark writer compute first loads and validates that input and then applies MERGE INTO as an upsert.
The same merge path handles newly arrived records and corrections to existing records. The writer pool owns this work; reader and maintenance compute do not perform the hourly merge.
The table uses optimistic concurrency. If another writer commits a conflicting table change first, the losing operation detects that its expected table state is stale and retries rather than publishing a conflicting partial result.
Writing data files is not the publication event. After Spark has produced the required files, the writer must successfully commit new Iceberg table metadata. That commit creates a new snapshot that becomes the table's new current state.
This commit boundary is what makes hourly writes and late corrections safe for concurrent readers. A consumer sees a committed snapshot, not an intermediate collection of newly created files. If the commit does not succeed, the previous snapshot remains the authoritative table state.
Reader workloads run on their own Spark or SQL-engine compute pool. Each query resolves a table snapshot and reads the data files referenced by that snapshot. Commits that happen after the query has established its snapshot do not turn the query into a mixture of old and new table versions.
That gives BI and analytics users, data scientists, applications, and downstream data products a stable, consistent view while the writer publishes hourly updates in parallel. A later query can observe the newer committed snapshot, while a query already using an earlier snapshot continues against that earlier table state.
The operational trade-off is that snapshot and file cleanup must be conservative enough that retained history and in-flight work are not invalidated by aggressive deletion.
A third Spark compute pool owns maintenance. The selected maintenance operations are rewrite_data_files for compaction, optional rewrite_manifests, expire_snapshots for snapshot retention, and remove_orphan_files for unreferenced-file cleanup.
rewrite_data_files rewrites data files into a healthier physical layout after repeated merges. A successful rewrite publishes replacement files through a new snapshot. rewrite_manifests can reorganize manifest metadata when needed. expire_snapshots removes old snapshot history according to the retention policy. remove_orphan_files cleans files that are not referenced by valid table metadata after a conservative age threshold.
Separate maintenance compute prevents large rewrite work from taking the compute capacity needed by hourly writers or interactive readers. Storage and catalog state remain shared; the isolation is in compute resources.
The diagram keeps recent snapshots based on both age and a minimum retained count. This preserves a rollback and time-travel window without retaining every snapshot indefinitely.
Snapshot expiration removes old snapshot metadata when the retention policy permits it. Files that remain referenced by retained snapshots must remain protected. This means retention directly affects storage use: longer history gives operators more rollback and investigation flexibility but keeps more metadata and referenced data files alive.
The platform should therefore treat snapshot retention as an explicit operating policy rather than an accidental side effect of hourly commits.
The important failure case is a rewrite that creates replacement files and then stops before committing replacement metadata. In that case, the current snapshot does not change. Readers continue using the last committed snapshot and never see a half-rewritten table.
The failed operation can leave newly created files in object storage that no committed snapshot references. Those files are not part of the visible logical table. The maintenance owner can retry the rewrite, and remove_orphan_files can later delete the unreferenced files after the conservative safe-age threshold.
This is different from rolling back a committed snapshot. The interrupted rewrite never became visible in the first place, so recovery is retry plus safe cleanup rather than undoing partially published table state.
Object storage holds the table's data files. The Iceberg catalog and metadata track snapshots, manifests, and table state. Writer, reader, and maintenance Spark resources are separate compute boundaries that operate against that shared table state.
This means a failed Spark task does not itself redefine the logical table. The critical correctness state is the committed Iceberg metadata and the data files referenced by those commits. Producer teams provide new records and corrections. Consumer teams read committed table states. The platform owns compute separation, table maintenance, retention policy, metadata lifecycle, and recovery procedures.
At this scale, raw row count is only one part of the problem. Likely operational pressure can come from merge amplification, small-file accumulation, manifest growth, snapshot accumulation, commit conflicts, maintenance backlog, or high reader concurrency.
The platform should therefore observe hourly job completion, whether a metadata commit succeeded, file counts and file sizes, snapshot growth, maintenance progress, query pressure, and saturation in each compute pool. A failed compute task and a failed metadata commit are different states and should be diagnosed separately.
The design deliberately pays for independent writer, reader, and maintenance compute because isolation protects hourly publication and interactive reads from heavy background rewrites. The cost is more compute administration, while the benefit is a smaller noisy-neighbor blast radius.
- Use Apache Iceberg format v2 as the logical table and atomic publication boundary.
- Store production data files in object storage and keep table metadata, snapshots, and manifests in the Iceberg metadata and catalog layer.
- Partition by day(event_ts) and use the selected sort order (entity_id, event_ts).
- Route each hourly load, including late corrections, to isolated Spark writer compute.
- Load and validate the batch, then apply MERGE INTO as an upsert.
- Use optimistic concurrency; detect commit conflicts and retry against valid current table state instead of exposing overlapping partial updates.
- Publish a successful hourly change only through the atomic metadata commit that creates a new snapshot.
- Run consumers on isolated Spark or SQL reader compute and have each query read a stable snapshot.
- Run rewrite_data_files, optional rewrite_manifests, expire_snapshots, and remove_orphan_files on isolated Spark maintenance compute.
- Retain snapshots by age and minimum count, and clean orphan files only after a conservative safe-age threshold.
- If a rewrite stops before commit, leave the previous snapshot current, retry the rewrite, and later delete unreferenced files through orphan cleanup.
Ten billion rows make unnecessary scans, rewrites, and metadata work expensive. Day-level partitioning limits how much data many time-based operations must consider without creating an hourly partition for every load. Sorting by entity_id and event_ts improves locality for the selected correction pattern. MERGE INTO can still rewrite affected files, so repeated hourly updates may create fragmentation and compaction work. Reader planning cost can grow when there are too many files or metadata objects. Snapshot retention also has a storage cost because data referenced by retained snapshots must remain available. Separate writer, reader, and maintenance compute costs more than one shared pool, but it prevents maintenance or query spikes from consuming the resources needed for hourly commits. The platform should watch file growth, metadata growth, commit conflicts, maintenance backlog, query concurrency, and compute saturation instead of treating row count as the only scaling limit.
This tests whether a candidate can operate a very large mutable lakehouse table instead of only loading data into it. The interviewer is looking for sound judgment about table layout, atomic commits, concurrent reads, late corrections, compaction, metadata lifecycle, failure recovery, and workload isolation.
Common mistakes include partitioning too finely, treating partitioning and sorting as the same decision, performing row-by-row mutations instead of a table-format merge, assuming newly written files are visible before the metadata commit, and pausing readers during every hourly update. Another mistake is sharing one compute pool for readers, writers, and compaction, which allows maintenance or query spikes to starve hourly processing. Candidates also often describe an interrupted rewrite as table corruption even when no new metadata was committed. In this design, the old snapshot remains current and the extra unreferenced files can be cleaned later. Finally, snapshot expiration and orphan cleanup should not be made aggressively destructive because retained snapshot references and recently written files need a safe lifecycle boundary.
Center the explanation on the Iceberg commit boundary. State that Spark may create files during a merge or rewrite, but the logical table changes only when new metadata commits a snapshot. Then connect that rule to stable readers, late corrections, failed-rewrite recovery, retention, compaction, and isolated compute pools.




