Databricks DE · Topic 2.2

Writing to Delta: MERGE, OVERWRITE & CTAS

Four ways to write a Delta table — pick by how much you're changing: row-level (MERGE), full/partition reload (INSERT OVERWRITE), rebuild (CREATE OR REPLACE), or derive (CTAS). This deep dive walks each with the mechanism and the code.

MERGE = upsertCDC / SCD2replaceWhereAtomic writes

The one-liner

MERGE changes only matching rows (upsert); INSERT OVERWRITE swaps all/partition data; CREATE OR REPLACE rebuilds the table while keeping its name & history; CTAS makes a new table from a query. Use the smallest tool that fits the change.

📇 Analogy (a contacts app): MERGE = sync (update changed, add new, remove gone); INSERT OVERWRITE = wipe & paste a fresh export; CREATE OR REPLACE = reset the whole app; CTAS = save a filtered copy as a new list.

Which write should I use?

▸ click a statement

MERGE INTO — row-level upsert by a match key. Use for CDC, SCD, dedup, daily deltas. Cheapest when only some rows change.

INSERT OVERWRITE — atomically replace all rows or a partition. Use for full or partition reloads / backfills.

CREATE OR REPLACE TABLE — rebuild definition + data, resetting schema cleanly while keeping the table name and history. Prefer over DROP+CREATE.

CTAS (CREATE TABLE x AS SELECT …) — create a brand-new table from a query. Great for derived silver/gold tables.

Why it matters

Wrong choice = wasted compute: people full-overwrite when a targeted MERGE is cheaper and safer.
MERGE powers CDC and slowly changing dimensions — guaranteed interview territory. Be able to write SCD1 and SCD2 from memory.

🌙 Real-world use case: a nightly feed of changed customers → one MERGE updates existing, inserts new, and optionally expires departed customers — atomically, without rewriting the whole table.

1 · MERGE — the one that matters

Match a source (incoming changes) against the target (Delta table) on a key, then apply up to three clause types — all in one atomic commit.

MERGE INTO customers t                       -- target (the Delta table)
USING customer_updates s                     -- source (incoming changes)
ON t.id = s.id                               -- the match key
WHEN MATCHED        THEN UPDATE SET *         -- in both → update existing
WHEN NOT MATCHED    THEN INSERT *             -- source only → add new
WHEN NOT MATCHED BY SOURCE THEN DELETE;       -- target only → remove gone rows

▸ click each clause

WHEN MATCHED▸

Row exists in both source and target → UPDATE or DELETE (with optional conditions; multiple matched clauses allowed, first match wins). The "update existing" path.

WHEN NOT MATCHED▸

Row is in the source only → INSERT. Unspecified columns get NULL. The "add new" path.

WHEN NOT MATCHED BY SOURCE▸

Row is in the target only → UPDATE/DELETE. Cannot reference source columns (no source row exists). Requires DBR 12.2 LTS+. The "expire/remove gone rows" path.

Conditional clauses — one MERGE, several actions

MERGE INTO customers t USING customer_updates s ON t.id = s.id
WHEN MATCHED AND s.op = 'DELETE'      THEN DELETE
WHEN MATCHED AND s.op = 'UPDATE'      THEN UPDATE SET name = s.name, tier = s.tier
WHEN NOT MATCHED AND s.op <> 'DELETE' THEN INSERT (id, name, tier) VALUES (s.id, s.name, s.tier);

⚠ Gotcha that fails the merge: multiple source matches

If more than one source row matches the same target row, the merge errors out. Dedup the source to one row per key first — keep the latest by event time:

from pyspark.sql import functions as F, Window
w = Window.partitionBy("id").orderBy(F.col("event_ts").desc())
deduped = (raw_updates
    .withColumn("rn", F.row_number().over(w))
    .filter("rn = 1").drop("rn"))      # one row per key → safe to MERGE

Performance: always prune the target

MERGE rewrites the files holding matched rows. A predicate on the target lets Delta file-skip so it touches a fraction of the table.

MERGE INTO orders t USING daily_changes s
ON t.id = s.id
   AND t.order_date >= current_date() - INTERVAL 7 DAYS   -- prunes old files
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

Deletion vectors (merge-on-read, delta.enableDeletionVectors) cut cost further by marking rows deleted instead of rewriting whole files.

Schema evolution inside MERGE + the PySpark API

MERGE enforces schema by default; opt in to let new source columns flow in. The DeltaTable builder is the DataFrame-API equivalent.

SET spark.databricks.delta.schema.autoMerge.enabled = true;   -- session-wide

from delta.tables import DeltaTable
tgt = DeltaTable.forName(spark, "main.sales.customers")
(tgt.alias("t")
   .merge(deduped.alias("s"), "t.id = s.id")
   .whenMatchedUpdateAll()
   .whenNotMatchedInsertAll()
   .whenNotMatchedBySourceDelete()
   .execute())

Worked example — SCD Type 2 (keep history)

SCD2 closes the old row and inserts a new current row, so you keep full history. Step through what one MERGE does when customer C1 moves Silver → Gold:

▸ step through the SCD2 merge

STEP 1 / 4

The SCD2 merge (two-pass source)

MERGE INTO dim_customer t
USING (
  -- pass 1: changed row keyed normally → closes the old version
  SELECT id AS merge_key, * FROM customer_updates
  UNION ALL
  -- pass 2: same change, NULL key → can't match → forces an INSERT
  SELECT NULL AS merge_key, u.* FROM customer_updates u
  JOIN dim_customer d ON u.id = d.id AND d.is_current = true
  WHERE u.tier <> d.tier
) s
ON t.id = s.merge_key AND t.is_current = true
WHEN MATCHED AND t.tier <> s.tier THEN
  UPDATE SET is_current = false, end_date = current_date()   -- close old
WHEN NOT MATCHED THEN
  INSERT (id, tier, is_current, start_date, end_date)
  VALUES (s.id, s.tier, true, current_date(), NULL);          -- open new

2 · INSERT OVERWRITE — full & partition reloads

Atomically swap a table's (or a partition's) contents in one commit — readers never see an empty/partial table.

-- Full table reload (replace everything atomically)
INSERT OVERWRITE main.sales.orders SELECT * FROM staging.orders_full;

Targeted overwrite with replaceWhere (only one day)

(corrected_day.write.format("delta").mode("overwrite")
   .option("replaceWhere", "order_date = '2024-01-07'")   # only this day is replaced
   .saveAsTable("main.sales.orders"))
# Fails if any row's order_date != 2024-01-07 — the constraint check protects you.

Dynamic partition overwrite (only the partitions present in new data)

spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")
(new_partitions.write.format("delta").mode("overwrite")
   .saveAsTable("main.sales.orders"))   # untouched partitions stay as-is

You can't combine replaceWhere and partitionOverwriteMode=dynamic in one write — pick one targeting strategy.

3 · CREATE OR REPLACE TABLE — atomic rebuild

Redefine + repopulate in one atomic commit, keeping the table name and history lineage. Prefer it over DROP + CREATE, which loses history and leaves a window where the table doesn't exist.

CREATE OR REPLACE TABLE main.sales.dim_date AS
SELECT d AS date_key, year(d) AS yr, month(d) AS mo
FROM (SELECT explode(sequence(DATE'2020-01-01', DATE'2030-12-31', INTERVAL 1 DAY)) AS d);

4 · CTAS — derive a new table from a query

CREATE TABLE AS SELECT builds a new table, inferring schema from the query — the default for silver/gold derived tables.

CREATE TABLE main.sales.gold_daily_revenue AS
SELECT order_date, sum(amount) AS revenue
FROM main.sales.orders GROUP BY order_date;

-- Want the structure but not the data? Copy the schema only:
CREATE TABLE main.sales.orders_empty LIKE main.sales.orders;

Pick the smallest tool that fits

	Rows touched	Atomic	Keeps history	Typical use
MERGE	only matched files	✅	✅	CDC, SCD, upserts, dedup
INSERT OVERWRITE	all / a partition	✅	✅	full / partition reload
CREATE OR REPLACE	whole table	✅	✅ (vs DROP+CREATE)	schema reset / rebuild
CTAS	new table	✅	n/a (new)	derived silver/gold table

Decision rule: some rows changed → MERGE · whole partition/table stale → INSERT OVERWRITE · schema/definition must change → CREATE OR REPLACE · building a new derived table → CTAS.

Uses, edge cases & limitations

Uses: MERGE → CDC/upserts/SCD1+2/dedup; INSERT OVERWRITE → full/partition reloads & backfills; CREATE OR REPLACE → clean rebuilds; CTAS → new derived tables.
Edge cases: multiple source rows per key → MERGE fails (dedup first); replaceWhere data must satisfy the predicate or the write errors; dynamic partition overwrite is per-partition — a misplaced row can clobber a whole partition.
Limitations: WHEN NOT MATCHED BY SOURCE can't reference source columns and needs DBR 12.2 LTS+. OVERWRITE / REPLACE rewrite far more than a targeted MERGE — don't use them for a few changed rows.

Common gotchas

❌ INSERT OVERWRITE for a small daily delta — rewrites everything; use MERGE.
❌ Forgetting to dedup the source → "multiple matches" MERGE failure.
❌ DROP+CREATE instead of CREATE OR REPLACE — loses history/lineage.
❌ MERGE on a huge table with no predicate — always add a target-pruning predicate.
❌ Assuming schema evolution is on — MERGE enforces by default; opt in with spark.databricks.delta.schema.autoMerge.enabled.