Pipelines

Pipeline load strategies

This is the reference companion to Choose a load strategy. Use it to look up how a strategy behaves and what it adds to the destination table.

Summary

Strategy Needs a key column Required settings Audit columns added
Truncate & Reload No None LoadDate
Incremental Load Yes Watermark column LoadDate, SourceModifiedDate
Incremental Batch Yes Batch column LoadDate
Incremental Hash Yes None (hash source optional) LoadDate, RowHash, IsDeleted
SCD Type 2 Yes Change detection method, plus that method’s setting See SCD Type 2

A table with strategy None gets no load code.

Truncate & Reload

  • Behavior. Empties the destination table, then reloads every row from the source.
  • Choose it for small tables and reference data you can rebuild cheaply. It is the default for new tables.
  • Watch out. Destructive on every run. Validation warns when a source schema is also the destination.

Incremental Load

  • Behavior. Reads only source rows whose watermark column is later than the last successful run, minus the lookback window, and merges them into the destination by key.
  • Settings. Watermark Column (source datetime) and Lookback Minutes (default 5, up to 1440).
  • Run-time option. SQL Server and Fabric Warehouse procedures accept @WatermarkOverride to re-read from a date you choose. The PySpark function takes watermark_override.

Incremental Batch

  • Behavior. Reads source rows with a batch or sequence number higher than the last one loaded, and merges them by key.
  • Settings. Source Batch Column (a numeric column).
  • Run-time option. @BatchId (batch_id in PySpark).

Incremental Hash

  • Behavior. Compares a hash of each source row with the stored RowHash. New rows are inserted, changed rows are updated, and rows missing from the source are flagged with IsDeleted.
  • Settings. Hash Source: Computed (Generated View) (default) creates a helper view, vw_hash_<source table>, that adds the hash. Existing Source Column uses a hash column the source already has.
  • Choose it when rows change but the source has no reliable timestamp.

SCD Type 2

  • Behavior. Keeps history. When a row changes, the current version is closed (its ExpirationDate is set and IsCurrent becomes false) and a new current version is inserted.
  • Settings. Change Detection Method (Hash, Watermark, or Batch) and that method’s setting. Handle Deletes closes rows that disappear from the source. Handle Reactivations reopens rows that come back. Both are on by default.
  • Current rows. Identify them with IsCurrent = 1. The open row’s ExpirationDate holds an end-of-time date (9999-12-31 23:59:59), not NULL.

Audit columns by change detection method:

Method Audit columns
Hash EffectiveDate, ExpirationDate, IsCurrent, IsDeleted, RowHash, LoadDate
Watermark EffectiveDate, ExpirationDate, IsCurrent, IsDeleted, LoadDate, SourceModifiedDate
Batch EffectiveDate, ExpirationDate, IsCurrent, IsDeleted, LoadDate

Strategies you cannot pick directly

Analyze All and Star Schema plans can assign strategies that are not in the dropdown: SCD Type 1, Fact: Append Only, Fact: Upsert, and Fact: Snapshot Reload. They generate load code like the others.

Strategy Behavior Audit columns
SCD Type 1 Overwrites changed attributes in place, no history. Needs a key and a change detection method. LoadDate plus RowHash, IsDeleted (Hash) or SourceModifiedDate (Watermark)
Fact: Append Only Inserts new rows, never updates. No key needed. LoadDate, BatchId
Fact: Upsert Inserts new rows and updates existing ones by key, using a row hash. LoadDate, BatchId, RowHash, IsDeleted
Fact: Snapshot Reload Deletes and reloads the rows for one snapshot date. No key needed. LoadDate, SnapshotDate

🚩 TODO: These strategies show as an empty cell in the Strategy dropdowns. Confirm the intended display before documenting how to change them.

Audit columns

Kenseme adds audit columns to the destination table when you finalize. You do not map them; the load fills them in. If you already map a column with the same name, Kenseme does not add a second one. Changing strategy and finalizing again removes the old strategy’s audit columns and adds the new ones.

Column Meaning
LoadDate When the row was loaded.
RowHash Hash of the source values, used to detect changes.
IsDeleted 1 when the row no longer exists in the source.
SourceModifiedDate The source’s last-modified value.
BatchId The load batch the row belongs to.
SnapshotDate The business date of a snapshot.
EffectiveDate, ExpirationDate, IsCurrent SCD Type 2 history.

Output by destination

Destination Output
SQL Server, Fabric Warehouse T-SQL stored procedures. Each run is wrapped in a transaction and logged to the zz_<schema>_Jobs table.
Databricks, Fabric Lakehouse PySpark functions that run Delta operations. Helper views are materialized views, refreshed before the loads. See PySpark notebooks.
PostgreSQL, MySQL, Snowflake, Redshift Not supported yet. You can map, validate, and finalize, but load generation stops with an error.