Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
Microsoft Fabric

Implementing Change Data Capture in a Fabric Data Pipeline: Using Lookup, GetMetadata, and Delta Lake MERGE to Detect and Apply Source Changes Without Full Reloads

Learn how to build a production-ready CDC pipeline in Microsoft Fabric that uses Lookup activities for watermark state management, GetMetadata for schema validation, Copy Activity for incremental extraction, and Delta Lake MERGE to apply inserts, updates, and deletes to your lakehouse without ever running a full reload. Covers hard delete detection, MERGE performance tuning, and failure recovery patterns.

🔥 Expert28 min readNov 20, 2026Updated Nov 20, 2026
Implementing Change Data Capture in a Fabric Data Pipeline: Using Lookup, GetMetadata, and Delta Lake MERGE to Detect and Apply Source Changes Without Full Reloads
On this page
  • Introduction
  • Prerequisites
  • Understanding the CDC Architecture We're Building
  • Setting Up the Watermark Control Table
  • Building the Pipeline: The Lookup Activity
  • The GetMetadata Activity: Validating Before You Move
  • The Copy Activity: Extracting Only Changed Rows
  • Capturing the Current Batch Timestamp
  • The MERGE Notebook: Applying Changes to the Silver Layer
  • Updating the Watermark After Success
  • Handling Hard Deletes: When Rows Disappear From Source
  • Approach 1: Periodic Full Comparison (Simple, Expensive)
  • Approach 2: SQL Server Change Tracking
  • Approach 3: Hash-Based Change Detection
  • Delta Lake MERGE Performance: What Slows It Down and How to Fix It
  • Why MERGE is Slow on Unpartitioned Tables
  • Solution 1: Partition the Silver Table
  • Solution 2: Narrow the MERGE Scope with a Subquery
  • Solution 3: Low Shuffle Merge
  • Extending to Slowly Changing Dimensions
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • "MERGE matched the same target row multiple times"
  • Watermark Advances But Silver Table Has Gaps
  • Copy Activity Returns 0 Rows Every Run After The First
  • GetMetadata Fails With "Dataset Not Found"
  • MERGE Is Extremely Slow (Hours Instead of Minutes)
  • Notebook Output Not Available in Pipeline
  • Late-Arriving Records Missed by Watermark
  • Summary & Next Steps

Implementing Change Data Capture in a Fabric Data Pipeline: Using Lookup, GetMetadata, and Delta Lake MERGE to Detect and Apply Source Changes Without Full Reloads

Introduction

Imagine you're managing a data platform that ingests customer orders from an operational SQL database into a Fabric lakehouse. Every night, your pipeline runs a full copy of the orders table — 50 million rows, dozens of columns, pulling across a network connection — just to pick up the 3,000 rows that actually changed since yesterday. The other 49,997,000 rows are identical to what you already have. You're burning compute, burning time, and hammering your source system for no reason.

This is the problem Change Data Capture (CDC) solves. Instead of asking "give me everything," a CDC-aware pipeline asks "give me only what changed, when it changed, and what the change was." Applied correctly, it transforms your nightly 45-minute full reload into a 90-second incremental load that's kinder to your source systems, cheaper to run, and more reliable under data volume growth. But implementing CDC in Microsoft Fabric requires you to wire together several pipeline activities that most practitioners haven't combined before: Lookup to retrieve watermark state, GetMetadata to inspect source structures, Copy Activity to stage only changed rows, and finally a Delta Lake MERGE operation — either in a Notebook or via SQL — to intelligently apply inserts, updates, and deletes to your lakehouse tables.

By the end of this lesson, you'll have built a complete, production-ready CDC pipeline in Microsoft Fabric that handles all three change types without full reloads. You'll understand not just how each piece works, but why the architecture is designed the way it is, what can go wrong at scale, and how to extend the pattern to cover slow-changing dimensions, multi-source pipelines, and schema evolution scenarios.

What you'll learn:

  • How to design a CDC watermark strategy using pipeline Lookup activities against a control table
  • How to use GetMetadata to validate source structure before attempting incremental loads
  • How to stage changed rows from SQL sources into the lakehouse bronze layer without overwriting existing data
  • How to implement a Delta Lake MERGE statement in PySpark to apply inserts, updates, and deletes in a single atomic operation
  • How to handle edge cases: late-arriving records, schema changes mid-pipeline, delete detection without source CDC flags, and duplicate change events

Prerequisites

Before working through this lesson, you should be comfortable with the following:

  • Fabric data pipelines including Copy Activity, Lookup Activity, and If Condition — if you need a foundation, Orchestrating Loads with Fabric Data Pipelines: Copy Activities, Parameters, and Schedules covers the core concepts.
  • Delta Lake and PySpark basics — specifically how Delta tables store data and support time travel. If you're newer to this, Writing and Running Your First PySpark Notebook in Microsoft Fabric: Reading Files, Creating Delta Tables, and Saving to the Lakehouse will get you there.
  • Delta MERGE patterns — we'll use MERGE extensively. Writing Data from a Spark Notebook to a Fabric Lakehouse Delta Table: Append, Overwrite, and Merge Patterns with PySpark is the prerequisite lesson for the merge section.
  • Watermark-based incremental loading — this lesson builds on the concept introduced in Incrementally Loading Data into a Fabric Lakehouse with Watermarks and Pipeline Lookup Activities.
  • A Fabric workspace with at least Contributor access and a lakehouse already created.

Understanding the CDC Architecture We're Building

Before writing a single activity, you need to understand the full data flow and why each component exists. Let's use a concrete scenario: you have an Azure SQL Database (or SQL Server on-premises via gateway) that hosts an Orders table in an e-commerce operational system. That table has millions of rows and receives thousands of inserts, updates, and deletes every day. Your lakehouse needs to stay synchronized within a few hours, and a full reload every run is no longer acceptable.

Here's the architecture we'll build:

[SQL Source: Orders Table]
        |
        | (filtered by watermark: ModifiedAt > last_watermark)
        v
[Pipeline: Lookup → GetMetadata → Copy Activity → Notebook MERGE]
        |
        | Bronze: staged CSV/Parquet files (raw, unchanged)
        v
[Lakehouse Bronze Layer: orders_staging Delta Table]
        |
        | MERGE on order_id (insert new, update changed, delete flagged)
        v
[Lakehouse Silver Layer: orders Delta Table]
        |
        | Watermark control table updated after successful MERGE
        v
[Control Table: pipeline_watermarks]

The key design decision here is separation of concerns:

  1. The pipeline handles orchestration, state management, and data movement
  2. The notebook handles transformation and the MERGE logic
  3. The control table stores state between runs

This matters because if the MERGE step fails, you can replay it without re-extracting from source. If extraction fails, you haven't touched the silver table at all. Each step is independently restartable.

Key insight

The bronze staging layer isn't just a landing zone — it's a replay buffer. If your MERGE notebook crashes halfway through, you re-run just the notebook against the same staged data. This decoupling is what makes the pipeline reliable at scale.


Setting Up the Watermark Control Table

A watermark is simply a marker that records where you stopped reading last time. For time-based CDC, it's a timestamp. For sequence-based CDC, it's an integer or GUID. We'll use timestamps since they're the most common pattern with SQL operational systems.

You need to store this watermark somewhere persistent across pipeline runs. We'll use a Delta table in the lakehouse itself — specifically in a control schema within your lakehouse.

Create this table using a Fabric notebook. Open a new notebook attached to your lakehouse and run:

from delta.tables import DeltaTable
from pyspark.sql import SparkSession
from pyspark.sql.types import StructType, StructField, StringType, TimestampType
import datetime

spark = SparkSession.builder.getOrCreate()

# Create control table schema
schema = StructType([
    StructField("pipeline_name", StringType(), False),
    StructField("source_table", StringType(), False),
    StructField("last_watermark", TimestampType(), True),
    StructField("last_run_status", StringType(), True),
    StructField("last_run_timestamp", TimestampType(), True)
])

# Seed the initial watermark — use epoch time so first run picks up everything
initial_data = [
    ("orders_cdc", "dbo.Orders", datetime.datetime(2000, 1, 1, 0, 0, 0), "initialized", datetime.datetime.utcnow())
]

df = spark.createDataFrame(initial_data, schema)

# Write as Delta table to the control schema
df.write.format("delta").mode("overwrite").saveAsTable("control.pipeline_watermarks")

print("Control table created successfully.")

Note

We seed the initial watermark as the year 2000 — a deliberately ancient date. This means the very first run will pick up the entire table history from your source, which is exactly what you want for the initial load. After that first run, the watermark updates to "now" and subsequent runs are truly incremental.

Now let's also look at what your source Orders table needs to support CDC. For timestamp-based watermarking to work reliably, your source table must have a column that records when each row was last modified. In SQL Server or Azure SQL, this typically looks like:

CREATE TABLE dbo.Orders (
    order_id        INT             NOT NULL PRIMARY KEY,
    customer_id     INT             NOT NULL,
    order_date      DATE            NOT NULL,
    total_amount    DECIMAL(10,2)   NOT NULL,
    status          VARCHAR(50)     NOT NULL,
    is_deleted      BIT             NOT NULL DEFAULT 0,  -- soft delete flag
    created_at      DATETIME2       NOT NULL DEFAULT GETUTCDATE(),
    modified_at     DATETIME2       NOT NULL DEFAULT GETUTCDATE()
);

The modified_at column is your watermark column. Every time a row is inserted or updated, modified_at must be set to the current time. The is_deleted flag handles soft deletes — we'll come back to hard delete detection later.

Warning

If your source table doesn't have a reliable modified_at column, you cannot do timestamp-based CDC. You have three options: (1) add the column to the source table, (2) use SQL Server Change Tracking or Change Data Capture at the database level, or (3) fall back to a full reload with MERGE deduplication. Never use created_at alone — it won't capture updates, only inserts.


Building the Pipeline: The Lookup Activity

With your control table in place, the pipeline can begin. The first activity in every run is a Lookup activity that reads the current watermark from your control table. This tells the rest of the pipeline where the last run stopped.

In your Fabric pipeline designer, add a Lookup activity. Configure it as follows:

Source dataset: Point to your lakehouse using the SQL Analytics Endpoint. The query should be:

SELECT 
    pipeline_name,
    source_table,
    last_watermark,
    last_run_status
FROM control.pipeline_watermarks
WHERE pipeline_name = 'orders_cdc'

First row only: Check this box. You want a single record returned, not an array.

After this Lookup runs, you can reference its output throughout the rest of the pipeline using expressions like:

@activity('LookupWatermark').output.firstRow.last_watermark
@activity('LookupWatermark').output.firstRow.last_run_status

It's also good practice to check whether the previous run completed successfully. Add an If Condition activity after the Lookup that checks @equals(activity('LookupWatermark').output.firstRow.last_run_status, 'success'). If the status is not success — meaning the last run failed partway through — you might want to re-run from the same watermark (not advance it), or trigger an alert. For now, the pipeline will proceed regardless, which means a failed run simply replays from the same watermark on the next attempt. That's actually the safe behavior you want.

Key insight

Never advance the watermark until the MERGE has successfully completed. If you update the watermark after extraction but before the MERGE, a pipeline failure leaves you with stale silver data and no way to know you missed a window of changes.


The GetMetadata Activity: Validating Before You Move

The GetMetadata activity is often skipped by practitioners who consider it overhead. This is a mistake at scale. GetMetadata lets you inspect properties of your source or destination before attempting data movement — and catching a problem here is far cheaper than catching it mid-copy when you're halfway through writing millions of rows.

Add a GetMetadata activity after the Lookup. Connect it to a dataset pointing to your Azure SQL source, and configure it to retrieve:

  • Column count — to detect schema changes (a column added or removed in source will cause your Copy Activity to behave unexpectedly)
  • Exists — confirms the source table is accessible with the current credentials

In the GetMetadata activity settings, set Field list to include columnCount and exists.

After GetMetadata runs, add another If Condition activity that evaluates:

@and(
    equals(activity('GetMetadataOrders').output.exists, true),
    equals(activity('GetMetadataOrders').output.columnCount, 12)
)

Replace 12 with the actual column count of your source table. If the condition is false — meaning the table disappeared or a column was added — route to a Fail activity with a descriptive message:

Schema mismatch detected on dbo.Orders. Expected 12 columns, found 
@{activity('GetMetadataOrders').output.columnCount}. Halting pipeline.

This seems like extra work, but consider the alternative: a schema change in your source silently causes your lakehouse table to store rows with shifted or missing columns, which then corrupts your silver data. By the time anyone notices, days of data may be wrong. The GetMetadata check costs you milliseconds and saves you hours of debugging.

Tip

Store the expected column count as a pipeline parameter rather than hardcoding it. When you intentionally add a column to the source, you update the parameter and your pipeline adapts. Hardcoding invites fragility.

For handling schema evolution properly once you've detected it, the patterns in Handling Schema Evolution in Fabric Lakehouse Delta Tables: Adding Columns, Merging Incompatible Schemas, and Enforcing Constraints Across Medallion Layers are exactly what you need.


The Copy Activity: Extracting Only Changed Rows

Now we get to the data movement. The Copy Activity extracts rows from the SQL source that have changed since the last watermark and stages them into the lakehouse bronze layer.

Add a Copy Activity after your GetMetadata validation. This is where the watermark value from your Lookup activity becomes critical.

Source configuration:

  • Dataset: Azure SQL Database, table dbo.Orders
  • Use query (not table): Switch to Query mode and use a dynamic query expression

The source query should look like this:

SELECT 
    order_id,
    customer_id,
    order_date,
    total_amount,
    status,
    is_deleted,
    created_at,
    modified_at
FROM dbo.Orders
WHERE modified_at > '@{activity('LookupWatermark').output.firstRow.last_watermark}'
  AND modified_at <= GETUTCDATE()
ORDER BY modified_at ASC

Warning

Always use a closed upper bound (<= GETUTCDATE()) in your extraction query, not just an open lower bound. Without the upper bound, rows that are modified during the pipeline's own run window can slip through — you read them partway through the copy, then they get modified again, and you end up with an inconsistent snapshot. The upper bound also becomes your new watermark value.

You also need to capture this upper bound timestamp so you can store it after the MERGE succeeds. Add a Set Variable activity parallel to the Copy Activity that sets a pipeline variable new_watermark to @utcNow(). Technically you want the timestamp at the start of your extraction window, so set this variable before the Copy Activity begins, not after.

Sink configuration:

  • Dataset: Lakehouse file path, something like Files/bronze/orders_staging/
  • Format: Parquet (preferred over CSV for type fidelity — timestamps, decimals, and booleans all round-trip correctly)
  • Write behavior: Overwrite existing files in this partition

Each pipeline run overwrites the staging folder with the latest batch of changes. This is intentional — the staging layer holds only the current batch, not a cumulative history. The Delta table in silver holds the history.

Performance settings for Copy Activity:

For large change sets, configure parallelism:

  • Degree of copy parallelism: 4–8 (depends on your source system's connection limits)
  • Enable staging: Yes, via Azure Blob Storage if your source is behind a firewall

If you're regularly extracting more than 1 million rows per run, consider partitioning the extraction by order_id range using the Copy Activity's partition settings. This pushes multiple parallel queries to the source and dramatically improves throughput.


Capturing the Current Batch Timestamp

Before moving to the MERGE step, you need a reliable record of what timestamp window this batch covers. Add a Lookup activity after the Copy Activity that queries the maximum modified_at from the rows you just staged:

SELECT MAX(modified_at) as batch_max_watermark
FROM OPENROWSET(
    BULK 'https://onelake.dfs.fabric.microsoft.com/<workspace>/<lakehouse>.Lakehouse/Files/bronze/orders_staging/**',
    FORMAT = 'PARQUET'
) AS staged

Actually, querying staged Parquet files via OPENROWSET is clunky and requires specific OneLake configurations. A cleaner approach: set a pipeline variable new_watermark to @utcNow() at the very beginning of the pipeline run (before extraction starts), and use that as the upper bound in your SQL query AND as the watermark to store after success. This way the watermark reflects "data as of the moment this pipeline started running," which is a clean, reproducible boundary.

Pipeline Variable: new_watermark = @utcNow()  [set at pipeline start]
Extraction query: WHERE modified_at > {last_watermark} AND modified_at <= {new_watermark}
After success: store new_watermark as the new last_watermark

This pattern has an important property: it's idempotent. If you run the pipeline twice with the same new_watermark, you extract the same rows and perform the same MERGE. MERGE is itself idempotent by design. This means you can safely retry failed runs.


The MERGE Notebook: Applying Changes to the Silver Layer

This is where the real intelligence lives. The pipeline's Copy Activity has deposited a batch of changed rows in Files/bronze/orders_staging/. Now a Notebook activity applies those changes to the silver Delta table using a MERGE operation.

Add a Notebook activity to your pipeline after the Copy Activity. Configure it to reference a notebook named apply_orders_cdc_merge. Pass pipeline parameters into the notebook:

Parameter Name Value
new_watermark @variables('new_watermark')
batch_path Files/bronze/orders_staging/

Inside the notebook, the PySpark code handles the full MERGE logic:

# Parameters injected from pipeline
new_watermark = getArgument("new_watermark")
batch_path = getArgument("batch_path")

from delta.tables import DeltaTable
from pyspark.sql import functions as F
from pyspark.sql.types import *

# Step 1: Load the staged changes batch
staged_df = spark.read.format("parquet").load(batch_path)

print(f"Staged row count: {staged_df.count()}")
print(f"Schema: {staged_df.dtypes}")

# Step 2: Deduplicate within the batch
# If the same order_id appears multiple times in one batch
# (e.g., updated twice within the extraction window),
# keep only the most recent version
staged_deduped = (
    staged_df
    .withColumn("_row_num", F.row_number().over(
        Window.partitionBy("order_id").orderBy(F.col("modified_at").desc())
    ))
    .filter(F.col("_row_num") == 1)
    .drop("_row_num")
)

print(f"After deduplication: {staged_deduped.count()} rows")

The deduplication step is critical and commonly missed. Within a single extraction window, the same order_id might appear multiple times if an order was modified several times before your pipeline ran. Without deduplication, your MERGE will try to match the same target row multiple times, which Delta Lake will reject with a MERGE matched the same target row multiple times error.

# Step 3: Ensure the silver orders table exists
# If this is the very first run, create an empty table with the right schema
try:
    silver_table = DeltaTable.forName(spark, "silver.orders")
    table_exists = True
except:
    table_exists = False

if not table_exists:
    # Create empty table from staged schema (first run only)
    empty_df = spark.createDataFrame([], staged_deduped.schema)
    empty_df.write.format("delta").saveAsTable("silver.orders")
    silver_table = DeltaTable.forName(spark, "silver.orders")
    print("Created silver.orders table on first run.")
# Step 4: Execute the MERGE
# This is the core CDC operation:
# - Rows in source but not in target → INSERT
# - Rows in both → UPDATE if any column changed
# - Rows in target flagged is_deleted=True in source → mark as deleted (or DELETE physically)

(
    silver_table.alias("target")
    .merge(
        staged_deduped.alias("source"),
        "target.order_id = source.order_id"
    )
    # When matched and the row is a soft delete: update with deleted flag
    .whenMatchedUpdate(
        condition="source.is_deleted = true",
        set={
            "status":        "source.status",
            "is_deleted":    "source.is_deleted",
            "modified_at":   "source.modified_at"
        }
    )
    # When matched and not deleted: update all business columns
    .whenMatchedUpdate(
        condition="source.is_deleted = false",
        set={
            "customer_id":   "source.customer_id",
            "order_date":    "source.order_date",
            "total_amount":  "source.total_amount",
            "status":        "source.status",
            "is_deleted":    "source.is_deleted",
            "modified_at":   "source.modified_at"
        }
    )
    # When not matched in target: insert new row
    .whenNotMatchedInsert(
        values={
            "order_id":      "source.order_id",
            "customer_id":   "source.customer_id",
            "order_date":    "source.order_date",
            "total_amount":  "source.total_amount",
            "status":        "source.status",
            "is_deleted":    "source.is_deleted",
            "created_at":    "source.created_at",
            "modified_at":   "source.modified_at"
        }
    )
    .execute()
)

print("MERGE completed successfully.")

Note

We're using soft deletes here — rows flagged is_deleted = true remain in the table but are marked as deleted. This is almost always the right choice for a lakehouse: hard deletes destroy history. Your Power BI reports can filter WHERE is_deleted = false. If your source does hard deletes (rows simply disappear), see the section on delete detection below.

# Step 5: Return the new watermark to the pipeline
# This is used by the pipeline to update the control table
mssparkutils.notebook.exit(new_watermark)

The mssparkutils.notebook.exit() call sends the watermark value back to the pipeline as the notebook's output. The pipeline will use this to update the control table.

The pattern of passing parameters into notebooks and reading their outputs is explained in detail in Using Notebook Variables and Parameters in Microsoft Fabric: Passing Pipeline Values to PySpark for Dynamic Lakehouse Ingestion.


Updating the Watermark After Success

After the Notebook activity completes successfully, add a final Lookup activity (or a Notebook activity) that updates the control table with the new watermark and marks the run as successful.

The cleanest way to do this is a Notebook activity with PySpark:

new_watermark = getArgument("new_watermark")
from pyspark.sql import functions as F
import datetime

# Parse the watermark from pipeline
wm_ts = datetime.datetime.fromisoformat(new_watermark.replace("Z", "+00:00"))

# Update control table using MERGE (idempotent upsert)
from delta.tables import DeltaTable

control_table = DeltaTable.forName(spark, "control.pipeline_watermarks")

update_df = spark.createDataFrame([
    ("orders_cdc", "dbo.Orders", wm_ts, "success", datetime.datetime.utcnow())
], ["pipeline_name", "source_table", "last_watermark", "last_run_status", "last_run_timestamp"])

(
    control_table.alias("t")
    .merge(update_df.alias("s"), "t.pipeline_name = s.pipeline_name")
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

print(f"Watermark updated to: {wm_ts}")

This notebook runs only on the Success dependency path from the MERGE notebook. If the MERGE fails, this never runs, and the control table retains the old watermark. The next run will re-extract from the same point and replay. This is exactly the safety net you need.

Tip

Add a parallel path from the MERGE notebook's Failure dependency to a "mark failure" notebook that updates last_run_status to 'failed' in the control table. This lets you query the control table to see which pipelines failed without opening the Monitoring Hub. Combine this with email alerts as described in Scheduling and Automating Fabric Data Pipeline Runs with Activity-Level Retries, Alerts, and Email Notifications.


Handling Hard Deletes: When Rows Disappear From Source

The soft delete pattern works beautifully when your source system supports it. But many operational databases actually delete rows — there's no is_deleted flag, the row just vanishes. How do you detect that in a watermark-based CDC pipeline?

This is a genuinely hard problem, and the answer depends on how strict your delete-detection requirements are. Here are three approaches in increasing order of reliability:

Approach 1: Periodic Full Comparison (Simple, Expensive)

Once per week (or month), run a full comparison between source and target. Extract all current order_id values from source, compare against the silver table, and mark rows absent from source as deleted. This is a background job, not your main CDC pipeline. For tables under ~10 million rows, this is practical.

# Full scan approach - run weekly, not every pipeline run
source_ids = spark.read.jdbc(
    url=jdbc_url,
    table="(SELECT order_id FROM dbo.Orders) ids",
    properties=connection_props
).select("order_id")

silver_active = spark.read.table("silver.orders").filter("is_deleted = false").select("order_id")

# Find rows in silver that no longer exist in source
deleted_ids = silver_active.join(source_ids, "order_id", "left_anti")

print(f"Detected {deleted_ids.count()} hard-deleted rows")

# Mark them as deleted in silver
from delta.tables import DeltaTable
silver_table = DeltaTable.forName(spark, "silver.orders")

(
    silver_table.alias("t")
    .merge(deleted_ids.alias("d"), "t.order_id = d.order_id")
    .whenMatchedUpdate(set={"is_deleted": "true", "modified_at": F.current_timestamp()})
    .execute()
)

Approach 2: SQL Server Change Tracking

If your source is SQL Server or Azure SQL, enable Change Tracking at the database and table level. This provides a system-maintained log of all inserts, updates, and deletes with a version number. Your pipeline reads from CHANGETABLE(CHANGES dbo.Orders, @last_version) instead of filtering by timestamp.

This is the most reliable approach and eliminates every edge case with timestamps. The tradeoff is that you need DBA-level access to enable Change Tracking, and your pipeline query structure changes significantly.

Approach 3: Hash-Based Change Detection

For sources where you can't modify anything, compute a row hash on all business columns during extraction and store it in the silver layer. On each run, extract all rows (or a recent window), compute hashes, and compare. Rows with changed hashes are updates; rows completely absent are deletes. This is essentially a smart full reload — it doesn't save extraction cost but does save MERGE cost on the target side.

Warning

Avoid Approach 3 for tables over a few million rows. Computing row hashes for 50 million rows every run defeats most of the purpose of CDC. Use it only for smaller reference tables where reliability matters more than performance.


Delta Lake MERGE Performance: What Slows It Down and How to Fix It

A common shock for practitioners moving to Delta Lake from SQL Server: MERGE on a large Delta table can be surprisingly slow. Here's why, and what you can do.

Why MERGE is Slow on Unpartitioned Tables

Delta Lake's MERGE operation works by:

  1. Reading all Parquet files in the target table
  2. Finding which files contain rows that match the merge condition
  3. Rewriting those files with the merged data
  4. Updating the Delta transaction log

Step 1 is the killer. On an unpartitioned silver.orders table with 50 million rows across 500 Parquet files, every MERGE has to read all 500 files to find the ones containing matching order_id values. That's expensive even with file-level statistics, because order_id is typically randomly distributed across files.

Solution 1: Partition the Silver Table

Partition by a low-cardinality column that correlates with your merge condition. For orders, partitioning by order_date truncated to month is a good choice if your changes are temporally clustered (recent orders change more than old ones):

# When creating the silver table, add partitioning
df.write \
  .format("delta") \
  .partitionBy("order_month") \  # derived column: year-month of order_date
  .saveAsTable("silver.orders")

Warning

Don't partition by order_id or any high-cardinality column — you'll create millions of tiny files, which is worse than no partitioning. The Optimizing Delta Table Performance in a Fabric Lakehouse article covers the full set of partitioning trade-offs.

Solution 2: Narrow the MERGE Scope with a Subquery

If you know your changed rows are concentrated in certain partitions (e.g., only orders from the last 30 days get updated), restrict the target side of the MERGE:

# Instead of merging against the full silver table,
# merge against only the relevant partitions
from pyspark.sql import functions as F

# Load only the last 90 days of target data
recent_silver = spark.read.table("silver.orders") \
    .filter(F.col("order_date") >= F.date_sub(F.current_date(), 90))

# MERGE staged changes against recent_silver only
# Then append anything outside the 90-day window separately

This pattern reduces the scan dramatically but requires you to handle the case where a staged change falls outside the window (usually via a separate INSERT for genuinely new orders with old dates).

Solution 3: Low Shuffle Merge

Enable the optimizeWrite and autoCompact Delta table properties to reduce small file problems that accumulate over many MERGE runs:

spark.sql("""
    ALTER TABLE silver.orders 
    SET TBLPROPERTIES (
        'delta.autoOptimize.optimizeWrite' = 'true',
        'delta.autoOptimize.autoCompact' = 'true'
    )
""")

Also run OPTIMIZE + ZORDER periodically (weekly or after large batches) to keep file layouts efficient:

spark.sql("OPTIMIZE silver.orders ZORDER BY (order_id)")

Extending to Slowly Changing Dimensions

If you're applying CDC to dimension tables rather than fact tables, you likely need to track history — not just apply the latest value. This is the slowly changing dimension (SCD) pattern, and Delta Lake MERGE handles it natively.

For a Customers dimension with Type 2 history, the MERGE logic adds expiry conditions:

from delta.tables import DeltaTable
from pyspark.sql import functions as F

# Source: staged customer changes
# Target: silver.customers with current_flag and effective_dates

(
    silver_customers.alias("target")
    .merge(
        staged_customers.alias("source"),
        "target.customer_id = source.customer_id AND target.current_flag = true"
    )
    # When matched and data actually changed: expire the existing row
    .whenMatchedUpdate(
        condition="""
            target.email != source.email 
            OR target.address != source.address 
            OR target.tier != source.tier
        """,
        set={
            "current_flag":   "false",
            "effective_to":   "source.modified_at"
        }
    )
    # When not matched: insert new row (new customer or new version of existing)
    .whenNotMatchedInsert(
        values={
            "customer_id":    "source.customer_id",
            "email":          "source.email",
            "address":        "source.address",
            "tier":           "source.tier",
            "current_flag":   "true",
            "effective_from": "source.modified_at",
            "effective_to":   "null"
        }
    )
    .execute()
)

# After expiring old rows, insert new versions of changed customers
changed_customers = staged_customers.join(
    spark.read.table("silver.customers").filter("current_flag = false AND effective_to IS NOT NULL"),
    "customer_id",
    "inner"
).select(staged_customers["*"], F.lit(True).alias("current_flag"), 
         F.lit(None).alias("effective_to"))

changed_customers.write.format("delta").mode("append").saveAsTable("silver.customers")

The full SCD Type 2 implementation with all edge cases is covered in Implementing Slowly Changing Dimensions in a Fabric Lakehouse: Using PySpark and Delta Lake MERGE to Track Historical Changes Across Medallion Layers.


Hands-On Exercise

Build a complete CDC pipeline for a Products table from an Azure SQL source into your Fabric lakehouse. The Products table has columns: product_id, product_name, category, price, stock_quantity, is_active, modified_at.

Step 1: Set up the control table Using a notebook, create control.pipeline_watermarks and seed an initial watermark for a products_cdc pipeline entry. Set last_watermark to 2000-01-01.

Step 2: Create the pipeline Build a pipeline with this activity sequence:

  1. Set Variable: capture run_start_time = @utcNow()
  2. Lookup: read last_watermark from control table for products_cdc
  3. GetMetadata: validate the Products table has 7 columns and exists
  4. If Condition: check column count == 7, fail with descriptive message if not
  5. Copy Activity: extract rows WHERE modified_at > {last_watermark} AND modified_at <= {run_start_time}, write Parquet to Files/bronze/products_staging/
  6. Notebook Activity: run apply_products_cdc_merge notebook, pass batch_path and new_watermark
  7. Notebook Activity (Success path): update control table watermark to run_start_time, status to 'success'
  8. Notebook Activity (Failure path): update control table status to 'failed', send alert

Step 3: Write the MERGE notebook In apply_products_cdc_merge:

  • Load staged Parquet from batch_path
  • Deduplicate by product_id, keeping latest modified_at
  • MERGE into silver.products: update if price, stock_quantity, or is_active changed; insert if new product
  • Exit with the new watermark value

Step 4: Test the pipeline Run the pipeline once (full initial load), then manually update 3 rows and insert 2 new rows in your source. Run again and verify:

  • The 3 updated rows in silver have new values
  • The 2 new rows appear in silver
  • The watermark advanced correctly
  • Unchanged rows were not affected

Step 5: Test failure recovery Deliberately break the MERGE notebook (introduce a syntax error), run the pipeline, then fix it and run again. Verify the pipeline re-processes the same batch correctly.


Common Mistakes & Troubleshooting

"MERGE matched the same target row multiple times"

Cause: Your staged batch contains duplicate order_id values. This happens when an order was modified multiple times within the extraction window.

Fix: Add the deduplication step before the MERGE (shown in the notebook code above). Use row_number() OVER (PARTITION BY order_id ORDER BY modified_at DESC) and filter to rank = 1.

Watermark Advances But Silver Table Has Gaps

Cause: The watermark update notebook runs even when the MERGE notebook fails. This happens if your activity dependency is set to "Completion" instead of "Success."

Fix: Change the dependency from the MERGE notebook to the watermark update notebook to "Success" only. Right-click the dependency arrow in the pipeline designer and check the dependency condition.

Copy Activity Returns 0 Rows Every Run After The First

Cause: The watermark stored in the control table is in UTC, but your source modified_at column is in local time. After the first run, the watermark is ahead of local time, so no rows match.

Fix: Always use UTC everywhere. Set GETUTCDATE() as the default for modified_at in your source table. If the source is in local time, apply a timezone offset in your Copy Activity query.

GetMetadata Fails With "Dataset Not Found"

Cause: The linked service credentials have expired or the firewall rules on your SQL source have changed.

Fix: Test the linked service connection in Fabric before debugging the pipeline. For on-premises sources, check that the self-hosted integration runtime service is running.

MERGE Is Extremely Slow (Hours Instead of Minutes)

Cause: The silver table is large and unpartitioned, forcing a full table scan on every MERGE.

Fix: Add partitioning (by month or category, depending on your data), run OPTIMIZE ... ZORDER BY (order_id) to cluster data physically, and enable autoOptimize. Also check whether your Spark cluster has enough executors — a single-node cluster will be painfully slow for large MERGEs.

Notebook Output Not Available in Pipeline

Cause: The notebook uses print() to output the watermark instead of mssparkutils.notebook.exit().

Fix: The pipeline can only receive notebook return values via mssparkutils.notebook.exit("value"). After this call, reference the output in pipeline expressions as @activity('NotebookName').output.result.exitValue.

Late-Arriving Records Missed by Watermark

Cause: A source system batch load inserts rows with modified_at timestamps that are hours old (e.g., a nightly sync from a mainframe). By the time your CDC pipeline runs, these rows' modified_at values fall before the current watermark.

Fix: Build a configurable lookback buffer into your extraction query. Instead of modified_at > {last_watermark}, use modified_at > DATEADD(HOUR, -2, '{last_watermark}'). Because your MERGE is idempotent, re-processing rows you've already seen just re-applies the same update. It's slightly wasteful but far better than missing records. For more details on building error-handling into the pipeline itself, see Implementing End-to-End Pipeline Error Handling in Microsoft Fabric: If Condition Activities, Failed Dependencies, and Email Alerts.


Summary & Next Steps

You've built a complete CDC pipeline that handles the three hardest problems in incremental data loading: detecting what changed, applying changes without destroying history, and recovering gracefully from failure. Let's review the key design principles that hold the whole thing together:

  1. Watermark state lives in a Delta control table, not in pipeline parameters or configuration files. This survives workspace reconfiguration, pipeline redeployment, and infrastructure changes.

  2. GetMetadata is your early warning system. A 100ms schema check prevents hours of debugging corrupted silver data.

  3. Bronze staging is a replay buffer. Separating extraction from transformation means you can replay the MERGE without hitting the source again.

  4. Deduplication before MERGE is non-negotiable. One duplicate key in the staged batch will crash the MERGE operation.

  5. Advance the watermark only after confirmed success. The dependency chain in your pipeline enforces this automatically if you set it up correctly.

  6. MERGE performance degrades with table size unless you partition thoughtfully and run periodic OPTIMIZE operations.

For your next steps, consider extending this architecture in a few directions:

  • Multi-source CDC orchestration: Use Orchestrating Multi-Notebook Workflows in Microsoft Fabric to run CDC pipelines for multiple source tables in parallel with shared error handling.

  • Database Mirroring as an alternative: If your source is Azure SQL or Snowflake, Database Mirroring in Microsoft Fabric provides built-in near-real-time replication without the pipeline complexity we've built here. Know when to use the managed service versus the custom approach.

  • Delta Time Travel for auditing: After several weeks of CDC runs, your silver Delta table has a rich history of transactions. Implementing Delta Lake Time Travel in a Fabric Lakehouse shows you how to query that history for compliance and debugging purposes.

  • Direct Lake reporting on CDC tables: Once your silver layer is reliably incremental, the data freshness characteristics make it an excellent candidate for Direct Lake semantic models in Power BI. The framing and partition configuration details are in Implementing Incremental Refresh for Direct Lake Semantic Models in Microsoft Fabric.

CDC is one of those patterns that seems complex until it clicks — and once it clicks, you'll apply it everywhere. The pipeline you've built today is production-grade and extensible. Add more source tables by duplicating the pattern and adding a row to the control table. The architecture scales horizontally without redesign.

Work With Us

From insight to implementation

Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

Let's Build

Microsoft Fabric Fundamentals

Previous

Building a Fabric Dataflow Gen2 to Lakehouse Pipeline with Incremental Extraction, Merge Semantics, and Scheduled End-to-End Refresh

Related Insights

Microsoft FabricPractitioner

Building a Fabric Dataflow Gen2 to Lakehouse Pipeline with Incremental Extraction, Merge Semantics, and Scheduled End-to-End Refresh

20 min
Microsoft FabricExpert

Implementing Column-Level Encryption and Dynamic Data Masking in a Fabric Warehouse: Protecting Sensitive Fields While Preserving Direct Lake and T-SQL Query Access

27 min
Microsoft FabricPractitioner

Implementing Fabric Warehouse Stored Procedures and Cross-Database Views: Encapsulating Business Logic and Joining Lakehouse SQL Endpoint Tables with Warehouse Tables Using T-SQL

24 min

On this page

  • Introduction
  • Prerequisites
  • Understanding the CDC Architecture We're Building
  • Setting Up the Watermark Control Table
  • Building the Pipeline: The Lookup Activity
  • The GetMetadata Activity: Validating Before You Move
  • The Copy Activity: Extracting Only Changed Rows
  • Capturing the Current Batch Timestamp
  • The MERGE Notebook: Applying Changes to the Silver Layer
  • Updating the Watermark After Success
  • Handling Hard Deletes: When Rows Disappear From Source
  • Approach 1: Periodic Full Comparison (Simple, Expensive)
  • Approach 2: SQL Server Change Tracking
  • Approach 3: Hash-Based Change Detection
  • Delta Lake MERGE Performance: What Slows It Down and How to Fix It
  • Why MERGE is Slow on Unpartitioned Tables
  • Solution 1: Partition the Silver Table
  • Solution 2: Narrow the MERGE Scope with a Subquery
  • Solution 3: Low Shuffle Merge
  • Extending to Slowly Changing Dimensions
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • "MERGE matched the same target row multiple times"
  • Watermark Advances But Silver Table Has Gaps
  • Copy Activity Returns 0 Rows Every Run After The First
  • GetMetadata Fails With "Dataset Not Found"
  • MERGE Is Extremely Slow (Hours Instead of Minutes)
  • Notebook Output Not Available in Pipeline
  • Late-Arriving Records Missed by Watermark
  • Summary & Next Steps