Skip to main content
The Merge - SCD2 write mode tracks historical changes by adding new rows instead of updating existing ones. This lets you preserve a complete history, since every change generates a new entry rather than erasing old information. To be specific:
  • New rows are added for incoming records with unique keys that don’t exist in the target table.
  • For records matching existing unique keys, a new row is added only when the incoming data differs from the existing record.
  • New rows are assigned a start date and a null end date (indicating it’s currently valid).
  • If the unique key of the new record matched an existing record, the existing row is assigned an end date to mark when it stopped being valid.
  • This creates a complete timeline showing how data evolved over time.

​
Parameters

​
Example: Route assignment history

A vehicle assignment table tracks which route each vehicle is assigned to over time. When a vehicle is assigned to a new route, Prophecy preserves the previous assignment and creates a new current record. In this example, Determine new records by checking timestamp column is enabled, and assigned_at is the timestamp column.
Existing target tableIncoming data setUpdated target tableBecause the incoming row matches an existing vehicle_id but has a newer assigned_at value, Prophecy preserves the existing row by assigning it a valid_to date and inserts a new current row with a null valid_to value. This creates a complete history of the vehicle’s route assignments rather than overwriting the previous assignment.

​
Parameters (PySpark only)

Private Preview When PySpark is the project language, the SCD2 write mode includes the following configuration options.