DP-700: incremental loads with watermark in Fabric

I will teach how to implement incremental loads using a watermark column in ingestion pipelines in Microsoft Fabric. This skill is common on the DP-700 exam and, in practice, reduces cost and processing time by avoiding reloading the entire table. By applying this pattern, you can go from processing tens or hundreds of gigabytes per run to only a few megabytes or a few thousand records, depending on the data change rate.

What you need to know

An incremental load ingests only new or changed records since the last run. The most common technique uses a temporal or numeric column (the “watermark”) that identifies how far data has already been read. In Fabric this applies when you use Power Query or Dataflows to filter rows before writing to a Lakehouse/table.

Types of watermark and when to use them: DateTime columns (TransactionDate) are preferable when records have reliable timestamps; sequential identifiers (increasing ID) are useful in ordered streams. For large volumes (for example 100M records daily), an incremental load can reduce work by >95% if only 5M are new.

Practical considerations: define the granularity (seconds, minutes, days), pay attention to timezones (UTC versus local) and convert types explicitly to avoid failures. Also decide on a tolerance window for late arriving data (for example 3–7 days), since replays and backfills are common in a real ecosystem.

In practice: step-by-step

Follow these practical steps to implement an incremental load pattern, with concrete examples.

1. Choose the watermark column: prefer timestamp columns (DateTime) or an increasing identifier (sequential ID). Avoid using the file modification date if the internal data has its own dates.
2. Create a control table: persist the last watermark in a small table in the Lakehouse or in a metadata file in OneLake. Example schema: (datasource_id STRING, last_watermark DATETIME, last_max_id BIGINT NULL, updated_at DATETIME, run_id STRING). For 50 different sources, the table will have 50 rows; for a single stream, one row is enough.
3. Create the Dataflow/Power Query: add a parameter (for example LastWatermark) and apply a filter in the query to only select rows where TransactionDate > LastWatermark. This drastically reduces the data read and turns IO and CPU into proportions linear with the change rate.

let
  Source = Csv.Document(File.Contents("/path/to/file.csv"),[Delimiter=",", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
  ToTable = Table.FromRows(Source, {"TransactionID","TransactionDate","Amount","Customer"}),
  ParsedDate = Table.TransformColumnTypes(ToTable, {{"TransactionDate", type datetime}}),
  Filtered = Table.SelectRows(ParsedDate, each [TransactionDate] > DateTime.FromText(Parameters[LastWatermark]))
in
  Filtered

Note: in Power Query used in Fabric, the parameter Parameters[LastWatermark] comes from the pipeline that executes the Dataflow. You can also parameterize by datasource_id for multi-source.

4. Write the data to the Lakehouse with upsert logic: if you need to keep the table updated (updates and deletes), use the MERGE/Upsert operation supported by the Lakehouse or write to a staging table and perform a SQL/Delta MERGE afterwards. Example: inserting 200k new records and 5k updates per run is typical in a medium scenario.
5. Update the watermark: after a successful load, calculate the new last watermark (for example SELECT MAX(TransactionDate) from the loaded data) and write it to the control table. If you processed 1M records with maximum 2026-07-20T12:34:56Z, write that timestamp as the new watermark. Use a transaction or atomic operation to avoid race conditions.
6. Schedule and monitor: configure the pipeline to run at the desired frequency (daily, hourly). For telemetry data you can schedule every 15 minutes; for sales maybe once per hour or per day. Add alerts for failures and metrics (records processed, run time, incremental percentage) to ensure observability.

Common mistakes

• Using the file date instead of the record date: the file creation date may not reflect when records were generated. This causes duplication or data loss when files are resent.
• Not handling late arriving data: if you have records with TransactionDate earlier than the last watermark, you will lose them. Solutions: tolerance window (e.g. reprocess last 7 days), deduplication key with MERGE and auditing logs to identify losses. In concrete percentages, a system with 0.5–2% late records can represent significant financial values if not handled.
• Not managing schema drift: adding or renaming columns without changing the logic can break the pipeline; explicitly select the required columns, validate the schema at the start of the run and apply defensive transformations.
• Concurrency conditions: multiple parallel runs can read and update the control table simultaneously; use locking mechanisms, run_id and idempotency checks to prevent races.

How to practice

Practice this pattern with a Microsoft Fabric trial account and a small set of CSV files in OneLake or Blob Storage. Create 30 daily files with 1k–10k records each, simulate late arrivals by resending some files and observe how the workload changes when you apply the watermark filter. Implement the pipeline in three components: Dataflow (Power Query) that filters by watermark, a control table in the Lakehouse to store the last watermark and a MERGE/upsert task to apply changes.

To prepare for DP-700, use the official Microsoft Practice Assessment (free) and the official Study Guide on Microsoft Learn (free). These resources help validate your understanding of the measured skills and practice scenarios without resorting to unauthorized materials.

In summary

• Incremental loads with watermark reduce time and cost by ingesting only new/changed records — often with performance gains on the order of 80–99% in I/O and CPU, depending on the change rate.
• You need a watermark column, a control table for the last watermark and pipelines that apply filters before writing to the Lakehouse.
• Handle late arriving data, updates (upsert/merge), schema drift and concurrency to ensure reliability.
• Practice in Fabric with Dataflows/Power Query, Lakehouse and the control table; use Microsoft’s official resources for study and validation.