CRUD on Delta Tables

You've built Delta tables, loaded data, and queried it with Spark SQL. But in the real world, data never sits still — new records arrive, bad rows need fixing, stale data gets archived, and sometimes a schema change forces a full rebuild. If your Delta table is a black box that only accepts appends, you'll find yourself rebuilding tables just to update a single row. In this lesson, you'll learn to perform CRUD operations on Delta tables — Create, Read, Update, Delete — using Databricks' native commands, so you can treat your data lakehouse tables like living, breathing entities that evolve with your business.

The problem this lesson solves

Static tables are fine for batch snapshots, but production data is rarely static. Imagine you're running a customer analytics pipeline. A new customer signs up at 9:00 AM, a typo in an address is corrected at 11:30 AM, and by the end of the day three customers request deletion for GDPR compliance. If you can only append to your table, you're stuck: your reports show outdated addresses, duplicate sign-ups, and data that shouldn't exist. You need the ability to update existing rows, delete obsolete rows, and insert new rows — all while keeping your table consistent and queries performant.

That's exactly what Delta Lake's CRUD operations provide. Delta tables support full ACID transactions, meaning every INSERT, UPDATE, DELETE, and MERGE is atomic — either it fully succeeds or fully fails, with no partial writes. This is a game-changer compared to traditional data lakes where reads during writes could see corrupt or incomplete data.

Moreover, delta tables keep a transaction log (the _delta_log directory) that records every operation. This enables time travel, auditability, and the ability to roll back mistakes. Without CRUD, your table would be append-only and immutable, forcing you to rewrite entire files for even small changes — slow, expensive, and error-prone.

In this lesson, you'll move beyond basic INSERT INTO and learn how to perform CRUD operations on Delta tables using both Spark SQL and DataFrame APIs. You'll see how to efficiently manage your data, handle schema evolution, and troubleshoot the most common pitfalls.

Core concept / mental model

Let's build a clear mental model. Think of a Delta table as a living file cabinet managed by a meticulous secretary (the transaction log).

• Create: You buy a new cabinet and label it. This is your CREATE TABLE or CREATE OR REPLACE TABLE.
• Read: You open the drawer and look at the files. This is SELECT or .read.
• Update: You flip through the pages, change a phone number, and put the page back. This is UPDATE or .update().
• Delete: You pull out obsolete pages and shred them. This is DELETE or .delete().
• Merge (the power move): You dump a pile of new and updated pages on the desk, and the secretary matches them to existing pages, inserting new ones, updating matches, and optionally removing what's gone. This is MERGE.

What makes Delta special is that every action writes to the _delta_log — a series of JSON files that record what changed and in what order. That log ensures that all readers and writers see a consistent snapshot, even under concurrent access. Deletes don't immediately remove files from disk; they mark them as inactive in the log, and a background process (VACUUM) eventually cleans them up. This is why you can time travel and why operations are fast: they only rewrite the files that contain the affected data, using data skipping to minimise scanning.

Key terms: - Transaction log: JSON files in _delta_log/ that record every operation. - ACID: Atomicity, Consistency, Isolation, Durability — guarantees for concurrent operations. - Data skipping: Reads only the files that match the predicate, speeding updates and deletes. - Schema evolution: Automatically adding new columns when mergeSchema is enabled.

Pro tip: Because updates and deletes rewrite files, they are not free. Always filter by partition columns or narrow predicates to limit how many files you touch.

How it works step by step

Now let's walk through the mechanics of performing CRUD operations on Delta tables. You'll typically work in a Databricks notebook using either PySpark DataFrames or Spark SQL. The steps are:

1. Create a Delta table

You can create a table from an existing DataFrame or from a query. Use CREATE TABLE with USING DELTA to specify the format.

CREATE TABLE IF NOT EXISTS customers (
  customer_id INT,
  name STRING,
  email STRING,
  signup_date DATE
) USING DELTA

2. Insert data

Use INSERT INTO or INSERT OVERWRITE to add data.

INSERT INTO customers VALUES
  (1, 'Alice', 'alice@example.com', '2025-01-10'),
  (2, 'Bob', 'bob@example.com', '2025-02-14');

3. Update rows

The UPDATE statement changes existing rows based on a condition. In PySpark, you use DataFrame API.

UPDATE customers
SET email = 'alice.new@example.com'
WHERE customer_id = 1;

4. Delete rows

DELETE removes rows that match a predicate.

DELETE FROM customers WHERE customer_id = 2;

5. Merge data (upsert)

MERGE is the powerhouse for handling incremental updates. It matches source rows with target rows using a condition, then inserts, updates, or deletes based on what you specify.

MERGE INTO customers AS t
USING (SELECT 1 AS customer_id, 'Alice Updated' AS name, 'alice@new.com' AS email, '2025-01-10' AS signup_date) AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET name = s.name, email = s.email
WHEN NOT MATCHED THEN INSERT *

6. Read data

Reading is just a SELECT or loading as a DataFrame. You can also query the version history with DESCRIBE HISTORY.

7. Clean up (optional)

Run VACUUM to delete old files that are no longer referenced by the transaction log. This reclaims storage space.

That's the basic flow. Next, let's apply this in a notebook.

Hands-on walkthrough

Let's work through a complete, runnable example in a Databricks notebook. We'll create a table, perform all CRUD operations, and verify the results.

Setup

First, create a database (schema) if needed.

CREATE DATABASE IF NOT EXISTS retail;
USE retail;

Step 1 — Create and insert

from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, IntegerType, StringType, DateType
import datetime

# Create a DataFrame with some sample data
schema = StructType([
    StructField("order_id", IntegerType(), True),
    StructField("customer_id", IntegerType(), True),
    StructField("status", StringType(), True),
    StructField("order_date", DateType(), True)
])

data = [
    (1001, 501, "PENDING", datetime.date(2025, 3, 1)),
    (1002, 502, "SHIPPED", datetime.date(2025, 3, 2)),
    (1003, 503, "DELIVERED", datetime.date(2025, 3, 3))
]
orders_df = spark.createDataFrame(data, schema)

# Write as a Delta table
orders_df.write.format("delta").mode("overwrite").saveAsTable("orders")

Expected outcome: a Delta table orders with 3 rows.

Step 2 — Read

# Read the table
orders = spark.table("orders")
orders.show()

Output:

+--------+-----------+---------+----------+
|order_id|customer_id|   status|order_date|
+--------+-----------+---------+----------+
|    1001|        501|  PENDING|2025-03-01|
|    1002|        502|  SHIPPED|2025-03-02|
|    1003|        503|DELIVERED|2025-03-03|
+--------+-----------+---------+----------+

Step 3 — Update

from delta.tables import DeltaTable

# Update the status of order 1001 to 'PROCESSING'
delta_orders = DeltaTable.forName(spark, "orders")
delta_orders.update(
    condition="order_id = 1001",
    set={"status": "PROCESSING"}
)

spark.table("orders").filter("order_id = 1001").show()

Output:

+--------+-----------+----------+----------+
|order_id|customer_id|    status|order_date|
+--------+-----------+----------+----------+
|    1001|        501|PROCESSING|2025-03-01|
+--------+-----------+----------+----------+

Step 4 — Delete

# Delete order 1003 (delivered, maybe archived)
delta_orders.delete("order_id = 1003")
spark.table("orders").show()

Output:

+--------+-----------+----------+----------+
|order_id|customer_id|    status|order_date|
+--------+-----------+----------+----------+
|    1001|        501|PROCESSING|2025-03-01|
|    1002|        502|  SHIPPED|2025-03-02|
+--------+-----------+----------+----------+

Step 5 — Upsert with MERGE

# New data: a new order (2001) and an update to order 1002
new_data = [
    (1002, 502, "OUT_FOR_DELIVERY", datetime.date(2025, 3, 2)),  # updated status
    (2001, 504, "PENDING", datetime.date(2025, 3, 5))  # new order
]
new_orders_df = spark.createDataFrame(new_data, schema)

# Perform merge
delta_orders.alias("target").merge(
    new_orders_df.alias("source"),
    "target.order_id = source.order_id"
).whenMatchedUpdate(set={
    "status": "source.status"
}).whenNotMatchedInsertAll().execute()

spark.table("orders").orderBy("order_id").show()

Output:

+--------+-----------+---------------+----------+
|order_id|customer_id|         status|order_date|
+--------+-----------+---------------+----------+
|    1001|        501|     PROCESSING|2025-03-01|
|    1002|        502|OUT_FOR_DELIVERY|2025-03-02|
|    2001|        504|        PENDING|2025-03-05|
+--------+-----------+---------------+----------+

Step 6 — OPTIMIZE and VACUUM (housekeeping)

# OPTIMIZE compacts small files and updates statistics
spark.sql("OPTIMIZE orders")

# VACUUM removes old files older than 7 days (default) — be careful!
# spark.sql("VACUUM orders RETAIN 7 HOURS")  # uncomment to run

You've now performed all CRUD operations on a Delta table. Great job!

Compare options / when to choose what

Delta provides several ways to modify data. Choosing the right one matters for performance and correctness. Here's a comparison:

When to use what: - Use INSERT when you're appending new data that doesn't update existing rows. - Use INSERT OVERWRITE when you need to replace a full partition or table (common in batch ETL with idempotent loads). - Use UPDATE for small, targeted corrections. - Use DELETE for GDPR or data retention deletes. - Use MERGE for UPSERTs — this is your go-to for CDC and streaming into delta.

Variations: - PySpark DataFrame API: df.write.mode('overwrite').format('delta').saveAsTable('...') for create, .filter().update(), .filter().delete(), .merge() from DeltaTable. - Spark SQL: Use CREATE, INSERT, UPDATE, DELETE, MERGE statements directly. - Delta Live Tables (DLT): Declarative pipelines with CREATE OR REFRESH that handle updates automatically, but with limited control.

Trade-offs: MERGE is more expensive than single UPDATE or INSERT because it scans and rewrites more files. Partition and Z-Ordering columns can drastically cut the files touched. Also, VACUUM can break time travel if you keep fewer than your retention period — always set a sensible RETAIN.

Troubleshooting & edge cases

CRUD on Delta tables is smooth, but you'll hit predictable issues. Here's how to diagnose and fix them.

1. UPDATE or DELETE requires a predicate

Delta refuses to update/delete without a WHERE clause to protect your data. You'll see an error like:

Error: The provided condition must be deterministic and not be a literal boolean true.

Fix: Add a predicate even if it's broad (e.g., WHERE 1=1 for a full-table operation).

2. MERGE with multiple matches

If your source has multiple rows that match one target, Delta throws:

Error: The condition that matches the source and target rows must be unique.

Fix: Deduplicate your source before merge. For example, keep only the latest record per key:

from pyspark.sql.window import Window
import pyspark.sql.functions as F

df = df.withColumn("rn", F.row_number().over(Window.partitionBy("key").orderBy(F.desc("timestamp"))))\
       .filter("rn = 1").drop("rn")

3. Schema mismatch on merge

When the source has new columns, MERGE fails unless you enable schema merging.

spark.sql("SET spark.databricks.delta.schema.autoMerge.enabled = true")

or per-table:

delta_orders.merge(
    new_df,
    "target.id = source.id"
).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

4. VACUUM removes files needed for time travel

You run VACUUM and later try to read an older version — you get:

Error: The specified version does not exist.

Fix: Set a reasonable retention period (default 7 days) and avoid vacuuming if you need long history. Always understand that vacuum is irreversible.

5. Concurrency conflicts (checkpoint failures)

Two jobs updating the same partition can cause conflicts. Delta handles retries, but you might see ConcurrentAppendException. Fix: Use INSERT OVERWRITE with partition overwrite mode spark.sql.sources.partitionOverwriteMode=dynamic to avoid whole-table rewrites, or use MERGE which handles retries better.

6. Surprise row counts after DELETE

Deletes don't remove physical files immediately; they just add a transaction log entry. If you SELECT count immediately, it's correct. But if you inspect the storage, files may still exist. That's normal.

Real-world edge case: Data skew

If your merge condition rely on a column with high cardinality, you may rewrite many small files. Use OPTIMIZE with ZORDER BY on the join key to improve performance.

What you learned & what's next

You've mastered the core of performing CRUD operations on Delta tables. You now know how to: - Create tables with Delta format. - Insert new data with INSERT. - Update and delete existing rows using UPDATE and DELETE. - Perform UPSERTs with MERGE. - Clean up and optimize with OPTIMIZE and VACUUM.

You've seen how the transaction log makes these operations ACID-compliant and how to avoid the most common pitfalls.

What's next: Your data pipeline isn't done after writing. You'll want to leverage time travel and versioning to audit changes and roll back mistakes. The next lesson covers time travel and versioning on Delta tables — you'll learn how to query historical snapshots, restore deleted data, and enable long-term governance. That's the superpower that turns your lakehouse into a reliable source of truth.