Delta Lake Overview and Interview Atlas

Answer First: Delta Lake is an open table format and transaction layer that records table state in a transaction log while storing data in data files such as Parquet.

Memory Map: data files + transaction log -> snapshot -> reliable table operations.

Version and product-name guardrails

• Separate open-source Delta Lake behavior from Databricks-only SQL commands and managed services. Check the Delta Lake release index and the target runtime before making a version claim.
• A checkpoint every 10 commits is a common default, not a protocol guarantee: delta.checkpointInterval is configurable, and connector applications decide when to checkpoint: official Delta Kernel guide.
• Time travel does not preserve files independently of retention. VACUUM can remove files needed by old snapshots or long-running readers; inspect history and active workloads before shortening retention.
• Liquid clustering is incremental because OPTIMIZE rewrites only data needed for clustering; it does not mean “new data only”. It is incompatible with partitioning and ZORDER, and runtime support is versioned: official guide.
• “Statistics on the first 32 columns” is the current default for Unity Catalog external tables, not a universal Delta limit. Managed-table statistics can be selected by predictive optimization, and explicit statistics columns are configurable: official guide.
• Protocol, table-feature, CDF, deletion-vector, UniForm, clustering, and schema-evolution support is version-sensitive. Validate reader and writer compatibility before enabling a feature.
• Performance percentages and file-size targets in the legacy notes are workload hypotheses, not guarantees; verify with table history, operation metrics, query plans, and representative benchmarks.

Canonical Delta Lake module map

Use the chapter navigation in order, or jump from the link-only question index to the exact concept owner.

Delta Lake & Lakehouse Architecture

Memory Map

Answer First: A managed table uses a platform-managed storage location and lifecycle, while an external table registers data at a user-controlled location. Dropping the registration therefore has different data-retention consequences.

Memory Map: the difference between managed and external tables in Databricks -> storage owner defines responsibility -> registration chooses managed or external lifecycle -> DROP and retention behavior follow ownership -> location metadata proves the boundary [03_Delta_Lake_and_Lakehouse.md:596].

Q19: What is the difference between managed and external tables in Databricks?

Answer:

-- Managed (data stored in default warehouse location)
CREATE TABLE managed_orders (id LONG, amount DECIMAL);

-- External (data at your specified location)
CREATE TABLE external_orders (id LONG, amount DECIMAL)
LOCATION 's3://my-bucket/orders/';

SECTION 5: LAKEHOUSE ARCHITECTURE

Answer First: A lakehouse keeps open object storage and adds ACID transaction metadata, governance, and warehouse-style performance. Unlike a raw data lake it provides reliable table semantics, while unlike a traditional warehouse it preserves open files and separates storage from compute.

Memory Map: a data lakehouse? How does it differ from data lake and data warehouse -> data lakehouse it differ from data lake and data warehouse sets the specific transaction and interoperability constraint -> engine ecosystem defines interoperability needs -> mutation model selects transaction semantics -> maintenance ownership sets operating cost -> cross-engine test determines fit [03_Delta_Lake_and_Lakehouse.md:677].

Q22: What is a data lakehouse? How does it differ from data lake and data warehouse?

Simple Explanation:

• Data Lake = Big messy warehouse (cheap rent, hard to find things, no ACID)
• Data Warehouse = Expensive organized office (everything in place, but high rent, vendor lock-in)
• Data Lakehouse = Organized warehouse (cheap rent + everything labeled and easy to find + ACID + open format)

The Lakehouse combines the BEST of both: cheap object storage (like a lake) + ACID transactions and fast BI queries (like a warehouse).

Answer:

Answer First: Delta Lake / Iceberg / Hudi: ACID transactions on data lakes.

Memory Map: What key technologies enable the lakehouse -> open object storage preserves interoperable files -> transaction metadata adds reliable mutation -> scalable compute serves SQL and ML -> centralized governance joins both workloads [03_Delta_Lake_and_Lakehouse.md:700].

Q23: What key technologies enable the lakehouse?

Answer:

1. Delta Lake / Iceberg / Hudi: ACID transactions on data lakes
2. Photon / vectorized engines: Warehouse-level query performance
3. Unity Catalog: Unified governance across all data assets
4. Serverless compute: On-demand, auto-scaling
5. SQL endpoints / SQL Warehouses: Direct BI tool connectivity
6. MLflow: Integrated ML lifecycle management

Answer First: Eliminates the "two-tier" architecture (data lake + data warehouse).

Memory Map: What problems does the lakehouse solve -> single governed copy removes lake-to-warehouse duplication -> reliable tables support updates and concurrency -> independent compute serves varied workloads -> shared lineage reduces operational fragmentation [03_Delta_Lake_and_Lakehouse.md:712].

Q24: What problems does the lakehouse solve?

Answer:

1. Eliminates the "two-tier" architecture (data lake + data warehouse)
   • No more ETL from lake to warehouse
   • Single copy of data serves all workloads
2. Reduces data duplication and ETL complexity
3. Single source of truth for BI and ML
4. Open formats prevent vendor lock-in
5. Cost-effective storage with warehouse-level performance
6. Unified governance across all workloads

Interview Tip: "When asked 'Why Databricks over Snowflake?', mention: open formats (no vendor lock-in), unified BI + ML on one platform, Delta Lake is open source, and Photon gives warehouse-level speed on open data."

What NOT to Say: "Lakehouse means storing everything in one table" — No, it's an ARCHITECTURE pattern (Bronze/Silver/Gold layers) on cheap storage with ACID.

Answer First: DataFrame overwrite replaces table data within the write scope, whereas REPLACE TABLE atomically replaces the table definition and data. The required schema and metadata transition determine which operation is correct.

Memory Map: the difference between DataFrame.write.mode("overwrite") and REPLACE TABLE in Delta -> difference between dataframe write mode overwrite and replace table in delta applies a distinct transaction-log action set -> starting table version defines state -> metadata and file actions apply the operation -> atomic commit publishes a new snapshot -> history and metrics verify the transition [03_Delta_Lake_and_Lakehouse.md:818].

Q28: What is the difference between DataFrame.write.mode("overwrite") and REPLACE TABLE in Delta?

Answer:

# Overwrite specific partitions only
df.write.format("delta") \
    .mode("overwrite") \
    .option("replaceWhere", "date = '2025-01-15'") \
    .saveAsTable("orders")

# This is IDEMPOTENT — safe for retry/re-run

Answer First: WAP ensures data quality before making data visible to consumers.

Memory Map: you implement write-audit-publish (WAP) pattern with Delta Lake -> writer creates an isolated candidate table -> validation checks quality and reconciliation -> atomic promotion changes the consumer pointer -> failed checks leave published state untouched [03_Delta_Lake_and_Lakehouse.md:840].

Q29: How do you implement write-audit-publish (WAP) pattern with Delta Lake?

Answer: WAP ensures data quality before making data visible to consumers.

# Step 1: Write to a staging area (or use table clones)
staging = "staging_orders"
df.write.format("delta").mode("overwrite").saveAsTable(staging)

# Step 2: Audit — run quality checks
quality_check = spark.sql(f"""
    SELECT
        COUNT(*) as total_rows,
        SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) as null_ids,
        SUM(CASE WHEN amount < 0 THEN 1 ELSE 0 END) as negative_amounts
    FROM {staging}
""").collect()[0]

assert quality_check["null_ids"] == 0, "Null order IDs found!"
assert quality_check["negative_amounts"] == 0, "Negative amounts found!"

# Step 3: Publish — atomically swap
spark.sql(f"""
    INSERT OVERWRITE TABLE production_orders
    SELECT * FROM {staging}
""")

Alternative with Delta's RESTORE: If quality check fails after writing to production, RESTORE TABLE production_orders TO VERSION AS OF .

Delta Lake & Lakehouse Deep Dive

Answer First: MERGE must resolve at most one source row for each target row that an update or delete clause changes. Deduplicate the source deterministically by business key and ordering column before the match.

Memory Map: What happens when source has duplicate keys? How to fix -> multiple changes for one target make mutation ambiguous -> deterministic ordering ranks candidate rows -> deduplication retains one winner per key -> uniqueness assertion runs before commit [DB_01_Delta_Lake_Deep_Dive.md:187].

Q6: What happens when source has duplicate keys? How to fix?

Simple Explanation: MERGE requires that each target row matches at most ONE source row. If your source data has duplicate booking_ids (e.g., two records for booking ABC123), MERGE doesn't know which one to use → it throws an error.

The fix: Deduplicate the source data BEFORE merging. Keep only the latest record per key.

-- Problem: bookings_staging has 2 rows for booking_id = 'ABC123'
-- Solution: Use ROW_NUMBER to keep only the latest one

WITH deduped AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY booking_id        -- Group by booking_id
            ORDER BY updated_at DESC       -- Latest record first
        ) AS rn                            -- rn=1 means the latest record
    FROM bookings_staging
)
SELECT * FROM deduped WHERE rn = 1         -- Keep only the latest record per booking

Why duplicates happen in real life:

• Source system sent the same event twice (retry)
• Multiple Kafka partitions delivered the same record
• File was reprocessed accidentally

SECTION 5: LAKEHOUSE ARCHITECTURE (30 min)

Q15: What is a Data Lakehouse? How is it different from Data Lake and Data Warehouse?

Simple Explanation:

Data Lake = Cheap cloud storage (like ADLS Gen2 or S3) where you dump all your raw data in any format (JSON, CSV, Parquet). It's cheap and flexible, but messy — no ACID transactions, no schema enforcement, slow for BI queries.

Data Warehouse = Expensive, structured database (like Azure Synapse, Snowflake) optimized for BI queries. Fast and well-governed, but expensive and bad for ML/unstructured data.

Data Lakehouse = The BEST of both. It takes the cheap storage of a data lake, adds Delta Lake for ACID transactions and schema enforcement, adds Photon for fast BI queries, and adds Unity Catalog for governance. You get data lake flexibility + data warehouse reliability at data lake prices.

Real-world analogy:

• Data Lake = Big messy warehouse (cheap rent, hard to find things)
• Data Warehouse = Expensive organized office (everything in place, but high rent)
• Lakehouse = Organized warehouse (cheap rent + everything labeled and easy to find)

Q16: What technologies make the Lakehouse possible?

These are the key building blocks — know what each one does:

1. Delta Lake → Adds ACID transactions to cloud storage (the foundation of lakehouse)
2. Photon Engine → C++ query engine that makes queries as fast as a data warehouse (see Day 3)
3. Unity Catalog → Centralized governance — who can access what data (see Day 3)
4. Serverless SQL Warehouses → BI tools (Power BI, Tableau) connect directly to Databricks
5. MLflow → Manage the ML lifecycle (track experiments, deploy models)
6. Lakeflow Declarative Pipelines → Build ETL pipelines with built-in data quality checks (see Day 2)

Interview tip: When asked "Why Databricks over Snowflake?", mention: open formats (no vendor lock-in), unified BI + ML on one platform, Delta Lake is open source, and Photon gives warehouse-level speed on open data.

SECTION 6: NEW 2025-2026 FEATURES (30 min)

Answer First: Delta 4.x adds capabilities such as Variant for semi-structured values, type widening, and newer protocol features. Each feature must be checked against the Spark, Java, reader, and writer compatibility boundary before adoption.

Memory Map: What's new in Delta Lake 4.x? (Mention 2-3 in interview to show you're up to date) -> Variant stores semi-structured values under protocol support -> widening permits selected safe type changes -> feature adoption may raise reader or writer requirements -> compatibility matrix gates rollout [DB_01_Delta_Lake_Deep_Dive.md:655].

Q17: What's new in Delta Lake 4.x? (Mention 2-3 in interview to show you're up to date)

Breaking change: Delta 4.x needs Spark 4.x and Java 17+ (older versions won't work).

Interview tip: Mention Variant Data Type and Type Widening — they solve real problems that interviewers care about.

Answer First: Lakebase is a brand NEW feature (GA on Azure March 2026). It's a serverless PostgreSQL-compatible database built into Databricks.

Memory Map: Lakebase -> managed PostgreSQL service handles low-latency row transactions -> application reads and writes operational state -> governed integration exposes data to analytics -> replication lag measures freshness [DB_01_Delta_Lake_Deep_Dive.md:692].

Q19: What is Lakebase?

Simple Explanation: Lakebase is a brand NEW feature (GA on Azure March 2026). It's a serverless PostgreSQL-compatible database built into Databricks.

Why does it exist? Delta Lake is great for analytics (batch queries, BI), but NOT great for low-latency application queries (like "get this passenger's details in 10 ms for the mobile app"). Lakebase fills this gap — it's a real database for application use cases, running inside Databricks.

Key features:

• Scale-to-zero: When nobody is querying, it costs $0 (shuts down automatically)
• Database branching: Create an instant copy of your database for testing (like git branch for databases!)
• Instant restore: Go back to any point in time if something goes wrong
• Auto-failover HA: If one server fails, another takes over automatically

When to use Lakebase vs Delta tables:

Q20: What is the difference between Managed and External tables?

Simple Explanation: When you create a table in Unity Catalog, you choose where the data is stored:

• Managed Table: Databricks decides where to store the data (in a Databricks-managed location). If you DROP the table, BOTH the metadata AND the data are deleted. Simple and recommended for most cases.

• External Table: YOU specify where the data lives (e.g., a specific ADLS Gen2 path). If you DROP the table, only the metadata is removed — the actual data files survive. Use this when data must persist even if the table definition is removed, or when data is shared with other systems.

Real-world analogy:

• Managed = Renting a furnished apartment. If you end the lease, furniture goes too.
• External = Renting an empty apartment and bringing your own furniture. If you end the lease, you take your furniture with you.

-- Managed table (recommended for new tables — simpler to manage)
CREATE TABLE travel_catalog.bookings.flights (
    flight_id LONG,                    -- Unique flight identifier
    departure STRING,                  -- Departure airport code (e.g., BLR)
    arrival STRING                     -- Arrival airport code (e.g., DEL)
);
-- Databricks stores data in its managed location automatically
-- DROP TABLE will delete everything

-- External table (for data that must survive table drops)
CREATE TABLE travel_catalog.bookings.legacy_flights (
    flight_id LONG,
    departure STRING,
    arrival STRING
) LOCATION 'abfss://container@storage.dfs.core.windows.net/legacy/flights/';
-- Data lives at YOUR ADLS Gen2 path
-- DROP TABLE only removes the table definition, data files stay in ADLS

Example use case: "We use managed tables for new Delta tables (Predictive Optimization works automatically). We use external tables for legacy data migrated from Oracle that other systems also read."

Delta Lake & Lakehouse — Quick Recall

🧠 MASTER MEMORY MAP — Day 1

⚡ MUST KNOW DIRECT QUESTIONS (Cover the answer, test yourself!)

🔑 MID-LEVEL QUESTIONS

⚡ MUST KNOW DIRECT QUESTIONS

WITH deduped AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY booking_id ORDER BY updated_at DESC
  ) AS rn FROM source_data
)
SELECT * FROM deduped WHERE rn = 1

🔑 MID-LEVEL QUESTIONS

⚡ MUST KNOW DIRECT QUESTIONS

SET spark.databricks.delta.retentionDurationCheck.enabled = false;
VACUUM bookings RETAIN 0 HOURS;

🔑 MID-LEVEL QUESTIONS

⚡ MUST KNOW DIRECT QUESTIONS

SECTION 5: LAKEHOUSE ARCHITECTURE

🧠 Memory Map: Lakehouse

⚡ MUST KNOW DIRECT QUESTIONS

SECTION 6: NEW 2025-2026 FEATURES

🧠 Memory Map: What's New

⚡ MUST KNOW DIRECT QUESTIONS

Delta Lake Advanced Masterclass -- Never Get Caught Off Guard Again

TABLE OF CONTENTS

1. OPTIMIZE -- When It Helps and When It Hurts
2. Liquid Clustering vs Z-ORDER vs Partitioning -- The Complete Decision Guide
3. Deletion Vectors -- Internal Mechanics
4. Change Data Feed (CDF) -- CDC with Delta Lake
5. UniForm -- Universal Format
6. Predictive Optimization
7. The Small File Problem -- Root Causes and Real Solutions
8. VACUUM -- Risks, Production Incidents, and Gotchas
9. Delta Lake vs Apache Iceberg vs Apache Hudi
10. Rapid-Fire Interview Questions with Traps

Answer First: Choose Iceberg for a multi-engine or Snowflake-heavy ecosystem, Delta for a Databricks-centered platform with deep native integration, and Hudi when record-level upserts and incremental ingestion dominate.

Memory Map: When would you choose each format? Give me the decision framework -> engine ecosystem establishes interoperability constraints -> mutation workload selects transaction semantics -> platform services determine operational fit -> cross-engine pilot verifies the format choice [DB_06_Delta_Lake_Advanced_Masterclass.md:1228].

Q23: When would you choose each format? Give me the decision framework.

Answer:

The nuanced interview answer: "In practice, the choice is often driven by ecosystem, not features. If you are on Databricks, you use Delta Lake. If you are multi-engine or Snowflake-heavy, you lean toward Iceberg. Hudi is the best choice for streaming upsert-heavy workloads but has a smaller community. The formats are converging -- Delta has UniForm for Iceberg compatibility, Iceberg added merge-on-read, and all three support ACID, time travel, and schema evolution."

Answer First: delta.enableChangeDataFeed is a table property that records change data for future commits; readChangeFeed is a reader option that requests those recorded rows. Enabling capture does not make earlier versions retroactively available.

Memory Map: the difference between "delta.enableChangeDataFeed" and "readChangeFeed" -> table property enables recording for future commits -> read option requests the recorded change stream -> enabling later cannot reconstruct earlier mutations -> history and sample reads verify the boundary [DB_06_Delta_Lake_Advanced_Masterclass.md:1313].

Q25: What is the difference between "delta.enableChangeDataFeed" and "readChangeFeed"?

Answer:

Answer First: Ordered log actions, checkpoint-assisted reconstruction, conflict checks, and atomic publication provide the transaction behavior asked about here.

Memory Map: the Delta Lake protocol -- reader and writer versions -> protocol action declares minimum reader and writer capabilities -> table features can raise those versions -> incompatible clients reject access safely -> detail output confirms negotiated requirements [DB_06_Delta_Lake_Advanced_Masterclass.md:1412].

Q29: Explain the Delta Lake protocol -- reader and writer versions.

Answer:

Answer First: Write-Audit-Publish isolates a candidate dataset, runs quality and reconciliation checks, then promotes only an approved result. With Delta, use a staging table or clone and an atomic publication step rather than nonexistent table-branch syntax.

Memory Map: Write-Audit-Publish (WAP) pattern with Delta? How do you implement it -> candidate write remains isolated from consumers -> audit queries evaluate rules and counts -> approved data receives an atomic publish step -> rejected data preserves the previous visible version [DB_06_Delta_Lake_Advanced_Masterclass.md:1506].

Q31: What is Write-Audit-Publish (WAP) pattern with Delta? How do you implement it?

Answer:

Answer First: Delta row tracking assigns stable row identifiers and records the commit version of row changes for supported workloads. It helps incremental materialization and row-level change correlation, but it is a table feature with protocol compatibility requirements.

Memory Map: Row Tracking and what is it used for -> row tracking and it used for changes row visibility through table metadata -> row-level metadata records identity or removal -> reader combines it with Parquet data -> logical state changes without immediate full rewrite -> later compaction materializes the result [DB_06_Delta_Lake_Advanced_Masterclass.md:1540].

Q32: What is Row Tracking and what is it used for?

Answer:

Answer First: Priority: HIGHEST — Delta Lake is 30-40% of any Databricks interview.

Memory Map: Type Widening and Variant types in Delta Lake -> type widening and variant types in delta lake chooses enforcement, widening, or explicit evolution -> incoming fields meet the table contract -> enforcement or explicit evolution decides compatibility -> metadata version records the shape -> downstream reader test validates change [DB_06_Delta_Lake_Advanced_Masterclass.md:1563].

Q33: What are Type Widening and Variant types in Delta Lake?

Answer (latest features):

Delta Lake — Complete Interview Guide

Memory Map

Answer First: Delta Lake is the transaction protocol and storage framework; a Delta table is one dataset made of Parquet files plus its own transaction log. The protocol gives that table atomic commits, snapshot reads, schema controls, and history.

Memory Map: The Simple Answer -> storage framework supplies transaction semantics over files -> one dataset combines Parquet data with its log directory -> readers reconstruct a snapshot from both -> operations publish later versions [Delta_01_Complete_Guide.md:32].

The Simple Answer

Real-World Analogy

Answer First: Delta Lake is the transaction protocol and storage framework; each Delta table is a concrete dataset made of Parquet data files plus its own _delta_log metadata.

Memory Map: Technical Difference — What Each Actually IS -> technology defines the protocol and implementation -> concrete dataset is an instance using that protocol -> metadata binds schema properties and files -> commands operate on the instance [Delta_01_Complete_Guide.md:64].

Technical Difference — What Each Actually IS

┌─────────────────────────────────────────────────────────────┐
│                      DELTA LAKE (Technology)                 │
│                                                             │
│  ┌─────────────────┐  ┌─────────────────┐                  │
│  │  Delta Table:    │  │  Delta Table:    │                  │
│  │  orders          │  │  customers       │                  │
│  │                  │  │                  │                  │
│  │  📁 _delta_log/  │  │  📁 _delta_log/  │  ← each table   │
│  │  📄 data.parquet │  │  📄 data.parquet │    has its OWN   │
│  │                  │  │                  │    transaction   │
│  └─────────────────┘  └─────────────────┘    log            │
│                                                             │
│  Provides: ACID, Schema Enforcement, Time Travel,           │
│            MERGE, OPTIMIZE, VACUUM, Data Skipping           │
└─────────────────────────────────────────────────────────────┘

Side-by-Side Comparison

Answer First: ) USING DELTA -- ← tells Spark: "use Delta Lake format".

Memory Map: How You Create a Delta Table (using Delta Lake) -> USING DELTA selects the table provider -> schema and location establish metadata -> first commit records protocol and creation state -> catalog or path resolves the new dataset [Delta_01_Complete_Guide.md:96].

How You Create a Delta Table (using Delta Lake)

# Method 1: SQL
spark.sql("""
    CREATE TABLE orders (
        order_id INT,
        customer STRING,
        amount DOUBLE,
        order_date DATE
    ) USING DELTA                          -- ← tells Spark: "use Delta Lake format"
    LOCATION 's3://my-bucket/orders/'
""")

# Method 2: DataFrame write
df.write.format("delta").save("s3://my-bucket/orders/")
#                 ↑
#          this "delta" = use Delta Lake technology

# Method 3: In Databricks (default since DBR 8.0+)
spark.sql("CREATE TABLE orders (...)")
#   ↑ In Databricks, DELTA is the DEFAULT format
#     You don't even need to say USING DELTA!

# What gets created on disk:
# s3://my-bucket/orders/
# ├── _delta_log/                    ← Delta Lake adds this (transaction log)
# │   └── 00000000000000000000.json  ← First commit: "table created with schema..."
# ├── part-00000.snappy.parquet      ← Actual data (still Parquet!)
# └── part-00001.snappy.parquet      ← More data

Answer First: Without Delta Lake (plain Parquet): With Delta Lake (Delta Table):.

Memory Map: What Delta Lake GIVES to a Delta Table -> ordered commits add atomic mutation and recovery -> schema checks protect writes -> statistics support file pruning -> maintenance improves physical layout [Delta_01_Complete_Guide.md:149].

What Delta Lake GIVES to a Delta Table

Answer First: "Delta Lake is an open-source storage layer that sits on top of cloud storage like S3 or ADLS. It adds ACID transactions, schema enforcement, time travel, and scalable metadata handling to data lakes.

Memory Map: Interview Answers — What to Say -> concise response names open files plus transaction metadata -> mechanism explains atomic versioned commits -> tradeoff notes protocol-aware readers -> example connects the model to production [Delta_01_Complete_Guide.md:163].

Interview Answers — What to Say

What NOT to Say:

Answer First: Delta LAKE = the L is for LAYER (it's a storage LAYER / technology).

Memory Map: Quick Memory Trick -> layer mnemonic distinguishes technology from dataset -> log-plus-files mnemonic recalls table structure -> version mnemonic recalls atomic commits -> example reinforces the distinction [Delta_01_Complete_Guide.md:191].

Quick Memory Trick

Answer First: Delta Lake adds a transaction log ( delta log/ ) on top of Parquet files. This log tracks every change — which files were added, removed, or modified. It turns a chaotic collection of files into a proper table with ACID transactions, schema enforcement, and version history.

Memory Map: Delta Lake? Why do we need it -> plain files lack coordinated mutation state -> ordered log actions define each snapshot -> optimistic checks serialize conflicting writers -> history enables audit and recovery [Delta_01_Complete_Guide.md:205].

Q01 — What is Delta Lake? Why do we need it?

Question: Your interviewer asks: "Why can't we just use Parquet files on a data lake? Why do we need Delta Lake?"

The Problem (Plain Parquet):

The Solution (Delta Lake): Delta Lake adds a transaction log (_delta_log/) on top of Parquet files. This log tracks every change — which files were added, removed, or modified. It turns a chaotic collection of files into a proper table with ACID transactions, schema enforcement, and version history.

One-liner for interviews: "Delta Lake is an open-source storage layer that adds ACID transactions, scalable metadata handling, and time travel to existing data lakes built on Parquet."

Answer First: Delta Lake combines atomic commits, snapshot isolation, schema controls, versioned history, scalable metadata, and maintenance over Parquet data. Together these features turn a file collection into a reliable transactional table.

Memory Map: the key features of Delta Lake? (The Top 8) -> atomic commits protect concurrent changes -> enforcement and evolution govern schema -> history and change feeds expose versions -> statistics layout and cleanup sustain performance [Delta_01_Complete_Guide.md:252].

Q02 — What are the key features of Delta Lake? (The Top 8)

Question: "List the main features of Delta Lake and explain why each matters."

Interview Tip: Don't just list features — connect them to PROBLEMS they solve. "We needed MERGE because our booking system sends daily change feeds that contain inserts, updates, and cancellations — all in one file."

What NOT to Say: "Delta Lake is a database." — No, it's a STORAGE LAYER on top of cloud storage. The data still lives as Parquet files on ADLS/S3/GCS.

Q28 — Managed vs External Tables

Question: "What is the difference? When do you use each?"

-- Managed (recommended for new tables)
CREATE TABLE catalog.schema.orders (order_id LONG, amount DECIMAL);
-- Data stored in Databricks-managed location
-- DROP TABLE deletes everything

-- External (for data shared with other systems)
CREATE TABLE catalog.schema.legacy_orders (order_id LONG, amount DECIMAL)
LOCATION 'abfss://container@storage.dfs.core.windows.net/legacy/orders/';
-- Data stays at YOUR path even if table is dropped

Q32 — What's new in Delta Lake 4.x?

Question: "What are the latest features? Mention 2-3 to show you're up to date."

Interview power move: "I'd use Liquid Clustering with Deletion Vectors enabled and CDF turned on — that gives me automatic compaction, fast updates, and incremental ETL. For cross-team sharing with Snowflake users, I'd enable UniForm so they read the same table as Iceberg."

Answer First: Run the lab as a controlled state transition: create a disposable baseline, perform one operation, inspect table history and files, then verify the expected version and rows. Reset only the isolated lab location between attempts.

Memory Map: (This section replaces YouTube videos — read + copy-paste + see the output) -> disposable baseline makes each lab repeatable -> one operation creates one observable transition -> log and table inspection connect cause to effect -> cleanup prevents shared-state contamination [Delta_01_Complete_Guide.md:1474].

(This section replaces YouTube videos — read + copy-paste + see the output)

Setup (copy-paste this first)

# ─── SETUP ────────────────────────────────────────────────
# Create a fresh Delta table in a clean location
path = "/tmp/delta_lab/orders"

# Clean up anything from previous runs
dbutils.fs.rm(path, recurse=True)   # ← wipes old data (safe, /tmp only)

# Create initial data
from pyspark.sql import Row
data = [
    Row(order_id=1, customer="Alice", amount=100.0),
    Row(order_id=2, customer="Bob",   amount=200.0),
    Row(order_id=3, customer="Carol", amount=300.0),
]
df = spark.createDataFrame(data)

# Write as Delta — this creates the table AND the _delta_log
df.write.format("delta").save(path)

print("✅ Delta table created at:", path)

Answer First: A new Delta table contains Parquet data files plus a _delta_log directory whose first JSON commit declares protocol, metadata, and added files. Inspect both locations to connect physical files to the committed snapshot.

Memory Map: Step 1 — Look at what Delta just created -> creation writes data files and a transaction directory -> first JSON file receives version zero -> filesystem listing reveals both components -> table query confirms the initial snapshot [Delta_01_Complete_Guide.md:1509].

Step 1 — Look at what Delta just created

# List files in the folder
files = dbutils.fs.ls(path)
for f in files:
    print(f.name)

# EXPECTED OUTPUT:
# _delta_log/                          ← the transaction log folder
# part-00000-abc123.snappy.parquet     ← actual data (Parquet)
# part-00001-def456.snappy.parquet

# Now peek inside _delta_log
log_files = dbutils.fs.ls(f"{path}/_delta_log")
for f in log_files:
    print(f.name)

# EXPECTED OUTPUT:
# 00000000000000000000.json    ← ONE commit file. This is commit #0.

Answer First: The first Delta commit is the table’s creation record: protocol and metadata define compatibility and schema, add actions select the initial files, and commitInfo describes the write. Reading the JSON exposes those actions directly.

Memory Map: Step 2 — Read commit #0 (see the "birth certificate" of the table) -> protocol action records compatibility -> metadata action records schema and properties -> add actions identify active files -> version zero becomes the creation certificate [Delta_01_Complete_Guide.md:1531].

Step 2 — Read commit #0 (see the "birth certificate" of the table)

# Read the first commit JSON
commit_0 = spark.read.json(f"{path}/_delta_log/00000000000000000000.json")
commit_0.show(truncate=False)

# EXPECTED OUTPUT (5 rows, one per action):
# ┌──────────┬────────────────────────────────────────┐
# │ action   │ details                                 │
# ├──────────┼────────────────────────────────────────┤
# │ protocol │ minReaderVersion=1, minWriterVersion=2 │  ← compat info
# │ metaData │ schema={order_id INT, customer STR,..} │  ← table schema
# │ add      │ path=part-00000-abc.parquet, size=921  │  ← data file 1
# │ add      │ path=part-00001-def.parquet, size=874  │  ← data file 2
# │ commitInfo│ operation=WRITE, timestamp=...         │  ← who did what
# └──────────┴────────────────────────────────────────┘

Answer First: A Delta UPDATE publishes a new commit rather than editing the original commit. The new version records the row-level change through file actions while the prior snapshot remains addressable until retention removes required files.

Memory Map: Step 3 — Do an UPDATE. Watch a NEW commit appear -> update reads the current snapshot -> changed rows produce new file actions -> atomic commit publishes the next version -> history shows the recorded operation [Delta_01_Complete_Guide.md:1550].

Step 3 — Do an UPDATE. Watch a NEW commit appear.

from delta.tables import DeltaTable
dt = DeltaTable.forPath(spark, path)

# Update Alice's amount from 100 → 150
dt.update(
    condition = "customer = 'Alice'",
    set       = {"amount": "150.0"}
)

# Now check _delta_log AGAIN
log_files = dbutils.fs.ls(f"{path}/_delta_log")
for f in log_files:
    print(f.name)

# EXPECTED OUTPUT:
# 00000000000000000000.json    ← original commit (unchanged)
# 00000000000000000001.json    ← NEW commit! ✨ This is the UPDATE

Answer First: The update commit records removed or logically changed old data plus the new file state and operation metrics. Comparing commit 1 with commit 0 shows how Delta changes snapshots without mutating history.

Memory Map: Step 4 — Read commit #1. See what Delta did behind the scenes -> remove action retires replaced file state -> add action introduces rewritten rows -> operation metadata explains the mutation -> snapshot reconstruction uses the combined actions [Delta_01_Complete_Guide.md:1572].

Step 4 — Read commit #1. See what Delta did behind the scenes.

commit_1 = spark.read.json(f"{path}/_delta_log/00000000000000000001.json")
commit_1.show(truncate=False)

# EXPECTED OUTPUT:
# ┌───────────┬───────────────────────────────────────────┐
# │ action    │ details                                    │
# ├───────────┼───────────────────────────────────────────┤
# │ remove    │ path=part-00000-abc.parquet (OLD file)    │  ← old file marked removed
# │ add       │ path=part-00002-xyz.parquet (NEW file)    │  ← new file with updated row
# │ commitInfo│ operation=UPDATE, numUpdatedRows=1        │
# └───────────┴───────────────────────────────────────────┘

# 🧠 KEY INSIGHT:
#    Delta NEVER modifies a Parquet file in place.
#    It writes a NEW file with the updated row,
#    and marks the OLD file as "removed" in the log.
#    The old file still EXISTS on disk until VACUUM runs!

Answer First: Delta history returns committed versions with timestamps, operations, and metrics, letting operators trace state changes and choose a recovery point. It describes commits; retained data files still determine whether an old snapshot can be read.

Memory Map: Step 5 — Check the history (Delta's "audit log") -> step 5 check history delta s audit log selects the recoverable version and retained-file boundary -> known commit identifies prior state -> retained log and data reconstruct the snapshot -> read, clone, or restore applies recovery -> row reconciliation confirms result [Delta_01_Complete_Guide.md:1594].

Step 5 — Check the history (Delta's "audit log")

dt.history().select("version", "timestamp", "operation", "operationMetrics").show(truncate=False)

# EXPECTED OUTPUT:
# ┌───────┬──────────────────┬─────────┬────────────────────────┐
# │version│ timestamp         │ operation│ operationMetrics        │
# ├───────┼──────────────────┼─────────┼────────────────────────┤
# │  1    │ 2026-04-05 10:31 │ UPDATE  │ {numUpdatedRows: 1}    │
# │  0    │ 2026-04-05 10:30 │ WRITE   │ {numOutputRows: 3}     │
# └───────┴──────────────────┴─────────┴────────────────────────┘
#
# Every commit = one row in DESCRIBE HISTORY. That's your audit trail.

Routed Delta question-bank section indexes

TOPIC 1: DELTA LAKE (Internals, Transaction Log, ACID, MERGE, OPTIMIZE, VACUUM, Z-ORDER, Liquid Clustering)

L1 — Direct / Simple Questions

L2

Delta Transaction Log and ACID

Answer First: A Delta reader reconstructs a table snapshot from ordered log actions and checkpoints; writers use optimistic concurrency and atomic commit publication.

Memory Map: actions -> commit version -> checkpoint -> snapshot -> conflict check -> publish.

SECTION 1: DELTA LAKE INTERNALS

Answer First: The Delta transaction log records ordered metadata plus add and remove file actions. Readers reconstruct a snapshot from those actions, while atomic commit versions and optimistic validation provide ACID concurrency.

Memory Map: the Delta Lake transaction log (_delta_log)? Explain how it ensures ACID transactions -> candidate metadata and file actions enter validation -> atomic publication creates one ordered version -> readers reconstruct a stable snapshot -> history and active files prove isolation [03_Delta_Lake_and_Lakehouse.md:26].

Q1: What is the Delta Lake transaction log (_delta_log)? Explain how it ensures ACID transactions.

Simple Explanation: Think of a bank ledger. Every deposit, withdrawal, and transfer is recorded in order. If someone asks "what was the balance yesterday?", you replay the ledger up to yesterday. The Delta transaction log works the same way — it records every change to your table in numbered JSON files.

Answer: The _delta_log/ directory is an ordered record of every transaction performed on a Delta table.

Structure:

Each JSON commit file contains:

• add actions: New Parquet files added
• remove actions: Files logically deleted (still physically present until VACUUM)
• metaData: Schema changes, table properties
• protocol: Reader/writer version
• commitInfo: Timestamp, operation, user, metrics

How ACID is ensured:

• Atomicity: Each commit writes a single JSON file atomically using put-if-absent (filesystem rename)
• Consistency: Schema enforcement rejects mismatched writes
• Isolation: Snapshot isolation — readers see a consistent snapshot
• Durability: Data persisted as Parquet files on durable cloud storage

Interview Tip: "They won't ask 'What is ACID?' — they'll ask 'How does Delta Lake ensure data consistency when multiple pipelines write to the same table?' Answer with the transaction log + optimistic concurrency."

What NOT to Say: "Delta Lake is a database" — No, it's a STORAGE LAYER on top of Parquet files on cloud storage.

Answer First: A checkpoint materializes the table state at a version so readers need only replay later JSON commits. This bounds snapshot reconstruction time as transaction history grows.

Memory Map: long commit history -> checkpoint captures active metadata and files -> later JSON actions advance the snapshot -> bounded replay time proves the benefit [03_Delta_Lake_and_Lakehouse.md:69].

Q2: What are checkpoint files? Why are they critical?

Simple Explanation: Imagine your bank account has 10,000 transactions since it was opened. To know your current balance, you'd need to add up ALL 10,000 transactions — very slow! A monthly statement gives you the current balance directly. Checkpoints are like monthly statements for Delta — a snapshot of the full table state so you don't replay every commit.

Answer: Every 10 commits (default), Delta creates a Parquet checkpoint file that consolidates the full table state at that point.

Why needed:

• Without checkpoints: Reading version 10,000 requires replaying 10,000 JSON files
• With checkpoints: Read the latest checkpoint + only subsequent JSON files
• _last_checkpoint file stores the latest checkpoint version

Configurable: delta.checkpointInterval (default: 10)

Interview Tip: "If asked 'what happens when a Delta table has millions of commits?', mention checkpoints immediately — it shows you understand the internals, not just the API."

Answer First: Two hotel receptionists are booking rooms at the same time. "Optimistic" means: we ASSUME they won't book the same room, let both work, and only check for conflicts when they try to save. If they booked different rooms → both succeed.

Memory Map: optimistic concurrency control in Delta Lake. What happens when two writers conflict -> writer records its read version -> proposed actions identify touched data -> commit-time comparison detects conflict -> retry or new version resolves contention [03_Delta_Lake_and_Lakehouse.md:89].

Q3: Explain optimistic concurrency control in Delta Lake. What happens when two writers conflict?

Simple Explanation: Two hotel receptionists are booking rooms at the same time. "Optimistic" means: we ASSUME they won't book the same room, let both work, and only check for conflicts when they try to save. If they booked different rooms → both succeed. If they booked the same room → one gets an error and retries.

Answer:

Isolation levels:

Interview Tip: "To avoid conflicts in practice: partition your writes so different pipelines touch different files, and add the partition column to your MERGE condition."

What NOT to Say: "Delta locks the whole table during writes" — No, Delta uses optimistic concurrency, NOT pessimistic locking.

Answer First: Data files (Parquet) may be partially written to storage.

Memory Map: What happens if a write fails midway in Delta Lake -> writer may leave unreferenced data files in storage -> failed validation never publishes a new version -> readers continue using the previous snapshot -> cleanup later removes orphaned files [03_Delta_Lake_and_Lakehouse.md:207].

Q7: What happens if a write fails midway in Delta Lake?

Answer:

• Data files (Parquet) may be partially written to storage
• But the transaction log entry is never committed (atomic operation)
• On next read, Delta only considers files referenced in committed log entries
• The orphaned Parquet files are cleaned up by VACUUM
• This is the key benefit of ACID — partial writes don't corrupt the table

Interview Tip: "The key insight: the commit JSON file is the SINGLE point of atomicity. Data files can be partially written — it doesn't matter until the commit JSON makes them visible."

SECTION 1: DELTA LAKE INTERNALS (1.5 hours)

Answer First: But there's a big problem: if two people write to the same folder at the same time, data can get corrupted.

Memory Map: Delta Lake? And what is the transaction log -> Parquet files hold columnar rows -> ordered JSON actions define active file state -> atomic version creation coordinates writers -> checkpoints accelerate snapshot reconstruction [DB_01_Delta_Lake_Deep_Dive.md:11].

Q1: What is Delta Lake? And what is the transaction log?

Simple Explanation: Think of a normal data lake — you store files (like Parquet) in cloud storage (Azure ADLS). But there's a big problem: if two people write to the same folder at the same time, data can get corrupted. There's no "undo" button. There's no way to know what changed.

Delta Lake solves this. It adds a "smart layer" on top of your Parquet files. This smart layer is called the transaction log (stored in a folder called _delta_log/). It's like a diary that records every change — "file X was added", "file Y was removed", "schema changed", etc.

Real-world analogy: Imagine a hotel booking register. Every time a booking is made or cancelled, the receptionist writes it in a numbered logbook (commit 1, commit 2, commit 3...). If someone asks "what did our bookings look like yesterday?", you can replay the logbook up to yesterday. That logbook = Delta transaction log.

Why do we need it?

• Without Delta: Two booking agents update same file → data gets corrupted
• With Delta: Transaction log ensures only one change goes through at a time (like a queue)

Technical details:

What's inside each JSON commit file?

• add → "I added this new Parquet file" (new data was written)
• remove → "I logically deleted this file" (but file is still physically there until VACUUM cleans it)
• metaData → "The table schema changed" or "table properties changed"
• commitInfo → "Who did this, when, what operation (INSERT/UPDATE/DELETE)"

What is ACID? (You know this from databases, same concept here):

Example: "When 10 booking agents update the same passenger table simultaneously, Delta's transaction log ensures no partial writes corrupt the table. Each agent's changes are atomic — either fully applied or not at all."

Interview tip: They won't ask "What is ACID?". They'll ask "How does Delta Lake ensure data consistency when multiple pipelines write to the same table?" — answer with the transaction log + optimistic concurrency.

Answer First: To read the current table state, you'd need to read ALL 10,000 files — very slow!.

Memory Map: checkpoint files? Why are they important -> transaction log accumulates JSON commit actions -> checkpoint compacts log state into Parquet -> readers combine it with newer commits -> snapshot reconstruction time verifies the benefit [DB_01_Delta_Lake_Deep_Dive.md:59].

Q2: What are checkpoint files? Why are they important?

Simple Explanation: Imagine your transaction log has 10,000 commits (10,000 JSON files). To read the current table state, you'd need to read ALL 10,000 files — very slow!

A checkpoint is a summary file. Every 10 commits (by default), Delta creates a single Parquet file that says "here's the complete state of the table right now." So instead of reading 10,000 files, you read 1 checkpoint + the few commits after it.

Real-world analogy: Like a bank account statement. Instead of adding up every transaction since the account was opened, the monthly statement gives you the current balance. You only need to add transactions after the statement date.

Key points:

• Created every 10 commits (configurable via delta.checkpointInterval)
• Format: Parquet (not JSON) — faster to read
• _last_checkpoint file → tells Delta which checkpoint is the latest
• Without checkpoints: reading table = replaying ALL commits (slow!)
• With checkpoints: reading table = read latest checkpoint + only a few recent JSONs (fast!)

Q3: What is Optimistic Concurrency Control?

Simple Explanation: When two people try to update the same table at the same time, Delta uses "optimistic concurrency" to handle it. The word "optimistic" means: Delta ASSUMES there won't be a conflict, lets both work, and only checks for conflicts at commit time.

Real-world analogy: Two travel agents are updating different passenger records in the same table. Agent A updates passenger 1's email. Agent B updates passenger 2's address. Both started with version 5 of the table. Agent A finishes first and creates version 6. When Agent B tries to commit, Delta checks: "Did Agent A touch the same data as Agent B?" If no → Agent B's changes go in as version 7. If yes → conflict error.

Two isolation levels:

Interview tip: If asked "How do you handle concurrent writes?", mention: optimistic concurrency + partition your writes by date/region so different pipelines touch different files → no conflicts.

SECTION 1: DELTA LAKE INTERNALS

🧠 Memory Map: Transaction Log

SECTION 0: DELTA LAKE vs DELTA TABLE — The #1 Confusion

Answer First: The Key Insight — Delta Table = Parquet + Transaction Log resolves to ordered log actions, checkpoint-assisted snapshot reconstruction, conflict checks, and a published commit version.

Memory Map: The Key Insight — Delta Table = Parquet + Transaction Log -> query pattern defines projection needs -> columnar encoding and codec trade CPU for size -> reader prunes and decodes data -> bytes scanned validate the format choice [Delta_01_Complete_Guide.md:128].

The Key Insight — Delta Table = Parquet + Transaction Log

SECTION 1: WHY DELTA LAKE EXISTS

Q03 — How does Delta Lake ensure ACID transactions?

Question: "Walk me through how each ACID property is implemented in Delta Lake."

Follow-up they'll ask: "What happens if a write fails midway?"

Interview Tip: "The key insight is that the commit JSON file is the SINGLE point of atomicity. Data files can be partially written — it doesn't matter until the commit JSON makes them visible."

SECTION 2: TRANSACTION LOG DEEP DIVE

Answer First: Each JSON commit file contains these actions.

Memory Map: the Delta Transaction Log (_delta_log)? What's inside each commit -> commit file contains protocol metadata add remove and operation actions -> monotonically increasing filename orders versions -> readers apply actions to prior state -> history exposes the resulting transaction [Delta_01_Complete_Guide.md:309].

Q04 — What is the Delta Transaction Log (_delta_log)? What's inside each commit?

Question: "Explain the Delta transaction log in detail. What exactly is stored in each commit file?"

Each JSON commit file contains these actions:

// Example: 00000000000000000005.json (Commit #5)
{
  "commitInfo": {
    "timestamp": 1711670400000,
    "operation": "MERGE",
    "operationParameters": {"predicate": "t.id = s.id"},
    "operationMetrics": {
      "numTargetRowsInserted": "1500",
      "numTargetRowsUpdated": "300",
      "numTargetRowsDeleted": "50"
    }
  },
  "add": {
    "path": "part-00000-new-file.snappy.parquet",
    "size": 1073741824,
    "partitionValues": {"date": "2026-03-29"},
    "stats": "{\"numRecords\":50000,\"minValues\":{\"id\":1},\"maxValues\":{\"id\":50000}}"
  },
  "remove": {
    "path": "part-00000-old-file.snappy.parquet",
    "deletionTimestamp": 1711670400000,
    "dataChange": true
  }
}

Critical detail: remove is LOGICAL, not physical

Interview Tip: "The transaction log is append-only — you never modify existing commit files, you only add new ones. This is what makes it safe for concurrent readers."

Answer First: Large checkpoint state can be written across multiple checkpoint parts that readers load together. The parts represent one checkpoint version, not independent commits.

Memory Map: large table state -> one checkpoint version is split into numbered parts -> reader loads every part before later commits -> complete part set reconstructs the snapshot [Delta_01_Complete_Guide.md:364].

Q05 — What are checkpoint files? Why are they critical?

Question: "Without checkpoints, what happens when a Delta table has 100,000 commits?"

Problem without checkpoints:

Solution — Checkpoints:

Key details:

• Checkpoint interval: delta.checkpointInterval (default: 10)
• _last_checkpoint file points to the latest checkpoint
• Checkpoints are in Parquet format (columnar, fast to read)
• Multi-part checkpoints exist for very large tables (splits into multiple files)

Q06 — How does Optimistic Concurrency Control work?

Question: "Two pipelines write to the same Delta table at the same time. What happens?"

Two isolation levels:

How to AVOID conflicts in practice:

1. Partition writes by date/region so different pipelines touch different files
2. Add partition column to MERGE condition: ON t.id = s.id AND t.date = s.date
3. Use Liquid Clustering to minimize file overlap

Interview Tip: "The word 'optimistic' is key — Delta assumes no conflict, lets both writers proceed, and only checks at commit time. This is efficient because most concurrent writes DON'T conflict."

VISUAL ANIMATION 1 — Optimistic Concurrency Conflict

TIME  →  T0              T1               T2               T3
         │               │                │                │
JOB A    ├─ reads v5 ────┼─ writes data ──┼─ commits v6 ─✅ │
         │               │                │                │
JOB B    ├─ reads v5 ────┼─ writes data ──┼────────────────┼─ tries to commit v6 ❌
         │               │                │                │    CONFLICT!
         │               │                │                │    Job A already wrote v6
         │               │                │                │
         │               │                │                ├─ Delta checks:
         │               │                │                │    "Did Job A touch the
         │               │                │                │     same files as me?"
         │               │                │                │
         │               │                │                ├─ IF NO overlap:
         │               │                │                │    retry commit as v7 ✅
         │               │                │                │
         │               │                │                └─ IF YES overlap:
         │               │                │                     throw ConcurrentModification ❌
         │               │                │                     → user must retry the WHOLE job

KEY INSIGHT:
  • Delta does NOT lock the table while writing (unlike traditional DBs)
  • Both jobs write data files in PARALLEL — no slowdown
  • Conflict is detected ONLY at commit time (when writing the JSON)
  • Only ONE commit can "win" version 6 → the other must retry or fail

Routed Delta question-bank prompts — 10-transaction-log-acid

2. What file format does Delta Lake use under the hood?

3. What is the _delta_log directory and what does it contain?

5. What is a checkpoint file in the Delta transaction log?

1. Explain the anatomy of a Delta Lake transaction — what happens when you write to a Delta table?

   Key points: (1) Spark writes new Parquet files to storage, (2) Creates a new JSON commit file in _delta_log listing added/removed files, (3) Uses optimistic concurrency — checks if any conflicting commits happened since the read, (4) If no conflict, commit succeeds atomically; if conflict, retries or fails. Mention that readers never see partial writes because they only read committed versions.

17. What is the difference between managed and external Delta tables? > Key points: Managed: Databricks manages both metadata AND data files. Dropping the table deletes the data. External: Databricks manages only metadata; data files live at a user-specified location. Dropping the table leaves the data intact. Use managed for most tables (simpler lifecycle). Use external when data is shared across platforms or you need control over the storage location.

20. Explain Delta Lake 4.x features: UniForm, Universal Format. Why do they matter? > Key points: UniForm generates Iceberg-compatible metadata alongside Delta metadata, allowing Iceberg readers (Snowflake, BigQuery, Trino) to read Delta tables natively. No data duplication — same Parquet files, dual metadata. Matters because it breaks the "table format war" — you can use Delta for writes and let external tools read via Iceberg. Reduces vendor lock-in and enables multi-engine architectures.

4. What are the four ACID properties and how does Delta Lake guarantee them?

1. What is Delta Lake and why was it created?

   Open-source storage layer that brings ACID transactions, schema enforcement, and time travel to data lakes. Created to solve the reliability problems of raw data lakes (no transactions, no schema control, corrupt reads from concurrent writes).

14. What is the difference between Delta Lake and Apache Parquet? > Parquet is a file format. Delta Lake is a storage layer built ON TOP of Parquet that adds ACID transactions, schema enforcement, time travel, and the transaction log. Parquet alone has no transactions or versioning.

2. How does optimistic concurrency control work in Delta Lake? What happens during write conflicts?

7. How does the Delta transaction log handle concurrent writes from multiple clusters?

Delta MERGE and Schema Evolution

Answer First: Safe MERGE pipelines deduplicate the source, constrain the match domain, make clause ordering explicit, and treat schema evolution as a governed contract change.

Memory Map: deduplicate -> match -> update/insert/delete -> validate schema -> audit result.

SECTION 2: MERGE INTO — ALL SCENARIOS

Answer First: MERGE joins a source relation to a target and applies ordered matched, not-matched, and not-matched-by-source clauses in one atomic table commit.

Memory Map: the MERGE INTO syntax. Write a basic upsert -> deduplicated source joins the bounded target -> matched clause applies the update -> unmatched clause inserts a new key -> one commit publishes both actions [03_Delta_Lake_and_Lakehouse.md:223].

Q8: Explain the MERGE INTO syntax. Write a basic upsert.

Simple Explanation: MERGE is the Swiss Army knife of Delta Lake — it combines INSERT + UPDATE + DELETE into ONE atomic command. Think of it like: "Look at my new data. Compare it with existing data. If a record exists → update it. If it's new → insert it. If it's cancelled → delete it." All in one go, safely.

Answer:

MERGE INTO target_table AS t
USING source_table AS s
ON t.id = s.id                         -- Match condition

WHEN MATCHED AND s.op = 'DELETE' THEN
    DELETE                              -- Delete matching rows

WHEN MATCHED AND s.updated_at > t.updated_at THEN
    UPDATE SET *                        -- Update with all source columns

WHEN NOT MATCHED THEN
    INSERT *                            -- Insert all source columns

WHEN NOT MATCHED BY SOURCE THEN
    DELETE                              -- Delete target rows not in source (Databricks extension)

PySpark equivalent:

from delta.tables import DeltaTable

target = DeltaTable.forName(spark, "target_table")

target.alias("t").merge(
    source_df.alias("s"),
    "t.id = s.id"
).whenMatchedUpdate(
    condition="s.updated_at > t.updated_at",
    set={"*": "*"}  # or explicit: {"name": "s.name", "email": "s.email"}
).whenNotMatchedInsertAll() \
 .execute()

Interview Tip: "Be ready to write MERGE from memory — this is the #1 coding question in Databricks interviews. Practice it 3-4 times until it flows naturally."

What NOT to Say: "I'll write separate INSERT, UPDATE, and DELETE statements" — That's not atomic. MERGE does all three in one transaction.

Answer First: Multiple source rows for one target key make an update or delete ambiguous. Rank source rows by a deterministic sequence and retain one row per business key before MERGE.

Memory Map: you handle duplicate keys in the source during MERGE -> deterministic ordering ranks rows within each business key -> deduplication retains one source winner -> uniqueness assertion runs before ordered clauses -> unambiguous match can commit safely [03_Delta_Lake_and_Lakehouse.md:271].

Q9: How do you handle duplicate keys in the source during MERGE?

Simple Explanation: MERGE needs each target row to match at most ONE source row. If your source has two records for the same ID, MERGE doesn't know which one to use → error. Fix: deduplicate the source first using ROW_NUMBER.

Answer: If source has duplicate keys matching the same target row, MERGE throws an error: "Cannot perform Merge as multiple source rows matched...".

Solution: Deduplicate source first:

from pyspark.sql.functions import row_number, col
from pyspark.sql import Window

w = Window.partitionBy("id").orderBy(col("updated_at").desc())
deduped_source = source_df \
    .withColumn("rn", row_number().over(w)) \
    .filter(col("rn") == 1) \
    .drop("rn")

# Now merge with deduped source
target.alias("t").merge(deduped_source.alias("s"), "t.id = s.id") \
    .whenMatchedUpdateAll() \
    .whenNotMatchedInsertAll() \
    .execute()

Answer First: Model order events by business key, deduplicate the staged source, then order matched clauses so cancellation and update rules cannot shadow each other.

Memory Map: a MERGE for an e-commerce order system (create, update, cancel) -> staging retains the newest event per order -> matched cancellation precedes matched update -> unmatched records insert new orders -> operation counts reconcile every event type [03_Delta_Lake_and_Lakehouse.md:299].

Q10: Design a MERGE for an e-commerce order system (create, update, cancel).

Answer:

MERGE INTO orders_fact t
USING orders_staging s
ON t.order_id = s.order_id

-- Cancel: mark as deleted
WHEN MATCHED AND s.status = 'CANCELLED' THEN DELETE

-- Update: only if newer
WHEN MATCHED AND s.updated_at > t.updated_at THEN
    UPDATE SET
        t.status = s.status,
        t.amount = s.amount,
        t.shipping_address = s.shipping_address,
        t.updated_at = s.updated_at

-- New order
WHEN NOT MATCHED THEN
    INSERT (order_id, customer_id, status, amount, shipping_address, created_at, updated_at)
    VALUES (s.order_id, s.customer_id, s.status, s.amount, s.shipping_address, s.created_at, s.updated_at)

Answer First: MERGE performance depends on how much target data the match predicate can prune and how many files its actions must rewrite. Bound the target domain, reduce and deduplicate the source, then inspect scan and rewrite metrics.

Memory Map: the performance concern with MERGE? How do you optimize it -> selective match predicate prunes target files -> source reduction lowers join work -> favorable layout reduces touched data -> scan and rewrite metrics identify the bottleneck [03_Delta_Lake_and_Lakehouse.md:326].

Q11: What is the performance concern with MERGE? How do you optimize it?

Answer: MERGE scans the entire target table to find matches. For large tables, this is the bottleneck.

Optimization techniques:

1. Partition pruning — add partition columns to ON clause:

MERGE INTO target t
USING source s
ON t.id = s.id AND t.date = s.date  -- date is partition column → prunes partitions

2. Z-ORDER on merge key:

OPTIMIZE target ZORDER BY (id);  -- Data skipping on the merge key

3. Reduce source data before merge:

# Don't merge 100M rows if only 1M changed
changed_records = source_df.join(target_df, "id", "left_anti")  # New records
changed_records = changed_records.union(
    source_df.join(target_df, "id", "inner")
    .filter(source_df["hash"] != target_df["hash"])  # Changed records
)

4. Broadcast small source:

target.alias("t").merge(
    broadcast(source_df).alias("s"),  # Broadcast if source is small
    "t.id = s.id"
)

5. Compact target first:

OPTIMIZE target;  -- Fewer, larger files = fewer tasks

6. Use Photon runtime — significantly faster MERGE operations.

Answer First: MERGE schema evolution can add source fields when assignments directly map compatible columns and evolution is enabled. Explicit assignments keep the accepted schema change visible and testable.

Memory Map: MERGE with schema evolution — how does it work -> direct compatible assignments expose new source fields -> explicit evolution updates table metadata -> incompatible changes still fail validation -> schema history confirms the committed shape [03_Delta_Lake_and_Lakehouse.md:372].

Q12: MERGE with schema evolution — how does it work?

Answer:

# Enable automatic schema merge
spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled", "true")

# Now source with new columns will automatically add them to target
target.alias("t").merge(
    source_df.alias("s"),  # source has columns not in target
    "t.id = s.id"
).whenMatchedUpdateAll() \
 .whenNotMatchedInsertAll() \
 .execute()
# New columns from source are added to target table

Answer First: Schema enforcement rejects incompatible writes before they change the table, while schema evolution accepts only explicitly permitted changes and updates table metadata. Treat evolution as a governed contract because downstream readers must understand the new schema.

Memory Map: schema evolution in Delta Lake. What are the options -> enforcement rejects incompatible writes before commit -> mergeSchema allows explicit additive changes -> session auto-merge broadens evolution scope -> downstream compatibility tests gate adoption [03_Delta_Lake_and_Lakehouse.md:641].

Q21: Explain schema evolution in Delta Lake. What are the options?

Answer:

Add new columns during write:

# mergeSchema — adds new columns, preserves existing
df.write.format("delta") \
    .mode("append") \
    .option("mergeSchema", "true") \
    .saveAsTable("my_table")

Replace entire schema:

# overwriteSchema — replaces schema completely
df.write.format("delta") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .saveAsTable("my_table")

Column rename/drop (requires column mapping):

-- Enable column mapping first
ALTER TABLE my_table SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name');

-- Now you can rename and drop columns
ALTER TABLE my_table RENAME COLUMN old_name TO new_name;
ALTER TABLE my_table DROP COLUMN unused_column;

SECTION 2: MERGE INTO — ALL SCENARIOS (1.5 hours)

Q5: What is MERGE? Basic syntax (upsert)

Simple Explanation: MERGE is the most important operation in Databricks. It combines INSERT + UPDATE + DELETE into a single command. In simple words: "Look at my new data (source). Compare it with existing data (target). If a record already exists → update it. If it's new → insert it. If it's cancelled → delete it."

This is called an upsert (update + insert).

Why do we need it? Without MERGE, you'd need to write 3 separate queries (one for insert, one for update, one for delete) — and they wouldn't be atomic. MERGE does everything in one atomic operation.

Real-world analogy: The platform receives a daily file of booking changes. Some are new bookings (INSERT), some are updates to existing bookings (UPDATE), some are cancellations (DELETE). MERGE handles all three in one go.

-- MERGE = Compare source (new data) with target (existing table), then act
MERGE INTO bookings_fact AS t            -- t = target (our existing bookings table)
USING bookings_staging AS s              -- s = source (new booking data that just arrived)
ON t.booking_id = s.booking_id           -- Match condition: how to find matching records

-- Case 1: Booking exists AND is cancelled → delete it
WHEN MATCHED AND s.status = 'CANCELLED' THEN
    DELETE

-- Case 2: Booking exists AND has been updated → update the record
WHEN MATCHED AND s.updated_at > t.updated_at THEN
    UPDATE SET
        t.status = s.status,             -- Update status (e.g., CONFIRMED → CHECKED_IN)
        t.fare_amount = s.fare_amount,   -- Maybe fare was recalculated
        t.passenger_count = s.passenger_count,
        t.updated_at = s.updated_at      -- Track when this change happened

-- Case 3: Booking doesn't exist in target → it's a brand new booking → insert
WHEN NOT MATCHED THEN
    INSERT (booking_id, flight_id, passenger_id, status, fare_amount,
            passenger_count, created_at, updated_at)
    VALUES (s.booking_id, s.flight_id, s.passenger_id, s.status, s.fare_amount,
            s.passenger_count, s.created_at, s.updated_at)

-- Case 4: Record exists in target but NOT in source → orphaned data → clean up
-- (Databricks extension — not available in standard SQL)
WHEN NOT MATCHED BY SOURCE AND t.status = 'PENDING' THEN
    DELETE

Interview tip: Be ready to write MERGE from memory. This is the #1 coding question in Databricks interviews. Practice it 3-4 times.

Q7: How to make MERGE faster? (CRITICAL — commonly asked)

Simple Explanation: The biggest problem with MERGE is: it has to scan the ENTIRE target table to find matching records. If your bookings table has 2 billion rows, MERGE reads all 2 billion rows just to match a few thousand new records. This is VERY slow.

Why is it slow? MERGE works like this: for every row in the source, scan the entire target to find a match. More target data = slower MERGE.

6 ways to make it faster:

1. Partition pruning — tell MERGE which partition to look in:

-- WITHOUT partition column: MERGE scans ALL data across ALL dates
MERGE INTO bookings t USING staging s
ON t.booking_id = s.booking_id              -- Scans 2 billion rows!

-- WITH partition column: MERGE only scans matching date partitions
MERGE INTO bookings t USING staging s
ON t.booking_id = s.booking_id
   AND t.booking_date = s.booking_date      -- Only scans today's partition!
-- This is like telling MERGE: "only look in March 2026 folder, not all folders"

2. Z-ORDER on merge key — organize data so matching records are close together:

-- Z-ORDER sorts/groups data by booking_id within files
-- This makes data skipping work better during MERGE
OPTIMIZE bookings ZORDER BY (booking_id);
-- Now when MERGE looks for booking ABC123, it can skip most files
-- (See Q10 below for full explanation of Z-ORDER)

3. Don't merge rows that haven't changed — filter source first:

-- Problem: Source has 1 million rows, but only 10,000 actually changed
-- Without filter: MERGE processes all 1 million rows (wasteful)
-- With filter: MERGE only processes 10,000 changed rows (fast!)

MERGE INTO bookings t
USING (
    SELECT s.* FROM staging s
    LEFT JOIN bookings t ON s.booking_id = t.booking_id
    WHERE t.booking_id IS NULL                  -- Brand new bookings (not in target)
       OR s.hash_value != t.hash_value          -- Changed bookings (different data)
    -- Unchanged bookings are filtered out — saves time!
) AS s
ON t.booking_id = s.booking_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

4. Broadcast small source — if source is small, send it to all workers:

# What is broadcast? When source is small (e.g., 10K rows),
# Spark sends a copy to every worker machine.
# This avoids expensive "shuffle" (moving data between machines).
from pyspark.sql.functions import broadcast
target.alias("t").merge(
    broadcast(source_df).alias("s"),       # Send source to all workers
    "t.booking_id = s.booking_id"          # Workers can match locally
)
# Use when: source < 100 MB. Don't use when source is large.

5. Compact target first — fewer files = fewer tasks = faster:

-- If target has 50,000 small files, MERGE creates 50,000 tasks (slow!)
-- OPTIMIZE combines small files into ~1 GB files (e.g., 500 files)
OPTIMIZE bookings;
-- Now MERGE only creates ~500 tasks (much faster!)
-- See Q9 below for full explanation of OPTIMIZE

6. Use Photon runtime — C++ engine that's 3-5x faster for MERGE:

• Just select "Photon" runtime when creating your cluster
• No code changes needed — same SQL, just runs faster
• See Day 3 for full Photon explanation

Q8: What is schema evolution with MERGE?

Simple Explanation: Sometimes your source data has NEW columns that don't exist in the target table yet. For example, the airline adds a "loyalty_tier" field to passenger data. Normally, MERGE would fail because the target table doesn't have this column.

With schema evolution, Delta automatically adds the new column to the target table during MERGE.

-- Step 1: Enable auto schema merge (tell Delta: "it's okay if source has new columns")
SET spark.databricks.delta.schema.autoMerge.enabled = true;

-- Step 2: MERGE as normal — new columns from source get added to target automatically
MERGE INTO passengers t                -- Target: has columns (id, name, email)
USING staging s                        -- Source: has columns (id, name, email, loyalty_tier) ← NEW!
ON t.passenger_id = s.passenger_id
WHEN MATCHED THEN UPDATE SET *         -- * means "all columns" — includes loyalty_tier
WHEN NOT MATCHED THEN INSERT *         -- New column "loyalty_tier" is auto-added to target table
-- After this: target table now has (id, name, email, loyalty_tier)

When to use: When source systems add new fields over time (very common in real life). When NOT to use: When you want strict schema control (e.g., regulatory tables where schema changes need approval).

SECTION 2: MERGE INTO (UPSERT)

🧠 Memory Map: MERGE

Answer First: DVs make MERGE faster at write time (no file rewrites)

Memory Map: Deletion Vectors interact with MERGE -> row-position metadata marks changed records -> write avoids immediately rewriting whole files -> readers merge markers with Parquet rows -> later compaction materializes clean file state [DB_06_Delta_Lake_Advanced_Masterclass.md:434].

Q9: How do Deletion Vectors interact with MERGE?

Answer -- this is a nuanced interview question:

MERGE INTO target USING source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET target.value = source.value
WHEN NOT MATCHED THEN INSERT *

Without DVs (traditional):

With DVs enabled:

The trade-off interviewers want to hear:

• DVs make MERGE faster at write time (no file rewrites)
• But they create more files and require OPTIMIZE sooner
• Net effect: MERGE throughput improves 2-10x with DVs enabled
• This is especially impactful for tables with frequent small MERGEs

Answer First: A deterministic source, explicit match domain, ordered change clauses, and result validation are the required controls for this MERGE or schema case.

Memory Map: What happens if a MERGE operation fails midway -> candidate file actions remain unpublished during execution -> failure prevents atomic version creation -> retry starts from a fresh snapshot -> history confirms no partial mutation [DB_06_Delta_Lake_Advanced_Masterclass.md:1351].

Q27: What happens if a MERGE operation fails midway?

Answer:

Q30: How does Delta Lake handle schema enforcement vs schema evolution?

Answer:

Interview trap: "What is the difference between mergeSchema and overwriteSchema?"

SECTION 3: MERGE — THE MOST IMPORTANT COMMAND

Q07 — Write a MERGE from memory (upsert with all clauses)

Question: "Write a MERGE that handles inserts, updates, and deletes in one command."

This is the #1 coding question in Databricks interviews. Memorize it.

-- MERGE = Compare source (new data) with target (existing table), then act
MERGE INTO orders_fact AS t                -- t = target (existing table)
USING orders_staging AS s                  -- s = source (new data)
ON t.order_id = s.order_id                -- Match condition

-- Case 1: Record exists AND is cancelled → delete it
WHEN MATCHED AND s.status = 'CANCELLED' THEN
    DELETE

-- Case 2: Record exists AND has changed → update it
WHEN MATCHED AND s.updated_at > t.updated_at THEN
    UPDATE SET
        t.status = s.status,
        t.amount = s.amount,
        t.updated_at = s.updated_at

-- Case 3: Record doesn't exist in target → insert it
WHEN NOT MATCHED THEN
    INSERT (order_id, customer_id, status, amount, created_at, updated_at)
    VALUES (s.order_id, s.customer_id, s.status, s.amount, s.created_at, s.updated_at)

-- Case 4: Record in target but NOT in source → clean up (Databricks extension)
WHEN NOT MATCHED BY SOURCE AND t.status = 'PENDING' THEN
    DELETE

PySpark equivalent:

from delta.tables import DeltaTable

target = DeltaTable.forName(spark, "orders_fact")

target.alias("t").merge(
    source_df.alias("s"),
    "t.order_id = s.order_id"
).whenMatchedDelete(
    condition="s.status = 'CANCELLED'"
).whenMatchedUpdate(
    condition="s.updated_at > t.updated_at",
    set={"status": "s.status", "amount": "s.amount", "updated_at": "s.updated_at"}
).whenNotMatchedInsertAll(
).execute()

Q08 — How do you handle duplicate keys in source during MERGE?

Question: "Your staging table has duplicate order_ids. MERGE throws an error. How do you fix it?"

Why it fails: MERGE requires each target row to match AT MOST one source row. Duplicate keys = ambiguous → error.

The fix — deduplicate source with ROW_NUMBER:

WITH deduped_source AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id           -- Group duplicates together
            ORDER BY updated_at DESC        -- Keep the LATEST record
        ) AS rn
    FROM orders_staging
)
MERGE INTO orders_fact AS t
USING (SELECT * FROM deduped_source WHERE rn = 1) AS s    -- Only latest per key
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

Why duplicates happen in real life:

• Kafka retry delivered the same event twice
• Source system sent the same file twice
• Backfill job reprocessed already-loaded data

Q09 — How to make MERGE faster? (6 techniques)

Question: "Our MERGE takes 3 hours on a 500 GB table. How do you optimize it?"

Why MERGE is slow: It scans the ENTIRE target to find matching records. 500 GB table + 10K source rows = reads all 500 GB just to match 10K rows.

1. Add partition column to match condition:

-- BAD: Scans ALL data across ALL dates
MERGE INTO orders t USING staging s
ON t.order_id = s.order_id                              -- Scans 500 GB!

-- GOOD: Only scans matching date partitions
MERGE INTO orders t USING staging s
ON t.order_id = s.order_id AND t.order_date = s.order_date  -- Scans ~2 GB!

2. Z-ORDER or Liquid Cluster on merge key:

OPTIMIZE orders ZORDER BY (order_id);
-- Data skipping now works for MERGE — skips irrelevant files

3. Filter unchanged rows from source:

MERGE INTO orders t
USING (
    SELECT s.* FROM staging s
    LEFT JOIN orders t ON s.order_id = t.order_id
    WHERE t.order_id IS NULL OR s.hash_value != t.hash_value
) AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

4. Broadcast small source:

from pyspark.sql.functions import broadcast
target.alias("t").merge(
    broadcast(source_df).alias("s"),     # Source < 100 MB → broadcast to all workers
    "t.order_id = s.order_id"
)

5. Compact target first:

OPTIMIZE orders;  -- 50,000 small files → 500 large files → fewer tasks

6. Use Photon runtime:

• Select "Photon" when creating cluster — 3-5x faster for MERGE
• No code changes needed

Q10 — MERGE with Schema Evolution

Question: "Source data has a new column that doesn't exist in the target. How do you handle it?"

-- Enable auto schema merge
SET spark.databricks.delta.schema.autoMerge.enabled = true;

-- MERGE as normal — new columns from source auto-added to target
MERGE INTO customers t
USING staging s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET *       -- * includes new columns
WHEN NOT MATCHED THEN INSERT *       -- New column auto-added to target schema

When to use: Source systems add new fields over time (very common). When NOT to use: Strict regulatory tables where schema changes need approval.

Q25 — Schema Evolution — All the options

Question: "How does Delta handle schema changes? What options are available?"

Option 1: Add new columns (mergeSchema):

df.write.format("delta") \
    .mode("append") \
    .option("mergeSchema", "true") \
    .saveAsTable("orders")
# New columns in df are auto-added to the table schema
# Existing columns are preserved

Option 2: Replace entire schema (overwriteSchema):

df.write.format("delta") \
    .mode("overwrite") \
    .option("overwriteSchema", "true") \
    .saveAsTable("orders")
# Completely replaces the schema — destructive!

Option 3: Rename/drop columns (requires column mapping):

-- Enable column mapping first
ALTER TABLE orders SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name');

-- Now you can rename and drop
ALTER TABLE orders RENAME COLUMN old_name TO new_name;
ALTER TABLE orders DROP COLUMN unused_column;

Option 4: Type widening (Delta 4.0+):

-- Change column type without rewriting data
ALTER TABLE orders SET TBLPROPERTIES ('delta.enableTypeWidening' = 'true');
ALTER TABLE orders ALTER COLUMN amount TYPE DECIMAL(20,2);
-- INT → BIGINT, FLOAT → DOUBLE, etc. — NO full rewrite needed!

Answer First: Delta MERGE matches a deduplicated source to a bounded target and applies ordered update, delete, or insert clauses in one atomic commit. Match cardinality and target pruning are the main correctness and performance guardrails.

Memory Map: LAB 2 — Watch MERGE Work (The #1 Delta Operation) -> deduplicated staging data enters a bounded match -> matched rows update under ordered conditions -> missing keys insert new records -> table history and counts verify the lab [Delta_01_Complete_Guide.md:1646].

LAB 2 — Watch MERGE Work (The #1 Delta Operation)

# ─── SETUP target table ───────────────────────────
target_path = "/tmp/delta_lab/customers"
dbutils.fs.rm(target_path, recurse=True)

target_data = [
    Row(id=1, name="Alice",   status="active"),
    Row(id=2, name="Bob",     status="active"),
    Row(id=3, name="Carol",   status="active"),
]
spark.createDataFrame(target_data).write.format("delta").save(target_path)

print("📋 TARGET (before MERGE):")
spark.read.format("delta").load(target_path).show()
# +---+-----+------+
# | id| name|status|
# +---+-----+------+
# |  1|Alice|active|
# |  2|  Bob|active|
# |  3|Carol|active|
# +---+-----+------+

# ─── Build source (daily change feed) ──────────────
source_data = [
    Row(id=2, name="Bob",     status="inactive"),   # UPDATE  (Bob deactivated)
    Row(id=3, name="Carol",   status="DELETE_ME"),  # DELETE  (special marker)
    Row(id=4, name="Dave",    status="active"),     # INSERT  (new customer)
]
source_df = spark.createDataFrame(source_data)

print("📥 SOURCE (daily change feed):")
source_df.show()
# +---+----+---------+
# | id|name|   status|
# +---+----+---------+
# |  2| Bob| inactive|     ← will UPDATE existing row
# |  3|Carol|DELETE_ME|    ← will DELETE existing row
# |  4|Dave|   active|     ← will INSERT new row
# +---+----+---------+

# ─── THE MERGE ─────────────────────────────────────
from delta.tables import DeltaTable
dt = DeltaTable.forPath(spark, target_path)

dt.alias("t").merge(
    source_df.alias("s"),
    "t.id = s.id"                                   # ← match on id
).whenMatchedDelete(
    condition = "s.status = 'DELETE_ME'"            # ← if marker, delete
).whenMatchedUpdate(
    set = {"name": "s.name", "status": "s.status"}  # ← else update
).whenNotMatchedInsert(
    values = {"id": "s.id", "name": "s.name", "status": "s.status"}  # ← new rows: insert
).execute()

print("📋 TARGET (after MERGE):")
spark.read.format("delta").load(target_path).orderBy("id").show()
# +---+-----+--------+
# | id| name|  status|
# +---+-----+--------+
# |  1|Alice|  active|   ← unchanged
# |  2|  Bob|inactive|   ← UPDATED ✅
# |  4| Dave|  active|   ← INSERTED ✅
# +---+-----+--------+
#                         ← Carol (id=3) DELETED ✅

# ─── Verify: one commit for the whole thing ────────
dt.history().select("version", "operation", "operationMetrics").show(truncate=False)
# +-------+---------+─────────────────────────────────────────────────┐
# |version|operation│ operationMetrics                                 │
# +-------+---------+─────────────────────────────────────────────────┤
# |   1   │ MERGE   │ numTargetRowsInserted=1                          │
# |       │         │ numTargetRowsUpdated=1                           │
# |       │         │ numTargetRowsDeleted=1                           │
# |   0   │ WRITE   │ numOutputRows=3                                  │
# +-------+---------+─────────────────────────────────────────────────┘
#
# 🎯 ONE atomic commit handled INSERT + UPDATE + DELETE together.
#    That's why MERGE is the most important Delta operation.

Answer First: Why it happens: Your source has 2+ rows with the same join key → Delta can't decide which one wins.

Memory Map: MERGE with Duplicate Keys in Source -> multiple rows share one target key -> engine cannot choose an update winner -> ranking or aggregation reduces the source first -> precommit uniqueness check prevents ambiguity [Delta_01_Complete_Guide.md:1965].

Gotcha 4: MERGE with Duplicate Keys in Source

Why it happens: Your source has 2+ rows with the same join key → Delta can't decide which one wins.

Fix:

from pyspark.sql.window import Window
from pyspark.sql.functions import row_number, desc

# Deduplicate source BEFORE merging
w = Window.partitionBy("id").orderBy(desc("updated_at"))
source_deduped = source_df.withColumn("rn", row_number().over(w)) \
                          .filter("rn = 1") \
                          .drop("rn")
# Now source has ONE row per id — merge will succeed

Interview trap: This is THE most common MERGE production bug. If you know this fix, you sound senior.

Databricks-routed Delta concepts: 04_ETL_Scenarios_and_Design

Q25: Scenario — Your MERGE statement takes 3 hours. How do you optimize it?

Simple Explanation: A slow MERGE usually means the target table is too large, not well organized, or the source is not pre-filtered. The fix is systematic: profile first, then apply targeted optimizations.

Answer:

Interview Tip: Walk through the diagnosis checklist step by step. Say "First I check the Spark UI, then I look at file count, then partition pruning, then data skipping. The fix depends on the bottleneck." Methodical > guessing.

What NOT to Say: "Just add more workers." More workers do not help if the bottleneck is data skew (one worker gets 80% of the data) or small files (10,000 files = 10,000 tasks of 1 KB each).

Databricks-routed Delta concepts: DB_02_ETL_Pipelines_Databricks

Q17: Scenario — Our MERGE on booking fact table takes 3 hours. How do you fix it?

Simple Explanation: This is a debugging scenario. The approach: first INVESTIGATE (why is it slow?), then FIX (apply optimizations in order of impact).

-- STEP 1: INVESTIGATE — check the table's health
DESCRIBE DETAIL fact_bookings;
-- Key things to look at:
-- numFiles: 50,000 → TOO MANY! Small file problem (should be ~500 files for 2 TB)
-- sizeInBytes: 2 TB → That's the data size. 50K files means avg file is only 40 MB (should be ~1 GB)

-- STEP 2: FIX (in order of impact — do #1 first, then check if it's fast enough)

-- Fix 1: Compact files (biggest impact if you have small file problem)
OPTIMIZE fact_bookings;
-- Combines 50,000 small files → ~500 large files
-- MERGE now creates ~500 tasks instead of 50,000 → much faster

-- Fix 2: Add partition column to MERGE ON clause (partition pruning)
MERGE INTO fact_bookings t USING staging s
ON t.booking_id = s.booking_id
   AND t.booking_date = s.booking_date;
-- booking_date = partition column → MERGE only scans today's data, not all 2 TB

-- Fix 3: Z-ORDER or Liquid Clustering on the merge key
OPTIMIZE fact_bookings ZORDER BY (booking_id);
-- Groups similar booking_ids together → data skipping works better during MERGE
-- OR for new tables: ALTER TABLE fact_bookings CLUSTER BY (booking_date, booking_id);

-- Fix 4: Switch to Photon runtime (3-5x faster MERGE, no code changes)
-- Just change the cluster runtime to Photon in the job configuration

-- Fix 5: Filter source to only merge rows that actually changed
-- (See Day 1, Q7 for the full technique)

-- Fix 6: Prevent future small files
ALTER TABLE fact_bookings SET TBLPROPERTIES (
    'delta.autoOptimize.optimizeWrite' = 'true',   -- Coalesce on write
    'delta.autoOptimize.autoCompact' = 'true'       -- Auto-compact after writes
);

Databricks-routed Delta concepts: DB_02_Quick_Recall

Databricks-routed Delta concepts: DB_04_Production_CICD_MockInterview

MOCK Q4: "Our booking fact table MERGE takes 3 hours. How would you optimize?"

Systematic approach (don't jump to solutions — investigate first):

1. DESCRIBE DETAIL → Check numFiles (small file problem?) and sizeInBytes
2. OPTIMIZE → Compact small files into ~1 GB files
3. Add partition column to MERGE ON clause → partition pruning
4. Z-ORDER on merge key → better data skipping
5. Filter source → only MERGE rows that actually changed
6. Enable Photon → 3-5x faster MERGE
7. Consider Liquid Clustering → automatic, incremental optimization
8. Enable autoOptimize for the future

Interview tip: Always start with diagnosis, not solutions. "Before optimizing, I'd check the table health: DESCRIBE DETAIL to see file count and size, then look at the Spark UI to see where the time is spent." Then walk through fixes in order of impact.

What NOT to say: "Just add more workers." That might help, but it doesn't address root causes like small files, missing partition pruning, or data skew. Also don't say "Rewrite the MERGE as DELETE + INSERT" — MERGE is the correct approach for upserts, and the optimization is in how you structure the ON clause and target table.

Routed Delta question-bank prompts — 20-merge-schema-evolution

6. What is schema enforcement in Delta Lake?

7. What is schema evolution and how do you enable it?

19. What are table constraints in Delta Lake (CHECK, NOT NULL)? > Declarative rules enforced at write time. NOT NULL prevents null values in a column. CHECK constraints enforce arbitrary conditions (e.g., CHECK (fare > 0)). Writes that violate constraints are rejected.

8. Compare schema enforcement vs schema evolution — give an example where each is appropriate.

6. Explain how MERGE INTO works internally. What are the performance implications of a full table scan in MERGE?

12. Explain the difference between Copy-on-Write and Merge-on-Read in Delta Lake. > Key points: Copy-on-Write (default pre-Deletion Vectors): UPDATE/DELETE rewrites the entire affected Parquet file. Fast reads, slow writes. Merge-on-Read (with Deletion Vectors): marks rows as deleted in a bitmap, reads merge the bitmap at read time. Fast writes, slightly slower reads. Periodic compaction merges the deletions. Choose based on read-vs-write frequency.

Delta Time Travel, CDF, and Row-Level Features

Answer First: Time travel reconstructs retained snapshots, while Change Data Feed exposes committed row changes for downstream incremental processing; both depend on retained history.

Memory Map: commit history -> retained files -> snapshot or row changes -> downstream recovery.

SECTION 3: TIME TRAVEL & RECOVERY

Answer First: Because Delta keeps a diary of every change (the transaction log), you can "rewind" and see your data at any previous point.

Memory Map: time travel work? Show all query methods -> version or timestamp resolves a historical snapshot -> checkpoint plus later actions reconstruct active files -> reader scans that immutable state -> retention determines continued availability [03_Delta_Lake_and_Lakehouse.md:393].

Q13: How does time travel work? Show all query methods.

Simple Explanation: Because Delta keeps a diary of every change (the transaction log), you can "rewind" and see your data at any previous point. Like pressing rewind on a video — "what did this table look like 3 days ago?" This works because old Parquet files are kept around (until VACUUM deletes them).

Answer:

-- By version number
SELECT * FROM orders VERSION AS OF 5;
SELECT * FROM orders@v5;

-- By timestamp
SELECT * FROM orders TIMESTAMP AS OF '2025-01-15 10:30:00';

-- Check history first
DESCRIBE HISTORY orders;

-- Restore entire table
RESTORE TABLE orders TO VERSION AS OF 5;
RESTORE TABLE orders TO TIMESTAMP AS OF '2025-01-15';

PySpark:

# By version
df = spark.read.format("delta").option("versionAsOf", 5).load(path)

# By timestamp
df = spark.read.format("delta").option("timestampAsOf", "2025-01-15").load(path)

Answer First: Normally, deleting 1 row from a 1 GB file means rewriting the ENTIRE 1 GB file. That's like reprinting a 500-page book because of one typo. Deletion Vectors just stick a Post-it note on the page saying "ignore this line." The actual reprint happens later during OPTIMIZE.

Memory Map: Deletion Vectors? How do they improve UPDATE/DELETE performance -> delete writes row-position markers instead of replacing large files -> scan combines markers with columnar data -> write latency falls while read work can rise -> compaction later removes marked rows [03_Delta_Lake_and_Lakehouse.md:541].

Q17: What are Deletion Vectors? How do they improve UPDATE/DELETE performance?

Simple Explanation: Normally, deleting 1 row from a 1 GB file means rewriting the ENTIRE 1 GB file. That's like reprinting a 500-page book because of one typo. Deletion Vectors just stick a Post-it note on the page saying "ignore this line." The actual reprint happens later during OPTIMIZE.

Answer: Instead of rewriting entire Parquet files for DELETE/UPDATE/MERGE, deletion vectors mark individual rows as deleted in a separate lightweight file.

Without deletion vectors:

• DELETE 1 row from a 1 GB Parquet file → rewrite entire 1 GB file
• MERGE affecting 100 files → rewrite all 100 files

With deletion vectors:

• DELETE 1 row → write a tiny deletion vector file (~bytes)
• Reads filter out deleted rows using the deletion vector
• Physical rewrite is deferred to next OPTIMIZE

-- Enable deletion vectors
ALTER TABLE my_table SET TBLPROPERTIES ('delta.enableDeletionVectors' = 'true');

Trade-off: Slightly slower reads (must apply deletion vectors) but much faster writes. OPTIMIZE reclaims space.

Answer First: A deep clone copies table data and metadata into an independent target, while a shallow clone initially references source data files. Retention and source-lifecycle requirements determine whether that dependency is safe.

Memory Map: clone operations? Explain deep clone vs shallow clone -> source snapshot fixes the starting state -> shallow reference or deep copy chooses ownership -> target inherits dependency or independence -> source-retention test reveals lifecycle [03_Delta_Lake_and_Lakehouse.md:617].

Q20: What are clone operations? Explain deep clone vs shallow clone.

Answer:

-- Deep clone (full copy)
CREATE TABLE orders_backup DEEP CLONE orders;

-- Shallow clone (metadata only)
CREATE TABLE orders_test SHALLOW CLONE orders;

-- Incremental deep clone (only new changes since last clone)
CREATE TABLE orders_backup DEEP CLONE orders;  -- subsequent runs are incremental

Answer First: Delta Sharing is an open protocol for secure data sharing across organizations.

Memory Map: Delta Sharing? How does it work -> delta sharing it work defines provider ownership and recipient access semantics -> provider selects governed objects -> share grants recipient access -> credentialed client reads current data -> audit and revocation preserve provider control [03_Delta_Lake_and_Lakehouse.md:767].

Q26: What is Delta Sharing? How does it work?

Answer: Delta Sharing is an open protocol for secure data sharing across organizations.

How it works:

1. Provider shares a Delta table via a Delta Sharing Server
2. Provider generates a sharing profile (JSON with endpoint + credentials)
3. Recipient uses any client (Spark, pandas, Power BI) to read shared data
4. Data is read directly from the provider's storage — no copying
5. Recipient gets read-only access with the provider's access controls

# Recipient reads shared data
df = spark.read.format("deltaSharing").load("profile.json#share.schema.table")

Key benefits:

• No data copying (cost-effective)
• Open protocol (not Databricks-specific)
• Audit logging on provider side
• Fine-grained access control

Answer First: Normally, when you DELETE or UPDATE even a single row in a Parquet file, Delta has to rewrite the ENTIRE file. If the file is 1 GB, Delta writes a new 1 GB file just to remove one row. This is very expensive.

Memory Map: Deletion Vectors -> bitmap-like metadata identifies removed positions -> original data file remains physically present -> compatible readers suppress marked records -> rewrite maintenance eventually materializes changes [DB_01_Delta_Lake_Deep_Dive.md:463].

Q11: What are Deletion Vectors?

Simple Explanation: Normally, when you DELETE or UPDATE even a single row in a Parquet file, Delta has to rewrite the ENTIRE file. If the file is 1 GB, Delta writes a new 1 GB file just to remove one row. This is very expensive.

Deletion Vectors solve this by creating a tiny separate file that says "row #47 in file X is deleted." The original file stays untouched. When reading, Delta checks the deletion vector and skips that row. The actual file rewrite happens later during OPTIMIZE (not immediately).

Real-world analogy: Instead of reprinting an entire 500-page book because of one typo, you just stick a Post-it note on the page saying "ignore this line." The actual reprint happens later when convenient.

• Without Deletion Vectors: DELETE 1 row from 1 GB file → rewrite entire 1 GB file (slow)
• With Deletion Vectors: DELETE 1 row → write tiny marker file (~bytes) (fast!)
• Cleanup: OPTIMIZE will do the actual file rewrite later

-- Enable deletion vectors on a table
ALTER TABLE bookings SET TBLPROPERTIES (
    'delta.enableDeletionVectors' = 'true'    -- Tells Delta: use DV for this table
);
-- After this, DELETEs and UPDATEs become much faster
-- Reads are slightly slower (must check deletion vectors) — but usually worth it

Trade-off: Writes get much faster, reads get slightly slower. Run OPTIMIZE periodically to clean up.

New in Delta 4.1 (2025): You can enable deletion vectors without blocking concurrent writes (conflict-free enablement).

SECTION 4: TIME TRAVEL & RECOVERY (30 min)

Q13: What is Time Travel? How to query old versions of a table?

Simple Explanation: Because Delta keeps a log of every change (the transaction log), you can "go back in time" and see what the data looked like at any previous point. This is called Time Travel.

Why is it useful?

• Debugging: "The report showed wrong numbers yesterday — let me check yesterday's data"
• Recovery: "Someone accidentally deleted 1000 bookings — let me restore them"
• Auditing: "What did the passenger table look like before the migration?"

-- METHOD 1: Query by version number
-- (Every commit gets a version: 0, 1, 2, 3, ...)
SELECT * FROM bookings VERSION AS OF 5;     -- See table as it was at version 5
SELECT * FROM bookings@v5;                   -- Shorthand for the same thing

-- METHOD 2: Query by timestamp
-- (Go back to a specific date/time)
SELECT * FROM bookings TIMESTAMP AS OF '2026-03-15 10:30:00';

-- See full history of all changes
DESCRIBE HISTORY bookings;
-- Shows: version, timestamp, operation (INSERT/DELETE/MERGE), user, metrics

-- RESTORE: Roll back the entire table to a previous version
RESTORE TABLE bookings TO VERSION AS OF 5;
-- WARNING: This creates a NEW version (not destructive) — you can undo the restore too!

Limits to remember:

• Default data retention: 7 days — can't time travel beyond this (VACUUM deletes old files)
• Default log retention: 30 days — DESCRIBE HISTORY works for 30 days
• VACUUM breaks time travel for vacuumed versions

SECTION 4: TIME TRAVEL & RECOVERY

🧠 Memory Map: Time Travel

SECTION 3: Deletion Vectors -- Internal Mechanics

Answer First: Deletion vectors mark removed row positions without immediately rewriting the containing Parquet file. Reads apply the bitmap, and later maintenance can materialize the change by rewriting files.

Memory Map: Deletion Vectors? How do they work internally -> protocol feature enables row-position deletion metadata -> transaction action references the marker -> snapshot reader applies it to the referenced file -> reorganization can purge obsolete rows [DB_06_Delta_Lake_Advanced_Masterclass.md:304].

Q7: What are Deletion Vectors? How do they work internally?

Answer:

The problem Deletion Vectors solve:

Without Deletion Vectors (traditional Delta behavior):

With Deletion Vectors:

Internal structure of a Deletion Vector:

How reads work with Deletion Vectors:

Answer First: Interview insight: "Deletion Vectors trade write performance for read performance. They make writes fast (no rewrite) but reads slightly slower (bitmap filtering). OPTIMIZE is the mechanism that rebalances -- it purges DVs by rewriting files without the deleted rows."

Memory Map: the performance implications of Deletion Vectors? When do they help vs hurt -> avoided file rewrites accelerate sparse mutations -> extra metadata adds read-side filtering -> dense changes can erase the benefit -> workload and compaction metrics decide suitability [DB_06_Delta_Lake_Advanced_Masterclass.md:367].

Q8: What are the performance implications of Deletion Vectors? When do they help vs hurt?

Answer:

When DVs help dramatically (the sweet spot):

When DVs accumulate and hurt reads:

How OPTIMIZE purges Deletion Vectors:

Interview insight: "Deletion Vectors trade write performance for read performance. They make writes fast (no rewrite) but reads slightly slower (bitmap filtering). OPTIMIZE is the mechanism that rebalances -- it purges DVs by rewriting files without the deleted rows."

Table property to enable/disable:

-- Enable deletion vectors (default in Databricks Runtime 14.x+)
ALTER TABLE my_table SET TBLPROPERTIES ('delta.enableDeletionVectors' = true);

-- Check if DVs are enabled
DESCRIBE DETAIL my_table;
-- Look for 'deletionVectors' in table properties

SECTION 4: Change Data Feed (CDF) -- CDC with Delta Lake

Answer First: Change Data Feed (CDF) is a Delta Lake feature that records what changed (not just the final state) for each commit. It captures row-level changes with change types: INSERT, UPDATE (pre-image and post-image), and DELETE.

Memory Map: Change Data Feed? How does it work internally -> change data feed it work internally defines a bounded change-consumption path -> enabled version range selects commits -> typed row changes carry commit metadata -> consumer checkpoints progress -> source-to-sink reconciliation proves delivery [DB_06_Delta_Lake_Advanced_Masterclass.md:476].

Q10: What is Change Data Feed? How does it work internally?

Answer:

Change Data Feed (CDF) is a Delta Lake feature that records what changed (not just the final state) for each commit. It captures row-level changes with change types: INSERT, UPDATE (pre-image and post-image), and DELETE.

Enabling CDF:

-- On a new table:
CREATE TABLE bookings (...) USING DELTA
TBLPROPERTIES ('delta.enableChangeDataFeed' = true);

-- On an existing table:
ALTER TABLE bookings SET TBLPROPERTIES ('delta.enableChangeDataFeed' = true);
-- NOTE: CDF only captures changes AFTER it is enabled. It is NOT retroactive.

What CDF records internally:

When you run: UPDATE bookings SET status='CANCELLED' WHERE id='ABC123'

Without CDF:
- Delta just rewrites the file with the new value
- No record of what the old value was
- Transaction log shows: remove old_file, add new_file

With CDF:
- Delta writes the change to a special _change_data/ directory:

bookings/
├── _delta_log/
│   └── 00000000000000000042.json
├── _change_data/                          <-- CDF data lives here
│   └── cdc-00000-42.snappy.parquet       <-- Change records for commit 42
│       Contains:
│       ┌──────────┬──────────┬───────────────────┬──────────────────┐
│       │ id       │ status   │ _change_type      │ _commit_version  │
│       ├──────────┼──────────┼───────────────────┼──────────────────┤
│       │ ABC123   │ ACTIVE   │ update_preimage   │ 42               │
│       │ ABC123   │ CANCELLED│ update_postimage  │ 42               │
│       └──────────┴──────────┴───────────────────┴──────────────────┘
├── part-00000-abc123.snappy.parquet
└── ...

The four change types:

Answer First: Change Data Feed emits row-level inserts, updates, and deletes for enabled table versions so downstream jobs can process only committed changes. Consumers must track versions and stay within the table’s retained history.

Memory Map: you read Change Data Feed? What are the query patterns -> batch reader requests starting and ending versions -> streaming reader advances from a checkpointed offset -> commit metadata labels each emitted change -> consumer reconciliation protects progress [DB_06_Delta_Lake_Advanced_Masterclass.md:533].

Q11: How do you read Change Data Feed? What are the query patterns?

Answer:

# Method 1: Read changes between two versions
changes_df = (spark.read
    .format("delta")
    .option("readChangeFeed", "true")
    .option("startingVersion", 10)
    .option("endingVersion", 20)
    .table("bookings")
)

# Method 2: Read changes from a specific timestamp
changes_df = (spark.read
    .format("delta")
    .option("readChangeFeed", "true")
    .option("startingTimestamp", "2026-03-01T00:00:00Z")
    .option("endingTimestamp", "2026-03-15T23:59:59Z")
    .table("bookings")
)

# Method 3: Streaming -- read changes as a continuous stream
stream_df = (spark.readStream
    .format("delta")
    .option("readChangeFeed", "true")
    .option("startingVersion", 0)
    .table("bookings")
)

# The DataFrame has extra columns:
# _change_type: insert, update_preimage, update_postimage, delete
# _commit_version: which version of the table this change belongs to
# _commit_timestamp: when the change was committed

Real-world CDC pipeline pattern:

# Propagate changes from bronze -> silver -> gold
# This is the pattern interviewers want to see

# 1. Read changes from the bronze layer since last processed version
bronze_changes = (spark.read.format("delta")
    .option("readChangeFeed", "true")
    .option("startingVersion", last_processed_version)
    .table("bronze.raw_bookings")
)

# 2. Apply business logic (only process inserts and post-images)
silver_changes = (bronze_changes
    .filter("_change_type != 'update_preimage'")  # Don't need old values for silver
    .withColumn("processed_at", current_timestamp())
    .withColumn("is_deleted", col("_change_type") == "delete")
)

# 3. MERGE into silver table
silver_changes.createOrReplaceTempView("changes")
spark.sql("""
    MERGE INTO silver.bookings t
    USING changes s ON t.booking_id = s.booking_id
    WHEN MATCHED AND s.is_deleted = true THEN DELETE
    WHEN MATCHED THEN UPDATE SET *
    WHEN NOT MATCHED AND s.is_deleted = false THEN INSERT *
""")

Answer First: Change Data Feed emits row-level inserts, updates, and deletes for enabled table versions so downstream jobs can process only committed changes. Consumers must track versions and stay within the table’s retained history.

Memory Map: the gotchas with Change Data Feed -> retention can remove requested history -> initial snapshot semantics may surprise consumers -> updates emit preimage and postimage rows -> durable checkpoint and idempotent sink prevent gaps [DB_06_Delta_Lake_Advanced_Masterclass.md:603].

Q12: What are the gotchas with Change Data Feed?

Answer -- interviewers love these:

Gotcha 1: CDF is NOT retroactive

Gotcha 2: CDF and OPTIMIZE/VACUUM interaction

Gotcha 3: INSERT OVERWRITE does not generate CDF

-- This DOES generate CDF:
INSERT INTO bookings VALUES (...)
DELETE FROM bookings WHERE ...
UPDATE bookings SET ...
MERGE INTO bookings ...

-- This does NOT generate CDF (or generates it differently):
INSERT OVERWRITE bookings SELECT * FROM ...
-- INSERT OVERWRITE is treated as: delete all + insert all
-- CDF records: deletes for ALL old rows + inserts for ALL new rows
-- This can produce a MASSIVE CDF output for large tables

Gotcha 4: CDF storage overhead

Gotcha 5: Streaming CDF with schema evolution

SECTION 5: UniForm -- Universal Format

Answer First: The data science team uses Apache Spark with Iceberg.

Memory Map: Delta UniForm? Why was it created -> Delta commits remain the writable authority -> asynchronous conversion publishes compatible metadata -> read-only Iceberg clients query the same data files -> converted Delta version reveals metadata freshness [DB_06_Delta_Lake_Advanced_Masterclass.md:663].

Q13: What is Delta UniForm? Why was it created?

Answer:

The problem: Your organization uses Delta Lake in Databricks, but:

• The data science team uses Apache Spark with Iceberg
• The analytics team uses Presto/Trino with Hudi
• A partner company uses Snowflake which reads Iceberg
• Your streaming team uses Flink which supports Iceberg natively

Each team wants to read the SAME data but each requires a different table format's metadata. Without UniForm, you would need to maintain multiple copies of the same data in different formats.

UniForm solution: Delta Lake writes its native Delta metadata AND simultaneously generates metadata compatible with other formats (Iceberg and Hudi).

How to enable UniForm:

-- Enable Iceberg compatibility:
CREATE TABLE my_table (...) USING DELTA
TBLPROPERTIES (
    'delta.universalFormat.enabledFormats' = 'iceberg'
);

-- Enable on existing table:
ALTER TABLE my_table SET TBLPROPERTIES (
    'delta.universalFormat.enabledFormats' = 'iceberg'
);

-- Enable both Iceberg and Hudi:
ALTER TABLE my_table SET TBLPROPERTIES (
    'delta.universalFormat.enabledFormats' = 'iceberg,hudi'
);

Answer First: UniForm keeps Delta as the writable authority and asynchronously generates compatible Iceberg or Hudi metadata after Delta commits. External Iceberg clients are read-only, converted metadata can lag the latest Delta version, and some Delta features are not representable through every external reader.

Memory Map: the limitations and gotchas of UniForm -> Delta transaction commits successfully before conversion -> asynchronous generation can group or lag commits -> read-only clients consume only supported Iceberg metadata -> converted_delta_version and manual sync diagnose freshness [DB_06_Delta_Lake_Advanced_Masterclass.md:713].

Q14: What are the limitations and gotchas of UniForm?

Answer:

Limitation 1: Write overhead

Limitation 2: Feature asymmetry

Not all Delta features have equivalents in Iceberg/Hudi:

Feature                      | Delta | Iceberg via UniForm
─────────────────────────────┼───────┼────────────────────
Deletion Vectors             | Yes   | Yes (mapped to Iceberg delete files)
Liquid Clustering            | Yes   | Partially (Iceberg sees partitioning)
Change Data Feed             | Yes   | No (CDF is Delta-specific)
Time Travel (arbitrary)      | Yes   | Limited (Iceberg snapshots only)
Column Mapping               | Yes   | Yes
Schema Evolution             | Yes   | Yes (with some type limitations)
CHECK Constraints            | Yes   | No
Generated Columns            | Yes   | No

Limitation 3: Catalog requirements

Limitation 4: One-way synchronization

Interview question: "If your company uses Databricks but a partner uses Snowflake, how do you share data?"

• Answer 1 (best): Enable UniForm with Iceberg format. Snowflake reads the Iceberg metadata natively. Zero data duplication.
• Answer 2: Delta Sharing (share Delta tables directly -- Snowflake has a Delta Sharing connector).
• Answer 3 (worst): Export to Parquet files. Manual, error-prone, not real-time.

Q28: What is the difference between Shallow Clone and Deep Clone?

Answer:

SECTION 6: TIME TRAVEL, CLONES, AND RECOVERY

Q18 — Time Travel — Query any version of your table

Question: "How does time travel work? Show me all the ways to use it."

-- METHOD 1: Query by version number
SELECT * FROM orders VERSION AS OF 5;
SELECT * FROM orders@v5;                   -- Shorthand

-- METHOD 2: Query by timestamp
SELECT * FROM orders TIMESTAMP AS OF '2026-03-15 10:30:00';

-- See full history of changes
DESCRIBE HISTORY orders;
-- Shows: version, timestamp, operation, user, metrics

-- RESTORE: Roll back the entire table to a previous version
RESTORE TABLE orders TO VERSION AS OF 5;
-- Creates a NEW version (non-destructive) — you can undo the restore too!

-- Compare two versions
SELECT * FROM orders@v10 EXCEPT ALL SELECT * FROM orders@v5;
-- Shows rows that exist in v10 but not v5

Limits:

• Data retention: 7 days default (VACUUM deletes files older than this)
• Log retention: 30 days default (DESCRIBE HISTORY works for 30 days)
• After VACUUM, time travel is broken for vacuumed versions

Interview Tip: "Time travel works because Delta keeps old Parquet files around. VACUUM is what deletes them. So time travel range = VACUUM retention period."

Q20 — Deep Clone vs Shallow Clone

Question: "What are clones? When would you use deep vs shallow?"

-- Deep clone: Full independent copy
CREATE TABLE orders_backup DEEP CLONE orders;
-- Subsequent runs are INCREMENTAL (only copies changes since last clone)

-- Shallow clone: Fast reference copy
CREATE TABLE orders_test SHALLOW CLONE orders;
-- Perfect for testing — make changes without affecting production
-- WARNING: If source is VACUUMed, shallow clone may break!

SECTION 7: SPECIAL FEATURES

Answer First: "CDF is essential for incremental ETL in medallion architecture. Instead of reprocessing all Silver data to update Gold, we read only the changes. This reduces pipeline runtime from hours to minutes."

Memory Map: Change Data Feed (CDF) — Track row-level changes -> table property enables change recording -> each commit publishes typed row changes -> consumer reads a bounded version range -> checkpoint records downstream progress [Delta_01_Complete_Guide.md:1038].

Q21

Question: "How do you track what rows changed in a Delta table? How does downstream consume only the changes?"

-- Step 1: Enable Change Data Feed on the table
ALTER TABLE orders SET TBLPROPERTIES ('delta.enableChangeDataFeed' = 'true');

-- Step 2: Read only the changes between two versions
SELECT * FROM table_changes('orders', 5, 10);
-- Returns ALL changes between version 5 and 10 with metadata:

What CDF returns:

Why CDF matters for data engineering:

PySpark to read changes:

# Read changes since version 5
changes_df = spark.read.format("delta") \
    .option("readChangeFeed", "true") \
    .option("startingVersion", 5) \
    .table("orders")

# Read changes since a timestamp
changes_df = spark.read.format("delta") \
    .option("readChangeFeed", "true") \
    .option("startingTimestamp", "2026-03-15") \
    .table("orders")

Interview Tip: "CDF is essential for incremental ETL in medallion architecture. Instead of reprocessing all Silver data to update Gold, we read only the changes. This reduces pipeline runtime from hours to minutes."

Answer First: Deletion vectors record removed row positions without immediately rewriting the containing Parquet file, reducing write amplification for selective updates and deletes. Readers apply the vector until later maintenance rewrites the file.

Memory Map: Deletion Vectors — Faster DELETEs and UPDATEs -> sparse mutation records removed row positions -> active file stays in the snapshot -> scan filters those positions at read time -> optimization rewrites data when beneficial [Delta_01_Complete_Guide.md:1101].

Q22 — Deletion Vectors — Faster DELETEs and UPDATEs

Question: "How do Deletion Vectors improve UPDATE/DELETE performance?"

Without Deletion Vectors:

With Deletion Vectors:

-- Enable deletion vectors
ALTER TABLE orders SET TBLPROPERTIES (
    'delta.enableDeletionVectors' = 'true'
);

Trade-offs:

Interview Tip: "Deletion Vectors change the write pattern from copy-on-write to a more efficient mark-and-sweep approach. The actual file rewrite is deferred to OPTIMIZE, which runs during off-peak hours."

Answer First: No data copying — recipient reads directly from your storage.

Memory Map: Delta Sharing — Share data without copying -> provider grants governed assets without copying files -> recipient uses a credentialed sharing client -> revocation remains under provider control -> audit events record consumption [Delta_01_Complete_Guide.md:1150].

Q23 — Delta Sharing — Share data without copying

Question: "How can you share Delta tables with external organizations without copying data?"

How it works:

Provider (your org)                     Recipient (partner org)
┌─────────────────┐                    ┌─────────────────┐
│ Delta Table on  │                    │ Any client:     │
│ your storage    │───── REST API ────→│ - Spark         │
│ (S3/ADLS/GCS)   │   (open protocol) │ - pandas        │
└─────────────────┘                    │ - Power BI      │
       ↑                               │ - Databricks    │
  Data stays HERE                      └─────────────────┘
  (never copied)                        Reads directly from
                                        your storage

# Recipient reads shared data — one line!
df = spark.read.format("deltaSharing").load("profile.json#share.schema.table")

Key benefits:

• No data copying — recipient reads directly from your storage
• Open protocol — not locked to Databricks (works with any client)
• Provider controls access — revoke anytime, audit all reads
• Live data — recipient always sees the latest version

Answer First: Problem: Different tools expect different table formats.

Memory Map: UniForm — One table, all formats -> Delta snapshot remains the writable authority -> generated Iceberg or Hudi metadata exposes shared files -> foreign readers consume the compatible view -> validation protects cross-engine consistency [Delta_01_Complete_Guide.md:1181].

Q24 — UniForm — One table, all formats

Question: "What is UniForm? Why does it matter?"

Problem: Different tools expect different table formats:

• Databricks → Delta Lake
• Snowflake → Iceberg
• Trino/Presto → Hudi or Iceberg
• BigQuery → Iceberg

UniForm solution: Write once as Delta, automatically generates Iceberg and Hudi metadata.

-- Enable UniForm on a table
ALTER TABLE orders SET TBLPROPERTIES (
    'delta.universalFormat.enabledFormats' = 'iceberg'
);
-- Now this Delta table can be read as an Iceberg table by Snowflake, Trino, etc.
-- Data is NOT duplicated — only metadata is generated in Iceberg format

Why it matters:

• One copy of data, readable by ALL engines
• No ETL to copy data between formats
• Eliminates "format wars" (Delta vs Iceberg vs Hudi)

Answer First: Now you've SEEN the transaction log grow. No video needed.

Memory Map: Step 6 — TIME TRAVEL (go back to BEFORE the update) -> historical version precedes the update -> versionAsOf reads its active files -> comparison shows the prior row state -> current table remains unchanged [Delta_01_Complete_Guide.md:1610].

Step 6 — TIME TRAVEL (go back to BEFORE the update)

# Read version 0 (the original, before Alice got updated)
df_before = spark.read.format("delta").option("versionAsOf", 0).load(path)
df_before.show()

# EXPECTED OUTPUT (Alice is still 100.0):
# +--------+--------+------+
# |order_id|customer|amount|
# +--------+--------+------+
# |       1|   Alice| 100.0|   ← original value!
# |       2|     Bob| 200.0|
# |       3|   Carol| 300.0|
# +--------+--------+------+

# Now read current version (version 1)
df_now = spark.read.format("delta").load(path)
df_now.show()

# EXPECTED OUTPUT:
# +--------+--------+------+
# |order_id|customer|amount|
# +--------+--------+------+
# |       1|   Alice| 150.0|   ← updated value
# |       2|     Bob| 200.0|
# |       3|   Carol| 300.0|
# +--------+--------+------+
#
# 🎯 Same table. Two different versions. This is Time Travel.

Now you've SEEN the transaction log grow. No video needed.

VISUAL ANIMATION 2 — Time Travel Mechanics

DELTA TABLE /customers/ — Timeline of Operations:

v0: CREATE          v1: INSERT 100      v2: UPDATE 20      v3: DELETE 5
    │                   │                   │                   │
    ▼                   ▼                   ▼                   ▼
┌─────────┐         ┌─────────┐         ┌─────────┐         ┌─────────┐
│ empty   │         │ file-A  │         │ file-A  │ removed │ file-A  │ removed
│         │    →    │         │    →    │ file-B  │         │ file-B  │
│         │         │         │         │ file-C  │ added   │ file-C  │
│         │         │         │         │         │         │ file-D  │ added
└─────────┘         └─────────┘         └─────────┘         └─────────┘
 _delta_log          _delta_log          _delta_log          _delta_log
 /0.json             /0.json             /0.json             /0.json
                     /1.json             /1.json             /1.json
                                         /2.json             /2.json
                                                             /3.json

TIME TRAVEL QUERIES:

  SELECT * ... VERSION AS OF 0  →  reads NO files        (empty table)
  SELECT * ... VERSION AS OF 1  →  reads file-A          (100 rows)
  SELECT * ... VERSION AS OF 2  →  reads file-B, file-C  (80 old + 20 updated)
  SELECT * ... VERSION AS OF 3  →  reads file-B, file-C,  (75 rows after delete)
                                         file-D

🧠 KEY INSIGHT:
   Delta doesn't "roll back" anything for Time Travel.
   It just reads the OLD files that are STILL on disk
   (because VACUUM hasn't removed them yet).
   Time Travel = reading old files according to an old commit.

Databricks-routed Delta concepts: 04_ETL_Scenarios_and_Design

Answer First: CDC captures changes FROM an external database INTO Delta. CDF captures changes that happen WITHIN a Delta table and makes them available to downstream consumers. They solve different problems.

Memory Map: Delta Lake Change Data Feed (CDF)? How is it different from CDC -> source CDC reads an external database log -> table change feed exposes mutations already committed in Delta -> both carry ordered operation metadata -> origin and consumer boundary distinguish them [04_ETL_Scenarios_and_Design.md:565].

Q9: What is Delta Lake Change Data Feed (CDF)? How is it different from CDC?

Simple Explanation: CDC captures changes FROM an external database INTO Delta. CDF captures changes that happen WITHIN a Delta table and makes them available to downstream consumers. They solve different problems.

Analogy: CDC is like a news reporter covering events at City Hall (external source) and writing articles (into your newspaper). CDF is like the newspaper's own internal changelog — when an article gets edited, CDF tells the website team "hey, this article was updated, here is the before and after."

Enable CDF:

ALTER TABLE my_table SET TBLPROPERTIES (delta.enableChangeDataFeed = true);

Read changes:

CDF columns added automatically:

Interview Tip: CDF is the glue between medallion layers. Say "I enable CDF on Silver so that Gold only processes the changed rows, not the entire Silver table."

What NOT to Say: Confusing CDC and CDF. They sound similar but are fundamentally different. CDC = external to Delta. CDF = within Delta.

Databricks-routed Delta concepts: 05_Performance_Tuning_and_Production

Answer First: GDPR requires that when a customer requests deletion, ALL their data must be permanently removed — including historical versions. Delta Lake retains history by default, so you must DELETE the data and then VACUUM to physically remove old files that contain the deleted records.

Memory Map: physical erasure request -> DELETE publishes a new logical state -> retention-safe purge removes unreferenced files -> storage inspection and audit record prove completion [05_Performance_Tuning_and_Production.md:1231].

Q20: How do you handle GDPR "right to be forgotten" in Delta Lake?

Simple Explanation: GDPR requires that when a customer requests deletion, ALL their data must be permanently removed — including historical versions. Delta Lake retains history by default, so you must DELETE the data and then VACUUM to physically remove old files that contain the deleted records.

Analogy: Imagine writing someone's name in a notebook with carbon copies. Deleting the original page (DELETE) is not enough — you must also destroy all the carbon copies (VACUUM). Only then is the data truly gone.

Technical depth:

# Challenge: Delta Lake retains history. GDPR requires permanent deletion.

# Step 1: Delete the customer's data from ALL tables
customer_id_to_delete = "CUST-12345"

# Delete from all relevant tables (must cover EVERY table with this customer's data)
spark.sql(f"DELETE FROM dim_customer WHERE customer_id = '{customer_id_to_delete}'")
spark.sql(f"DELETE FROM fact_orders WHERE customer_id = '{customer_id_to_delete}'")
spark.sql(f"DELETE FROM silver_interactions WHERE customer_id = '{customer_id_to_delete}'")

# Step 2: VACUUM to physically remove old files (including pre-delete versions)
# Must set retention to 0 for GDPR compliance
spark.conf.set("spark.databricks.delta.retentionDurationCheck.enabled", "false")  # ← Disable safety check
spark.sql("VACUUM dim_customer RETAIN 0 HOURS")        # ← Delete ALL old files immediately
spark.sql("VACUUM fact_orders RETAIN 0 HOURS")
spark.sql("VACUUM silver_interactions RETAIN 0 HOURS")

# Step 3: Log the deletion for compliance (prove you did it)
spark.sql(f"""
    INSERT INTO gdpr_deletion_log VALUES
    ('{customer_id_to_delete}', current_timestamp(), 'completed',
     'tables: dim_customer, fact_orders, silver_interactions')
""")

# Alternative: Use pseudonymization instead of deletion
# Replace PII with hashed/random values — preserves analytics while removing identity
# This is often preferred because it doesn't break aggregate reports

Interview Tip: Mention the tension: "VACUUM with 0 hours breaks time travel and concurrent readers. In practice, I batch GDPR deletions monthly and run VACUUM during a maintenance window to minimize impact on production queries."

What NOT to Say: "Just DELETE the records." Without VACUUM, the old Parquet files still contain the customer's data — DELETE only updates the transaction log.

Databricks-routed Delta concepts: DB_02_ETL_Pipelines_Databricks

Answer First: People often confuse CDC and CDF. They sound similar but are different things.

Memory Map: Change Data Feed (CDF)? How is it different from CDC -> database capture transports upstream inserts updates and deletes -> table-native feed publishes internal commit changes -> downstream checkpoint consumes version ranges -> architecture chooses based on mutation origin [DB_02_ETL_Pipelines_Databricks.md:421].

Q8: What is Change Data Feed (CDF)? How is it different from CDC?

Simple Explanation: People often confuse CDC and CDF. They sound similar but are different things:

• CDC = Capturing changes from an external source system (Oracle → Kafka → Delta)
• CDF = Tracking changes that happen inside a Delta table (Delta table feature)

CDF (Change Data Feed) is a Delta Lake feature. When enabled, Delta records what changed in a table — every insert, update, delete — so downstream consumers can read ONLY the changes instead of the full table.

Real-world analogy:

• CDC = A reporter covering live events at the airport (captures external events)
• CDF = The airport's flight status board history (records what changed inside the airport system)

Why is CDF useful? Without CDF: Gold layer reads the ENTIRE Silver table every day (slow, wasteful). With CDF: Gold layer reads only the rows that changed since last run (fast, efficient).

-- Step 1: Enable CDF on a table
ALTER TABLE bookings SET TBLPROPERTIES ('delta.enableChangeDataFeed' = 'true');
-- After this, Delta starts tracking every change to this table

-- Step 2: Read changes between versions
SELECT * FROM table_changes('bookings', 5, 10);
-- Returns ONLY the rows that changed between version 5 and version 10
-- Much faster than reading the entire table!

-- Step 3: Read changes by timestamp
SELECT * FROM table_changes('bookings', '2026-03-01', '2026-03-15');
-- What changed in the bookings table during March 1-15?

-- Step 4: Streaming read of changes (for real-time downstream processing)
spark.readStream.format("delta") \
    .option("readChangeFeed", "true") \         -- Enable CDF reading
    .option("startingVersion", 5) \              -- Start from version 5
    .table("bookings")
-- This continuously reads new changes as they happen

CDF adds these extra columns to the output:

Use cases:

• Silver → Gold incremental: Gold layer reads only changed Silver rows (not the full table)
• Audit trail: "What changes were made to this booking last week?"
• Downstream sync: Push changes to another system (e.g., sync to a search index)

Databricks-routed Delta concepts: DB_02_Quick_Recall

-- Enable CDF on a table
ALTER TABLE bookings SET TBLPROPERTIES ('delta.enableChangeDataFeed' = true);
-- Read only changes since version 5
SELECT * FROM table_changes('bookings', 5);

Databricks-routed Delta concepts: DB_03_Azure_Platform_Governance

Answer First: GDPR (EU data protection law) gives every person the right to say: "Delete ALL my data from your systems." For a travel platform, this means: when a passenger requests deletion, we must remove their data from EVERY table — and prove it's really gone.

Memory Map: governed erasure workflow -> tags and lineage enumerate subject data -> accountable owners delete tables, exports, and backups -> request log records regulator-facing evidence [DB_03_Azure_Platform_Governance.md:606].

Q14: How do you handle GDPR "Right to Be Forgotten" in Delta Lake?

Simple Explanation: GDPR (EU data protection law) gives every person the right to say: "Delete ALL my data from your systems." For a travel platform, this means: when a passenger requests deletion, we must remove their data from EVERY table — and prove it's really gone.

The challenge with Delta Lake: Delta keeps history (time travel). Even after you DELETE a row, the old data is still in the old Parquet files. You must also VACUUM to physically remove those old files.

Two approaches:

Approach 1: Hard Delete (complete removal)

-- Step 1: Delete the passenger from ALL tables that contain their data
DELETE FROM dim_passenger WHERE passenger_id = 'PAX-12345';
-- Removes this passenger's row from the dimension table

DELETE FROM fact_bookings WHERE passenger_id = 'PAX-12345';
-- Removes all their bookings

DELETE FROM silver_interactions WHERE passenger_id = 'PAX-12345';
-- Removes all their interaction records

-- Step 2: VACUUM with 0 hours retention to physically remove old files
-- WARNING: This breaks time travel! But GDPR requires it.
SET spark.databricks.delta.retentionDurationCheck.enabled = false;
-- Must disable the safety check (Delta normally prevents 0-hour vacuum)

VACUUM dim_passenger RETAIN 0 HOURS;
-- Physically deletes ALL old files — data is truly gone
VACUUM fact_bookings RETAIN 0 HOURS;
VACUUM silver_interactions RETAIN 0 HOURS;

-- Step 3: Log the deletion for compliance audit
INSERT INTO gdpr_deletion_log VALUES (
    'PAX-12345',                     -- Which passenger was deleted
    current_timestamp(),             -- When it was deleted
    'completed',                     -- Status
    'tables: dim_passenger, fact_bookings, silver_interactions'  -- Which tables
);
-- This log PROVES to regulators that we deleted the data

Approach 2: Pseudonymization (preferred — keeps analytics working) Instead of deleting the entire row, replace PII with random/hashed values. The row stays for analytics, but the person can't be identified.

UPDATE dim_passenger SET
    full_name = 'REDACTED',                           -- Can't identify the person
    email = CONCAT(MD5(email), '@redacted.com'),      -- Hashed (one-way, can't reverse)
    phone = 'REDACTED',
    address = 'REDACTED'
WHERE passenger_id = 'PAX-12345';
-- The booking data still exists for analytics (revenue, route stats)
-- But the person's identity is completely removed

Interview tip: Mention BOTH approaches and when to use each. Hard delete = when regulation requires complete removal. Pseudonymization = when you need to keep analytics data but remove identity.

Databricks-routed Delta concepts: DB_03_Quick_Recall

Routed Delta question-bank prompts — 30-time-travel-cdf

8. What is Time Travel in Delta Lake? How do you query an older version?

9. What is the VACUUM command and what does it do?

10. What is the default retention period for VACUUM? > 7 days (168 hours). Files newer than 7 days are never deleted, ensuring active queries can complete. Override with VACUUM table RETAIN X HOURS.

16. What is Change Data Feed (CDF) in Delta Lake? > A feature that records row-level changes (insert, update_preimage, update_postimage, delete) for each commit. Downstream consumers read ONLY the changes instead of reprocessing the entire table. Enable with delta.enableChangeDataFeed = true.

20. What is the RESTORE command in Delta Lake? > Rolls back a Delta table to a previous version: RESTORE TABLE table TO VERSION AS OF 5. Creates a new commit that makes the table state identical to the specified version. Used for recovering from bad writes.

9. What happens if you run VACUUM with a retention of 0 hours? What are the risks?

15. Explain how Time Travel works internally — what is stored in each JSON commit file? > Key points: Each JSON commit file records: (1) add actions — new Parquet files added, (2) remove actions — files logically deleted, (3) metadata changes (schema, properties), (4) operation type and metrics. To read version N, Delta replays all commits from the last checkpoint up to N, building the set of active files. Old files remain on storage until VACUUM deletes them.

16. Compare Change Data Feed (CDF) vs reading the transaction log directly for CDC. > Key points: CDF provides a clean API: spark.read.option("readChangeFeed", "true").table() returning rows with _change_type (insert, update_preimage, update_postimage, delete). Reading the log directly requires parsing JSON files and reconstructing changes yourself — complex, error-prone, and unsupported. CDF is the right approach; it's purpose-built, handles edge cases, and provides pre/post images for updates.

15. What is the DESCRIBE HISTORY command used for? > Shows the commit history of a Delta table — who made each change, when, what operation (WRITE, MERGE, OPTIMIZE), and the version number. Used for auditing and time travel reference.

5. What is the difference between OPTIMIZE and VACUUM? Can you run them together?

Delta Performance and Maintenance

Answer First: Delta maintenance balances file size, data layout, skipping statistics, write amplification, retention, and concurrent-reader safety against measured workload evidence.

Memory Map: measure -> file layout -> compact/cluster -> retain history -> vacuum safely -> remeasure.

Current liquid clustering semantics

Answer First: Liquid clustering is incremental because OPTIMIZE rewrites data files as necessary to cluster records; defining or changing clustering keys does not continuously recluster every write.

Writes and updated rows are not automatically reclustered without OPTIMIZE. Predictive optimization can schedule that work for eligible Unity Catalog managed tables, but CLUSTER BY alone is a layout declaration, not a background maintenance loop. See the official liquid clustering guide.

Answer First: If someone asks for data from March 15, you skip ALL folders that don't include March.

Memory Map: file-level statistics and data skipping -> writer records minimum maximum and null counts -> query predicate rejects impossible ranges -> reader opens only candidate files -> scanned bytes prove pruning [03_Delta_Lake_and_Lakehouse.md:122].

Q4: Explain file-level statistics and data skipping.

Simple Explanation: Imagine 100 file folders, each labeled with a date range (Jan 1-15, Jan 16-31, Feb 1-15...). If someone asks for data from March 15, you skip ALL folders that don't include March. Delta does the same — each Parquet file has min/max statistics, and Delta skips files that can't contain matching rows.

Answer: Delta stores min/max statistics for the first 32 columns (by default) in the transaction log.

How data skipping works:

SELECT * FROM orders WHERE order_date = '2025-01-15'

1. Delta reads file statistics from the transaction log
2. For each file, checks: min(order_date) <= '2025-01-15' AND max(order_date) >= '2025-01-15'
3. Files where the condition is impossible are skipped entirely
4. Only matching files are read

Configuration: delta.dataSkippingNumIndexedCols (default 32)

Interview Tip: "Data skipping works BEST when data is sorted/clustered. That's why Z-ORDER and Liquid Clustering are so important — they make min/max ranges tight per file, so more files get skipped."

What NOT to Say: "Data skipping reads file headers" — No, stats are stored in the TRANSACTION LOG, not in the Parquet files themselves. Delta reads the log, not the data.

Answer First: OPTIMIZE rewrites and compacts active files to improve layout; VACUUM deletes unreferenced files older than the retention threshold. OPTIMIZE changes current physical organization, whereas VACUUM removes historical recovery files.

Memory Map: the difference between OPTIMIZE and VACUUM -> difference between optimize and vacuum sets one retention, safety, and deletion boundary -> unreferenced file ages past the policy -> safety check protects readers and history -> physical deletion removes the file -> history and storage checks confirm irreversibility [03_Delta_Lake_and_Lakehouse.md:149].

Q5: What is the difference between OPTIMIZE and VACUUM?

Simple Explanation: OPTIMIZE = Organizing your messy desk. 10,000 Post-it notes scattered everywhere → combine them into 10 neat notebooks. Easier to find things now! VACUUM = Throwing away old drafts you no longer need. Once thrown away, you can't go back to them.

Answer:

-- Compact files (target ~1 GB per file)
OPTIMIZE orders;

-- Compact + co-locate by column
OPTIMIZE orders ZORDER BY (customer_id);

-- Clean up old files (7-day retention)
VACUUM orders;

-- Clean up with custom retention
VACUUM orders RETAIN 168 HOURS;  -- 7 days

Interview Tip: "Always mention OPTIMIZE and VACUUM together — they're a pair. OPTIMIZE creates new files, VACUUM cleans up the old ones. Without VACUUM after OPTIMIZE, storage keeps growing."

What NOT to Say: "OPTIMIZE deletes old files" — No, OPTIMIZE is non-destructive. VACUUM is the one that deletes.

Answer First: Breaks ALL time travel — can't query any previous version

Memory Map: Can you run VACUUM with retention of 0 hours? What are the risks -> zero-hour policy makes every unreferenced file immediately eligible -> concurrent readers may still need those files -> deletion destroys history and recovery -> retention safety check blocks the unsafe request [03_Delta_Lake_and_Lakehouse.md:186].

Q6: Can you run VACUUM with retention of 0 hours? What are the risks?

Answer: Yes, but you must disable the safety check:

SET spark.databricks.delta.retentionDurationCheck.enabled = false;
VACUUM my_table RETAIN 0 HOURS;

Risks:

1. Breaks ALL time travel — can't query any previous version
2. Concurrent readers may fail — readers that started before VACUUM may reference deleted files
3. No recovery — deleted data is gone permanently

Rule: NEVER do this in production. Use default 7-day retention.

What NOT to Say: "I'll set retention to 0 to save storage" — This is a red flag for interviewers. Shows you don't understand the risk to concurrent readers and time travel.

SECTION 4: Z-ORDERING, LIQUID CLUSTERING, OPTIMIZATION

Answer First: Works great for dates, bad for user id (millions of tiny drawers).

Memory Map: Z-Ordering? How does it differ from partitioning -> z ordering it differ from partitioning chooses workload-specific layout keys and rewrite behavior -> measured filters select layout keys -> OPTIMIZE rewrites files as needed -> data locality improves skipping -> files and bytes scanned validate layout [03_Delta_Lake_and_Lakehouse.md:464].

Q15: What is Z-Ordering? How does it differ from partitioning?

Simple Explanation: Partitioning = Filing cabinet with one drawer per month. Need January data? Open only the January drawer. Works great for dates, bad for user_id (millions of tiny drawers). Z-Ordering = Within each drawer, sorting papers by customer name. Now you can quickly find "Customer X" without scanning the entire drawer.

Answer:

-- Partition by date (low cardinality), Z-order by customer_id (high cardinality)
CREATE TABLE orders (
    order_id LONG,
    customer_id LONG,
    order_date DATE,
    amount DECIMAL(10,2)
) PARTITIONED BY (order_date);

OPTIMIZE orders ZORDER BY (customer_id);

Z-ORDER best practices:

• Max 4 columns (effectiveness decreases with more)
• Choose columns in WHERE, JOIN, MERGE conditions
• High cardinality columns benefit most
• NOT idempotent — running again rewrites files

Answer First: Liquid Clustering (GA in Databricks) is the next-generation data layout that replaces both partitioning and Z-Ordering.

Memory Map: Liquid Clustering? How does it improve over Z-Ordering + Partitioning -> declared keys replace static directory partitions -> incremental rewrites cluster affected files -> key changes avoid full table recreation -> skipping metrics measure locality improvement [03_Delta_Lake_and_Lakehouse.md:499].

Q16: What is Liquid Clustering? How does it improve over Z-Ordering + Partitioning?

Answer: Liquid Clustering (GA in Databricks) is the next-generation data layout that replaces both partitioning and Z-Ordering.

-- Create with liquid clustering (replaces PARTITIONED BY + ZORDER)
CREATE TABLE orders (
    order_id LONG,
    customer_id LONG,
    order_date DATE,
    amount DECIMAL(10,2)
) CLUSTER BY (customer_id, order_date);

-- Change clustering keys without rewriting data
ALTER TABLE orders CLUSTER BY (order_date, region);

-- Trigger optimization
OPTIMIZE orders;

When to use what (2026 recommendation):

• New tables: Always use Liquid Clustering
• Existing tables with partitioning: Migrate to Liquid Clustering when possible
• Legacy tables: Continue with partitioning + Z-Ordering

Interview Tip: "For new tables, I always use CLUSTER BY instead of PARTITIONED BY. It's incremental, handles any cardinality, and I can change the clustering columns without rewriting the table."

What NOT to Say: "I'll partition by user_id" — High cardinality partitioning creates millions of tiny files. Use Liquid Clustering for high cardinality columns.

Answer First: Inspect effective properties and runtime support before changing them because some settings alter compatibility or history.

Memory Map: the most important Delta table properties -> effective properties control retention statistics and features -> protocol support determines valid values -> change may affect readers writers or maintenance -> detail and history verify the active configuration [03_Delta_Lake_and_Lakehouse.md:567].

Q18: What are the most important Delta table properties?

Answer:

ALTER TABLE my_table SET TBLPROPERTIES (
    -- Write optimization
    'delta.autoOptimize.optimizeWrite' = 'true',         -- Coalesce small files on write
    'delta.autoOptimize.autoCompact' = 'true',            -- Auto compaction after writes

    -- Change Data Feed
    'delta.enableChangeDataFeed' = 'true',                -- Track row-level changes

    -- Retention
    'delta.logRetentionDuration' = 'interval 30 days',    -- How long to keep commit logs
    'delta.deletedFileRetentionDuration' = 'interval 7 days',  -- VACUUM threshold

    -- Column mapping (enables column rename/drop)
    'delta.columnMapping.mode' = 'name',

    -- Deletion vectors (faster deletes)
    'delta.enableDeletionVectors' = 'true',

    -- Checkpoint interval
    'delta.checkpointInterval' = '10'
);

Answer First: Safe VACUUM policy starts from the longest reader and writer duration, preserves required recovery history, and rejects aggressive retention as a routine optimization.

Memory Map: Scenario — A Delta table has 10,000 small files (each <1 MB). Queries are slow. How do you fix this -> delta table has 10 000 small files each 1 mb queries slow fix this isolates the relevant fragmentation or maintenance cause -> file-count baseline exposes fragmentation -> compaction policy selects candidate files -> rewritten layout reduces overhead -> operation metrics and scan time prove benefit [03_Delta_Lake_and_Lakehouse.md:792].

Q27: Scenario — A Delta table has 10,000 small files (each <1 MB). Queries are slow. How do you fix this?

Answer:

-- Step 1: Compact files immediately
OPTIMIZE slow_table;                                    -- Merge into ~1 GB files
OPTIMIZE slow_table ZORDER BY (frequently_filtered_col); -- Plus data skipping

-- Step 2: Clean up old files
VACUUM slow_table RETAIN 168 HOURS;

-- Step 3: Prevent future small files
ALTER TABLE slow_table SET TBLPROPERTIES (
    'delta.autoOptimize.optimizeWrite' = 'true',     -- Coalesce on write
    'delta.autoOptimize.autoCompact' = 'true'         -- Auto compact after writes
);

-- Step 4: For streaming sources, increase trigger interval
# .trigger(processingTime="5 minutes")  -- Instead of "10 seconds"

-- Step 5: For new tables, use Liquid Clustering
-- CREATE TABLE ... CLUSTER BY (col)  -- Handles compaction automatically

Q4: What is data skipping? How do file-level statistics work?

Simple Explanation: When you query a Delta table, you don't want to read ALL the data files. That's wasteful. Delta stores min and max values for each column in each data file (inside the transaction log). When you run a query with a WHERE clause, Delta checks: "Can this file possibly contain matching rows?" If the answer is NO, it SKIPS the file entirely.

Real-world analogy: You have 100 file folders, each labeled with date ranges (Jan 1-15, Jan 16-31, Feb 1-15, ...). If someone asks for bookings on March 15, you skip ALL folders that don't include March 15. You only open the relevant folder.

-- Query: Find bookings on a specific date
SELECT * FROM bookings WHERE booking_date = '2026-03-15'

-- What Delta does behind the scenes:
-- File A: min(booking_date) = 2026-01-01, max = 2026-01-31
--         → SKIP! March 15 can't possibly be in this file
-- File B: min(booking_date) = 2026-03-01, max = 2026-03-31
--         → READ! March 15 might be in this file
-- File C: min(booking_date) = 2026-06-01, max = 2026-06-30
--         → SKIP! March 15 can't be here either