I'm just trying to double check myself to ensure I'm not missing anything. We have a driving key scenario on a link (Key A and Key B are the driving Key). Also, we are sourcing from SQL Server with Change Tracking enabled to identify our deltas. This means if we have a delete, it's possible the delete would have the same Key A and Key B but a different Key C than the original record that was initially loaded. I've diagrammed it in the attached images. Here is what each image represents:
- Initial record is staged and one row is inserted into a Link, Link Effectivity Satellite with no end date and a Status Tracking Satellite with an 'I' status
- New record appears in the source as does a delete of the old record but because of the driving key it has new Link Key value because Key C is null for the record representing the delete. 2 New records inserted in to link
- 3 New records inserted to the effectivity satellite - 1 to close the original record because a new record has appeared with that driving key (even though it's a delete), 1 record for the new row, and 1 record for the deleted row. Both of the latter 2 have NULL end dates
- 2 New records inserted to the status tracking satellite - 1 for the new record, 1 for the delete
- Last is a query to return the correct data - The link is joined to each satellite with a filter for the latest record based on the load date (we'll add a Point In Time table based on the performance) and we've applied a where clause to filter for End Date is null & Status <> 'D'
Did I miss anything? I feel this covered it and aligns with the DV2.0 standards but I haven't encountered this scenario many times where I'm instituting both these applications of satellites at once. Thanks for the feedback!