Writing Data from a Spark Notebook to a Fabric Lakehouse Delta Table: Append, Overwrite, and Merge Patterns with PySpark

Introduction

You've loaded your raw data, cleaned it up in a Spark notebook, and now you're staring at a beautiful DataFrame — and you need to get it into a Delta table so the rest of your organization can actually use it. This is where many beginners hit a wall. It's not enough to just "write it somewhere." You need to understand how you're writing it, because the wrong approach can silently duplicate your data, wipe out records you needed, or leave your table in an inconsistent state that's painful to unwind.

In Microsoft Fabric, every lakehouse table is a Delta table. Delta is a storage format built on Parquet files with an additional transaction log that tracks every change — inserts, updates, deletes, schema changes. That transaction log is what separates Delta from a plain Parquet folder and gives you capabilities like ACID guarantees (Atomicity, Consistency, Isolation, Durability) and time travel. But to take advantage of those capabilities, you need to use the right write patterns. Blindly appending when you should be merging is one of the most common causes of bad data in a lakehouse.

By the end of this lesson, you'll understand and be able to implement the three fundamental write patterns in PySpark: append, overwrite, and merge (also called upsert). You'll know when to use each one, what pitfalls to watch out for, and how to verify that your writes actually did what you expected.

What you'll learn:

• How Delta table writes work under the hood in a Fabric lakehouse
• When to use append versus overwrite versus merge — and why it matters
• How to write a DataFrame to a new or existing Delta table with PySpark
• How to implement a merge/upsert pattern using DeltaTable from the delta.tables library
• How to inspect the Delta transaction log to confirm your write operations

---

Prerequisites

Before diving in, you should have:

• A Fabric workspace with at least Contributor access. If you don't have one yet, see Fabric Capacities and Workspaces: F SKUs, Trials, and Setting Up Your First Workspace.
• A lakehouse created in that workspace. If this is your first time, Building Your First Lakehouse in Microsoft Fabric: Files, Tables, and the SQL Analytics Endpoint will get you there.
• Familiarity with basic PySpark DataFrames — reading data, filtering, selecting columns. If you need a refresher, Transforming Data with Spark Notebooks in Microsoft Fabric: PySpark for Lakehouse Tables covers the fundamentals.

---

Understanding How Delta Writes Work in a Fabric Lakehouse

Before writing a single line of code, it's worth building a mental model of what actually happens when you write a DataFrame to a Delta table.

A Delta table is not a single file. It's a directory containing two things: a set of Parquet data files, and a _delta_log subfolder that holds JSON transaction log files. Every time you write to the table — whether appending, overwriting, or merging — a new entry is added to that transaction log describing exactly what changed. This is what enables features like ACID transactions and time travel.

In a Fabric lakehouse, your tables live under Tables/ in OneLake. When you write a DataFrame from a Spark notebook, Fabric gives you two convenient ways to address the target table:

1. By path — using the abfss:// URI that points to the physical location in OneLake
2. By name — using the Spark catalog, which lets you reference the table by lakehouse_name.table_name

The path-based approach is explicit and portable. The catalog-based approach is cleaner and integrates automatically with the SQL Analytics Endpoint, meaning that once you write a table by name, it immediately appears in SQL views and downstream reporting. For most work inside a Fabric notebook with an attached lakehouse, the catalog approach is recommended.

---

Setting Up Your Sample Data

Throughout this lesson we'll use a realistic scenario: a fictional retail company that loads daily sales transactions into a sales_transactions Delta table. New transactions come in every day, some records get corrected after the fact, and we need our table to reflect reality accurately.

Let's start by creating a sample DataFrame that represents a batch of sales data arriving on a given day:

from pyspark.sql import Row
from pyspark.sql.types import StructType, StructField, StringType, IntegerType, DoubleType, DateType
from datetime import date

# Define the schema explicitly — always good practice
schema = StructType([
    StructField("transaction_id", StringType(), nullable=False),
    StructField("customer_id",    StringType(), nullable=True),
    StructField("product_sku",    StringType(), nullable=True),
    StructField("quantity",       IntegerType(), nullable=True),
    StructField("unit_price",     DoubleType(),  nullable=True),
    StructField("sale_date",      DateType(),    nullable=True),
])

initial_data = [
    Row("TXN-001", "CUST-100", "SKU-A", 2, 29.99, date(2024, 6, 1)),
    Row("TXN-002", "CUST-101", "SKU-B", 1, 49.99, date(2024, 6, 1)),
    Row("TXN-003", "CUST-102", "SKU-A", 5, 29.99, date(2024, 6, 1)),
]

df_initial = spark.createDataFrame(initial_data, schema=schema)
df_initial.show()

This gives us three clean transactions from June 1st, 2024. Simple enough to follow, realistic enough to be meaningful.

---

The Overwrite Pattern: Replacing the Entire Table

The simplest write mode to understand is overwrite. When you write with mode("overwrite"), Spark replaces the entire contents of the target table with whatever is in your DataFrame. The old data is gone, the new data takes its place.

Think of it like replacing a whiteboard. Whatever was there before gets erased and you write fresh.

# Write the initial batch — create the table for the first time
(df_initial
    .write
    .format("delta")
    .mode("overwrite")
    .saveAsTable("sales_transactions"))

After running this, a sales_transactions table appears in your lakehouse with exactly three rows.

When should you use overwrite?

Overwrite makes sense when your table represents a complete, current snapshot of something — not a growing history of events. Good candidates include:

• Reference tables (product catalog, customer dimension) that are small enough to reload entirely
• Aggregated summary tables where you recompute the full result set each time
• Staging tables that exist purely as a temporary holding area before further transformation

When overwrite will hurt you:

If your table is meant to accumulate history — like our sales_transactions table that should hold months of data — overwrite is destructive. Every daily load would erase the previous days. Don't do this.

---

The Append Pattern: Adding Rows Without Touching Existing Data

Append is the opposite of overwrite. When you write with mode("append"), Spark adds your DataFrame's rows to the existing table and leaves all existing rows untouched. Nothing is deleted. Nothing is modified.

# Simulate the next day's batch arriving
new_data = [
    Row("TXN-004", "CUST-103", "SKU-C", 3, 19.99, date(2024, 6, 2)),
    Row("TXN-005", "CUST-100", "SKU-A", 1, 29.99, date(2024, 6, 2)),
]

df_day2 = spark.createDataFrame(new_data, schema=schema)

# Append the new records to the existing table
(df_day2
    .write
    .format("delta")
    .mode("append")
    .saveAsTable("sales_transactions"))

Now the table has five rows — the original three from June 1st plus the two new ones from June 2nd. The transaction log records a new version that added two files.

When should you use append?

Append is the right choice when:

• New data is genuinely new — each batch contains records that have never existed in the table before
• Your source system guarantees no duplicates or corrections (rare, but it happens with event logs or IoT streams)
• You're building a bronze/raw layer in a medallion architecture where you intentionally keep every raw record that ever arrived

The danger with append: silent duplicates

Here is the trap that catches almost everyone. What happens if your pipeline re-runs? Or if a batch is accidentally sent twice by the upstream source system? With pure append mode, you get duplicates — and Delta has no way to know they're duplicates. The table just grows, and your row counts quietly become wrong.

# This is BAD — running the same batch twice causes duplicates
(df_day2
    .write
    .format("delta")
    .mode("append")
    .saveAsTable("sales_transactions"))

# The table now has SEVEN rows, not five
spark.table("sales_transactions").count()  # Returns 7

If your pipeline might ever re-run a batch, and virtually all pipelines do, you need the merge pattern.

---

The Merge Pattern: Upsert Logic with DeltaTable

Merge (also called upsert — "update or insert") is the most powerful and most commonly needed write pattern in real-world lakehouse engineering. It says: "For each row in my new data, if a matching row already exists in the target table, update it; if no match exists, insert it as a new row."

This handles two critical real-world scenarios:

1. Corrections — A transaction was recorded with the wrong quantity. The corrected record arrives in the next batch. You need it to replace the original, not sit alongside it as a duplicate.
2. Idempotent loads — If you re-run the same batch, you get the same result. Existing rows are updated with the same values (no change), new rows are inserted. No duplicates.

The merge operation in PySpark uses the DeltaTable class from the delta.tables module, which gives you a fluent API to express merge logic.

Basic Merge: Insert New, Update Existing

from delta.tables import DeltaTable

# Reference the existing target table
target = DeltaTable.forName(spark, "sales_transactions")

# New batch — includes one correction (TXN-003 quantity changed)
# and one genuinely new record (TXN-006)
update_data = [
    Row("TXN-003", "CUST-102", "SKU-A", 3, 29.99, date(2024, 6, 1)),  # Quantity corrected: 5 → 3
    Row("TXN-006", "CUST-104", "SKU-B", 2, 49.99, date(2024, 6, 3)),  # Genuinely new
]
df_updates = spark.createDataFrame(update_data, schema=schema)

# Execute the merge
(target.alias("tgt")
    .merge(
        df_updates.alias("src"),
        "tgt.transaction_id = src.transaction_id"  # Match condition
    )
    .whenMatchedUpdateAll()      # If match found: update all columns
    .whenNotMatchedInsertAll()   # If no match found: insert new row
    .execute())

Let's break down what this code is doing step by step:

• DeltaTable.forName(spark, "sales_transactions") — loads a reference to the existing Delta table. This is a metadata reference, not a full scan of the data.
• .alias("tgt") and .alias("src") — give the target and source DataFrames short names so we can refer to them in the match condition. This is just SQL aliasing convention.
• "tgt.transaction_id = src.transaction_id" — the match condition. For every row in src, Delta looks for a row in tgt with the same transaction_id.
• .whenMatchedUpdateAll() — if a match is found, update every column in the target row to match the source row.
• .whenNotMatchedInsertAll() — if no match is found, insert the source row as a new record.
• .execute() — runs the merge. Without this, nothing happens.

After this merge, TXN-003 now shows quantity 3 (corrected), and TXN-006 has been added. The table has six rows, all correct.

Selective Updates: Only Update Specific Columns

Sometimes you don't want to update every column. Maybe you have an inserted_at timestamp column that should only be set on insert, never overwritten on update. You can be selective:

(target.alias("tgt")
    .merge(
        df_updates.alias("src"),
        "tgt.transaction_id = src.transaction_id"
    )
    .whenMatchedUpdate(set={
        "quantity":   "src.quantity",
        "unit_price": "src.unit_price"
        # transaction_id, customer_id, product_sku, sale_date stay unchanged
    })
    .whenNotMatchedInsertAll()
    .execute())

Merge with a Delete Clause

Delta merge also supports a whenMatchedDelete() clause, useful when your source includes a flag indicating that a record has been logically deleted:

(target.alias("tgt")
    .merge(
        df_updates_with_deletes.alias("src"),
        "tgt.transaction_id = src.transaction_id"
    )
    .whenMatchedDelete(condition="src.is_deleted = true")
    .whenMatchedUpdateAll(condition="src.is_deleted = false")
    .whenNotMatchedInsertAll(condition="src.is_deleted = false")
    .execute())

---

Verifying Your Writes: The Delta Transaction Log

After any write operation, it's good practice to verify what actually happened. Delta gives you two useful tools for this.

Check the Current Table State

# Count rows and inspect a sample
spark.table("sales_transactions").count()
spark.table("sales_transactions").show()

Inspect the Transaction History

# The DESCRIBE HISTORY command shows every operation on the table
display(spark.sql("DESCRIBE HISTORY sales_transactions"))

This returns a log of every operation, including the timestamp, the operation type (WRITE, MERGE, DELETE, etc.), the user who ran it, and metrics like how many rows were added or removed. For a merge, you'll see fields like numTargetRowsInserted, numTargetRowsUpdated, and numTargetRowsDeleted.

You can also use the DeltaTable API directly:

from delta.tables import DeltaTable

history = DeltaTable.forName(spark, "sales_transactions").history()
display(history)

---

Writing by Path vs. Writing to the Catalog

So far we've used saveAsTable(), which writes to the Spark catalog. You can also write by the physical path using save(). Here's how the two approaches look side by side:

# Option 1: Write to catalog (recommended for Fabric lakehouse)
(df_day2
    .write
    .format("delta")
    .mode("append")
    .saveAsTable("sales_transactions"))

# Option 2: Write to path (use when you need fine-grained control)
lakehouse_path = "abfss://your-workspace-id@onelake.dfs.fabric.microsoft.com/your-lakehouse.Lakehouse/Tables/sales_transactions"

(df_day2
    .write
    .format("delta")
    .mode("append")
    .save(lakehouse_path))

The catalog approach is almost always better for Fabric work because the table automatically appears in the lakehouse UI, becomes queryable through the SQL Analytics Endpoint, and integrates with Direct Lake reporting in Power BI without any additional registration steps.

---

Hands-On Exercise

Work through these steps in a Fabric Spark notebook attached to a lakehouse:

Step 1: Create the initial table using overwrite mode with at least five rows of realistic data. Use a domain you're familiar with — orders, inventory, employees, whatever makes sense to you.

Step 2: Verify the table exists by running spark.table("your_table_name").show() and spark.sql("DESCRIBE HISTORY your_table_name").show().

Step 3: Create a second DataFrame representing the next batch of data. Include at least two rows with IDs that already exist in the table (with some column value changed to simulate a correction) and at least two rows with genuinely new IDs.

Step 4: Run a merge operation using DeltaTable.forName(). After the merge, run DESCRIBE HISTORY and check the metrics — confirm that numTargetRowsUpdated and numTargetRowsInserted match your expectations.

Step 5: Run the same merge a second time (with the same source DataFrame). Verify that the row count hasn't changed — this is the idempotency test. If it passes, you've confirmed that your merge pattern is safe to re-run.

---

Common Mistakes & Troubleshooting

Problem: Duplicate rows appearing after repeated appends

This is almost always caused by using mode("append") on a table that receives data that could repeat across pipeline runs. Switch to a merge pattern with a meaningful match key.

Problem: AnalysisException: Table already exists when using overwrite

If you see this when using mode("overwrite") with saveAsTable, add .option("overwriteSchema", "true") if you've also changed the schema, or use spark.sql("DROP TABLE IF EXISTS your_table") before the write. More commonly, this error appears when you accidentally omit mode("overwrite") entirely and use the default mode, which is error mode.

# Default mode is "error" — this will fail if the table exists
df.write.format("delta").saveAsTable("sales_transactions")  # ❌

# Fix: be explicit about mode
df.write.format("delta").mode("overwrite").saveAsTable("sales_transactions")  # ✅

Problem: Merge is dramatically slower than expected

Merge requires Delta to scan the target table to find matches. If your target table is large and you're merging a small batch, make sure you add a partition filter to the merge condition to minimize the scan:

# Without partition filter — scans the entire table
"tgt.transaction_id = src.transaction_id"

# With partition filter — only scans partitions where sale_date matches
"tgt.sale_date = src.sale_date AND tgt.transaction_id = src.transaction_id"

This only helps if the table is partitioned by sale_date, but when it does, the performance difference can be enormous. See Optimizing Delta Table Performance in a Fabric Lakehouse for deeper coverage of partition strategies.

Problem: Schema mismatch error on append

If you've added a column to your source DataFrame that doesn't exist in the target table, a plain append will fail. You can allow schema evolution with:

(df_new
    .write
    .format("delta")
    .mode("append")
    .option("mergeSchema", "true")
    .saveAsTable("sales_transactions"))

Problem: After a merge, the table has the right data but queries seem slow

Merge operations can produce small Parquet files, which degrade read performance over time. Run OPTIMIZE periodically to compact them:

spark.sql("OPTIMIZE sales_transactions")

---

Summary & Next Steps

You now have a solid, practical understanding of the three core write patterns for Delta tables in a Fabric lakehouse:

• Overwrite replaces everything. Use it for full-refresh reference tables and aggregated snapshots.
• Append adds rows. Use it when new data is genuinely new and you're sure it'll never duplicate.
• Merge upserts. Use it whenever you need to handle corrections, prevent duplicates, or make loads idempotent — which is most of the time in production.

The merge pattern is the one you'll reach for most often in real engineering work. It's slightly more code to write, but it protects you from the silent data quality problems that accumulate when simpler patterns are used carelessly.

A natural next step from here is to think about how these write patterns fit into a larger orchestration strategy. When you're loading data on a schedule, you want your merge logic to be wrapped in a pipeline that handles retries, logging, and incremental watermarking. Incrementally Loading Data into a Fabric Lakehouse with Watermarks and Pipeline Lookup Activities picks up exactly where this lesson leaves off.

You should also think about what happens to your Delta tables over time as merges accumulate small files and the transaction log grows. Optimizing Delta Table Performance in a Fabric Lakehouse: V-Order, OPTIMIZE, VACUUM, and Z-Order for Faster Queries and Smaller Storage covers the maintenance operations that keep your tables fast and storage costs reasonable.