Browse cheat sheets · PySpark
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
- Problem
- Quick answer
- Code / commands
- 1. Read a registered Delta table
- 2. Read Delta by path
- 3. Read only required columns
- 4. Filter as early as possible
- 5. Append immutable rows
- 6. Add batch metadata before append
- 7. Overwrite a full table intentionally
- 8. Overwrite with schema replacement only when deliberate
- 9. Add compatible columns with schema evolution
- 10. Upsert with MERGE
- 11. Prefer explicit update/insert mappings for stable contracts
- 12. Deduplicate source rows before merge
- 13. Validate source uniqueness
- 14. Merge on composite business keys
- 15. Update only when source is newer
- 16. Insert only missing keys
- 17. Handle source-driven deletes explicitly
- 18. Use SQL MERGE when it is clearer
- 19. Read table history
- 20. Read an older Delta version
- 21. Read as of a timestamp
- 22. Validate target grain after writes
- 23. Reconcile row counts
- 24. Reconcile business totals
- 25. Partition a table when the access pattern justifies it
- 26. Avoid over-partitioning
- 27. Avoid repartition(1) before Delta writes
- 28. Reduce output partitions carefully
- 29. Compact small files with OPTIMIZE
- 30. Use Z-Order for repeated selective access patterns
- 31. Use VACUUM for storage cleanup
- 32. Do not shorten retention casually
- 33. Use staging for concurrent producers
- 34. Retry only understood concurrency conflicts
- 35. Inspect low-shuffle merge configuration when relevant
- 36. Avoid full-table rewrite for a small incremental change
- 37. Keep Bronze append-only when possible
- 38. Make reruns safe
- 39. Log write metadata
- 40. Inspect the plan before expensive writes
- What the result means
- What to look for
- Target grows every rerun
- Merge is slower than expected
- Merge fails intermittently under concurrency
- Reads slow down after many updates
- Old versions stop working
- Table schema changes unexpectedly
- Production notes
- Delta provides ACID transactions at table level
- Idempotency is an application concern
- Merge performance depends on how much target data is touched
- Maintenance operations consume resources
- Partitioning is optional
- Schema evolution should be governed
- Common mistakes
- 1. Blind append on replayable data
- 2. Using overwrite for every rerun
- 3. Merging duplicate source keys
- 4. Using updateAll() / insertAll() without reviewing schema coupling
- 5. Partitioning by high-cardinality keys
- 6. Using repartition(1) for production Delta output
- 7. Running VACUUM to improve query speed
- 8. Lowering retention without recovery planning
- 9. Treating a successful write as proof of correct grain
- 10. Retrying every write failure
- Next step
- 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
- Staging Delta
- Controlled merge
- Target Delta
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.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.