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.”
The prompt is vague on purpose. The first minutes are for you. Reduce the prompt to the core features and agree on the numbers before you draw.
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
The interviewer has the answers. In a SysRound round, Alex gives these answers, as a real interviewer does.
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 ask about
- 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 (L5/E5)
- 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 (L6+/E6+)
- Proves that file removal cannot race with snapshot selection or publication. Defines authority during failures, bounds metadata work, and explains regional ownership trade-offs.