Implementing Watermark-Based CDC Patterns in Power Query: Capturing Net-New and Changed Records from Relational Sources Using Persistent High-Water Mark Tracking

Introduction

Your sales database has 4.2 million order records. Every morning, your Power Query refresh loads all 4.2 million of them, joins them to a product dimension, applies business rules, and produces the output your dashboards depend on. By the time the load finishes — 23 minutes later — the data is already slightly stale. Meanwhile, only 847 new or modified records appeared since yesterday. You're doing 4.2 million units of work to capture 847 records of change.

This is the fundamental problem that Change Data Capture (CDC) solves. And while enterprise-grade CDC solutions like SQL Server's native CDC engine or Debezium are purpose-built for high-throughput streaming scenarios, Power Query can implement a highly effective watermark-based CDC pattern that dramatically reduces the data volume your queries must process — without requiring any database-level configuration changes, CDC license features, or streaming infrastructure. You build it in M, you persist it with a text file or SharePoint list, and it just works.

By the end of this lesson, you will have built a complete, production-ready watermark CDC system in Power Query. We're talking real architecture here: high-water mark storage, parameterized source queries that filter at the database, atomic watermark updates after successful load, and recovery logic for when things inevitably go sideways.

What you'll learn:

• How watermark-based CDC differs from full-load, log-based CDC, and diff-based approaches — and when each is appropriate
• How to design a persistent high-water mark store that survives refresh cycles
• How to build M code that reads the current watermark, queries only the delta, and writes the updated mark after success
• How to handle edge cases: duplicate boundaries, NULL timestamps, timezone drift, soft-deletes, and late-arriving records
• How to chain watermark logic into a multi-table incremental pipeline with dependency ordering
• Performance and architecture trade-offs you need to understand before shipping this to production

---

Prerequisites

This is an expert-level lesson. You should be comfortable with:

• Writing and reading M code fluently — not just using the GUI — including let...in blocks, function definitions, and record manipulation. If you need a refresher, start with Understanding the M Formula Language: Syntax, Data Types, and Expression Basics
• Connecting to relational sources in Power Query, ideally SQL Server. The pattern here assumes SQL Server but adapts to any source that supports query folding. See Connecting to SQL Server in Power Query: Native Queries and Credential Management for connection setup
• Parameterized queries in M — you'll need to understand how to pass values into source queries dynamically. Parameterized Queries and Dynamic Data Sources in Power Query covers the mechanics
• Basic understanding of multi-query dependency management. This pattern chains queries in sequence; Orchestrating Multi-Query Refresh Dependencies in Power Query: Controlling Load Order and Isolating Volatile Sources for Reliable Pipeline Execution is directly relevant

---

What Watermark-Based CDC Actually Is (And What It Isn't)

Before writing a single line of M, you need a precise mental model of what you're building — and why it's different from the alternatives.

Full-load refresh pulls every record from the source on every refresh cycle. Simple, reliable, requires no state management. The problems are obvious: as your source grows, refresh time grows proportionally, and you hammer the source database with queries that mostly return data you already have.

Log-based CDC (SQL Server's native CDC, Oracle GoldenGate, Debezium) reads the database transaction log to capture every insert, update, and delete at the row level, including before-and-after images of changed data. This is the gold standard for completeness — you can't miss a change — but it requires DBA-level configuration, usually a separate license tier, and infrastructure Power Query can't touch.

Diff-based / hash-based CDC loads all records but only loads records whose hash signature has changed since last run. This is better than full-load in terms of write volume but does nothing about read volume — you still pull every record from the source.

Watermark-based CDC operates on a simpler, powerful assumption: your source table has a column — typically UpdatedAt, ModifiedDate, or a sequence number like an auto-increment RowVersion — that increases monotonically whenever a record is inserted or updated. You store the maximum value of that column from the last successful load (the "high-water mark"), and on the next run, you filter the source to only pull records where that column is greater than your stored mark.

The beauty of this approach is that the filter pushes down to the source through query folding, so the database does the work of finding relevant records. You're not transferring 4.2 million records and filtering in the M engine — you're asking SQL Server to give you only the 847 records that changed. With proper indexing on the watermark column, this is a near-instant operation on the database side.

---

Designing Your High-Water Mark Store

The central engineering decision in this entire pattern is: where do you persist the watermark? Power Query itself is stateless — there's no built-in mechanism for a query to write a value that persists to the next refresh. You have to externalize this state.

Your options, ordered from simplest to most robust:

1. A text file on a local path or network share. A single-line CSV or JSON file containing the watermark value. Simple to read and write in M, but fragile in Power BI Service environments where local file paths don't exist, and you need a mechanism to actually write the file (which Power Query can't do — more on this shortly).

2. A SharePoint list. A one-row SharePoint list with columns for the watermark value, the table name, and a last-updated timestamp. Accessible from Power BI Service, readable from Power Query natively, and writable via Power Automate.

3. A database table in your warehouse or staging database. A WatermarkStore table with columns TableName, LastWatermark, and UpdatedAt. Readable from Power Query through the same SQL connector you're using for source data. The most robust option for enterprise scenarios.

4. A Power BI dataflow parameter or Dataverse table. Viable in Microsoft-native deployments, though more complex to automate writes.

For this lesson, we'll implement option 3 — a database-backed watermark store — because it's the most production-appropriate for relational CDC scenarios and gives you the full query-folding benefit. We'll also show the SharePoint variant because it's the right choice for desktop/service hybrid deployments.

Setting Up the Watermark Store Table

In your staging or warehouse database, create this table:

CREATE TABLE [dbo].[WatermarkStore] (
    [TableName]      NVARCHAR(128)  NOT NULL,
    [LastWatermark]  DATETIME2(7)   NOT NULL,
    [LastRunStatus]  NVARCHAR(20)   NOT NULL DEFAULT 'Success',
    [RecordsLoaded]  INT            NULL,
    [UpdatedAt]      DATETIME2(7)   NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT [PK_WatermarkStore] PRIMARY KEY ([TableName])
);

-- Seed initial values — these will cause the first run to load all history
INSERT INTO [dbo].[WatermarkStore] ([TableName], [LastWatermark], [LastRunStatus])
VALUES 
    ('Sales.Orders', '2000-01-01 00:00:00', 'Success'),
    ('Sales.OrderLines', '2000-01-01 00:00:00', 'Success'),
    ('CRM.Customers', '2000-01-01 00:00:00', 'Success');

The sentinel value of 2000-01-01 as the initial watermark is intentional — it causes the first run to load the entire history of each table, effectively bootstrapping your pipeline.

---

Building the Core M Query Architecture

Your complete watermark CDC system will consist of four logical query layers in Power Query:

1. WatermarkStore — reads current watermark values from the store
2. fn_GetDelta — a function that accepts a table name and watermark, returns only the delta records
3. [TableName]_Delta — invokes fn_GetDelta for each tracked table
4. NewWatermark — computes the new high-water mark from the delta results

Let's build each layer.

Layer 1: Reading the Watermark Store

let
    Source = Sql.Database("your-server", "YourWarehouse"),
    WatermarkTable = Source{[Schema="dbo", Item="WatermarkStore"]}[Data],
    
    // Cast to typed table immediately
    TypedTable = Table.TransformColumnTypes(WatermarkTable, {
        {"TableName", type text},
        {"LastWatermark", type datetime},
        {"LastRunStatus", type text},
        {"RecordsLoaded", Int64.Type},
        {"UpdatedAt", type datetime}
    }),
    
    // Convert to a record for named access — much cleaner than row lookups
    WatermarkRecord = Record.FromTable(
        Table.RenameColumns(
            Table.SelectColumns(TypedTable, {"TableName", "LastWatermark"}),
            {{"TableName", "Name"}, {"LastWatermark", "Value"}}
        )
    )
in
    WatermarkRecord

Wait — Record.FromTable expects a two-column table with columns named Name and Value. The renaming step above sets that up. The result is a record like:

[
    Sales.Orders = #datetime(2024, 11, 15, 03, 45, 22),
    Sales.OrderLines = #datetime(2024, 11, 15, 03, 45, 22),
    CRM.Customers = #datetime(2024, 11, 14, 18, 30, 00)
]

Now you can access any watermark with WatermarkRecord[Sales.Orders] — clean, readable, and strongly typed.

Layer 2: The Delta Fetch Function

This is where query folding becomes critical. The function must generate a SQL query that the Power Query engine can fold — meaning the filter predicate gets executed at the database, not in the M engine.

let
    fn_GetOrdersDelta = (watermark as datetime) as table =>
    let
        Source = Sql.Database("your-server", "YourWarehouse"),
        
        // Using Value.NativeQuery for explicit SQL control
        // This guarantees folding regardless of connector behavior
        DeltaRecords = Value.NativeQuery(
            Source,
            "SELECT 
                o.OrderID,
                o.CustomerID,
                o.OrderDate,
                o.ShipDate,
                o.TotalAmount,
                o.Status,
                o.UpdatedAt,
                o.IsDeleted
             FROM Sales.Orders o
             WHERE o.UpdatedAt > @watermark
             ORDER BY o.UpdatedAt ASC",
            [watermark = watermark],
            [EnableFolding = true]
        ),
        
        // Type enforcement at the function boundary
        TypedResult = Table.TransformColumnTypes(DeltaRecords, {
            {"OrderID", Int64.Type},
            {"CustomerID", Int64.Type},
            {"OrderDate", type datetime},
            {"ShipDate", type datetime},
            {"TotalAmount", type number},
            {"Status", type text},
            {"UpdatedAt", type datetime},
            {"IsDeleted", type logical}
        })
    in
        TypedResult
in
    fn_GetOrdersDelta

Note the use of Value.NativeQuery with a parameterized query — this is the explicit SQL approach that guarantees you get server-side filtering rather than hoping the M engine folds the predicate. The @watermark parameter is passed as a typed M value, and Power Query handles the SQL parameterization safely, preventing injection issues.

Layer 3: Invoking the Delta Fetch

let
    // Reference the watermark store query
    CurrentWatermarks = WatermarkStore,
    
    // Get the watermark for this specific table
    OrdersWatermark = CurrentWatermarks[#"Sales.Orders"],
    
    // Invoke the delta function with the current watermark
    RawDelta = fn_GetOrdersDelta(OrdersWatermark),
    
    // Apply any business transformations to the delta
    WithDerivedColumns = Table.AddColumn(RawDelta, "LoadedAt", 
        each DateTime.LocalNow(), type datetime),
    
    // Tag records as Insert or Update based on whether they exist in target
    // (This merge logic would reference your existing loaded table)
    FinalDelta = WithDerivedColumns
in
    FinalDelta

Layer 4: Computing the New Watermark

After the delta load completes, you need to compute the new watermark to persist back to the store. The new watermark is the maximum UpdatedAt value from the records you just loaded — but only if at least one record was returned.

let
    DeltaRecords = Orders_Delta,
    RowCount = Table.RowCount(DeltaRecords),
    
    NewWatermark = if RowCount = 0 
        then WatermarkStore[#"Sales.Orders"]  // No change — keep existing watermark
        else List.Max(DeltaRecords[UpdatedAt]),
    
    Result = [
        TableName = "Sales.Orders",
        NewWatermark = NewWatermark,
        RecordsLoaded = RowCount,
        ComputedAt = DateTime.UtcNow()
    ]
in
    Result

---

The Watermark Update Problem: Power Query Can't Write

Here's the hard truth that trips up everyone who designs this pattern: Power Query is a read-only transformation engine. It cannot write values back to a file or database. This means the "update the watermark" step has to happen outside Power Query.

Your architectural options:

Option A: Power Automate trigger after refresh success. Configure a Power Automate flow that fires when a Power BI dataset refresh completes successfully. The flow reads the NewWatermark table from the refreshed dataset via the Power BI REST API, then executes a SQL UPDATE against your WatermarkStore. This is the most seamless approach in a Power BI Service deployment.

Option B: A stored procedure called from Power Apps or a scheduled Azure Function. After the Power Query load writes delta records to a staging table, a scheduled job calls a stored procedure that computes and updates the watermark from the staged data.

Option C: The self-updating watermark in the destination table. Instead of a separate watermark store, your destination table has a view or computed column that exposes MAX(UpdatedAt). Power Query reads this as its watermark. No external write required — the act of writing new records to the destination automatically advances the watermark. This only works when Power Query is writing to a database destination (via Power Query in Dataflows, not Desktop/Service direct).

Option D: The file-based workaround for Power BI Desktop. Write a Python or PowerShell script that updates a CSV file with the new watermark, and schedule this script alongside your Power BI refresh. Power Query reads the file; the script updates it. Fragile, but functional for desktop-only deployments.

For this lesson, we'll architect with Option A (Power Automate) as the persistence mechanism, and design our M code to surface the new watermark as a queryable table that Power Automate can read.

// WatermarkUpdate query — this table gets loaded to the dataset
// Power Automate reads it via REST API after refresh completes
let
    OrdersMark = [
        TableName = "Sales.Orders",
        NewWatermark = DateTime.ToText(
            NewWatermarks[Orders][NewWatermark], 
            "yyyy-MM-dd HH:mm:ss.fffffff"
        ),
        RecordsLoaded = NewWatermarks[Orders][RecordsLoaded],
        RunTimestamp = DateTime.ToText(DateTime.UtcNow(), "yyyy-MM-dd HH:mm:ss")
    ],
    OrderLinesMark = [
        TableName = "Sales.OrderLines",
        NewWatermark = DateTime.ToText(
            NewWatermarks[OrderLines][NewWatermark],
            "yyyy-MM-dd HH:mm:ss.fffffff"
        ),
        RecordsLoaded = NewWatermarks[OrderLines][RecordsLoaded],
        RunTimestamp = DateTime.ToText(DateTime.UtcNow(), "yyyy-MM-dd HH:mm:ss")
    ],
    
    ResultTable = Table.FromRecords({OrdersMark, OrderLinesMark})
in
    ResultTable

This query produces a clean, typed table that Power Automate can read via the Power BI Datasets connector ("Run a query against a dataset") to extract the new watermarks and update your store.

---

Handling the Hard Edge Cases

This is where production systems diverge from tutorial examples. The happy path is straightforward. What separates a robust pipeline from a fragile one is how it handles the edges.

Duplicate Boundary Records

The boundary condition at the watermark is subtle. You're filtering WHERE UpdatedAt > @watermark. This means the record at exactly the watermark timestamp is excluded — which is what you want, because you already loaded it. But what if multiple records share the same UpdatedAt timestamp?

Consider this sequence:

• Run 1 loads records through UpdatedAt = 2024-11-15 03:45:22.123. New watermark = 2024-11-15 03:45:22.123.
• At that exact millisecond, three records were written to the source with UpdatedAt = 2024-11-15 03:45:22.123. Your filter > 03:45:22.123 correctly excludes all three.
• But what if five records existed at that timestamp, and only two were returned in Run 1 due to some pagination or row-level filter applied upstream?

The safest design is to filter WHERE UpdatedAt >= @watermark on the next run (inclusive boundary), and deduplicate against your existing data in the destination. This guarantees no records are missed at the boundary, at the cost of potentially reprocessing a small set of records from the previous batch.

// Inclusive boundary version — dedup in the merge step
DeltaRecords = Value.NativeQuery(
    Source,
    "SELECT * FROM Sales.Orders WHERE UpdatedAt >= @watermark ORDER BY UpdatedAt ASC",
    [watermark = watermark]
)

If your destination is a database table, the upsert (MERGE) operation handles the duplicates gracefully. If you're loading to Power BI's in-memory model via incremental refresh, you need to think carefully about whether your append logic deduplicates on OrderID.

Soft Deletes

Your source table should have an IsDeleted bit column and a DeletedAt timestamp column if you need to capture deletions. When a record is "deleted," the application sets IsDeleted = 1 and DeletedAt = SYSUTCDATETIME(), and critically, it also updates UpdatedAt = SYSUTCDATETIME(). This means your watermark filter will capture the deletion event as a normal changed record.

In your delta processing, you then route soft-deleted records to a deletion handler:

let
    DeltaAll = Orders_Delta,
    
    // Split the delta into active changes and deletions
    ActiveChanges = Table.SelectRows(DeltaAll, each [IsDeleted] = false or [IsDeleted] = null),
    DeletedRecords = Table.SelectRows(DeltaAll, each [IsDeleted] = true),
    
    // Active changes get upserted into the main table
    // Deleted records get written to an audit/tombstone table or
    // trigger removal from the destination
    
    // For this example, we tag them and union back
    TaggedActive = Table.AddColumn(ActiveChanges, "ChangeType", each "Upsert", type text),
    TaggedDeleted = Table.AddColumn(DeletedRecords, "ChangeType", each "Delete", type text),
    
    Combined = Table.Combine({TaggedActive, TaggedDeleted})
in
    Combined

Timezone Drift and UTC Alignment

One of the most insidious production bugs in watermark CDC is timezone inconsistency. Your source database server might be in UTC. Your Power Query evaluation might run in a different timezone depending on the Power BI Service gateway region. Your watermark store might persist values in local time. The result: watermark boundaries shift by hours, causing either overlapping loads or gaps.

The fix is ruthless consistency: always store and compare watermarks in UTC. In your source queries, cast timestamps to UTC explicitly:

SELECT 
    OrderID,
    SWITCHOFFSET(CONVERT(DATETIMEOFFSET, UpdatedAt), '+00:00') AS UpdatedAt_UTC
FROM Sales.Orders
WHERE SWITCHOFFSET(CONVERT(DATETIMEOFFSET, UpdatedAt), '+00:00') > @watermark

In M, use DateTime.UtcNow() rather than DateTime.LocalNow() for any system timestamps you generate. And when persisting to your watermark store, store the ISO 8601 string with explicit Z suffix to make the UTC intent unambiguous.

Late-Arriving Records

Late-arriving data is a fundamental challenge for any watermark-based system. If a source system has records with UpdatedAt timestamps backdated (perhaps due to a batch process that reprocesses historical records with an older effective timestamp), those records will never be captured by your watermark filter — they'll always be below the current watermark.

Your options are:

1. Accept the limitation. For many business scenarios, late-arriving corrections are rare enough that a periodic full reload (weekly or monthly) can catch them. The 98% case is covered by daily watermark CDC.

2. Use a surrogate insert timestamp. Add a CreatedInDB column using a database default of SYSUTCDATETIME() at the row level, capturing when the record physically arrived in the database regardless of business-date fields. Filter on CreatedInDB > @watermark instead of UpdatedAt.

3. Implement a lookback window. Instead of UpdatedAt > @watermark, use UpdatedAt > DATEADD(hour, -6, @watermark) — always reprocess the last 6 hours even on incremental loads. This catches late arrivers at the cost of processing slightly more data.

---

Multi-Table Pipeline Orchestration

Real pipelines don't have one table. You have orders, order lines, customers, products, inventory snapshots — all with their own watermarks and their own delta cadences. Some tables change hundreds of times per second; others change once a week.

The dependency structure matters here. If you're loading OrderLines that reference Orders, you must load Orders first (or at least ensure the parent record exists in your destination before loading the child). See Orchestrating Multi-Query Refresh Dependencies in Power Query: Controlling Load Order and Isolating Volatile Sources for Reliable Pipeline Execution for the mechanics of controlling evaluation order across queries.

In M, you can build a metadata-driven multi-table orchestration using a configuration table:

// TableConfig query — drives the entire pipeline
let
    Config = #table(
        type table [
            TableName = text,
            SourceSchema = text,
            SourceTable = text,
            WatermarkColumn = text,
            LoadOrder = Int64.Type,
            LoadStrategy = text
        ],
        {
            {"Sales.Orders",     "Sales", "Orders",     "UpdatedAt", 1, "Watermark"},
            {"Sales.OrderLines", "Sales", "OrderLines", "UpdatedAt", 2, "Watermark"},
            {"CRM.Customers",    "CRM",   "Customers",  "UpdatedAt", 1, "Watermark"},
            {"Ref.Products",     "Ref",   "Products",   "UpdatedAt", 1, "Watermark"}
        }
    )
in
    Config

Then build a generic delta function that uses the config metadata:

let
    fn_GetTableDelta = (schemaName as text, tableName as text, watermarkColumn as text, watermark as datetime) as table =>
    let
        Source = Sql.Database("your-server", "YourWarehouse"),
        
        // Build dynamic SQL — note the risk of SQL injection here
        // Only acceptable when schema/table/column names come from 
        // your own trusted config table, never from user input
        SqlText = "SELECT *, SYSUTCDATETIME() AS [_LoadedAt]
                   FROM [" & schemaName & "].[" & tableName & "]
                   WHERE [" & watermarkColumn & "] > @wm
                   ORDER BY [" & watermarkColumn & "] ASC",
        
        Result = Value.NativeQuery(
            Source,
            SqlText,
            [wm = watermark]
        )
    in
        Result
in
    fn_GetTableDelta

---

Integrating Watermark CDC with a Multi-Stage Architecture

Watermark CDC doesn't exist in isolation — it feeds into a broader pipeline architecture. The delta records you capture need to land somewhere meaningful: a staging layer, a history table, or an incremental refresh partition in Power BI.

For robust pipeline design, consider structuring your Power Query output as described in Building Multi-Stage Staging Architectures in Power Query: Separating Raw, Cleansed, and Conformed Layers for Scalable ETL Pipelines. Your watermark-fetched delta records should land in a raw staging layer first, unmodified — this gives you a replayable audit trail and decouples the capture step from the transformation step.

The raw delta layer exposes this shape:

// Raw_Orders_Delta — exactly what came from source, plus metadata
let
    DeltaRecords = fn_GetOrdersDelta(WatermarkStore[#"Sales.Orders"]),
    
    WithMetadata = Table.AddColumn(
        Table.AddColumn(DeltaRecords,
            "_ExtractTimestamp", each DateTime.UtcNow(), type datetime),
        "_WatermarkUsed", each WatermarkStore[#"Sales.Orders"], type datetime
    )
in
    WithMetadata

This allows you to diagnose any load after the fact: you know exactly what watermark was used, when the extract ran, and what raw data arrived. When something goes wrong in the transformation layer downstream, you can replay from the raw delta without re-querying the source.

---

Performance Optimization and Scaling Considerations

Index Your Watermark Columns

This seems obvious, but it's missed surprisingly often. Your UpdatedAt column on every source table must have an index. Without one, your watermark filter becomes a full table scan even though the WHERE clause is perfectly selective. For a table with 10 million rows, the difference between an indexed and non-indexed watermark filter is the difference between a 50ms response and a 45-second scan.

CREATE INDEX IX_Orders_UpdatedAt ON Sales.Orders(UpdatedAt ASC) INCLUDE (OrderID, CustomerID);

The INCLUDE clause adds the most commonly selected columns to the index so SQL Server can often satisfy the watermark query entirely from the index without touching the main table (a covering index). Identify your most common projection columns from the delta query and include them.

Buffer Your Delta Results

When your delta query returns and you're about to perform multiple transformations on the result, consider buffering it:

BufferedDelta = Table.Buffer(RawDelta)

Table.Buffer forces the M engine to evaluate the source query once and hold the results in memory. Without it, each downstream reference to RawDelta might trigger a re-evaluation of the source query — meaning multiple round-trips to your SQL Server. For small deltas this doesn't matter; for deltas with thousands of records going through multiple transformation passes, it prevents redundant queries.

Parallel Delta Fetches

If your multi-table pipeline has tables without dependencies on each other (orders and customers are independent), Power Query may evaluate their delta queries in parallel depending on the M engine's scheduling. You can't directly control parallelism in M, but you can ensure your queries are structurally independent — don't reference one table's result from another unless you genuinely need to. Let the engine schedule freely.

Monitoring Delta Volume

Include delta volume metrics in your watermark update payload. Tracking how many records arrive per run per table is your early warning system for anomalies:

• Near-zero records on a normally busy table: source system may have stopped updating timestamps (schema change? application bug?)
• Unusually high record count: bulk update in the source? Data quality issue causing mass re-timestamping?

Build a DeltaMetrics query that surfaces these counts:

let
    Metrics = #table(
        {"TableName", "RecordsInDelta", "MinTimestamp", "MaxTimestamp", "RunTimestamp"},
        {
            {
                "Sales.Orders",
                Table.RowCount(Orders_Delta),
                List.Min(Orders_Delta[UpdatedAt]),
                List.Max(Orders_Delta[UpdatedAt]),
                DateTime.UtcNow()
            }
        }
    )
in
    Metrics

Load this table to your dataset and you have a built-in operational dashboard for your pipeline health.

---

Hands-On Exercise

In this exercise, you'll build a complete two-table watermark CDC pipeline against a sample database. You'll need access to a SQL Server instance where you can create tables.

Setup (run in SQL Server):

-- Create a sample orders table with a watermark column
CREATE TABLE [dbo].[SampleOrders] (
    [OrderID]    INT            NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [CustomerID] INT            NOT NULL,
    [Amount]     DECIMAL(10,2)  NOT NULL,
    [Status]     NVARCHAR(20)   NOT NULL DEFAULT 'Pending',
    [IsDeleted]  BIT            NOT NULL DEFAULT 0,
    [UpdatedAt]  DATETIME2(7)   NOT NULL DEFAULT SYSUTCDATETIME()
);

-- Seed 100 historical records
INSERT INTO [dbo].[SampleOrders] (CustomerID, Amount, Status)
SELECT 
    (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 50) + 1,
    ROUND(RAND(CHECKSUM(NEWID())) * 1000 + 10, 2),
    CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 3 = 0 THEN 'Shipped' ELSE 'Pending' END
FROM sys.objects CROSS JOIN sys.columns;

-- Create your watermark store
CREATE TABLE [dbo].[WatermarkStore] (
    [TableName]      NVARCHAR(128)  NOT NULL PRIMARY KEY,
    [LastWatermark]  DATETIME2(7)   NOT NULL,
    [RecordsLoaded]  INT            NULL,
    [UpdatedAt]      DATETIME2(7)   NOT NULL DEFAULT SYSUTCDATETIME()
);

INSERT INTO [dbo].[WatermarkStore] (TableName, LastWatermark)
VALUES ('dbo.SampleOrders', '2000-01-01 00:00:00');

-- Create an index on the watermark column
CREATE INDEX IX_SampleOrders_UpdatedAt ON dbo.SampleOrders(UpdatedAt ASC);

Exercise Steps:

1. Connect Power Query to your SQL Server and create a query named WatermarkStore_Current that reads from dbo.WatermarkStore and converts it to a record using the Record.FromTable pattern shown earlier.

2. Create a function query named fn_SampleOrdersDelta that accepts a datetime parameter watermark and returns records from dbo.SampleOrders where UpdatedAt > watermark, using Value.NativeQuery with a parameterized query.

3. Create an invocation query named SampleOrders_Delta that reads the watermark from WatermarkStore_Current and calls fn_SampleOrdersDelta. Verify you get all 100 records on the first run.

4. Create a NewWatermarks query that computes the new watermark as List.Max of the UpdatedAt column from SampleOrders_Delta, with a fallback to the current watermark when the delta is empty.

5. Simulate a second run: Go back to SQL Server and run:

   -- Simulate 5 new orders
   INSERT INTO dbo.SampleOrders (CustomerID, Amount, Status)
   VALUES (1, 299.99, 'Pending'), (2, 149.50, 'Pending'), 
          (3, 599.00, 'Pending'), (4, 89.99, 'Pending'), (5, 1299.00, 'Pending');
   
   -- Simulate 3 record updates
   UPDATE dbo.SampleOrders 
   SET Status = 'Shipped', UpdatedAt = SYSUTCDATETIME()
   WHERE OrderID IN (5, 12, 27);
   
   -- Update the watermark store to the max timestamp from run 1
   UPDATE dbo.WatermarkStore 
   SET LastWatermark = (SELECT MAX(UpdatedAt) FROM dbo.SampleOrders WHERE OrderID <= 100),
       RecordsLoaded = 100
   WHERE TableName = 'dbo.SampleOrders';
   

6. Refresh your Power Query and verify that SampleOrders_Delta returns exactly 8 records (5 inserts + 3 updates), not 108.

7. Inspect the new watermark value in your NewWatermarks query — it should be the timestamp of the most recent of your 8 delta records.

---

Common Mistakes & Troubleshooting

Mistake 1: Using DateTime.LocalNow() as the new watermark. You'll get timezone drift between environments. The new watermark must come from List.Max(DeltaRecords[UpdatedAt]) — the actual data, not the system clock.

Mistake 2: Forgetting to handle the empty delta case. List.Max({}) returns null. If your delta is empty and you store null as the watermark, the next run will throw a type error when it tries to use null in a datetime comparison. Always guard: if Table.RowCount(delta) = 0 then currentWatermark else List.Max(delta[UpdatedAt]).

Mistake 3: Breaking query folding with premature transformations. If you add a Table.AddColumn or Table.TransformColumns step before your Table.SelectRows watermark filter when using the OData/SQL connector (not Value.NativeQuery), you may break folding. Always filter before transforming when using the standard connector approach, and validate with Query Diagnostics.

Mistake 4: Storing watermarks as text without UTC clarity. Text-formatted datetimes without timezone indicators are a source of insidious bugs. "2024-11-15 03:45:22" — is that UTC? Local? Always append Z for UTC or include the offset. When reading back, parse with DateTimeZone.FromText rather than DateTime.FromText if you've stored timezone-aware strings.

Mistake 5: Not testing the boundary condition. The most common missed test case: what happens when two records have UpdatedAt equal to the stored watermark? Test this explicitly by inserting records with a controlled timestamp equal to your current watermark, running a load, advancing the watermark, and verifying neither duplication nor omission.

Mistake 6: Referencing the watermark query inside the delta function definition. Your fn_GetOrdersDelta function must receive the watermark as a parameter, not reference the WatermarkStore query directly inside the function body. If it references WatermarkStore directly, you get unpredictable query evaluation order and potentially a circular dependency. Parameters are the clean interface.

Troubleshooting: Delta returns zero records unexpectedly. First check: has the UpdatedAt column actually been maintained in the source? Run SELECT MAX(UpdatedAt) FROM SourceTable and compare to your stored watermark. If MAX(UpdatedAt) is less than or equal to your watermark, either the data genuinely hasn't changed, or the source application stopped maintaining the timestamp column (which has happened in production when a new ETL process bypasses the ORM and issues direct INSERT statements without trigger coverage). Second check: timezone offset — if your watermark is in UTC and the source timestamps are in EST, you may be filtering against the wrong epoch.

Troubleshooting: Delta returns all records on every run. Your watermark update isn't persisting. The Power Query side is correct, but the write-back mechanism (Power Automate, script, stored procedure) isn't executing or is failing silently. Add explicit logging to your write-back mechanism and check whether WatermarkStore actually shows updated values after a successful refresh.

---

Summary & Next Steps

You've built something non-trivial here. Watermark-based CDC in Power Query is an architecture pattern, not just a query trick — it involves state management, dependency ordering, error recovery, and coordination with external systems to persist the high-water mark.

The core pattern distilled: read the current watermark from a persistent store, query the source with a filter WHERE UpdatedAt > @watermark, compute the new watermark from the delta's maximum timestamp, and persist the new watermark through an external write mechanism after confirming load success. Layer on top: timezone normalization, empty-delta handling, inclusive boundary guards, soft-delete routing, and delta volume metrics.

What to explore next:

• If you're handling historical record changes and need to track what a record was before it changed, combine this watermark CDC pattern with SCD Type 2 logic — Merging Slowly Changing Dimensions in Power Query: Tracking Historical Changes with Type 1 and Type 2 SCD Patterns shows you how to build before/after snapshots from delta records.

• For the related topic of automating incremental refresh more broadly — including Power BI's native incremental refresh feature which has some overlap with watermark CDC — Automating Incremental Data Refreshes in Power Query with Persistent State and Change Tracking is the natural next stop.

• If your source tables have inconsistent or evolving schemas — which is common in transactional systems that get updated by multiple development teams — Handling Dynamic Schema Changes in Power Query: Strategies for Evolving Source Data Structures shows you how to make your delta functions resilient to column additions and removals.

• For hardening your pipeline against load failures with proper error handling and diagnostics, Debugging and Error Handling in M: Building Robust try-otherwise Logic and Diagnostic Workflows in Power Query provides the patterns you'll need when a delta load fails partway through.

The combination of reliable watermark CDC with a multi-stage staging architecture and proper error handling gives you a production-grade incremental pipeline that competes meaningfully with purpose-built ETL tools — built entirely within Power Query's M engine.