Incremental data processing strategies

Important

Some or all of this functionality is available as part of a preview release. The content and the functionality are subject to change.

The solution uses different incremental processing strategies optimized for each layer transition:

Bronze to Silver: Watermark-based incremental loading

For ingesting data from source systems into bronze and transforming to silver, the solution uses watermark-based incremental loading:

Salesforce NPSP:

  • Uses SystemModstamp field as watermark to track last modification timestamp
  • Stores watermark values in Metadata_Watermark table in Bronze lakehouse
  • Only processes records modified since the last watermark
  • Performs MERGE operations to update existing records and insert new ones

Dynamics 365:

  • Uses custom watermark-based synchronization for data tables
  • Tracks last processed timestamp per entity
  • Performs full compare-and-sync for option sets (dimension tables)
  • Uses MERGE operations with INSERT/UPDATE/DELETE logic

Silver to Gold: Change Data Feed (CDF)

For transforming data from silver to gold layer, the solution uses Delta Lake change data feed (CDF) to capture only records that are inserted, updated, or deleted since the last refresh. The solution processes this delta instead of performing a full data reload, greatly reducing the compute overhead and shortening the processing windows.

ProcessCdfTable function

The ProcessCdfTable function, defined in the Fundraising_Config notebook, processes incremental changes from silver to gold. It uses Delta Lake change data feed (CDF) to identify new or modified rows in a source table and applies those changes to a target table.

This function helps ensure data is updated efficiently through tracking changes, enriching records, and handling updates and inserts (upserts).

Key concepts:

Concept Description
Change data feed (CDF) Enables reading only the changed rows (inserts/updates/deletes) from a Delta table since a specific commit version.
Commit version tracking Maintains a record of the last processed _commit_version using the ChangeDataFeedState table.
Surrogate key enrichment Optionally enriches records with dimension keys or other business logic through a user-defined enrich_func.
Idempotent merge Runs a parameterized MERGE INTO SQL template to insert or update the target table with processed data.

Processing workflow:

Prerequisites:

  • The source Delta table must have CDF enabled (automatically configured during schema creation):

    ALTER TABLE <table_name> SET TBLPROPERTIES (delta.enableChangeDataFeed = true);

  • The ChangeDataFeedState tracking table must exist in the target lakehouse (created automatically during setup).

Workflow:

  1. Determine last processed version
    The system checks the ChangeDataFeedState table for the most recent _commit_version processed for a given source/target table pair.

  2. Read changes from Delta source
    Using Delta Lake readChangeFeed option, the function reads only new or updated records since the last known version. Delete operations are handled separately.

  3. Apply deduplication logic
    If multiple updates exist for the same primary key, only the latest change is selected per _commit_version.

  4. Optional data enrichment
    If the table specifies an enrich_func, the filtered data is passed through this function for dimension lookups or other transformation logic.

  5. Run merge logic
    The processed snapshot is materialized into a temporary view, and the defined merge_sql_template is run to apply the changes to the target table.

  6. Update processing state
    After successful processing, the function records the most recent commit version to the ChangeDataFeedState table for future executions.

See also

  • Last updated on