Delta Lake basics: create a table, append, MERGE, and use time travel to undo mistakes

Delta Lake is Parquet files plus a transaction log (_delta_log). The log gives you things plain Parquet cannot: atomic writes, updates and deletes, and the ability to read the table as it was yesterday. This post covers the five operations you will use every week.

#Create a table

SQL:

CREATE TABLE sales.orders (
  order_id   BIGINT,
  customer   STRING,
  amount     DECIMAL(10,2),
  order_date DATE
) USING DELTA;

PySpark, from a DataFrame:

from pyspark.sql import functions as F

df = spark.createDataFrame(
    [(1, "acme", 120.50, "2026-10-01"), (2, "globex", 80.00, "2026-10-01")],
    "order_id BIGINT, customer STRING, amount DOUBLE, order_date STRING",
).withColumn("order_date", F.to_date("order_date"))

df.write.format("delta").mode("overwrite").saveAsTable("sales.orders")

On Databricks, Delta is the default format, so USING DELTA is optional.

#Append

INSERT INTO sales.orders VALUES (3, 'initech', 42.00, DATE'2026-10-02');

new_rows.write.format("delta").mode("append").saveAsTable("sales.orders")

Delta checks the schema on write. Appending a DataFrame with an extra column fails unless you allow schema evolution:

new_rows.write.format("delta").mode("append").option("mergeSchema", "true").saveAsTable("sales.orders")

#Upsert with MERGE

MERGE updates rows that match and inserts the rest, in one atomic operation.

MERGE INTO sales.orders AS t
USING staging.orders_updates AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET t.amount = s.amount, t.customer = s.customer
WHEN NOT MATCHED THEN INSERT *;

from delta.tables import DeltaTable

target = DeltaTable.forName(spark, "sales.orders")
(target.alias("t")
   .merge(updates.alias("s"), "t.order_id = s.order_id")
   .whenMatchedUpdateAll()
   .whenNotMatchedInsertAll()
   .execute())

Warning

If the source has two rows with the same order_id, MERGE fails with a "multiple source rows matched" error. Deduplicate the source first (for example keep the latest by timestamp).

#Update and delete

UPDATE sales.orders SET amount = amount * 1.1 WHERE customer = 'acme';
DELETE FROM sales.orders WHERE order_date < DATE'2025-01-01';

#See what happened: history

DESCRIBE HISTORY sales.orders;

Each row is a version: who ran what operation, when, and metrics such as rows written. This is the first place to look when someone asks "who changed this table?".

DESCRIBE DETAIL sales.orders;   -- location, number of files, size

#Time travel

Read an older version, by number or timestamp:

SELECT * FROM sales.orders VERSION AS OF 3;
SELECT * FROM sales.orders TIMESTAMP AS OF '2026-10-28 09:00:00';

old = spark.read.option("versionAsOf", 3).table("sales.orders")

Compare versions to find what changed:

SELECT * FROM sales.orders VERSION AS OF 5
EXCEPT
SELECT * FROM sales.orders VERSION AS OF 4;

#Undo a mistake: RESTORE

Someone ran a DELETE without a WHERE clause at version 7:

RESTORE TABLE sales.orders TO VERSION AS OF 6;

RESTORE is itself a new version, so you can undo the restore too.

Caution

Time travel only works while the old data files still exist. VACUUM removes files older than the retention period (7 days by default). Never lower the retention just to save space unless you accept losing the ability to go back.

#Keep it healthy

OPTIMIZE sales.orders;                 -- compact small files
VACUUM sales.orders;                   -- remove unreferenced files older than retention

Run them on a schedule for tables that receive many small writes. On Databricks, predictive optimization can do this for Unity Catalog managed tables.

#Quick reference

#Prevent it next time

• Grant MODIFY on production tables to pipelines, not people.
• Keep default VACUUM retention (7 days or more) so time travel can rescue you.
• Before any manual UPDATE or DELETE, run the same WHERE clause as a SELECT COUNT(*).