Delta Lake Core Concepts

Transaction log, ACID, MERGE internals, OPTIMIZE, VACUUM, Z-ordering, Change Data Feed and a CDC failure-scenario walkthrough.

Same approach as Part 1: question, model answer, follow-up. This is the part where interviewers probe depth — "explain MERGE internals" and the CDC failure-scenario walkthrough are where senior candidates separate from mid-level ones, so those two sections are the ones to over-prepare.

1. The Delta Transaction Log

Q: What is the Delta transaction log and how does it work?

A: Every Delta table is a directory of Parquet data files plus a _delta_log folder containing an ordered sequence of JSON commit files (000...000.json, 000...001.json, ...), each describing the actions that happened in that commit — add (file added), remove (file logically removed, not physically deleted), metadata changes, protocol changes. Every N commits (default 10), Databricks writes a checkpoint — a Parquet snapshot of the log state — so readers don't have to replay every JSON file from the beginning to reconstruct current table state. The current state of the table at any version is just "replay the log up to that version." This log is what gives Delta ACID guarantees on top of plain object storage that has no native transactions.

Q: How does Delta achieve atomicity using only object storage (ADLS/S3), which has no native multi-file transactions?

A: Optimistic concurrency control at the commit-file level. A writer stages new data files, then atomically creates the next sequential JSON commit file. On ADLS/blob storage this relies on a put-if-absent / conditional write (Databricks handles this — historically via a coordination service on S3 without native conditional writes, natively via atomic PUT on ADLS Gen2). If two writers race for the same commit version, one wins and the other detects the conflict and retries against the new log tip. The data files themselves are written once, immutably — the log is the only thing that needs an atomic append.

2. ACID Guarantees

Q: What do ACID guarantees actually mean in the context of Delta Lake, concretely?

A:

• Atomicity: a write either fully commits (new log entry) or doesn't appear at all — no partial/half-written table state visible to readers.
• Consistency: schema and constraints (if defined) are enforced on write, so the table never lands in a state that violates them.
• Isolation: concurrent readers always see a consistent snapshot (the log version at the time they started reading), never a half-committed write. Concurrent writers use optimistic concurrency control — conflicting writes fail fast rather than corrupting each other.
• Durability: once a commit JSON is written, the change is permanent (backed by the underlying object store's durability).

Q: What isolation level does Delta actually provide?

A: Snapshot isolation by default — readers see a consistent point-in-time snapshot for the duration of a query. For writers, Delta uses optimistic concurrency control: it doesn't lock, it detects conflicts at commit time by checking whether the files it read/intends to modify were changed by another commit since it started, and retries or fails accordingly.

3. Schema Enforcement

Q: What is schema enforcement and why does it matter?

A: On write, Delta checks the incoming DataFrame's schema against the target table's schema and rejects the write if columns are missing, have mismatched types, or there are unexpected extra columns (unless evolution is explicitly enabled). This is the guardrail that stops a silently-changed upstream source from corrupting a downstream table — e.g. a source system renaming a column or changing a type doesn't get through unnoticed, it fails loudly at write time where you can catch and handle it.

4. Schema Evolution

Q: How does schema evolution differ from schema enforcement, and how do you enable it?

A: Evolution is the controlled opt-in relaxation of enforcement — you explicitly allow certain kinds of schema drift (typically new columns being added) rather than having every write fail on any change. mergeSchema = true on a write, or .option("mergeSchema", "true"), or the table property delta.autoOptimize.autoCompact... more precisely: spark.databricks.delta.schema.autoMerge.enabled at session/table level, and for MERGE specifically spark.databricks.delta.schema.autoMerge.enabled = true or the WITH SCHEMA EVOLUTION clause. I treat this as intentional and narrow — new nullable columns, yes; silently allowing type changes or column drops, no. Type changes and renames should be explicit migrations, not auto-merged.

Q: What's the risk of leaving schema evolution on everywhere?

A: It converts a loud, catchable failure (schema enforcement rejecting the write) into a silent structural change downstream — new columns appear in Silver/Gold without anyone deciding they should be there, dashboards/consumers break because a column they depend on now has nulls from before it existed, or a typo'd column name gets silently added as a new column instead of erroring. I scope it to Bronze ingestion (where upstream drift is expected and should be captured) and keep it off for Silver/Gold, where schema should change through reviewed pipeline code, not silently at runtime.

5. Time Travel

Q: How does time travel work and what's it useful for?

A: Because the transaction log retains history (until VACUUM removes old files), you can query VERSION AS OF n or TIMESTAMP AS OF '...' to read the table as it existed at that commit. Uses: debugging ("what did Gold look like before today's bad run"), reproducing a report, auditing, and — importantly — recovering from a bad write by restoring: RESTORE TABLE t TO VERSION AS OF n. It's also how I diff pipeline runs: read version N and N-1, compare row counts/checksums to validate a transformation didn't unexpectedly change historical rows.

Q: What limits how far back you can time travel?

A: VACUUM — once old data files are physically deleted (default retention 7 days), you lose the ability to reconstruct versions that reference those files, even though the log entries may still exist. Log retention itself is also configurable (delta.logRetentionDuration, default 30 days) and independently prunes old JSON/checkpoint files.

6. MERGE — the one to be extremely fluent on

Q: Explain how MERGE INTO works internally.

A: MERGE joins the target table to a source DataFrame/table on a match condition, then for each row applies whichever clause matches: WHEN MATCHED THEN UPDATE/DELETE, WHEN NOT MATCHED THEN INSERT, WHEN NOT MATCHED BY SOURCE THEN ... (for rows in target with no source match — useful for handling deletes). Internally, Delta identifies which existing data files contain rows that could possibly match the join condition, reads just those files, computes the merged result, and writes new files for the changed data — then atomically swaps them into the table via a new log commit (old files marked remove, new files marked add, nothing physically deleted until VACUUM). It's not row-level mutation in place — Delta is copy-on-write at the file level, so MERGE always rewrites whole files that contain any touched row, even for a single-row update.

Q: What's the performance concern with MERGE at scale, and how do you mitigate it?

A: Because it rewrites entire files for any touched row, a MERGE with matches scattered across many files forces a rewrite of a large fraction of the table. Mitigations: partition the target table so the merge condition can prune to a small set of partitions (WHERE partition_col = ... alongside the join condition so Delta can skip irrelevant partitions entirely), Z-Order the target on the merge key so matching rows cluster into fewer files, and — for CDC-style upserts — merge frequently in smaller batches rather than accumulating a huge backlog that touches the whole table at once.

Q: Write the shape of a typical CDC upsert MERGE.

A:

MERGE INTO silver.customers AS target
USING (
  SELECT * FROM bronze_cdc_batch
  QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY change_timestamp DESC) = 1
) AS source
ON target.customer_id = source.customer_id
WHEN MATCHED AND source.op = 'D' THEN DELETE
WHEN MATCHED AND source.change_timestamp > target.last_updated THEN
  UPDATE SET *
WHEN NOT MATCHED AND source.op != 'D' THEN
  INSERT *

The QUALIFY ROW_NUMBER()... dedup is critical — if the CDC batch contains multiple changes to the same key (common when batching), MERGE will throw an error if the source has duplicate match keys against a single target row (UnsupportedOperationException: multiple source rows matched), so you must pre-reduce to one row per key per batch before merging. The change_timestamp > target.last_updated guard makes the merge safe against out-of-order/replayed events.

Q: What error does Delta throw if the source side of a MERGE has duplicate keys, and why?

A: MERGE requires that at most one source row match each target row per statement, because it can't deterministically decide which of several matching source rows should "win" for an UPDATE. It throws an error rather than silently picking one — this is a safety guarantee, and the fix is always to dedupe the source (keep-latest-by-timestamp, as above) before the merge runs.

7. DELETE and UPDATE

Q: How do DELETE/UPDATE differ from MERGE, and when do you use them directly?

A: DELETE FROM t WHERE ... and UPDATE t SET ... WHERE ... are simpler single-table operations — same copy-on-write mechanics as MERGE (rewrite files containing matched rows), but no join against a source. I use them for housekeeping (purge records older than retention policy, GDPR right-to-be-forgotten deletes) or corrective one-off fixes, not for ongoing upsert logic — that's what MERGE is for.

Q: How would you implement a GDPR "delete this user's data" request across a medallion architecture?

A: DELETE scoped by user ID in every layer that stores PII (Bronze, Silver, Gold, and any exports/serving layer), then VACUUM those tables so the physical data files are actually removed and not just logically un-referenced — logical delete alone leaves the data recoverable via time travel until VACUUM runs, which matters for a compliance requirement.

8. OPTIMIZE

Q: What does OPTIMIZE do and why does Delta need it?

A: Streaming/incremental writes tend to produce many small files (the "small file problem") — bad for read performance because of per-file open/scan overhead. OPTIMIZE table compacts small files into larger ones (target \~1GB by default) via bin-packing, without changing the logical data. I run it on a schedule (or via optimizeWrite/auto-compaction on write for streaming targets) as a separate maintenance task on tables that get frequent small commits — typically Bronze/Silver ingestion targets, less critical on Gold if it's written in fewer, larger batches already.

Q: OPTIMIZE vs auto-compaction — when do you use which?

A: Auto-compaction (delta.autoOptimize.autoCompact) runs a lightweight compaction inline after a write if files are small, which reduces small-file buildup with no separate job, but is less thorough than a full scheduled OPTIMIZE (which also does Z-Ordering if you ask it to). I use auto-compaction as a baseline safety net and still schedule periodic full OPTIMIZE ... ZORDER BY for tables with heavy point-lookup/filter query patterns.

9. VACUUM

Q: What does VACUUM do, and what's the danger of running it with too short a retention?

A: VACUUM table [RETAIN n HOURS] physically deletes data files that are no longer referenced by the current table version and are older than the retention threshold (default 7 days / 168 hours). The danger: if retention is set too low (or VACUUM ... RETAIN 0 HOURS is forced), you can delete files that a concurrently running long read/streaming query still needs (it opened the table at an older snapshot and hasn't finished reading those files yet) — causing that query to fail with a file-not-found error, and you also lose time-travel capability beyond that point. I never lower retention below what the longest-running concurrent reader/streaming checkpoint interval needs, and treat sub-7-day retention as something to do deliberately and rarely, not as routine housekeeping.

10. Partitioning

Q: How do you choose a partition column, and what are the failure modes of getting it wrong?

A: Partition on a column with (a) low-to-moderate cardinality, (b) that's commonly filtered on by downstream queries, and (c) that produces reasonably sized partitions (not too many tiny partitions, not a few huge ones) — date/business_date is the classic choice for time-series/medallion pipelines. Over-partitioning (e.g. partitioning by a high-cardinality ID) creates the small-file problem across thousands of partition directories and kills metadata/listing performance. Under-partitioning (no partitioning on a huge table with an obvious date-filter access pattern) means every query does a full table scan. I generally prefer date partitioning plus Z-Ordering on the actual query/merge key, rather than trying to solve everything through partition design alone.

11. Z-Ordering / Data Skipping

Q: What is Z-Ordering and how does it relate to data skipping?

A: Delta automatically collects per-file min/max statistics on (by default) the first 32 columns, and query/merge planning uses those stats to skip files that can't possibly contain matching rows (data skipping) — this works well when a file's value range for the filtered column is narrow. OPTIMIZE ... ZORDER BY (col) physically co-locates rows with similar values in that column into the same files during compaction, which makes those min/max ranges tight and non-overlapping across files, so data skipping becomes much more effective for filters/joins on that column. It's most valuable on high-cardinality columns used in point lookups or as the MERGE join key — exactly the columns partitioning is a bad fit for.

Q: Partitioning vs Z-Ordering — how do they compose?

A: Not either/or — partition on a coarse, commonly-filtered, moderate-cardinality column (date), then Z-Order within partitions on the fine-grained, high-cardinality column you filter/join on (customer_id, order_id). Partitioning prunes at the directory level, Z-Ordering improves file-level skipping within what's left.

12. Change Data Feed (CDF)

Q: What is Change Data Feed and how does it differ from just diffing two table versions yourself?

A: TBLPROPERTIES (delta.enableChangeDataFeed = true) makes Delta record, per commit, the actual row-level changes (_change_type: insert/update_preimage/update_postimage/delete, plus _commit_version and _commit_timestamp) as a queryable feed (table_changes() / readStream ... option("readChangeFeed", "true")), instead of you having to snapshot-diff two full versions of the table yourself. This is the mechanism I'd use to propagate changes out of a Delta table downstream — e.g. Silver table changes feeding a Gold aggregation incrementally (only reprocess rows that actually changed) rather than a full recompute, or feeding an external system/event bus.

Q: When would you use CDF vs plain incremental MERGE-based propagation?

A: If downstream needs to know not just the current state but what changed and how (for auditing, for incremental aggregation that needs to know a row moved from group A to group B and adjust both), CDF gives you that. If downstream just needs current state and a simple upsert suffices, you don't need CDF — plain MERGE keyed on the latest state is simpler and sufficient. CDF adds a bit of storage overhead (extra change files) so I enable it selectively on tables that actually feed incremental downstream consumers.

13. Concurrency

Q: What happens when two writers try to modify the same Delta table at the same time?

A: Optimistic concurrency control. Both writers read the current log version, do their work, and try to commit the next sequential version. Whichever commits first wins. The second writer's commit attempt detects the conflict (the log has moved since it started) and Delta checks whether the conflict is resolvable — if the two writes touched non-overlapping files/partitions and the operation types are compatible, Delta can often silently retry the second commit against the new log tip. If they genuinely conflict (both wrote to overlapping files, e.g. two concurrent MERGEs touching the same partition), the second write fails with a ConcurrentModificationException-style error (e.g. ConcurrentAppendException, ConcurrentDeleteReadException) and the caller must retry.

Q: How do you design around this rather than just hoping conflicts don't happen?

A: max_concurrent_runs: 1 on jobs that write the same table (from Part 1), partition-scoped writes so two legitimately-concurrent jobs (e.g. backfills for different dates) touch disjoint partitions and never conflict, and application-level retry logic around the write call for the cases where a transient conflict is expected and safe to retry (idempotent writes make blind retry safe).

14. CDC Implementation — the deep-dive section

Source (OLTP DB)
      ↓
CDC (Debezium / native DB CDC / Auto Loader on a change-log export)
      ↓
Bronze  (raw change events, append-only, as-is)
      ↓
MERGE   (dedupe + upsert into current-state table)
      ↓
Silver  (current state, one row per business key)

Q: Walk me through how you'd implement this end-to-end.

A:

1. Source → CDC: the source database emits a change stream (native CDC log, or a tool like Debezium/Fivetran/ADF's own CDC connectors) — each event carries the row's new values, an operation type (insert/update/delete), and a change timestamp/LSN/sequence number.
2. CDC → Bronze: land these events as-is, append-only, via Auto Loader (or an ADF copy activity) into a Bronze Delta table — no dedup, no upsert logic here. Bronze is the immutable, replayable record of every change event ever received, partitioned by ingestion date. This is deliberate: if Silver logic has a bug, I can rebuild Silver from Bronze without re-pulling from the source.
3. Bronze → MERGE → Silver: a scheduled/triggered task reads the new Bronze batch, deduplicates to one row per business key (keep the row with the max change timestamp/sequence per key — critical, covered in the MERGE section above), then MERGEs into Silver: UPDATE on matched + newer timestamp, INSERT on not-matched, DELETE on matched + op='D'. Silver ends up as current-state, one row per key.

Q: What happens if the job fails halfway through?

A: Delta's atomicity means "halfway through" isn't a real state from the table's perspective — the MERGE either commits as one atomic log entry or it doesn't commit at all. If the job process dies mid-MERGE (before the commit), the target table is untouched; the next run reprocesses the same Bronze batch and produces the same result (idempotent, per the MERGE dedup + timestamp-guard logic). Bronze ingestion itself, if using Auto Loader with checkpointing, resumes from the last committed checkpoint rather than reprocessing everything or losing track of what was ingested. So the recovery story is: nothing to manually clean up, just re-run — that's the whole point of designing it idempotently.

Q: What happens if the same batch arrives twice (duplicate delivery)?

A: At Bronze, both copies land — Bronze is append-only and doesn't dedupe (that's fine, it's the raw log). At the MERGE step, the dedup logic (ROW_NUMBER() ... QUALIFY = 1 keyed on business key + latest timestamp) collapses duplicates within the batch, and the MERGE's UPDATE SET * WHERE source.change_timestamp > target.last_updated guard means re-merging an already-applied change is a no-op — the target row's timestamp is already ≥ the duplicate's, so the WHEN MATCHED condition doesn't fire (or fires and writes the identical values, harmless either way). End state is identical whether the batch arrived once or twice.

Q: What happens if source records are updated (not just inserted)?

A: That's the normal case MERGE is built for — the CDC event has op = 'U' with the new values and a newer change_timestamp; the MERGE WHEN MATCHED AND source.change_timestamp > target.last_updated THEN UPDATE SET * clause replaces the Silver row's values. If I also need history (not just current state), I'd add an SCD Type 2 pattern instead of a plain overwrite-MERGE: close out the previous row (end_date = source.change_timestamp, is_current = false) and insert a new current row — same MERGE mechanics, just a richer set of WHEN clauses.

Q: What happens if the schema changes mid-stream (source adds/renames/drops a column)?

A: At Bronze, if I have mergeSchema = true enabled there, a new column from the source is captured automatically and appears going forward (old rows show null for it) — this is the layer where I want evolution enabled since Bronze's job is to faithfully capture whatever the source sends. At the Bronze → Silver MERGE step, schema evolution is not blanket-enabled — a new column appearing in the MERGE source without a corresponding deliberate change to the Silver MERGE statement/target schema would either fail (enforcement) or need explicit WITH SCHEMA EVOLUTION — and I want that to be a conscious decision (does this new column need to propagate to Silver? does Gold/consumers need to know?) rather than something that silently changes a curated table's contract. A column rename at source is the dangerous case — schema evolution can't distinguish "renamed column" from "old column dropped + new column added," so that needs explicit handling (a mapping/alias step) rather than relying on auto-merge.

Q: What happens if two jobs write to the same Silver table simultaneously — e.g. a normal incremental run and a manual backfill both firing at once?

A: Delta's optimistic concurrency control kicks in (see section 13). If they touch disjoint partitions/files (e.g. incremental run processing today's changes, backfill reprocessing a historical date range), Delta can often resolve this automatically and both commits succeed. If they genuinely overlap (both trying to MERGE changes touching the same business keys/partition), the second commit fails with a concurrent-modification exception and must retry — which is safe here specifically because the MERGE is idempotent, so a retry against the now-current log tip produces the correct result. At the orchestration layer I still prefer to prevent this proactively rather than rely purely on Delta catching it: max_concurrent_runs: 1 on the Silver-writing job, and backfills either run through the same job (so they share the concurrency limit) or are explicitly scoped to non-overlapping partitions before I let them run in parallel.

Closing line for this topic: "Every one of those failure scenarios reduces to the same two properties: Delta's atomic commit means there's no such thing as a half-applied write to clean up, and the MERGE being keyed on business key + change timestamp means re-applying an event — whether from a retry, a duplicate delivery, or a concurrent job — converges to the same correct state instead of needing separate handling per scenario."

Quick-fire recap (for a last pass before the interview)