Microsoft Fabric

Incremental Loads in Microsoft Fabric: Watermarks, CDC and MERGE Without Reprocessing Everything

Stop full reloads in Microsoft Fabric without losing correctness: bounded watermark windows, Change Tracking vs CDC, Delta MERGE, deletes, late data and safe reruns.

Par JaviPublié 21 min de lectureIntermédiaire

Most Fabric pipelines start as full reloads, and that is usually the right call. Read the whole source table, overwrite the Lakehouse table, done. The logic is obvious, a rerun is always safe, and nobody has to think about what changed.

Then the source grows. The nightly copy that took four minutes takes forty. The notebook rewrites hundreds of millions of rows to pick up a few thousand changes, the capacity spends most of the night on work that produces the same result as yesterday, and the business asks for hourly refreshes.

So the pipeline becomes incremental, usually like this:

Requête SQL: Incremental Loads in Microsoft Fabric: Watermarks, CDC and MERGE Without Reprocessing EverythingT-SQL
SELECT * FROM dbo.sales_orders WHERE modified_at > @last_run;
T-SQL1 ligneAffichage en lecture seule. Aucune connexion à une base de données.

That line is where the trouble starts, not where it ends. An incremental load is a small distributed system with state, and the hard problems are all about that state:

  • Late data: rows that commit after you read past them.
  • Retries: a rerun that appends the same rows twice.
  • Deletes: rows that disappear from the source and never show up in a modified_at filter.
  • Duplicate processing: the same change applied twice to an aggregate.
  • Inconsistent checkpoints: a watermark that says “done” when the data is not.
  • Partial failures: Silver updated, Gold not, watermark unknown.

This article is about getting those right in Microsoft Fabric: Lakehouse Delta tables, notebooks, pipelines and mirroring. Performance matters, but correctness comes first. A fast pipeline that silently loses one window of changes is worse than a slow full reload.

It assumes you know the basics of Lakehouses, notebooks, Delta tables and the Bronze, Silver and Gold layers.

The running example

Every example uses the same tables, so the pieces fit together:

  • Source: dbo.sales_orders in an Azure SQL Database or SQL Server, shaped like the sales_orders file from the End-to-End Sales Analytics lab. The key is sales_order_id. Like most operational tables, and unlike the lab’s static CSV, it also has a modified_at datetime2 column maintained on every insert and update, and an is_deleted flag.
  • Lakehouse tables: bronze_sales_orders (append-only extracts), silver_sales_orders (current state, one row per order) and gold_daily_sales (orders and revenue per order date).
  • Control table: etl_watermark, one row per pipeline and source.
  • Pipeline: pl_sales_orders_incremental, which runs a notebook with the window passed as notebook parameters.

When a full reload is still the right choice

Incremental logic has a permanent cost: more code, more state, more failure modes, more to explain to the next engineer. Pay it only where it buys something. A full reload is often the better design for:

  • Small reference tables. Currencies, countries, channel codes. Rewriting a few thousand rows costs nothing and removes every correctness question.
  • Small dimensions. The lab’s 50 customers and 40 products do not need change detection. Many production dimensions with tens of thousands of rows don’t either.
  • Infrequent loads. A monthly feed where the full read takes minutes.
  • Trusted source snapshots. If the source already publishes a complete, consistent daily extract, overwriting from it is simpler than reconstructing changes.
  • Sources without a reliable change signal. No trustworthy modified_at, no Change Tracking, no CDC. A full snapshot plus comparison (below) is better than a watermark you cannot trust.
  • Reconciliation and recovery jobs. Even incremental tables benefit from an occasional full rebuild or comparison to catch whatever the incremental path missed.

A good rule: go incremental when the full reload’s duration, capacity consumption or source load is an actual problem, or soon will be. Not because incremental sounds more professional.

Choosing a change-detection strategy

The first decision is how you learn what changed. Everything else depends on it.

Strategy Source requirement Inserts Updates Deletes Complexity Best fit Main limitation
High-watermark column A modified_at or rowversion column updated on every change Yes Yes, if every write updates the column No (physical deletes are invisible) Low Tables with a trustworthy change column Misses deletes; in-flight transactions and clock issues can skip rows
SQL Server Change Tracking Change Tracking enabled on the database and table; primary key Yes Yes (net change; join back for current values) Yes Medium Net changes from SQL Server or Azure SQL without history No intermediate values; if you fall behind the retention period, you must reload
CDC CDC enabled; capture process running (SQL Agent on SQL Server) Yes Yes, with before and after images Yes High When you need every change, or the old values Operational overhead; change tables are cleaned up after retention; schema changes need handling
Mirroring A source Fabric Mirroring supports Yes Yes Yes Low to set up A managed, near-real-time replica in OneLake You get a current-state replica, not a change list for downstream steps; source and feature limits apply
File arrival / ingestion metadata Files land in a known location with stable conventions New files Only if the source sends corrections as files Only if the source sends deletes or full snapshots Low to medium Partner drops, exports, API dumps Depends entirely on the producer’s conventions; file timestamps are unreliable
Full snapshot + comparison Ability to read the whole source Yes Yes Yes Medium Small to medium tables with no change signal; delete detection Reads everything every time; cost grows with the table

Change Tracking and CDC are not the same thing

Teams often treat them as two names for one feature. They are different mechanisms with different guarantees.

  • Change Tracking records that a row changed, synchronously, as part of the transaction. CHANGETABLE(CHANGES dbo.sales_orders, @last_sync_version) returns the primary key, the operation (I, U, D) and a version, collapsed to the net change since that version. It does not store old values or intermediate states. You join back to the base table to get the current row. Cleanup removes tracking data after the retention period, so every run must check CHANGE_TRACKING_MIN_VALID_VERSION() before reading changes. If your stored last_sync_version is older than that minimum, the changes in between can no longer be enumerated: do not continue normally. Reinitialize the target with a full reload, or reconcile it against the source, and then resume from a fresh version.
  • CDC reads the transaction log asynchronously and writes full row images, before and after updates, into change tables keyed by log sequence number (LSN). It can give you every change, not just the net result, which matters for auditing or slowly changing dimensions. It is also heavier to run: a capture process, change-table storage, cleanup, and extra work when the source schema changes. CDC change tables are also cleaned up after a retention period. In SQL Server the default is 4,320 minutes (three days), which administrators can change, so check the configured value and keep your consumer well inside it. Either way, CDC does not keep history indefinitely.

For most analytics loads that only need the current state of each row, Change Tracking is the simpler choice. Use CDC when you genuinely need the intermediate changes or the before-images.

Where Mirroring fits

Fabric Mirroring replicates supported databases into OneLake as Delta tables and handles inserts, updates and deletes for you. For many sources it removes the extraction half of this article entirely.

It does not remove the other half. The mirrored table is a current-state replica, so your Silver and Gold steps still need to know which rows changed since their last run, and they still need idempotent writes. Treat the mirrored table as the source for the rest of the pattern. If the source table carries a modified_at column, a watermark over the mirrored table is often the simplest downstream change signal.

The ETL vs ELT vs CDC guide compares these integration patterns at a higher level. The rest of this article uses the high-watermark pattern, because it is the most common and the easiest to get subtly wrong.

The high-watermark pattern

A watermark is a statement of fact: every change at or before this point has been fully processed into the target. Not “the last time the job ran”, and not “the highest value we have seen”. If the watermark ever claims more than that, data is lost.

Timestamp or increasing ID?

  • modified_at timestamp. Captures inserts and updates, but only if every write path maintains it: application code, stored procedures, bulk fixes run by a DBA at 2 a.m. Use datetime2 and UTC. The older datetime type rounds to about 3 milliseconds, which makes boundary comparisons unreliable.
  • Increasing identity key. Only sees inserts. Updates to existing rows are invisible. That is fine for append-only tables such as event logs, and wrong for orders that change status.
  • rowversion. SQL Server’s rowversion increases on every insert and update, database-wide. It is a better change signal than a timestamp, with one trap shared by all of these columns: values are assigned when a row is written, not when the transaction commits. Bound the window with MIN_ACTIVE_ROWVERSION(), not @@DBTS. It returns the lowest rowversion still held by an active transaction, not the current maximum, so reading only rows below it means you never read past a transaction that is still open.

Where to store the watermark

Store it in a durable, transactional table, one row per pipeline and source, that survives redeployments: a Lakehouse Delta table, a Warehouse table or a SQL database. Do not keep it in a pipeline variable or a notebook default parameter (they reset), in a file you overwrite (a failed write can corrupt it), or as MAX(modified_at) from the target (a partially written target then reports a watermark it has not earned).

The examples use a Lakehouse Delta table:

Requête SQL: Where to store the watermarkSpark SQL
-- Spark SQL, run once in the Lakehouse
CREATE TABLE IF NOT EXISTS etl_watermark (
  pipeline_name             STRING,
  source_name               STRING,
  last_successful_watermark TIMESTAMP,
  current_upper_bound       TIMESTAMP,
  last_execution_id         STRING,
  status                    STRING,
  updated_at                TIMESTAMP
) USING DELTA;
Spark SQL10 lignesAffichage en lecture seule. Aucune connexion à une base de données.

last_successful_watermark only moves after a run has fully succeeded. current_upper_bound records the window an in-flight run is working on, so a responder can see exactly what a failed run was doing. The execution ID links the row to the run’s record in your execution log.

When to advance it

Only after every downstream write the watermark vouches for has committed and passed validation. Advancing the watermark first is the most dangerous mistake in incremental loading:

  1. The job sets the watermark to 10:00.
  2. The Silver MERGE fails at 10:02.
  3. The next run reads changes after 10:00.

Every change between the previous watermark and 10:00 is now permanently skipped, and nothing fails. The pipeline is green, the dashboards look plausible, and the data is quietly wrong until someone reconciles it.

Safe watermark advancement

The pattern has six steps, and the order is the point:

  1. Read the previous successful watermark from etl_watermark.
  2. Capture a new upper bound from the source.
  3. Read only the bounded window: watermark < modified_at <= upper bound.
  4. Process the data: Bronze, Silver, Gold.
  5. Validate and commit downstream changes.
  6. Persist the new watermark (the upper bound) only after everything else succeeded.
  1. Sourcedbo.sales_orders
  2. Read last successful watermark
  3. Capture upper bound
  4. Extract bounded changeswatermark < modified_at <= upper bound
  5. BronzeAppend extract with execution ID
  6. Deduplicate and MERGE Silver
  7. Update GoldAffected dates only
  8. Validate
    • success
      Persist new watermarklast_successful_watermark = upper bound
    • failure
      Keep old watermarkRerun the same bounded window safely
Safe incremental load with a bounded watermark window. The watermark only moves on the success path. On failure it stays put, and the next run replays the same window, which is safe because every write is idempotent.

Why the upper bound matters

Without an upper bound, the job reads “everything after the watermark” and then sets the new watermark to the current time when it finishes. Rows written while the job was running fall into a gap: they are newer than what the extract saw, but older than the new watermark. They are never loaded.

Capturing the upper bound before extracting closes that gap. Rows written during the run are newer than the upper bound and belong to the next window. Take the upper bound from the source clock, not the Fabric notebook’s clock, so clock skew between systems cannot open a gap.

In a Fabric pipeline, a Lookup activity typically reads the watermark and captures the upper bound. (A Copy job can manage watermark or CDC state for you on simple ingestion; choosing between Copy jobs and pipelines covers when that is enough.)

Requête SQL: Why the upper bound mattersT-SQL
-- Lookup against the source: capture the upper bound on the source clock
SELECT SYSUTCDATETIME() AS window_end;
T-SQL2 lignesAffichage en lecture seule. Aucune connexion à une base de données.

The Copy activity (or a notebook) then extracts exactly that window:

Requête SQL: Why the upper bound matters (2)T-SQL
-- Extract against the source; window_start and window_end come from the pipeline
SELECT sales_order_id, customer_id, order_timestamp, channel, status,
       payment_method, currency, subtotal, tax_amount, shipping_amount,
       grand_total, is_deleted, modified_at
FROM dbo.sales_orders
WHERE modified_at >  @window_start
  AND modified_at <= @window_end;
T-SQL7 lignesAffichage en lecture seule. Aucune connexion à une base de données.

> on the lower bound and <= on the upper bound make consecutive windows meet exactly: no gap, no overlap. It is the mirror image of the half-open date windows (start <= date < end) used in the notebook parameters article; either convention works as long as each boundary belongs to exactly one window. The extract lands in bronze_sales_orders with the run’s execution_id, window_start, window_end and an _extracted_at timestamp.

Also make sure two runs of the same load cannot overlap. Two concurrent runs that read the same watermark will both process the window and race to advance it. Configure the pipeline so a new run does not start while one is in progress, or take a lease in the control table before starting.

Apply changes with Delta MERGE

The Silver step turns a window of extracted changes into current state. In a notebook attached to the Lakehouse:

PySpark
from delta.tables import DeltaTable
from pyspark.sql import functions as F, Window

# Notebook parameters set by the pipeline
execution_id = "..."
window_start = "2026-10-24 09:50:00"
window_end   = "2026-10-24 11:00:00"

order_columns = ["sales_order_id", "customer_id", "order_timestamp", "channel", "status",
                 "payment_method", "currency", "subtotal", "tax_amount", "shipping_amount",
                 "grand_total", "is_deleted", "modified_at"]

# Every extracted version of a row in this window, including any left by a failed earlier attempt
window_changes = (
    spark.table("bronze_sales_orders")
    .where((F.col("modified_at") > F.lit(window_start).cast("timestamp")) &
           (F.col("modified_at") <= F.lit(window_end).cast("timestamp")))
)

# One row per key: the latest version wins
latest = (
    window_changes
    .withColumn("rn", F.row_number().over(
        Window.partitionBy("sales_order_id")
              .orderBy(F.col("modified_at").desc(), F.col("_extracted_at").desc())))
    .where("rn = 1")
    .select(*order_columns)
)

silver = DeltaTable.forName(spark, "silver_sales_orders")
(
    silver.alias("t")
    .merge(latest.alias("s"), "t.sales_order_id = s.sales_order_id")
    .whenMatchedUpdateAll(condition="s.modified_at > t.modified_at")
    .whenNotMatchedInsertAll()
    .execute()
)
PySpark37 lignesCellule d’exemple en lecture seule. Rien sur cette page n’exécute de code.

Two details carry most of the correctness:

  • Deduplicate before the MERGE. If several source rows match the same target row and the MERGE would update or delete it, the update is ambiguous. Delta does not pick one for you; the MERGE fails with a multiple-match error. The window can legitimately contain several versions of an order, and a replay adds more, so preprocess the incremental source to exactly one row per business key (sales_order_id) before the MERGE.
  • Guard the update. s.modified_at > t.modified_at means an older or already-applied version never overwrites newer state. Replaying a window becomes a no-op for rows already applied, which is exactly what you want on a rerun. The trade-off: a correction written with an unchanged modified_at is ignored. If your source does that, fix the source or loosen the condition to >=.

What MERGE solves: inserts and updates applied atomically, in one Delta commit, keyed on a business key. What it does not solve: it does not detect changes, it does not see deletes the extract never contained, it does not deduplicate its source, and it does not guarantee a unique key in the target if the target already has duplicates. MERGE is the write mechanism, not the incremental design.

For making MERGE fast on large tables (pruning, file sizes, partitioning, concurrency), see Delta Lake MERGE performance patterns. For the basic read, write and MERGE syntax, keep the Delta read and write cheat sheet nearby.

Handling deletes

A watermark filter only sees rows that still exist. When a row is physically deleted from the source, there is nothing left to match modified_at > @window_start, so the delete never reaches Silver. The order stays in your reports forever. This is one of the most common production mistakes in incremental pipelines, and it is invisible until someone compares totals.

Choose a deliberate delete strategy:

  • CDC or Change Tracking delete events. Both report deletes (D in Change Tracking, operation 1 in CDC). Apply them with a whenMatchedDelete clause, or as a soft delete in Silver.
  • Soft-delete flag. The source sets is_deleted = 1 and updates modified_at instead of deleting. The watermark sees it like any other update. This is what the running example uses: Silver keeps the row with is_deleted = true, and Gold filters it out.
  • Status column. Business “deletes” are often status changes (Cancelled, Voided). They flow through as updates; just make sure Gold treats them correctly.
  • Tombstone records. File-based or event sources send an explicit delete record with the key. Handle it like a CDC delete.
  • Periodic source-to-target key reconciliation. When the source hard-deletes and offers nothing else, compare key sets. Read the source keys for a recent range, anti-join with Silver, and mark the missing keys as deleted. It costs a key scan, so run it on a schedule that matches how quickly deletes must disappear.

Prefer soft deletes in Silver even when the source hard-deletes. A deleted_at column keeps the history explainable, makes the delete replayable, and lets Gold decide what to show.

Late-arriving data

modified_at > last_watermark assumes rows appear in the source in modified_at order. They don’t always:

  • A long transaction writes a row with modified_at = 09:58 but commits at 10:03. A run that read up to 10:00 at 10:01 never saw it. The next run starts after 10:00 and skips it.
  • An application server with a slow clock stamps rows a few minutes in the past.
  • A replica you extract from lags behind the primary.
  • A batch process backdates modified_at on purpose.

The fix is a lookback window: start each extract a little before the last successful watermark.

last_successful_watermark  = 10:00
lookback                   = 10 minutes
next run: window_start     = 09:50   (watermark - lookback)
          window_end       = 11:00   (upper bound captured from the source)

The rows between 09:50 and 10:00 are read twice: once in the previous run, once now. That is safe only because the downstream steps are idempotent. The deduplication picks the latest version per key, and the guarded MERGE turns already-applied rows into no-ops. The watermark still advances to the upper bound, not to the start of the lookback.

The trade-off is correctness against cost. A longer lookback tolerates later rows but re-reads and re-merges more data on every run. Size it from evidence: the longest transaction you see on the source, replica lag, known clock skew. Then catch anything later still with reconciliation. A few minutes is typical for transactional skew; a source known to backdate rows by days needs a days-long lookback or a different change signal.

A different kind of lateness is business lateness: an order placed on 10 July but entered on 24 October. The watermark handles it correctly (its modified_at is 24 October). The impact is on Gold, where a historical date changes. That is covered below.

Idempotency and reruns

Idempotency is the property that makes everything else in this article safe: running the same window twice must leave the target in the same state as running it once. Fabric pipelines fail for ordinary reasons (a capacity throttle, a source timeout, a Spark session that dies, someone cancelling a run), and someone will press rerun. If a rerun can corrupt data, every failure becomes a data-quality incident. It is also what replaces SSIS checkpoints and package transactions when you migrate SSIS packages to Fabric, and it belongs in the platform design from the start: Designing a Production Data Platform treats restartability as an architecture decision alongside security, capacity and recovery.

The building blocks:

  • Deterministic business keys. Silver is keyed on sales_order_id, which comes from the source. A surrogate key generated at load time, such as a random ID or monotonically_increasing_id(), gives the same row a new identity on every rerun, and MERGE can no longer match it.
  • MERGE or overwrite by scope, never blind append, into state tables. Bronze can be append-only because it is history. Silver and Gold must converge to the same result however many times a window is applied.
  • Batch and run identifiers. Stamp Bronze rows with execution_id, window_start and window_end. You can then tell exactly which run wrote what, delete or ignore a failed attempt’s rows, and audit a replay.
  • Source file identifiers. For file sources, the file path and checksum are the batch identity (see below).
  • Duplicate protection. Deduplicate the window before the MERGE, and add a duplicate-key check on Silver to your validation step.
  • Retry safety by construction. The watermark moves last, so any failure leaves the system in a state where the same window can simply run again.

A failure scenario, step by step

The notebook processes the window 09:50 → 11:00:

  1. The Copy activity appends 1,240 rows to bronze_sales_orders under execution A.
  2. The Silver MERGE commits: 37 inserts, 1,180 updates.
  3. Gold recomputes the affected order dates and commits.
  4. The notebook fails before it updates etl_watermark, because the session times out.

State now: Silver and Gold contain the changes up to 11:00, but the watermark still says 10:00.

The next run (execution B) reads the watermark (10:00), applies the lookback (09:50) and captures a new upper bound (12:00):

  • Bronze receives the 09:50–11:00 rows again, plus newer ones, under execution B. Bronze now holds two copies of the replayed rows, and that is acceptable: Bronze is a log of extracts.
  • The Silver step deduplicates the window, including execution A’s rows. Rows already applied fail the s.modified_at > t.modified_at guard and are skipped. Only genuinely new versions are written.
  • Gold recomputes the affected dates from Silver and produces the same numbers as before for the replayed dates.
  • Validation passes and the watermark moves to 12:00.

Nothing was lost and nothing was double-counted. Now replay the same failure with two common shortcuts:

  • Silver written with append: 1,217 orders now have two rows, every count and sum on top of Silver is inflated, and the next MERGE against it fails with multiple matches.
  • Gold updated as revenue = revenue + window_revenue: the replayed window’s revenue is counted twice. No error is raised, and the totals are simply wrong.

If you would rather not accumulate replayed rows in Bronze, make the Bronze write idempotent too. Overwrite the window’s slice with Delta’s replaceWhere on a batch column, or delete the rows of failed executions before re-extracting. Either is fine, as long as Silver does not depend on Bronze being free of duplicates.

The notebook tutorial shows the surrounding mechanics: parameterizing the notebook, running it from a pipeline, failing deliberately when validation fails, and keeping reruns safe.

To prove all of this before production, Testing Incremental Loads and Idempotency turns each guarantee into a test (same batch twice, late rows, watermark boundaries, partial failure, replay), and Testing Microsoft Fabric Pipelines and Notebooks runs them as pipeline tests.

Bronze, Silver and Gold propagation

Incremental processing does not stop at the first layer. Each layer has its own change signal and its own write pattern. The Bronze, Silver and Gold guide covers what belongs in each layer; this is how changes move between them.

Layer Incremental input Write pattern Idempotency comes from
Bronze The bounded source window Append, stamped with execution_id and window bounds Downstream deduplication, or replaceWhere per batch
Silver Bronze rows in the window, deduplicated to one per key Guarded MERGE on the business key Deterministic key and the modified_at guard
Gold The keys or dates affected by the Silver changes Recompute the affected slice and replace it Recomputing from Silver instead of adding deltas

Frequent small windows have a side effect at every layer: many small appends and merges fragment the tables. Plan compaction alongside the load cadence; Delta Table Maintenance in Microsoft Fabric covers how.

Gold: recompute affected slices instead of adding deltas

For gold_daily_sales, the safe pattern is to find the order dates the window touched, recompute those dates completely from Silver, and replace them:

PySpark
# Dates touched by this window's changes
affected_dates = [r.order_date for r in
                  latest.select(F.to_date("order_timestamp").alias("order_date")).distinct().collect()]

if affected_dates:  # an empty window has nothing to recompute
    daily = (
        spark.table("silver_sales_orders")
        .where("is_deleted = false")
        .withColumn("order_date", F.to_date("order_timestamp"))
        .where(F.col("order_date").isin(affected_dates))
        .groupBy("order_date")
        .agg(F.count("*").alias("order_count"), F.sum("grand_total").alias("revenue"))
    )

    date_list = ", ".join(f"DATE'{d}'" for d in affected_dates)
    (
        daily.write.format("delta")
        .mode("overwrite")
        .option("replaceWhere", f"order_date IN ({date_list})")
        .saveAsTable("gold_daily_sales")
    )
PySpark21 lignesCellule d’exemple en lecture seule. Rien sur cette page n’exécute de code.

Two traps hide in that pattern:

  • Changes can move rows between groups. If an order’s order_timestamp is corrected from 9 July to 10 July, both dates are affected, but the window only contains the new value. Capture the old values of changed keys from Silver before the MERGE, and add their dates to the affected set.
  • A group can become empty. If every order on a date is deleted, the recompute returns no row for that date. replaceWhere still removes the old row, which is correct. A MERGE-based Gold update would leave the stale row behind unless you handle it explicitly.

Not every Gold table can be updated this way. Distinct counts across long periods, rankings, running totals and models that join many Silver tables can make “what was affected” expensive or impossible to compute exactly. When a Gold table is small or cheap to build, rebuilding it in full from Silver is often the better design: always correct, trivially idempotent, and easy to reason about. Keep the incremental effort where the volume is, usually Bronze and Silver.

Between layers, the filter does not have to be a source timestamp. Filtering on the run’s window or execution_id works, and so does Delta’s change data feed if you enable it on the Silver table. Change data feed retention is tied to table maintenance, though, so a downstream job that falls behind can lose its change history and need a rebuild.

Incremental files

File sources need their own change signal, because there is no modified_at column to filter. The unit of change is the file, and the state you keep is which files have been processed.

Keep a processed-file table next to the watermark table:

  • source_path and file_name: the identity of the file in the Lakehouse Files area or an external store reached through a shortcut.
  • file_size and file_modified_at: cheap change hints.
  • checksum: when producers re-send corrected files under the same name, a content hash tells a correction from a duplicate.
  • ingested_at and execution_id: when it was loaded and by which run.
  • status: loaded, failed or quarantined.

Each run lists the landing folder, anti-joins against the processed-file table, loads only new or changed files, and records them in the same success step that would otherwise advance a watermark. Because each run loads a known list of new files, record the source path explicitly at ingestion. For example, add it as a column (F.lit(path)) when reading each file, so Bronze rows carry their source file as their batch identifier. Some Spark file sources also expose a hidden _metadata column (_metadata.file_path), but availability depends on the runtime and source format. Verify it in your Fabric runtime before relying on it.

Do not treat file timestamps alone as a watermark. Copy tools can reset modification times. A partner can upload yesterday’s file today. A re-sent correction can carry an older timestamp than the file it replaces. A file can also still be in the middle of being written when you list the folder. Prefer explicit conventions: producers write to a temporary path and move the file when complete, or drop a completion marker. Fabric pipelines can be triggered by file events, but the processed-file table, not the trigger, remains the record of what was loaded.

Periodic reconciliation

Even a well-designed incremental pipeline drifts: a write path that forgot to update modified_at, a hard delete nobody told you about, a manual fix in the source, a bug last month. Reconciliation is the safety net that catches it. Run it on a schedule (daily for recent data, weekly or monthly for full history) and alert on differences:

  • Row counts by date or partition: source vs Silver, per order_date.

  • Totals: SUM(grand_total) per date on both sides.

  • Key coverage: keys in the source but missing from Silver (missed inserts), and keys in Silver but not in the source (missed deletes).

  • Duplicate keys in Silver:

    Requête SQL: Periodic reconciliationSpark SQL
    SELECT sales_order_id, COUNT(*) AS copies
    FROM silver_sales_orders
    GROUP BY sales_order_id
    HAVING COUNT(*) > 1;
    Spark SQL4 lignesAffichage en lecture seule. Aucune connexion à une base de données.
  • Checksums over a canonical column list per key range, where counts and totals are not sensitive enough.

When a reconciliation finds a gap, the fix should be the normal pipeline replaying a wider window (set the watermark back, or run a backfill with an explicit window_start), not a hand-written patch. If the pipeline cannot repair its own gaps, it is not idempotent yet.

Reconciliation detects problems; it does not replace a correct design. If it regularly finds differences, fix the change detection rather than relying on the nightly repair.

Operational metadata

Two small tables make an incremental pipeline operable. The watermark table above holds the state. The run record holds the evidence. Reuse your execution log for the second, rather than inventing a parallel system:

Field Where Meaning
pipeline_name, source_name Watermark table Which load this state belongs to
last_successful_watermark Watermark table Upper bound of the last fully successful run
current_upper_bound Watermark table Upper bound of the run in progress
last_execution_id, status, updated_at Watermark table Who last touched the row, and how it ended
execution_id Execution log One ID per attempt; retries get a new one
start_time, end_time, status Execution log started, succeeded, failed, cancelled, timed_out
rows_read, rows_written Execution log Extracted rows and rows written
rows_inserted, rows_updated, rows_deleted Execution log (extension) MERGE outcome, from the Delta operation metrics

Delta records MERGE metrics in the table history (numTargetRowsInserted, numTargetRowsUpdated, numTargetRowsDeleted), so you can read them from DESCRIBE HISTORY silver_sales_orders instead of running extra counts.

The final, success-only step of the notebook moves the watermark. The named parameter markers (:window_end) and the args argument of spark.sql require Spark 3.4 or later. Fabric Runtime 1.2 and later meet that (runtime comparison); on an older Spark version, pass the values another way:

PySpark
spark.sql(
    """
    UPDATE etl_watermark
    SET last_successful_watermark = CAST(:window_end AS TIMESTAMP),
        current_upper_bound       = NULL,
        last_execution_id         = :execution_id,
        status                    = 'succeeded',
        updated_at                = current_timestamp()
    WHERE pipeline_name = 'pl_sales_orders_incremental'
      AND source_name   = 'dbo.sales_orders'
    """,
    args={"window_end": window_end, "execution_id": execution_id},
)
PySpark13 lignesCellule d’exemple en lecture seule. Rien sur cette page n’exécute de code.

With both tables in place, the usual on-call questions have direct answers: which window failed, what it read, what it wrote, and whether rerunning is safe. Surface them in the same place you watch the rest of the platform. Monitoring Microsoft Fabric pipelines and notebooks covers linking pipeline runs to notebook runs and attributing time.

Production checklist

  • Change detection chosen deliberately: watermark, Change Tracking, CDC, mirroring, files or snapshot comparison, with its limitations written down.
  • Full reload kept where the table is small enough that incremental logic is not worth it.
  • Deletes handled: delete events, soft deletes or key reconciliation. Not ignored.
  • Late-data strategy: a lookback window sized from evidence, plus reconciliation for anything later.
  • Bounded extraction: upper bound captured from the source clock before extracting; > lower and <= upper.
  • Watermark persisted only after success, in a durable control table, never derived from the target.
  • No overlapping runs of the same load.
  • Idempotent reruns: deterministic keys, deduplicated MERGE, Gold recomputed rather than incremented. Tested by actually running a window twice.
  • Logging: execution ID, window bounds and MERGE metrics recorded for every run.
  • Reconciliation scheduled: counts, totals, key coverage, duplicates.
  • Monitoring: run duration, rows per window and watermark lag (how far last_successful_watermark trails the current time).
  • Alerting with a documented response: failed runs, a watermark that stops moving, reconciliation differences, a Change Tracking version close to its retention limit.

What you learned

  • A full reload is often the right design for small or infrequent loads. Go incremental when the cost is real.
  • The change signal (watermark, Change Tracking, CDC, mirroring, files or snapshot comparison) decides which inserts, updates and deletes you can see. Change Tracking and CDC are different mechanisms with different trade-offs.
  • A safe watermark load captures an upper bound first, reads a bounded window, and advances the watermark only after every downstream write has succeeded.
  • MERGE applies changes; it does not detect them, deduplicate its input or find deletes.
  • Lookback windows handle late commits, and idempotent writes make the overlap and every rerun safe.
  • Gold is often best recomputed for affected slices, or rebuilt in full when small. Avoid adding deltas.
  • Reconciliation catches what the design missed. It does not replace the design.

Étiquettes

  • Microsoft Fabric
  • Chargements incrémentiels
  • Delta Lake
  • MERGE
  • Pipelines de données
  • Architecture médaillon