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:
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.
Warning: Watermark CDC has a structural blind spot: hard deletes. If a record is deleted from the source table, its UpdatedAt timestamp doesn't change — the row simply disappears. Watermark CDC will never capture that deletion. You must handle deletes through a separate soft-delete pattern (a IsDeleted flag or DeletedAt timestamp) or accept that your downstream model doesn't need to reflect source deletes. We'll cover this in detail in the edge cases section.
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.
Note: Using DATETIME2(7) rather than DATETIME matters. SQL Server's DATETIME type has approximately 3.33ms precision, which can cause boundary issues where records created at the exact watermark moment are missed or duplicated. DATETIME2 has 100-nanosecond precision. If your source timestamps use DATETIME, cast them consistently in your queries.
Building the Core M Query Architecture
Your complete watermark CDC system will consist of four logical query layers in Power Query:
- WatermarkStore — reads current watermark values from the store
- fn_GetDelta — a function that accepts a table name and watermark, returns only the delta records
- [TableName]_Delta — invokes fn_GetDelta for each tracked table
- 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.
Key insight: Structuring your watermarks as a record rather than keeping them in a table makes downstream consumption dramatically cleaner. You avoid repeated Table.SelectRows lookups and the associated null-handling boilerplate every time you need a single watermark value.
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.
Warning: If you use Sql.Database and chain a Table.SelectRows filter in M, query folding is usually maintained for simple predicates on indexed columns — but it can break silently if you add certain M transformations before the filter. Using Value.NativeQuery makes your intent explicit and gives you full SQL control. For performance-critical pipelines, always verify folding is occurring using the Query Diagnostics feature (Tools > Start Diagnostics in Power BI Desktop). See Power Query Performance: Master Folding, Buffering & Optimization Techniques for how to interpret diagnostics output.
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
Tip: Always use List.Max on the UpdatedAt column of your actual result set rather than DateTime.LocalNow() or DateTime.UtcNow() as the new watermark. Using the system clock introduces a subtle timing bug: records written to the source database in the milliseconds between your query executing and your watermark being recorded could be missed on the next run because they'll have timestamps before your stored mark.
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
Key insight: Soft delete support requires coordination with your source application team. If developers hard-delete records without implementing soft-delete patterns, watermark CDC will silently miss those deletions forever. This is a business requirement conversation, not just a technical one. Get it in writing before you commit to a watermark-based architecture for data that must reflect deletions accurately.
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:
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.
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.
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
Warning: Dynamic SQL construction by string concatenation in M is safe only when the dynamic values come from your own trusted configuration, not from user-supplied input. Schema names, table names, and column names cannot be parameterized in SQL — they must be interpolated as strings. In an enterprise context, validate these values against a whitelist before using them in SQL construction.
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.
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.
Tip: Buffer at the boundary where raw source data transitions to transformation logic — after your watermark-filtered fetch, before your business rule applications. Never buffer before the watermark filter, as that would defeat the entire purpose by materializing the full table in memory first.
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:
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.
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.
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.
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.
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';
Refresh your Power Query and verify that SampleOrders_Delta returns exactly 8 records (5 inserts + 3 updates), not 108.
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:
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.
