Browse cheat sheets · PySpark

Technology: PySparkCategory: Data EngineeringDifficulty: IntermediateUpdated

Read and Write Delta Tables

Read and write Delta tables with PySpark using safe append, overwrite, merge, schema-evolution, partitioning, and maintenance patterns for production data pipelines.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Read a registered Delta table
  5. 2. Read Delta by path
  6. 3. Read only required columns
  7. 4. Filter as early as possible
  8. 5. Append immutable rows
  9. 6. Add batch metadata before append
  10. 7. Overwrite a full table intentionally
  11. 8. Overwrite with schema replacement only when deliberate
  12. 9. Add compatible columns with schema evolution
  13. 10. Upsert with MERGE
  14. 11. Prefer explicit update/insert mappings for stable contracts
  15. 12. Deduplicate source rows before merge
  16. 13. Validate source uniqueness
  17. 14. Merge on composite business keys
  18. 15. Update only when source is newer
  19. 16. Insert only missing keys
  20. 17. Handle source-driven deletes explicitly
  21. 18. Use SQL MERGE when it is clearer
  22. 19. Read table history
  23. 20. Read an older Delta version
  24. 21. Read as of a timestamp
  25. 22. Validate target grain after writes
  26. 23. Reconcile row counts
  27. 24. Reconcile business totals
  28. 25. Partition a table when the access pattern justifies it
  29. 26. Avoid over-partitioning
  30. 27. Avoid repartition(1) before Delta writes
  31. 28. Reduce output partitions carefully
  32. 29. Compact small files with OPTIMIZE
  33. 30. Use Z-Order for repeated selective access patterns
  34. 31. Use VACUUM for storage cleanup
  35. 32. Do not shorten retention casually
  36. 33. Use staging for concurrent producers
  37. 34. Retry only understood concurrency conflicts
  38. 35. Inspect low-shuffle merge configuration when relevant
  39. 36. Avoid full-table rewrite for a small incremental change
  40. 37. Keep Bronze append-only when possible
  41. 38. Make reruns safe
  42. 39. Log write metadata
  43. 40. Inspect the plan before expensive writes
  44. What the result means
  45. What to look for
  46. Target grows every rerun
  47. Merge is slower than expected
  48. Merge fails intermittently under concurrency
  49. Reads slow down after many updates
  50. Old versions stop working
  51. Table schema changes unexpectedly
  52. Production notes
  53. Delta provides ACID transactions at table level
  54. Idempotency is an application concern
  55. Merge performance depends on how much target data is touched
  56. Maintenance operations consume resources
  57. Partitioning is optional
  58. Schema evolution should be governed
  59. Common mistakes
  60. 1. Blind append on replayable data
  61. 2. Using overwrite for every rerun
  62. 3. Merging duplicate source keys
  63. 4. Using updateAll() / insertAll() without reviewing schema coupling
  64. 5. Partitioning by high-cardinality keys
  65. 6. Using repartition(1) for production Delta output
  66. 7. Running VACUUM to improve query speed
  67. 8. Lowering retention without recovery planning
  68. 9. Treating a successful write as proof of correct grain
  69. 10. Retrying every write failure
  70. Next step
  71. Related deep dives

Problem

You need to read, append, overwrite, or upsert Delta tables from PySpark without introducing duplicate rows, schema drift, accidental full rewrites, or unsafe rerun behavior.

The syntax is simple.

The production decisions are not.

You need to answer:

  • Is this load append-only or mutable?
  • Can the same batch be replayed?
  • What is the business key?
  • Should the table be rebuilt or incrementally updated?
  • Can schema changes appear automatically?
  • Will concurrent jobs write to the same target?
  • Is the table partitioned appropriately?
  • How will you validate the write?

The goal is to make the write pattern match the data contract.

Quick answer

Use this decision model:

Read existing Delta table
→ spark.read.table(...) or format("delta").load(...)

New immutable rows
→ append

Full deterministic rebuild
→ overwrite

Insert + update existing keys
→ MERGE

Replayable source
→ idempotent MERGE or replay-safe batch logic

Concurrent producers
→ append to staging, then reconcile into target

Read a registered table:

df = spark.read.table("sales")

Append:

(
    df.write
    .format("delta")
    .mode("append")
    .saveAsTable("sales")
)

Upsert:

from delta.tables import DeltaTable

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

(
    target.alias("t")
    .merge(
        source.alias("s"),
        "t.sale_id = s.sale_id",
    )
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

Do not use overwrite as the default rerun strategy for mutable production data.

Code / commands

1. Read a registered Delta table

df = spark.read.table(
    "sales"
)

Equivalent:

df = spark.sql(
    "SELECT * FROM sales"
)

Use registered table names when the catalog/Lakehouse context is stable and clear.


2. Read Delta by path

df = (
    spark.read
    .format("delta")
    .load("Tables/sales")
)

Use path-based reads when the path itself is part of the contract.

Avoid mixing name-based and path-based access casually for the same table.


3. Read only required columns

df = (
    spark.read.table("sales")
    .select(
        "sale_id",
        "merchant_id",
        "sale_date",
        "amount",
    )
)

Column pruning reduces downstream memory and shuffle width.


4. Filter as early as possible

from pyspark.sql import functions as F

df = (
    spark.read.table("sales")
    .filter(
        F.col("sale_date") >= "2026-10-01"
    )
    .select(
        "sale_id",
        "merchant_id",
        "sale_date",
        "amount",
    )
)

Use early filtering when it does not change the intended business semantics.


5. Append immutable rows

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

Use append when:

  • rows are new
  • existing rows should not change
  • replay handling is already designed

Append is not automatically idempotent.


6. Add batch metadata before append

output = (
    new_rows
    .withColumn(
        "_batch_id",
        F.lit(batch_id),
    )
    .withColumn(
        "_ingested_at_utc",
        F.current_timestamp(),
    )
)

This helps with:

  • replay detection
  • lineage
  • incident investigation

It does not prevent duplicates by itself.


7. Overwrite a full table intentionally

(
    rebuilt.write
    .format("delta")
    .mode("overwrite")
    .saveAsTable("sales_summary")
)

Use only when full replacement is truly the table contract.

Examples:

  • small deterministic dimensions
  • rebuildable summaries
  • controlled backfills

8. Overwrite with schema replacement only when deliberate

(
    rebuilt.write
    .format("delta")
    .mode("overwrite")
    .option(
        "overwriteSchema",
        "true",
    )
    .saveAsTable("sales_summary")
)

This can change the target schema.

Do not enable it globally.


9. Add compatible columns with schema evolution

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

Use only when schema evolution is an accepted operational policy.

Do not use it to hide uncontrolled upstream schema drift.


10. Upsert with MERGE

from delta.tables import DeltaTable

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

(
    target.alias("t")
    .merge(
        source.alias("s"),
        "t.sale_id = s.sale_id",
    )
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

Use when incoming rows can update existing business keys.


11. Prefer explicit update/insert mappings for stable contracts

(
    target.alias("t")
    .merge(
        source.alias("s"),
        "t.sale_id = s.sale_id",
    )
    .whenMatchedUpdate(
        set={
            "merchant_id": "s.merchant_id",
            "sale_date": "s.sale_date",
            "amount": "s.amount",
            "updated_at": "s.updated_at",
        }
    )
    .whenNotMatchedInsert(
        values={
            "sale_id": "s.sale_id",
            "merchant_id": "s.merchant_id",
            "sale_date": "s.sale_date",
            "amount": "s.amount",
            "updated_at": "s.updated_at",
        }
    )
    .execute()
)

This reduces accidental coupling between source and target schemas.


12. Deduplicate source rows before merge

from pyspark.sql import Window

w = (
    Window
    .partitionBy("sale_id")
    .orderBy(
        F.col("updated_at").desc(),
        F.col("source_sequence").desc(),
    )
)

source_deduped = (
    source
    .withColumn(
        "_rn",
        F.row_number().over(w),
    )
    .filter(F.col("_rn") == 1)
    .drop("_rn")
)

Use deterministic ordering when multiple source rows can target the same key.


13. Validate source uniqueness

duplicates = (
    source
    .groupBy("sale_id")
    .count()
    .filter(
        F.col("count") > 1
    )
)

Fail if duplicates are not allowed:

if duplicates.limit(1).count() > 0:
    raise RuntimeError(
        "Duplicate sale_id values in merge source"
    )

14. Merge on composite business keys

condition = """
    t.member_id = s.member_id
    AND t.merchant_id = s.merchant_id
    AND t.site_id = s.site_id
"""

Then:

(
    target.alias("t")
    .merge(
        source.alias("s"),
        condition,
    )
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

Use the full business key.


15. Update only when source is newer

(
    target.alias("t")
    .merge(
        source.alias("s"),
        "t.sale_id = s.sale_id",
    )
    .whenMatchedUpdateAll(
        condition="s.updated_at > t.updated_at"
    )
    .whenNotMatchedInsertAll()
    .execute()
)

Use only when updated_at is reliable enough to define version precedence.


16. Insert only missing keys

(
    target.alias("t")
    .merge(
        source.alias("s"),
        "t.sale_id = s.sale_id",
    )
    .whenNotMatchedInsertAll()
    .execute()
)

This can make repeated batches idempotent by business key.


17. Handle source-driven deletes explicitly

(
    target.alias("t")
    .merge(
        source.alias("s"),
        "t.sale_id = s.sale_id",
    )
    .whenMatchedDelete(
        condition="s.is_deleted = true"
    )
    .whenMatchedUpdateAll(
        condition="s.is_deleted = false"
    )
    .whenNotMatchedInsertAll(
        condition="s.is_deleted = false"
    )
    .execute()
)

Only delete when the source contract explicitly says the row is deleted.

Absence from one incremental batch is not proof of deletion.


18. Use SQL MERGE when it is clearer

MERGE INTO sales AS t
USING sales_stage AS s
ON t.sale_id = s.sale_id

WHEN MATCHED
    AND s.updated_at > t.updated_at
THEN UPDATE SET
    t.merchant_id = s.merchant_id,
    t.sale_date = s.sale_date,
    t.amount = s.amount,
    t.updated_at = s.updated_at

WHEN NOT MATCHED
THEN INSERT
(
    sale_id,
    merchant_id,
    sale_date,
    amount,
    updated_at
)
VALUES
(
    s.sale_id,
    s.merchant_id,
    s.sale_date,
    s.amount,
    s.updated_at
);

Choose the API or SQL form that is easiest to review.


19. Read table history

history = spark.sql(
    "DESCRIBE HISTORY sales"
)

history.show(
    truncate=False
)

Use history to investigate:

  • append
  • overwrite
  • merge
  • optimize
  • vacuum-related operational changes
  • unexpected modifications

20. Read an older Delta version

older = (
    spark.read
    .format("delta")
    .option(
        "versionAsOf",
        42,
    )
    .table("sales")
)

Use this for:

  • debugging
  • comparison
  • recovery analysis

Availability depends on retained history and files.


21. Read as of a timestamp

older = (
    spark.read
    .format("delta")
    .option(
        "timestampAsOf",
        "2026-10-03T12:00:00Z",
    )
    .table("sales")
)

Be explicit about timezone semantics when comparing historical versions.


22. Validate target grain after writes

If sale_id must be unique:

duplicates = (
    spark.table("sales")
    .groupBy("sale_id")
    .count()
    .filter(
        F.col("count") > 1
    )
)

Then:

if duplicates.limit(1).count() > 0:
    raise RuntimeError(
        "Target grain validation failed"
    )

A successful Delta transaction does not guarantee business-key uniqueness.


23. Reconcile row counts

source_count = source.count()
target_count = (
    spark.table("sales")
    .count()
)

print(
    "Source:",
    source_count,
)

print(
    "Target:",
    target_count,
)

Row count alone is not proof of correctness.

Use it alongside:

  • distinct business keys
  • batch IDs
  • null counts
  • business totals

24. Reconcile business totals

source_total = (
    source
    .agg(
        F.sum("amount").alias("amount")
    )
    .first()["amount"]
)

Compare with the appropriate target slice.

Use exact decimal types for financial data where practical.


25. Partition a table when the access pattern justifies it

(
    df.write
    .format("delta")
    .mode("append")
    .partitionBy(
        "sale_year",
        "sale_month",
    )
    .saveAsTable("sales")
)

Partitioning can help:

  • pruning
  • writer isolation
  • lifecycle management

Do not partition by high-cardinality identifiers.


26. Avoid over-partitioning

Poor candidates:

transaction_id
customer_id with millions of values
exact timestamp

Too many partitions can create:

  • many tiny files
  • metadata overhead
  • inefficient reads

Partition only for real access/write patterns.


27. Avoid repartition(1) before Delta writes

Risky:

(
    df.repartition(1)
    .write
    .format("delta")
    .mode("append")
    .saveAsTable("sales")
)

This creates a single write bottleneck.

Delta tables are expected to contain multiple files.


28. Reduce output partitions carefully

If output is small:

small = df.coalesce(20)

Then:

(
    small.write
    .format("delta")
    .mode("append")
    .saveAsTable("sales_summary")
)

Measure first.

Too few partitions can also create slow oversized tasks.


29. Compact small files with OPTIMIZE

spark.sql(
    "OPTIMIZE sales"
)

Use when repeated writes create many small files and read performance is affected.

Do not run after every small write by default.


30. Use Z-Order for repeated selective access patterns

spark.sql(
    """
    OPTIMIZE sales
    ZORDER BY (merchant_id, sale_date)
    """
)

Use when queries frequently filter on those columns and the table is large enough to benefit.

Do not Z-Order every column.


31. Use VACUUM for storage cleanup

spark.sql(
    "VACUUM sales"
)

Or explicit retention:

spark.sql(
    "VACUUM sales RETAIN 168 HOURS"
)

VACUUM removes old unreferenced files after the retention threshold.

It is not a query-performance substitute for OPTIMIZE.


32. Do not shorten retention casually

Old files may still be required for:

  • time travel
  • long-running readers
  • incident recovery
  • rollback analysis

Once removed, they are gone.

Treat retention as an operational recovery decision.


33. Use staging for concurrent producers

A safer pattern:

    • Producer A
    • Producer B
    • Producer C
  1. Staging Delta
  2. Controlled merge
  3. Target Delta
Staging for concurrent producers

This reduces overlap between independent writers.


34. Retry only understood concurrency conflicts

Conceptual pattern:

import time

for attempt in range(3):
    try:
        run_merge()
        break
    except Exception as exc:
        if not is_expected_delta_conflict(exc):
            raise

        if attempt == 2:
            raise

        time.sleep(2 ** attempt)

Do not retry:

  • schema bugs
  • bad data
  • invalid merge logic
  • permissions failures

35. Inspect low-shuffle merge configuration when relevant

On platforms/runtimes that support it:

print(
    spark.conf.get(
        "spark.microsoft.delta.merge.lowShuffle.enabled"
    )
)

Do not treat runtime optimization settings as a replacement for correct merge keys and data layout.


36. Avoid full-table rewrite for a small incremental change

Poor pattern:

target = spark.table("sales")

combined = (
    target
    .unionByName(source)
)

# global dedupe
# full overwrite

Better:

incremental source
→ deterministic dedupe
→ targeted MERGE

Use full rebuilds only when they are genuinely appropriate.


37. Keep Bronze append-only when possible

A common pattern:

Bronze
→ append raw source history

Silver
→ MERGE current state

Gold
→ curated serving layer

This preserves replayability and simplifies incident recovery.


38. Make reruns safe

Before allowing reruns, decide:

What happens if the same batch runs twice?

Good patterns include:

MERGE by business key
batch replay guard
partition replacement
staging + promote

Bad default:

blind append

for replayable mutable feeds.


39. Log write metadata

Useful fields:

pipeline_run_id
batch_id
source_system
ingested_at_utc
source_modified_at

Example:

output = (
    output
    .withColumn(
        "_pipeline_run_id",
        F.lit(pipeline_run_id),
    )
    .withColumn(
        "_batch_id",
        F.lit(batch_id),
    )
    .withColumn(
        "_ingested_at_utc",
        F.current_timestamp(),
    )
)

Keep operational metadata safe and non-sensitive.


40. Inspect the plan before expensive writes

output.explain("formatted")

Before a large write, confirm the transformation is not accidentally doing:

  • unnecessary shuffle
  • cross join
  • repeated scan
  • huge global sort
  • expensive Python UDF

Write tuning cannot fix a bad upstream plan.

What the result means

Pattern Use when
read.table() Registered Delta table
format("delta").load() Explicit Delta path
append New immutable rows
overwrite Intentional full rebuild
MERGE Upsert / incremental reconciliation
mergeSchema Controlled additive schema evolution
overwriteSchema Intentional schema replacement
OPTIMIZE Compact/reorganize active data files
Z-Order Improve skipping for repeated filters
VACUUM Remove old unreferenced files
time travel Read historical table state
staging + merge Safer concurrent producer pattern

The key distinction is:

Delta transaction success
!=
business correctness

Validate the resulting data contract.

What to look for

Target grows every rerun

Likely cause:

  • blind append
  • missing replay protection
  • wrong business key

Merge is slower than expected

Check:

  • source size
  • merge condition selectivity
  • target layout
  • partition pruning
  • file count
  • skew

Merge fails intermittently under concurrency

Check overlapping writers and target-file overlap.

Consider staging + controlled merge.

Reads slow down after many updates

Check:

  • small-file accumulation
  • layout
  • need for OPTIMIZE

Old versions stop working

Check whether old files were removed by retention/VACUUM.

Table schema changes unexpectedly

Check:

  • mergeSchema
  • source drift
  • overwrite schema behavior
  • deployment changes

Production notes

Delta provides ACID transactions at table level

This protects commit integrity.

It does not automatically enforce:

  • uniqueness
  • foreign keys
  • one-row-per-business-key rules

Validate those yourself.

Idempotency is an application concern

A Delta write can be transactionally valid and still duplicate data on rerun.

Design replay behavior deliberately.

Merge performance depends on how much target data is touched

A very small source can still cause expensive target work if the merge condition/layout is broad.

Maintenance operations consume resources

OPTIMIZE and VACUUM should be scheduled intentionally.

Do not run them blindly after every write.

Partitioning is optional

Do not assume every Delta table needs physical partitioning.

Use it only when it supports:

  • pruning
  • lifecycle
  • writer isolation

Schema evolution should be governed

Automatic schema growth can be convenient, but it can also break consumers.

Treat schema as a contract.

Common mistakes

1. Blind append on replayable data

This creates duplicates.

2. Using overwrite for every rerun

This turns small incremental updates into expensive full rewrites.

3. Merging duplicate source keys

Deduplicate deterministically first.

4. Using updateAll() / insertAll() without reviewing schema coupling

Convenient today can become fragile tomorrow.

5. Partitioning by high-cardinality keys

This produces many small partitions/files.

6. Using repartition(1) for production Delta output

This creates a single-task bottleneck.

7. Running VACUUM to improve query speed

Use OPTIMIZE/layout tuning for read performance.

8. Lowering retention without recovery planning

You can destroy time-travel availability.

9. Treating a successful write as proof of correct grain

Validate business keys and totals.

10. Retrying every write failure

Retry only understood transient/concurrency failures.

Next step

Use the load behavior to choose the next action.

  • New immutable rows only → append.
  • Same batch can replay → add idempotent merge or replay guard.
  • Existing keys can change → use MERGE.
  • Source has duplicate business keys → deduplicate with deterministic ordering first.
  • Concurrent producers conflict → use staging + controlled reconciliation.
  • Target accumulates small files → review partitioning and consider OPTIMIZE.
  • Schema changes unexpectedly → tighten schema evolution policy.
  • Old versions matter → protect retention before VACUUM.
  • Write remains slow → inspect the physical plan and move to PySpark Performance Quick Reference.

← All cheat sheets