Databricks System Design Interview: Building a Lakehouse with ACID on Object Storage
Databricks says the Delta Lake format underneath its platform processes exabytes of data per day across its customer base, and its published research reports that most of its largest customers run on it. The engineering trick is doing this on top of commodity cloud object storage like S3, which was never designed to give you transactions. Photon, the C++ query engine, has held the TPC-DS world record and delivers average speedups near 3x over the older runtime. Treat exact per-workload numbers as estimates unless a paper states them.
Databricks is a lakehouse: one system that stores data in cheap open-format files on object storage, then layers warehouse guarantees on top so you get ACID transactions, schema, and fast SQL without copying data into a separate warehouse. The center of the design is Delta Lake, which turns a folder of Parquet files into a real table by keeping an ordered transaction log next to the data. Every write appends a JSON commit that lists which files were added and removed, so a reader reconstructs an exact table snapshot by replaying the log. Concurrency is handled optimistically: writers assume conflicts are rare, do their work, and only check for a collision at commit time. Storage and compute are fully separated, so elastic Spark clusters spin up against the same files and scale independently. Photon accelerates queries with vectorized C++ execution, data skipping prunes files using per-file statistics, and OPTIMIZE compacts small files. Unity Catalog sits above all of it for governance, permissions, and lineage. The medallion pattern, bronze to silver to gold, organizes raw ingestion through cleaned and aggregated tables.
Asked at: Asked at Databricks, Snowflake, Amazon, Microsoft, and data-platform teams at companies like Netflix, Uber, and Airbnb that run large lakehouse or data-lake stacks. It shows up whenever a role touches analytics infrastructure, data engineering, or query engines.
Why this question is asked
This problem tests whether a candidate understands that a table is not a file format but a protocol. It forces you to reason about how to get ACID guarantees on a storage layer that only promises put-if-absent and eventual consistency, which is a genuine distributed-systems problem rather than a checklist of AWS services. Interviewers use it to probe the separation of storage and compute, optimistic concurrency, the small-files problem, columnar layout and data skipping, and batch plus streaming unification. It also rewards candidates who can weigh the lakehouse against the older two-tier lake-plus-warehouse design and explain the concrete costs of each.
Requirements
Always clarify these in the first 5 minutes of the interview. Do not start drawing boxes until both lists are agreed.
Functional requirements
- Store tabular data in open columnar files on cloud object storage and expose them as queryable tables
- Provide ACID transactions so concurrent readers and writers never see partial or corrupt state
- Support MERGE, UPDATE, DELETE, and UPSERT on data lake files, not just append
- Offer time travel so any past version of a table can be read by version number or timestamp
- Enforce schema on write and allow controlled schema evolution over time
- Run both batch jobs and structured streaming against the same tables with the same code
- Compact small files and lay out data for fast selective queries via OPTIMIZE and clustering
- Govern access, audit usage, and track lineage across all tables through a central catalog
- Scale compute elastically and independently of stored data volume
Non-functional requirements
- Serializable or snapshot isolation for writes despite weak object-store guarantees
- Interactive query latency for BI on tables holding billions of rows
- High write throughput for streaming ingestion measured in millions of events per second
- Durability inherited from object storage with eleven nines as the practical baseline
- Cost efficiency by using commodity object storage rather than proprietary warehouse storage
- Open formats so data is not locked into one vendor and can be read by many engines
- Metadata operations that stay fast even when a table has millions of files
Back-of-envelope scale estimates
Show your math. Pulling numbers from thin air signals you have not thought about the load.
Platform data processed
Exabytes per day (published, aggregate)
The Delta Lake VLDB paper states the format processes exabytes of data per day across Databricks customers. This is an aggregate platform figure, not a single table.
Files per large table
1M to 100M+ files (estimate)
A petabyte table split into 128 MB to 1 GB Parquet files yields roughly 1M to 8M files at 1 GB each, more at smaller sizes. Derived, and the reason listing every file per query is a non-starter.
Transaction log commit size
One JSON file per commit, KBs to MBs (estimate)
Each commit records added and removed file paths plus per-file stats. A commit touching thousands of files is a few MB of JSON. Derived from the log format.
Checkpoint interval
Every 10 commits by default
Delta writes a Parquet checkpoint every 10 commits so readers replay at most a handful of JSON files after the last checkpoint rather than the whole history.
Photon speedup
About 3x average, over 10x max (published)
The Photon SIGMOD 2022 paper and Databricks report average speedups near 3x versus the prior runtime and maximums above 10x on suitable workloads.
Target file size after OPTIMIZE
Roughly 128 MB to 1 GB (estimate)
Compaction targets file sizes large enough to amortize open and read overhead while staying small enough to prune and parallelize. Common guidance, exact target is tunable.
High-level architecture
A write starts in an engine, usually Spark or a SQL warehouse. The engine reads the table's transaction log in the _delta_log directory to learn the current version and the exact set of Parquet files that make up the table. It writes new Parquet data files into the table's storage path, then attempts to commit by writing the next numbered JSON log file, for example 00000000000000000042.json, using a put-if-absent operation. If that log file already exists, another writer won the race, so this writer re-reads the log, checks whether the two changes actually conflict, and retries. Readers do the reverse: they load the latest Parquet checkpoint, replay any JSON commits after it, and get a precise snapshot of which files to scan, which gives them a consistent view without locking out writers. On the read path the query planner uses per-file min and max statistics stored in the log to skip files that cannot match the query predicate, so a filtered query touches a fraction of the data. Compute is stateless and elastic: clusters and serverless SQL warehouses spin up against the same files, and Photon executes the plan with vectorized C++ operators. Above all of this, Unity Catalog resolves table names to storage locations, enforces permissions, and records lineage. Background OPTIMIZE jobs rewrite many small files into fewer large ones and can cluster data by frequently filtered columns.
In a real interview, sketch this on the whiteboard before diving into any single box.
Core components
Walk through each service. The interviewer wants to hear what each one owns, not just the names.
Delta Lake transaction log
An ordered set of JSON commit files plus periodic Parquet checkpoints stored in the _delta_log directory beside the data. Each commit is an atomic list of add and remove file actions with per-file statistics, and replaying the log yields the exact table state. This log is what turns a folder of files into an ACID table.
Cloud object storage
S3, ADLS, or GCS holds both the Parquet data files and the log. It gives cheap, durable, effectively infinite capacity but only weak guarantees, so Delta builds transactions using put-if-absent on the next log file name rather than trusting the store to coordinate anything.
Apache Spark and the Databricks Runtime
Spark provides the distributed execution model: a DAG scheduler breaks a job into stages separated by shuffles, and stages into tasks that run in parallel across executors with in-memory processing. The Databricks Runtime is a hardened Spark distribution with performance and reliability patches on top.
Photon
A vectorized query engine written in C++ that plugs into Spark's memory manager and runs SQL and DataFrame operators on batches of columns rather than row-at-a-time. It targets raw uncurated data and delivers roughly 3x average speedups, and helped Databricks set a TPC-DS record.
Structured Streaming
Spark's streaming model treats a stream as an unbounded table processed in micro-batches, and Delta tables serve as both streaming sources and sinks. This is what lets the same table and often the same code handle batch and streaming, with exactly-once delivery backed by the log.
Unity Catalog
The governance and metadata layer. It holds the metastore that maps three-level names, catalog.schema.table, to physical storage locations, and centralizes access control, auditing, and column and table level lineage across workspaces so permissions are not scattered per cluster.
OPTIMIZE and clustering
Maintenance operations that fight the small-files problem. OPTIMIZE compacts many small files into fewer large ones, and Z-ordering or liquid clustering physically co-locates rows that share filter values so data skipping prunes more aggressively.
Data model
Pick the right store per table. Justify each choice with the access pattern, not by reflex.
delta_log_commit (NNN.json)versiontimestampadd_file_actionsremove_file_actionsmetadatacommit_infoOne atomic commit. add and remove actions list file paths, sizes, partition values, and column stats. Numbered sequentially so version order is the serialization order.
delta_checkpoint (NNN.checkpoint.parquet)versionsurviving_add_actionsprotocolmetadatacolumn_statsA Parquet snapshot of the log state at a version, written every 10 commits so readers avoid replaying the full JSON history.
parquet_data_filefile_pathrow_group_statscolumn_mincolumn_maxnull_countnum_rowsColumnar data. Row-group and per-file min and max stats drive data skipping. Files are immutable, so an update writes a new file and removes the old one in the log.
table_metadatatable_idschema_jsonpartition_columnsclustering_columnstable_propertiesCurrent schema and layout, tracked in the log. Schema enforcement rejects non-conforming writes, and schema evolution updates this record in a new commit.
unity_catalog_objectcatalogschematable_namestorage_locationownerpermissionsThe metastore entry that resolves a logical name to a storage path and holds grants. Separate from the Delta log so governance is centralized and engine independent.
lineage_edgesource_tabletarget_tablejob_idcolumn_mappingrun_timestampRecorded by Unity Catalog as jobs run, capturing table and column level flow used for impact analysis and audit.
Deep dives
These are the conversations the interviewer is steering you toward. Practice each one until you can talk through it without notes.
ACID on object storage via the transaction log
Object stores give you durable bytes and a put-if-absent primitive, but no locks and no multi-key transactions, so Delta builds atomicity itself. The unit of truth is the ordered log of numbered JSON commits. To make a change, a writer computes the new set of Parquet files, then tries to write the next log file, say version 42, only if version 42 does not already exist. That single conditional put is the atomic commit point: either it succeeds and the change is instantly visible to every reader that replays the log, or it fails because someone else grabbed version 42 first. Readers never see half a write because uncommitted data files are just orphan objects that no log entry references. Durability and isolation both fall out of this: the data lives in the durable store, and isolation comes from the fact that a snapshot is defined entirely by the ordered log up to a version.
Optimistic concurrency and conflict detection
Delta assumes write conflicts are rare, which is usually true for append-heavy analytics, so it avoids pessimistic locking that would serialize everything. A writer records the version it read, does all its work, and at commit time tries to claim the next version number. If the put-if-absent fails because another commit landed first, Delta does not blindly abort. It reads the commits that appeared since its start version and checks whether they actually conflict. Two appends to different files can both succeed after a simple retry, since neither touched the other's data. A conflict is real when, for example, one transaction deletes files that another concurrent transaction also read or rewrote, which would break snapshot isolation. Real conflicts abort and the caller retries. This gives snapshot isolation by default and serializable behavior for the operations that need it, without a central lock manager.
The lakehouse versus the two-tier lake plus warehouse
The older pattern kept a data lake of raw files for cheap storage and data science, then ran ETL to copy a curated subset into a separate proprietary warehouse for fast SQL and BI. That copy is the problem. You pay for storage twice, you add hours of staleness between the lake and the warehouse, you maintain two sets of pipelines that drift, and BI tools see a different version of the truth than the data scientists. The lakehouse removes the second tier: warehouse-grade features, transactions, schema, indexing, and a fast engine, are applied directly to open files in the lake. One copy of the data serves SQL, streaming, and machine learning at once. The cost is that you now have to engineer reliability and performance on top of object storage yourself, which is exactly what Delta Lake, Photon, and Unity Catalog provide.
File layout, data skipping, and the small-files problem
Parquet stores data column by column, so a query that reads three of fifty columns only pays for three, and columnar data compresses well because similar values sit together. Delta records min and max values per file and per row group in the log, so the planner skips any file whose range cannot satisfy the predicate. That is the difference between scanning a petabyte and scanning a few gigabytes. Two things wreck this. First, too many tiny files, which streaming ingestion creates naturally, blow up metadata and per-file open cost, so OPTIMIZE rewrites them into files in the hundreds of megabytes. Second, if the filter column is scattered across all files, skipping helps nothing, so Z-ordering or liquid clustering physically co-locates rows with similar values in the filtered dimensions. Good layout and honest statistics are what make selective queries fast, and both are maintained as background work rather than blocking writers.
Spark execution: DAG, stages, tasks, and shuffle
A Spark job is a directed acyclic graph of transformations. The scheduler cuts the graph at points where data must be redistributed across the cluster, and each cut defines a stage boundary. Narrow transformations like filter and map stay within a partition and pipeline cheaply. Wide transformations like joins and group-bys need a shuffle, where records are repartitioned by key across the network, and the shuffle is usually the most expensive part of a job because it writes intermediate data and moves it between executors. Each stage is split into tasks, one per data partition, run in parallel across executor slots, and Spark keeps hot data in memory to avoid re-reading from storage. Understanding this model is what lets you explain why skewed keys, too many small partitions, or an accidental shuffle can dominate runtime, and why Photon's vectorized operators speed up the compute-bound parts within each task.
Batch and streaming unification and the medallion architecture
Structured Streaming treats a stream as an unbounded table processed in small micro-batches, and because a Delta table is both a valid streaming source and sink, the same table can be written by a stream and read by a batch report a second later. Exactly-once semantics come from the log: each micro-batch commits atomically with the offsets it consumed, so a retry after failure does not double-write. Teams organize this with the medallion pattern. Bronze tables hold raw ingested data close to the source with minimal processing. Silver tables hold cleaned, validated, joined data. Gold tables hold business-level aggregates that feed dashboards and models. Each hop is a Delta table, so every layer is transactional, time-travelable, and can be rebuilt by replaying from the layer before it. This is ELT rather than ETL, since raw data lands first and transformation happens inside the lakehouse.
Photon and the separation of storage and compute
Because the data lives in object storage and the table state lives in the log, compute holds no durable state of its own. That decoupling is what makes clusters elastic: you can run a large cluster for a nightly job, a small serverless warehouse for a dashboard, and several independent clusters against the same tables at once, and none of them owns the data. Photon is the engine that makes that compute fast. It is written in C++, not the JVM, and it processes batches of column values at a time so it keeps the CPU pipeline and SIMD units busy instead of interpreting one row at a time. It plugs into Spark's memory manager so it shares the same execution and can fall back to Spark for operators it does not yet support. It is built for the messy reality of lake data, variable string lengths, missing statistics, and wide schemas, which is why the paper reports large speedups on real customer workloads rather than only on clean benchmarks.
Trade-offs to discuss
Every senior interviewer expects you to surface at least 3 of these. Pick the decisions, state the alternatives, and justify your choice.
Lakehouse versus a separate data warehouse
One open copy of the data removes duplication, staleness, and lock-in and serves BI, streaming, and ML together. The price is engineering transactions and performance on top of object storage instead of buying a turnkey warehouse, and mature warehouses can still edge out on some highly tuned SQL.
Optimistic concurrency versus pessimistic locking
Optimistic control fits append-heavy analytics where conflicts are rare and avoids a central lock manager, so throughput stays high. Under many writers touching the same files it degrades into retries, so heavy update workloads need partitioning or write serialization to keep conflict rates low.
Object storage versus attached block storage
Object storage is cheap, durable, and effectively unlimited, which is why the whole design targets it. The cost is higher per-request latency and weak consistency, which forces the log-based commit protocol and heavy use of data skipping to avoid listing and reading too much.
Fewer large files versus many small files
Large files amortize open cost, shrink metadata, and speed scans, so OPTIMIZE targets them. But large files hurt streaming freshness and can reduce skipping granularity, so there is a tuning point between ingestion latency and query speed.
Micro-batch streaming versus true per-record streaming
Micro-batches give exactly-once semantics, simple recovery, and reuse of the batch engine and Delta commits, which is why the platform leans on them. The cost is added end-to-end latency compared with record-at-a-time engines, which matters for the lowest-latency use cases.
Central Unity Catalog governance versus per-cluster configuration
A central metastore gives consistent permissions, auditing, and lineage across every workspace and engine. It adds a dependency and a single control plane to secure and operate, but scattering access control per cluster is far harder to reason about and audit.
How Databricks actually does it
The design here follows Databricks' own published research rather than guesswork. The Delta Lake VLDB 2020 paper describes the transaction log, the put-if-absent commit protocol on object stores, optimistic concurrency, data skipping, Z-ordering, and time travel, and states the format processes exabytes per day across customers. The Lakehouse CIDR 2021 paper by Zaharia, Ghodsi, Xin, and Armbrust lays out the argument that open direct-access formats plus a metadata and transaction layer can replace the two-tier lake-plus-warehouse stack. Photon is documented in the SIGMOD 2022 best-industrial-paper, which reports the vectorized C++ engine, its integration with Spark's memory manager, and the roughly 3x average speedups. Delta Lake itself is open source under the Linux Foundation, and Unity Catalog provides the governance, metastore, and lineage layer. Where a specific throughput or file-count number is not in a paper, treat it as an estimate.
Sources
- Delta Lake: High-Performance ACID Table Storage over Cloud Object Stores (VLDB 2020)
- Lakehouse: A New Generation of Open Platforms (CIDR 2021)
- Photon: A Fast Query Engine for Lakehouse Systems (SIGMOD 2022)
- Understanding the Delta Lake Transaction Log (Databricks Engineering Blog)
- Delta Lake documentation and resources
Lessons to study before this interview
If any of these topics are fuzzy, the interviewer will catch it. Each lesson is 15 to 60 minutes with diagrams, code, and a quiz.
Related system design interview questions
Practice these next. They lean on the same core building blocks as Databricks.