Enable a built-in SCD2 update option in Copy Job.
When using CDC sources like Delta CDF, Copy Job should automatically manage validity dates (valid_from, valid_to) and current row flags.
This would simplify dimension management without custom MERGE logic.
3 Comments
yexuMicrosoft Employee
Status changed:NewtoPlannedThis is a great idea, and it already looks like it’s starting to materialise in Copy Job. When connected to a Delta table with CDF enabled, the behaviour is largely there — valid_from, valid_to, and is_current are being populated correctly, which is very encouraging.
I do have a couple of observations that, if addressed, would make this feature truly enterprise‑ready:
Data types and consistency
The validity dates are currently stored as strings, and the format differs between the initial load and subsequent loads. Standardising these as proper date/timestamp types with a consistent format would improve reliability and downstream usability.
Surrogate key support
It would be extremely valuable to have an option to generate and manage a surrogate key directly in the target table. While this can be handled downstream today, surrogate keys are a core part of the SCD Type 2 pattern. Supporting this natively in the Copy Job workflow would significantly reduce additional complexity and custom logic.
- Not sure if I should add this here or as it's own idea. In addition to creating SCD2 from CDC, which is fantastic BUT is limited by the retention of the CDC log (you can never reload and maintain the same history). It would be fantastic if there was an option to create an SCD2 from existing audit tables. E.g. have ACTION_TIME, ACTION_TYPE (I, U, D) or equivalent columns. The output would be the same as the current SCD2 preview but would allow it to be used on tables with full, reloadable history. Provide PK / BK, specify the action time or equivalent column, specify the insert, update or delete column .... job done.
