Stop truncating and reloading million-row tables every night. This lesson walks you through building a complete Dataflow Gen2 to Lakehouse pipeline with watermark-based incremental extraction, PySpark Delta MERGE semantics, and a scheduled orchestration pipeline that handles inserts, updates, and soft deletes correctly.

Here's a scenario that plays out constantly in real data engineering work: you have a source system — an Azure SQL database, a Salesforce org, a REST API — that updates records throughout the day. Some rows are new. Some are modifications to existing records. A handful might be soft-deleted. Your lakehouse needs to reflect that current state without you truncating and reloading a million-row table every few hours just to capture fifty changed records.
The naive approach — full extract, full overwrite — works fine at ten thousand rows. It falls apart at ten million, and it destroys your Fabric capacity budget in the process. What you actually want is a pipeline that extracts only what's changed since the last run, lands those changes in a staging layer, and then merges them into your target Delta table with proper upsert semantics. Run that on a schedule and you have a production-grade incremental load pattern that scales as your source data grows.
By the end of this lesson you'll have built exactly that: a Dataflow Gen2 that performs an incremental extract from a SQL source, writes results to a lakehouse staging table, and a Fabric Data Pipeline that orchestrates the whole chain — dataflow refresh, PySpark merge into the target table, watermark update — on a nightly schedule. You'll understand not just how to wire it together but why each piece is designed the way it is, so you can adapt it to your own sources and business rules.
What you'll learn:
You should already be comfortable with the basics before diving into this lesson:
You'll also need a SQL source that has a reliable modified_at or updated_at timestamp column. The examples below use an Azure SQL Database table called sales.orders, but the pattern transfers directly to PostgreSQL, Snowflake, Dataverse, or any connector that supports query folding in Power Query.
Before writing a single query, let's be explicit about the data flow so the design decisions make sense.
The pattern has four moving parts:
1. A watermark table — a small Delta table in your lakehouse with one row per tracked source table. It stores the last successful extraction timestamp. At the start of each pipeline run, we read this value. At the end of a successful run, we update it.
2. Dataflow Gen2 — connects to the source, filters to rows where modified_at > watermark_value, performs any cleaning or type coercion, and writes the result to a staging Delta table in the lakehouse. The watermark value is passed in as a parameter from the pipeline.
3. A PySpark notebook — reads the staging table and merges its rows into the target (final) Delta table using Delta Lake's MERGE command. This handles inserts for new rows and updates for modified ones in a single atomic operation.
4. A Fabric Data Pipeline — orchestrates activities in sequence: Lookup the current watermark, trigger the Dataflow Gen2 refresh passing the watermark as a parameter, run the notebook, update the watermark table on success.
Key insight
Separating staging from target gives you a natural checkpoint. If the merge notebook fails, your staging table still has the extracted data. You can re-run just the notebook without re-hitting the source. If the dataflow fails, your target table is completely untouched — no partial writes.
The architecture deliberately follows the bronze/silver pattern from the Medallion Architecture in Microsoft Fabric. The staging table is your bronze layer — raw extracted rows with minimal transformation. The merge target is silver — deduplicated, upserted, production-quality records.
Open your lakehouse and launch a new notebook from the ribbon (Home → New notebook). We'll create the watermark control table with PySpark.
from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, StringType, TimestampType
from datetime import datetime
spark = SparkSession.builder.getOrCreate()
# Define the schema
schema = StructType([
StructField("source_table", StringType(), False),
StructField("last_watermark", TimestampType(), False),
StructField("last_updated", TimestampType(), False)
])
# Seed with a far-past date so the first run pulls all history
initial_data = [
("sales.orders", datetime(2020, 1, 1, 0, 0, 0), datetime.utcnow())
]
df = spark.createDataFrame(initial_data, schema)
df.write.format("delta") \
.mode("overwrite") \
.option("mergeSchema", "true") \
.saveAsTable("watermark_control")
Run the cell. After a few seconds, watermark_control appears in your lakehouse Tables view. That seed date of 2020-01-01 acts as a "pull everything" marker for the first run — once the pipeline completes successfully, it will be replaced with the actual run timestamp.
Tip
If your source has years of history and you don't want the first run to pull all of it, change the seed date to something more recent — like six months ago. The pipeline respects whatever value is in this table, so you have full control over the initial load window.
We need two more tables: orders_staging for the incremental extract landing zone, and orders_silver as the merge target. Let's define them explicitly rather than letting the dataflow auto-create them, so we control the schema from day one.
from pyspark.sql.types import (
StructType, StructField, StringType, IntegerType,
DecimalType, TimestampType, BooleanType
)
orders_schema = StructType([
StructField("order_id", IntegerType(), False),
StructField("customer_id", IntegerType(), True),
StructField("order_status", StringType(), True),
StructField("order_total", DecimalType(18, 2), True),
StructField("region", StringType(), True),
StructField("is_deleted", BooleanType(), True),
StructField("created_at", TimestampType(), True),
StructField("modified_at", TimestampType(), True)
])
# Create staging table (overwritten each run)
spark.createDataFrame([], orders_schema).write \
.format("delta") \
.mode("overwrite") \
.saveAsTable("orders_staging")
# Create silver target table (merged each run)
spark.createDataFrame([], orders_schema).write \
.format("delta") \
.mode("overwrite") \
.saveAsTable("orders_silver")
Note
Pre-defining schemas prevents the auto-schema inference problems that come back to bite you later — things like a numeric column being inferred as long from one batch and integer from another, causing merge failures on type mismatch. Schema enforcement is especially valuable for tables that Direct Lake semantic models will read, since type inconsistencies can silently break framing.
Navigate to your workspace and create a new Dataflow Gen2. This is where the incremental extract logic lives.
In the Power Query editor, add a new source → Azure SQL Database. Enter your server and database credentials. Navigate to the sales.orders table and select it. Power Query will generate a standard source step like:
let
Source = Sql.Database("yourserver.database.windows.net", "SalesDB"),
sales_orders = Source{[Schema="sales", Item="orders"]}[Data]
in
sales_orders
Don't publish yet. We need to add a parameter.
In the Power Query editor, go to Manage Parameters → New Parameter. Configure it:
LastWatermark2020-01-01 00:00:00 (a placeholder; the pipeline will override this at runtime)This parameter is what allows the pipeline to tell the dataflow "only pull rows newer than this timestamp." The concept of passing dynamic values from a pipeline into a dataflow is covered in depth in Parameterizing Dataflow Gen2 Queries with Pipeline Integration, but the essential mechanics are: the pipeline passes a string value, the dataflow converts it to a typed parameter, and M uses it in a filter step that folds to the source as a SQL WHERE clause.
Back in your sales_orders query, add a step after the source navigation that filters on modified_at:
let
Source = Sql.Database("yourserver.database.windows.net", "SalesDB"),
sales_orders = Source{[Schema="sales", Item="orders"]}[Data],
FilteredRows = Table.SelectRows(
sales_orders,
each [modified_at] > LastWatermark
)
in
FilteredRows
This is where query folding matters enormously. If Table.SelectRows folds back to the source as a SQL predicate, only the matching rows travel across the wire. If it doesn't fold, Power Query pulls the entire table and filters in memory — the exact performance problem we're solving. To confirm folding is happening, right-click the FilteredRows step and choose "View Native Query." You should see a SQL statement with a WHERE modified_at > @p1 clause. If you get an error or see no native query, check that your connection type supports folding (Azure SQL, PostgreSQL, and Snowflake do; some REST and Excel connectors don't).
Warning
If your source column is modified_at as DateTimeOffset (timezone-aware) and your parameter is plain DateTime, the fold may fail silently — filtering in memory instead. Cast the parameter explicitly if needed: DateTimeZone.From(LastWatermark). Always verify via "View Native Query" before assuming folding is active.
Add a type enforcement step to lock in column types and trim any string whitespace, following the same discipline we set up in the schema creation step:
let
Source = Sql.Database("yourserver.database.windows.net", "SalesDB"),
sales_orders = Source{[Schema="sales", Item="orders"]}[Data],
FilteredRows = Table.SelectRows(
sales_orders,
each [modified_at] > LastWatermark
),
TypedColumns = Table.TransformColumnTypes(FilteredRows, {
{"order_id", Int32.Type},
{"customer_id", Int32.Type},
{"order_status", type text},
{"order_total", Currency.Type},
{"region", type text},
{"is_deleted", type logical},
{"created_at", type datetime},
{"modified_at", type datetime}
}),
TrimmedStrings = Table.TransformColumns(TypedColumns, {
{"order_status", Text.Trim, type text},
{"region", Text.Trim, type text}
})
in
TrimmedStrings
In the bottom panel of the Power Query editor, click "Add data destination" → Lakehouse. Select your workspace and lakehouse, then choose the orders_staging table as the target. Set the update method to Replace — staging is a scratch table that gets fully overwritten each run. That's intentional: staging only ever holds the current batch of changed rows, not a growing history.
Key insight
Using Replace (overwrite) on staging while using Merge on the target is the right division of concerns. Staging is cheap to overwrite — it's small by design, containing only the delta. The target table holds the full dataset and must be merged, not overwritten, to preserve records that weren't in this batch.
Publish the dataflow.
Create a new notebook in your workspace. This notebook will read orders_staging and merge its contents into orders_silver.
from delta.tables import DeltaTable
from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
# Read the staging batch
staging_df = spark.read.format("delta").table("orders_staging")
# If staging is empty (no changes since last watermark), exit gracefully
if staging_df.isEmpty():
print("No new or modified records in this batch. Skipping merge.")
mssparkutils.notebook.exit("skipped")
# Reference the target Delta table
target_table = DeltaTable.forName(spark, "orders_silver")
# Define merge logic:
# - Match on the business key (order_id)
# - When matched AND source is marked deleted: delete from target
# - When matched AND not deleted: update all fields
# - When not matched: insert new row
target_table.alias("target").merge(
staging_df.alias("source"),
"target.order_id = source.order_id"
).whenMatchedDelete(
condition="source.is_deleted = true"
).whenMatchedUpdateAll(
condition="source.is_deleted = false"
).whenNotMatchedInsertAll(
condition="source.is_deleted = false"
).execute()
print(f"Merge complete. Processed {staging_df.count()} staging rows.")
A few things worth unpacking here:
The empty-staging short-circuit. If nothing changed since the last watermark, staging will be empty. Rather than running a merge that touches the target table for no reason (and burns capacity), we exit early with a status value the pipeline can inspect.
Soft deletes. The is_deleted flag is how most transactional systems signal record removal without physically deleting the row. Our merge handles this explicitly: a matched row with is_deleted = true triggers a DELETE from the target. A matched row with is_deleted = false triggers an UPDATE. New rows with is_deleted = false get INSERT'd. New rows where is_deleted = true are ignored — we don't insert records that were created and deleted before we ever saw them.
whenMatchedUpdateAll vs explicit field mapping. Using updateAll() and insertAll() updates every column automatically, which is convenient but means your staging and target schemas must match exactly. If they ever diverge — say, you add a column to the source — you'll want to use explicit column mapping instead. Schema evolution in Delta tables is covered in Handling Schema Evolution in Fabric Lakehouse Delta Tables.
Tip
Add a watermark update step directly to this notebook after the merge executes successfully. That way, the watermark is only updated when the merge has actually committed — not just when the dataflow finished, which could leave you with an advanced watermark but incomplete target data.
Add the watermark update at the bottom of the notebook:
from datetime import datetime
from pyspark.sql.functions import lit, current_timestamp
# Update the watermark to now (UTC)
run_timestamp = datetime.utcnow()
spark.sql(f"""
UPDATE watermark_control
SET last_watermark = CAST('{run_timestamp.isoformat()}' AS TIMESTAMP),
last_updated = current_timestamp()
WHERE source_table = 'sales.orders'
""")
print(f"Watermark updated to {run_timestamp}")
Name this notebook nb_merge_orders_silver and save it.
Now we wire everything together. Create a new Data Pipeline in your workspace and name it pl_incremental_orders.
Add a Lookup activity. Configure it:
SELECT CAST(last_watermark AS VARCHAR(30)) AS last_watermark
FROM watermark_control
WHERE source_table = 'sales.orders'
Name this activity Lookup_Watermark.
Note
We cast the timestamp to VARCHAR here because Dataflow Gen2 parameters are passed as strings from the pipeline, then converted back to typed values inside the M query. It's a bit awkward but it's the correct approach — the alternative is passing an epoch integer, which is harder to debug.
Add a Dataflow activity, connected to Lookup_Watermark on success. Select your Dataflow Gen2 from the dropdown.
Under Parameters, add a parameter binding:
LastWatermark@activity('Lookup_Watermark').output.firstRow.last_watermarkThis passes the watermark string from the Lookup result directly into the dataflow parameter. Inside the dataflow, Power Query receives this string and uses it in the filter step. The dataflow runs, hits the source with a filtered query, and writes the resulting rows to orders_staging.
Add a Notebook activity, connected to the Dataflow activity on success. Select nb_merge_orders_silver.
No parameters needed here — the notebook reads staging and updates the watermark entirely based on the lakehouse tables. The pipeline doesn't need to pass anything.
Name this activity Notebook_Merge_Silver.
Warning
The Notebook activity will mark itself as succeeded even if your notebook raises a Python exception — unless you use mssparkutils.notebook.exit() with a non-zero code or the exception propagates to the top level. Always test your notebook in isolation first to verify it handles empty staging, schema mismatches, and other edge cases before trusting the pipeline's green checkmarks.
In the pipeline toolbar, click Schedule. Set it to run daily at 02:00 UTC (or whatever time gives your source data the most settlement time before extract). Enable the schedule and save.
Your pipeline canvas at this point has three activities in a linear chain:
Lookup_Watermark → [on success] → Dataflow_Refresh → [on success] → Notebook_Merge_Silver
This is intentionally simple. For production use, you'd add error handling branches — an email notification if the dataflow fails, a cleanup step if the notebook fails midway. The pattern for adding those branches is covered in Implementing End-to-End Pipeline Error Handling in Microsoft Fabric.
Before trusting this to run unattended, do a manual validation run.
1. Verify the initial state. In your lakehouse, query watermark_control:
SELECT * FROM watermark_control
You should see last_watermark = 2020-01-01 00:00:00 — the seed value.
2. Trigger the pipeline manually. Hit Run in the pipeline editor. Watch the activity statuses turn green one by one.
3. Check staging. After the dataflow completes, query orders_staging:
SELECT COUNT(*) as row_count,
MIN(modified_at) as earliest_change,
MAX(modified_at) as latest_change
FROM orders_staging
Since the watermark was 2020-01-01, this should return all rows in your source that have modified_at > 2020-01-01. That's your initial full load.
4. Check the target. After the notebook completes:
SELECT COUNT(*) as total_rows,
COUNT(CASE WHEN is_deleted = true THEN 1 END) as soft_deleted
FROM orders_silver
Row count should match staging (minus any is_deleted = true rows, which were filtered out of insert).
5. Check the watermark update. Query watermark_control again. The last_watermark should now reflect the timestamp when the notebook ran — typically within seconds of when you triggered the pipeline.
6. Trigger a second run immediately. Since no new changes have come through since the first run, staging should be empty after the second dataflow refresh. The notebook's empty-staging guard should fire, and the watermark should not advance.
SELECT COUNT(*) FROM orders_staging -- should be 0
This "idempotency test" is the most important validation. A pipeline that produces different results when run twice with the same source data is not production-ready.
Now that you've built the core pattern, extend it to handle a realistic complication: your orders source has an order_lines child table that also needs incremental loading. The order_lines table has a composite business key of (order_id, line_number) and its own modified_at column.
Your task:
watermark_control for sales.order_lines using the same seed date.order_lines_staging and order_lines_silver Delta tables in your lakehouse with an appropriate schema.order_lines using a separate LastWatermarkLines parameter.order_lines_staging → order_lines_silver, matching on the composite key (order_id, line_number).The parallel execution piece is the interesting challenge. In the pipeline canvas, you can have multiple independent activity chains that run simultaneously. Connect both Lookup activities to a single final Set Variable or dummy Wait activity that only fires when both notebook merges have succeeded. This gives you concurrency on the extraction without letting either chain mark the pipeline complete before both are done.
"The dataflow fails with a parameter type error"
This almost always means the string value from the Lookup activity isn't parseable as a datetime in the M query. Check the format coming out of your SQL cast — 2024-01-15 02:00:00.000 works fine; Jan 15 2024 2:00AM does not. Use CONVERT(VARCHAR(23), last_watermark, 121) in your SQL query to force the ISO 8601 format that Power Query expects.
"Query folding isn't happening and the dataflow is slow"
The most common culprit is adding a step before the filter that breaks the folding chain — things like Table.AddColumn with a custom function, or Table.RenameColumns on some connector types. Move the filter step as early as possible in the query, ideally immediately after the source navigation step, before any transforms that might break folding.
"The merge notebook is producing duplicate rows in orders_silver"
This means your source table has multiple rows with the same order_id but different modified_at values in the same batch — which can happen if a record is updated twice within a single extraction window. Delta Lake's MERGE processes each source row sequentially but doesn't deduplicate within the source itself. Add a deduplication step in the notebook before the merge:
from pyspark.sql.window import Window
from pyspark.sql.functions import row_number, desc
# Keep only the most recent version of each order_id in staging
w = Window.partitionBy("order_id").orderBy(desc("modified_at"))
staging_df = staging_df.withColumn("rn", row_number().over(w)) \
.filter("rn = 1") \
.drop("rn")
"The pipeline succeeds but orders_silver has stale data"
Check whether the Notebook activity is actually executing the latest saved version of the notebook. If you edited the notebook after adding it to the pipeline, the pipeline points to the notebook item by reference, so it should always use the current version — but verify by checking the notebook's last-modified timestamp in the workspace view against when the pipeline ran (visible in the Monitoring Hub).
"Delta table performance degrades after a few weeks of daily merges"
Daily merges generate small Delta files rapidly. You'll want to run OPTIMIZE and VACUUM on a weekly schedule. This is a standard maintenance task covered in Optimizing Delta Table Performance in a Fabric Lakehouse. Add an OPTIMIZE orders_silver ZORDER BY (region, order_status) call at the end of your merge notebook — it runs after the merge writes are committed, so it only needs to compact the new files.
"The watermark updated but I don't trust that the merge actually committed"
This is a valid concern, especially during development. Delta Lake's MERGE is atomic — either the entire merge transaction commits or nothing does. You can verify by using time travel to inspect the table's history:
spark.sql("DESCRIBE HISTORY orders_silver").show(5, truncate=False)
Each successful MERGE appears as a MERGE operation in the history with a row count. If the history shows a MERGE at 02:03 UTC and your watermark shows 2024-01-15 02:03:17, you can be confident the data is consistent.
You've built a production-pattern incremental load pipeline that does four things well: it only extracts what's changed, it lands changes in a staging layer without touching the target, it merges with correct upsert and soft-delete semantics, and it tracks its own progress with a persistent watermark so each run is independent and restartable.
The watermark → Dataflow Gen2 → staging → PySpark merge → watermark update cycle is a durable pattern. It works whether your source is Azure SQL or Snowflake or a REST API, whether your target table has ten columns or a hundred, and whether you're running this once a day or once an hour.
From here, there are several natural directions to go deeper:
The pattern you've built here is the backbone of most real Fabric data estates. Everything else — medallion layers, semantic models, Direct Lake reporting — builds on the assumption that your silver tables are current, consistent, and correctly merged.