# Design a data lakehouse: system design interview question

> Atomic table changes over files: concurrent writers, schema changes, consistent reads, and safe file removal.

Source: https://sysround.com/questions/design-data-lakehouse
Updated: 2026-11-05


Difficulty: hard. Levels: Senior, Staff+.

## How the interviewer opens it

"Design a transactional table layer over files in object storage. It must support concurrent writers, schema changes, and consistent snapshots for analytical queries."

## What to clarify first

- Table scale
- Data volume and file size
- Read and write traffic
- Write operations
- Schema changes
- Consistency, availability, and durability
- Latency
- Object storage
- History and operation duration
- Existing systems and exclusions

## Scope of a strong answer

Core requirements:

- Commit several files as one atomic table change, with concurrent writers.
- Support append operations and replacement of selected partitions.
- Give each query one consistent table snapshot.
- Add nullable columns without breaking compatible readers and writers.

Usually out of scope:

- Build a SQL query engine.
- Support transactions across tables.
- Support row updates, row deletes, or primary key constraints.
- Rename columns, remove columns, or change column types.
- Build the object storage service.

## What interviewers probe

### When a table change becomes visible

Object storage changes one object at a time. A table commit must expose all files together and survive uncertain responses.

### Concurrent data and schema changes

Some concurrent changes can merge safely. Partition replacements and incompatible schemas can silently remove or misread data.

### Queries over large tables

A large table can contain hundreds of thousands of files. Each query needs one stable state without excessive metadata work.

### File removal

An unreferenced file can belong to a failed writer, a valid writer, or an older snapshot that a reader still needs.

## Common mistakes

- The design uses an object listing as the authoritative table state.
- The design assumes object storage provides an atomic change across several files.
- Readers use the latest schema with historical snapshots.
- Writers retry stale partition replacements without checking concurrent changes.
- Each commit copies or scans the full file list.
- The service removes files without atomic reader protection or checks that reject expired writers.

## The bar by level

- **Senior:** Defines append and replacement conflicts, safe retries, and compatible schema changes. Controls metadata growth and gives an explicit protocol for reader protection and writer expiry.
- **Staff+:** Proves that file removal cannot race with snapshot selection or publication. Defines authority during failures, bounds metadata work, and explains regional ownership trade-offs.

Practice this question with an AI interviewer: https://sysround.com/practice/new?problem=data-lakehouse


---
SysRound (https://sysround.com) is an AI practice tool for system design interviews. It has a voice or typed AI interviewer and a shared whiteboard. At the end, you get feedback on each skill and a model answer. The AI feedback can be wrong.