Implementing Custom Delta Load and Change Data Capture Patterns in Power Query M: Watermark Tracking, Hash-Based Diff Detection, and Incremental Table Synchronization Without Native CDC Support

Introduction

You're pulling from a transactional database that has 8 million rows and growing. Your refresh takes 45 minutes. Someone's asking why the dashboard isn't updated. You know the answer: every refresh loads the entire table from scratch because you haven't implemented any kind of incremental strategy — and your source system has no native CDC (Change Data Capture) support, no audit columns, nothing. You're on your own.

This is the scenario more data teams face than anyone likes to admit. Native CDC is a database-level feature that requires DBA access, server configuration, and often an enterprise license. Power BI's built-in incremental refresh gets you part of the way, but it depends on query folding and RangeStart/RangeEnd parameters that only work cleanly with date-partitioned data. When you're dealing with REST APIs, flat files, non-SQL sources, or databases where you can't modify schema or enable server features, you need to build the incremental logic yourself — inside Power Query M.

By the end of this lesson, you'll have a complete toolkit for implementing delta load patterns entirely within M. We'll cover watermark-based row filtering, row-level hash generation for detecting changed records, and a three-way synchronization pattern (inserts, updates, deletes) that reconstructs a full current-state snapshot from incremental pulls. These are production-grade patterns, not toys.

What you'll learn:

• How to implement watermark-based delta loading using tracked high-water mark values
• How to generate deterministic row hashes in M for detecting changed records without comparing every column
• How to perform three-way diff logic (new / changed / deleted) between a current snapshot and a fresh source pull
• How to structure your M queries so that state flows cleanly between a "previous state" table and a "current state" table
• Common failure modes in incremental M pipelines and how to defend against them

---

Prerequisites

You should be comfortable with:

• M language fundamentals including let...in expressions, function definitions, and record/table operations — see M Language Fundamentals: Syntax, Types, and Expressions for Power Query for a refresher
• Table join and merge operations, particularly Table.Join variants — the article on Combining Queries with Table.NestedJoin, Table.Join, and Merge Strategies in Power Query M covers this thoroughly
• How M evaluates queries lazily — Understanding M Language Query Evaluation: Lazy Evaluation, Dependency Graphs, and Step Ordering in Power Query is essential background before tackling stateful patterns

---

The Core Problem: M Is Stateless by Design

Power Query M is designed to be a pure, declarative transformation language. Every time your query runs, it starts fresh. There's no built-in mechanism to say "here's what I had last time, only give me what changed." That's what makes implementing CDC patterns genuinely hard — and interesting.

To build incremental behavior, you need to simulate state. That means you need at least two persistent layers:

1. A "previous state" store — a snapshot of what you ingested last time, typically saved as a Power BI dataflow, an Excel table, a SharePoint list, or a database table you can write back to
2. A "current source" pull — the live data from your source, possibly filtered by a watermark

The M query logic sits between these two layers, comparing them and producing a reconciled current-state output.

Key insight: The reason most Power Query CDC implementations fail in production is not bad M code — it's an ill-defined state store. Before writing a single line of M, decide where your previous state will live and how your pipeline will write back to it after each successful run.

---

Pattern 1: Watermark-Based Delta Loading

The simplest and most efficient incremental pattern is watermark tracking. It works when your source table has a reliable LastModifiedDate, UpdatedAt, or monotonically increasing RowVersion column. You store the maximum value seen in the last run, then filter the next run to only pull rows where that column exceeds the watermark.

Setting Up a Watermark Parameter Table

Following the pattern described in Cross-Query State Management and Shared Parameter Tables in Power Query M, store your watermark in a named M query that you'll treat as a centralized parameter:

// Query: WatermarkConfig
let
    // In production, this would load from an Excel table, SharePoint list,
    // or database table that your pipeline updates after each successful run.
    // For illustration, we define it inline — replace with your actual source.
    WatermarkTable = Excel.Workbook(
        File.Contents("C:\PipelineState\watermarks.xlsx"), 
        true, 
        true
    ){[Item="Watermarks", Kind="Sheet"]}[Data],
    
    // Pull the watermark for the specific table we care about
    OrdersWatermark = Table.SelectRows(
        WatermarkTable, 
        each [TableName] = "Orders"
    ),
    
    WatermarkValue = if Table.IsEmpty(OrdersWatermark) 
        then #datetime(2000, 1, 1, 0, 0, 0)  // cold start default
        else DateTime.From(
            Table.First(OrdersWatermark)[LastSeenTimestamp]
        )
in
    WatermarkValue

Pulling the Delta

Now use that watermark to filter your source pull. This works especially well when your source supports query folding — the filter gets pushed to the server and you never transfer rows you don't need.

// Query: Orders_Delta
let
    Source = Sql.Database("prod-server.corp.com", "SalesDB"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    
    // Apply the watermark filter — this folds to SQL in most connectors
    FilteredOrders = Table.SelectRows(
        Orders,
        each [LastModifiedDate] > WatermarkConfig
    ),
    
    // Type the columns explicitly so downstream joins are stable
    TypedOrders = Table.TransformColumnTypes(FilteredOrders, {
        {"OrderID",     Int64.Type},
        {"CustomerID",  Int64.Type},
        {"OrderTotal",  Currency.Type},
        {"Status",      type text},
        {"LastModifiedDate", type datetime},
        {"IsDeleted",   type logical}
    })
in
    TypedOrders

Warning: Query folding breaks silently in some scenarios — if you add a step that can't fold before the filter, the entire filter runs in memory and you lose all the performance benefit. Check your query diagnostics, or read Implementing Custom Query Folding Logic in M: Keeping Transformations Native to the Data Source to understand when folding is and isn't happening.

Computing and Storing the New Watermark

After the delta pull, capture the maximum LastModifiedDate in the current batch. In a full pipeline, you'd write this value back to your watermark store — the mechanism for doing that is outside M itself (a Power Automate flow, a Python script, or a dataflow write-back step). But you can compute it in M so the orchestration layer can consume it:

// Query: Orders_NewWatermark
let
    MaxTimestamp = List.Max(Orders_Delta[LastModifiedDate]),
    
    // If the delta was empty (no changes), keep the old watermark
    NewWatermark = if MaxTimestamp = null 
        then WatermarkConfig 
        else MaxTimestamp
in
    NewWatermark

---

Pattern 2: Hash-Based Change Detection

Watermarks only work when your source has a reliable "changed at" indicator. Plenty of sources don't — legacy flat file exports, some REST APIs, and systems where the UpdatedAt column is unreliably populated or absent entirely.

Hash-based detection takes a different approach: you hash every row's key columns (or all columns), compare the hashes against what you stored last time, and flag anything that's new or different. This is more expensive than watermarking but catches changes that watermarks miss, including back-dated updates.

Building a Row Hash Function

M doesn't have a native SHA256 or MD5 function for arbitrary row content, but you can construct a deterministic row fingerprint by concatenating column values into a canonical string and hashing it with Binary.ToText on the result of Text.ToBinary — or by using Crypto.Hash where available (Power BI Service supports it; Power BI Desktop as of late 2023 also exposes it in most regions).

Here's a reusable hash function you'd define using the approach described in Writing Custom M Functions from Scratch in Power Query:

// Query: fnHashRow (a reusable function)
let
    fnHashRow = (row as record, columnsToHash as list) as text =>
    let
        // Extract only the columns we want to fingerprint
        SelectedValues = List.Transform(
            columnsToHash,
            each Record.Field(row, _)
        ),
        
        // Convert each value to a canonical text form, 
        // handling nulls explicitly so null != empty string
        CanonicalValues = List.Transform(
            SelectedValues,
            each if _ = null 
                 then "«NULL»"
                 else Text.From(_)
        ),
        
        // Concatenate with a separator that won't appear in your data
        ConcatenatedString = Text.Combine(CanonicalValues, "||"),
        
        // Hash using Binary encoding — produces a consistent fingerprint
        HashBytes = Crypto.Hash(
            CryptoAlgorithm.SHA256, 
            Text.ToBinary(ConcatenatedString, TextEncoding.Utf8)
        ),
        
        // Convert to hex string for easy storage and comparison
        HashHex = Binary.ToText(HashBytes, BinaryEncoding.Hex)
    in
        HashHex
in
    fnHashRow

Note: Crypto.Hash is available in Power BI Service and recent versions of Power BI Desktop. If you're targeting older environments or Power Query in Excel, you'll need a fallback. A simpler (though less collision-resistant) alternative is to use Text.Length + checksums derived from character codes — adequate for most business data but not cryptographically sound. For production pipelines handling sensitive data, always use a proper hash.

Applying the Hash to a Full Table

Now apply fnHashRow to every row of your source data, generating a column called RowHash:

// Query: Products_WithHashes
let
    Source = OData.Feed(
        "https://api.yoursystem.com/odata/Products",
        null,
        [Implementation = "2.0"]
    ),
    
    // Columns that constitute a meaningful change — 
    // exclude metadata columns like CreatedDate that don't indicate data changes
    HashColumns = {
        "ProductName", "CategoryID", "UnitPrice", 
        "UnitsInStock", "Discontinued", "SupplierId"
    },
    
    // Add the hash column using Table.AddColumn
    WithHashes = Table.AddColumn(
        Source,
        "RowHash",
        each fnHashRow(_, HashColumns),
        type text
    ),
    
    // Keep only the key + hash for the comparison step
    // (we'll join back to get full columns for new/changed rows)
    KeyAndHash = Table.SelectColumns(
        WithHashes, 
        {"ProductID", "RowHash"}
    )
in
    KeyAndHash

Tip: Separate your "hash manifest" (just key + hash) from your "full data" pull. When doing the diff, you only need the hashes — only fetch the full column payload for rows that are actually new or changed. This can dramatically reduce data transfer on large tables.

Three-Way Diff: New, Changed, and Deleted

Here's the real engine of hash-based CDC. You have two tables:

• PreviousSnapshot — key + hash from last run (stored in your state layer)
• CurrentSource — key + hash from this run

The three-way categorization works like an outer join:

// Query: Products_ChangeCategories
let
    // Load previous snapshot from state store
    PreviousSnapshot = Excel.Workbook(
        File.Contents("C:\PipelineState\products_snapshot.xlsx"), 
        true, true
    ){[Item="ProductHashes", Kind="Sheet"]}[Data],
    
    PreviousTyped = Table.TransformColumnTypes(PreviousSnapshot, {
        {"ProductID", Int64.Type},
        {"RowHash",   type text}
    }),
    
    // Current source hash manifest (from Products_WithHashes above)
    CurrentHashes = Products_WithHashes,  // references our earlier query
    
    // Full outer join to see everything from both sides
    Joined = Table.Join(
        CurrentHashes,  "ProductID",
        PreviousTyped,  "ProductID",
        JoinKind.FullOuter
    ),
    
    // Rename to disambiguate left vs right hashes
    Renamed = Table.RenameColumns(Joined, {
        {"RowHash",   "CurrentHash"},
        {"RowHash.1", "PreviousHash"}
    }),
    
    // Classify each row
    WithChangeType = Table.AddColumn(
        Renamed,
        "ChangeType",
        each 
            if [PreviousHash] = null then "INSERT"
            else if [CurrentHash] = null then "DELETE"
            else if [CurrentHash] <> [PreviousHash] then "UPDATE"
            else "UNCHANGED",
        type text
    ),
    
    // Filter out unchanged rows — we don't need to process them
    ChangedOnly = Table.SelectRows(
        WithChangeType,
        each [ChangeType] <> "UNCHANGED"
    )
in
    ChangedOnly

This gives you a categorized change set. From here, you join back to the full source data for INSERTs and UPDATEs, and you handle DELETEs by marking them (or removing them) from your materialized current-state table.

---

Pattern 3: Incremental Table Synchronization

The previous two patterns tell you what changed. This pattern addresses what to do about it — maintaining a synchronized current-state table that's built incrementally rather than rebuilt from scratch each time.

The core idea: your "current state" output is the union of:

1. Rows from the previous snapshot that weren't deleted or updated
2. New rows (INSERTs)
3. Replacement rows for changed records (UPDATEs — old row out, new row in)

Full Synchronization Query

Let's put it together for a realistic scenario: a Customers table sourced from a CRM REST API that has no timestamp columns and 500k+ records.

// Query: Customers_Synchronized (the final output query)
let
    // ── STEP 1: Load previous state ──────────────────────────────────────
    PreviousState = Excel.Workbook(
        File.Contents("C:\PipelineState\customers_current.xlsx"),
        true, true
    ){[Item="CurrentCustomers", Kind="Sheet"]}[Data],
    
    PreviousTyped = Table.TransformColumnTypes(PreviousState, {
        {"CustomerID",   Int64.Type},
        {"FullName",     type text},
        {"Email",        type text},
        {"Tier",         type text},
        {"AnnualSpend",  Currency.Type},
        {"Region",       type text},
        {"RowHash",      type text},
        {"_LoadedAt",    type datetime}
    }),
    
    // ── STEP 2: Pull current source and hash it ───────────────────────────
    RawSource = Json.Document(
        Web.Contents("https://crm.corp.com/api/v2/customers?limit=all")
    ),
    
    // Assuming the API returns a list of records under a "customers" key
    // See the JSON/XML processing guide for handling nested structures
    CustomerRecords = Table.FromRecords(
        List.Transform(
            RawSource[customers], 
            each _
        )
    ),
    
    HashCols = {"FullName", "Email", "Tier", "AnnualSpend", "Region"},
    
    CurrentWithHashes = Table.AddColumn(
        CustomerRecords,
        "RowHash",
        each fnHashRow(_, HashCols),
        type text
    ),
    
    CurrentTyped = Table.TransformColumnTypes(CurrentWithHashes, {
        {"CustomerID",   Int64.Type},
        {"FullName",     type text},
        {"Email",        type text},
        {"Tier",         type text},
        {"AnnualSpend",  Currency.Type},
        {"Region",       type text}
    }),
    
    // ── STEP 3: Build hash manifests for comparison ───────────────────────
    PreviousHashes = Table.SelectColumns(
        PreviousTyped, {"CustomerID", "RowHash"}
    ),
    
    CurrentHashes = Table.SelectColumns(
        CurrentTyped, {"CustomerID", "RowHash"}
    ),
    
    // ── STEP 4: Classify changes ──────────────────────────────────────────
    JoinedHashes = Table.Join(
        CurrentHashes, "CustomerID",
        PreviousHashes, "CustomerID",
        JoinKind.FullOuter
    ),
    
    ClassifiedJoin = Table.RenameColumns(JoinedHashes, {
        {"RowHash",   "CurrentHash"},
        {"RowHash.1", "PreviousHash"}
    }),
    
    WithChangeType = Table.AddColumn(
        ClassifiedJoin,
        "ChangeType",
        each 
            if [PreviousHash] = null then "INSERT"
            else if [CurrentHash] = null then "DELETE"
            else if [CurrentHash] <> [PreviousHash] then "UPDATE"
            else "UNCHANGED",
        type text
    ),
    
    // ── STEP 5: Build each segment of the output ──────────────────────────
    
    // Unchanged rows: take from previous state as-is (preserve _LoadedAt)
    UnchangedIDs = Table.SelectRows(
        WithChangeType, each [ChangeType] = "UNCHANGED"
    )[CustomerID],
    
    UnchangedRows = Table.SelectRows(
        PreviousTyped,
        each List.Contains(UnchangedIDs, [CustomerID])
    ),
    
    // Inserted rows: take from current source, add metadata
    InsertedIDs = Table.SelectRows(
        WithChangeType, each [ChangeType] = "INSERT"
    )[CustomerID],
    
    InsertedRows_Raw = Table.SelectRows(
        CurrentTyped,
        each List.Contains(InsertedIDs, [CustomerID])
    ),
    
    InsertedRows = Table.AddColumn(
        InsertedRows_Raw,
        "_LoadedAt",
        each DateTime.LocalNow(),
        type datetime
    ),
    
    // Updated rows: take from current source (new values), update _LoadedAt
    UpdatedIDs = Table.SelectRows(
        WithChangeType, each [ChangeType] = "UPDATE"
    )[CustomerID],
    
    UpdatedRows_Raw = Table.SelectRows(
        CurrentTyped,
        each List.Contains(UpdatedIDs, [CustomerID])
    ),
    
    UpdatedRows = Table.AddColumn(
        UpdatedRows_Raw,
        "_LoadedAt",
        each DateTime.LocalNow(),
        type datetime
    ),
    
    // Deleted rows: excluded from output (soft-delete variant shown below)
    
    // ── STEP 6: Union the three segments ─────────────────────────────────
    FinalOutput = Table.Combine({
        UnchangedRows,
        InsertedRows,
        UpdatedRows
    })
in
    FinalOutput

Warning: List.Contains on a large list used inside Table.SelectRows is an O(n×m) operation — it won't fold and will be slow for large tables. For high-volume scenarios, replace the List.Contains approach with an anti-join pattern using Table.Join with JoinKind.LeftAnti or JoinKind.Inner, which is significantly more efficient. See Advanced Table Operations: Group, Join, and Transform in M Language for anti-join patterns.

Efficient Anti-Join Alternative

Here's the more performant pattern for the "unchanged" segment:

// More efficient: use LeftAnti join to find rows NOT in the changed set
ChangedIDs = Table.SelectRows(
    WithChangeType, 
    each [ChangeType] = "UPDATE" or [ChangeType] = "DELETE"
),

UnchangedRows = Table.Join(
    PreviousTyped,          "CustomerID",
    ChangedIDs,             "CustomerID",
    JoinKind.LeftAnti
),

// LeftAnti returns rows from the left table that have no match on the right
// Result: previous rows that were neither updated nor deleted

---

Handling Soft Deletes

Many business requirements don't allow physical deletion of records — instead, deleted rows get flagged. Adjust the synchronization logic to carry deleted rows forward with a _IsDeleted flag:

// Modified: instead of excluding deletes, flag them
DeletedIDs = Table.SelectRows(
    WithChangeType, each [ChangeType] = "DELETE"
)[CustomerID],

DeletedRows_Previous = Table.SelectRows(
    PreviousTyped,
    each List.Contains(DeletedIDs, [CustomerID])
),

// Add or update the soft-delete flag
DeletedRows = Table.AddColumn(
    DeletedRows_Previous,
    "_IsDeleted",
    each true,
    type logical
),

// For non-deleted rows, ensure the column exists with false
NonDeletedWithFlag = Table.AddColumn(
    Table.Combine({UnchangedRows, InsertedRows, UpdatedRows}),
    "_IsDeleted",
    each false,
    type logical
),

// Final union including soft-deleted rows
FinalOutput = Table.Combine({NonDeletedWithFlag, DeletedRows})

---

Performance Implications and When to Use Each Pattern

Pattern	Best When	Weakness
Watermark	Source has reliable UpdatedAt / RowVersion	Misses back-dated changes; requires trustworthy audit column
Hash-based	No reliable timestamp; full fidelity needed	Requires full source pull; expensive on very large tables
Combined	Large tables, partial trustworthy timestamps	Most complex; highest implementation overhead

For tables under ~100k rows, hash-based detection is perfectly practical. For tables in the millions, try watermarking first and only fall back to hashing for columns that aren't covered by your watermark (for example, if your watermark column only tracks inserts, not updates to certain fields).

Key insight: The combined approach — use a watermark to reduce the pull to "recently touched" rows, then use hashing on that smaller set to detect actual field-level changes — gives you the best of both. You're not pulling all 8 million rows, but you're also not trusting the source's timestamp to tell you what changed.

For understanding the performance cost of each M operation in these patterns, M Language Performance Patterns and Anti-Patterns: Optimize Power Query for Speed is required reading before you push any of this to production.

---

Hands-On Exercise

Build a complete incremental sync pipeline for a simulated inventory dataset. Here's your scenario:

You have a Warehouse_Inventory Excel table with 10,000 rows and these columns: SKU (text, primary key), ProductName, QuantityOnHand (number), ReorderLevel (number), LastCounted (date), WarehouseLocation (text).

The table is exported fresh every night with no timestamp indicating what changed.

Your task:

1. Create an fnHashRow function query in Power Query that hashes the columns {ProductName, QuantityOnHand, ReorderLevel, WarehouseLocation} (deliberately exclude LastCounted — assume it's unreliable).

2. Create a Inventory_Current_WithHashes query that loads today's export and adds a RowHash column.

3. Create a Inventory_PreviousSnapshot query that loads a second Excel file representing yesterday's state (which already has a RowHash column from the previous run).

4. Create an Inventory_Changes query that performs the full outer join classification and produces a table with columns: SKU, ChangeType, CurrentHash, PreviousHash.

5. Create an Inventory_Synchronized query that unions unchanged (from yesterday), inserted, and updated rows — and excludes deleted rows (hard delete for this exercise).

6. Stretch goal: Add a _DaysInState column to unchanged rows that calculates how many days have passed since _LoadedAt from the previous snapshot. Use Working with Dates, Times, and Duration Values in Power Query M as a reference for duration arithmetic.

---

Common Mistakes & Troubleshooting

Mistake 1: Trusting `DateTime.LocalNow()` for Consistent Watermarks

DateTime.LocalNow() is evaluated at query execution time and can return slightly different values if a query is evaluated multiple times within a single refresh (due to lazy evaluation). For watermarks, always capture the "run timestamp" as a single named query value and reference it everywhere:

// Query: RunTimestamp (evaluated once, referenced everywhere)
let
    RunTimestamp = DateTime.LocalNow()
in
    RunTimestamp

// Then in other queries:
// _LoadedAt = RunTimestamp  (not DateTime.LocalNow() inline)

Mistake 2: Column Order Dependence in Hash Generation

Your fnHashRow function concatenates column values in the order you specify. If the list of columns changes order between runs (say, someone edits the function and reorders the list), every hash changes and you'll see the entire table flagged as UPDATEd. Always sort your columnsToHash list canonically:

// Sort the column list before hashing to make order-independent
SortedCols = List.Sort(columnsToHash),
SelectedValues = List.Transform(SortedCols, each Record.Field(row, _)),

Mistake 3: Type Coercion Changing Hash Output

If AnnualSpend is Currency.Type in one run and number in another, Text.From(1234.50) might produce "1234.5" vs "1234.50" depending on the type context. Lock down types before hashing with explicit Text.From formatting:

// Instead of Text.From(value) naively:
each 
    if _ = null          then "«NULL»"
    else if _ is number  then Number.ToText(_, "G15")
    else if _ is logical then (if _ then "true" else "false")
    else if _ is date    then Date.ToText(_, "yyyy-MM-dd")
    else if _ is datetime then DateTime.ToText(_, "yyyy-MM-ddTHH:mm:ss")
    else Text.From(_)

Tip: Define your canonical text conversion logic once inside fnHashRow as a nested helper, then reuse it for every value in the row. Don't scatter conversion logic across queries — one change to the format will cause every existing hash to become stale.

Mistake 4: State Store Mismatch After a Failed Run

If your pipeline successfully transforms data but fails before writing the new snapshot back to the state store, the next run will re-process the same changes because the watermark/snapshot wasn't updated. Build your orchestration so that the state store is only updated after the output load succeeds — never before. In Power Automate, this means putting the watermark update action after a confirmed successful dataflow refresh, not before it.

Mistake 5: Empty Delta Causing Null Watermark

If the source returns no rows (entirely quiet period with no changes), List.Max({}) returns null. An unchecked null watermark then gets stored, and on the next run you try to compare null > someTimestamp — which in M evaluates to false, causing a full reload. Always guard:

NewWatermark = if List.IsEmpty(Delta[LastModifiedDate]) or 
               List.Max(Delta[LastModifiedDate]) = null
    then WatermarkConfig   // keep existing watermark
    else List.Max(Delta[LastModifiedDate])

Mistake 6: The "Column Not Found" Crash on First Run

The first time your pipeline runs, there's no previous snapshot — the state store file doesn't exist yet. You need a cold-start path. Wrap your state store load in a try...otherwise expression:

PreviousState = 
    let
        AttemptLoad = try Excel.Workbook(
            File.Contents("C:\PipelineState\customers_current.xlsx"),
            true, true
        ){[Item="CurrentCustomers", Kind="Sheet"]}[Data]
    in
        if AttemptLoad[HasError] 
        then #table(
            {"CustomerID","FullName","Email","Tier","AnnualSpend","Region","RowHash","_LoadedAt"},
            {}  // empty table with correct schema
        )
        else AttemptLoad[Value]

This pattern is essential for making your pipeline self-initializing — first run it treats the entire source as INSERTs.

---

Summary & Next Steps

You've built three layered patterns for implementing change data capture in Power Query M without native CDC support:

1. Watermark tracking — the fastest approach for sources with reliable audit timestamps; filter at the source, store the high-water mark, advance it each run
2. Hash-based diff detection — the most thorough approach for sources without timestamps; generate deterministic row fingerprints, compare manifests, classify changes
3. Incremental table synchronization — the combination pattern that maintains a current-state snapshot by unioning unchanged, inserted, and updated segments while excluding or flagging deletes

These patterns give you genuine incremental behavior in environments where most people assume a full reload is the only option. The state management discipline — cold-start handling, null guards, deterministic formatting before hashing, watermark update ordering — is what separates a demo from a production pipeline.

Where to go from here:

• If you're applying these patterns to slowly changing dimension tables (tracking historical versions of records, not just current state), the next natural step is Implementing Slowly Changing Dimensions in Power Query M: Type 1, Type 2, and Hybrid Strategies for Historical Data Tracking — it builds directly on the hash and synchronization patterns you've learned here
• For multi-table pipelines where these patterns need to run in dependency order, Building Multi-Stage ETL Pipelines in Power Query M: Orchestrating Dependent Transformations with Modular Query Chains shows how to wire the query graph correctly
• If your source is an API returning paginated responses (common for CRM systems), Streaming and Pagination Patterns in M: Handling Large APIs and Multi-Page Data Sources with Custom Iterators shows how to build the source pull layer that feeds into these CDC patterns

The patterns in this lesson are composable — you can mix watermark filtering with hash verification, wrap the whole thing in the function-library approach from Building a Reusable Function Library in Power Query, and deploy it across dozens of tables by parameterizing the key columns, hash columns, and state store paths.