In this article
| Table of Contents | ||||||
|---|---|---|---|---|---|---|
|
This article emphasizes the different scenarios that are possible while performing an SCD2 operation and the behavior of the ELT SCD2 Snap, in each of these scenarios.
...
Customer ID (Natural Key) | Address (Cause Historization Values) | Start Date (Temporal Field 1) |
1 | Chicago | 1/1/20 |
1 | New York | 1/1/21 |
Output 1: Target table (After SCD2 operation)
Customer ID (Natural Key) | Address (New Cause Historization Entries) | Start Date | End Date | Current Row | Remarks / Key Take-aways |
1 | Chicago | 1/1/18 | 12/1/18 | false | No change. |
1 | New York | 1/1/19 | 1/1/20 | false | Not current row anymore. |
1 | Chicago | 1/1/20 | 1/1/21 | false | New entry but not current row. Start date for the newer row is the End date for this entry. |
1 | New York | 1/1/21 | MAX_DATE | true | Current row with default End Date. |
| Info |
|---|
The SCD2 operation uses a row-by-row approach. The final current row is arrived at, only after processing all rows for that natural key. |
...
Customer ID (Natural Key) | Address (Cause Historization Values) | Start Date (Temporal Field 1) |
1 | New York | 1/1/20 (or 1/1/17) |
1 | Boston | 1/1/21 |
Output 2: Target table (After SCD2 operation)
Customer ID (Natural Key) | Address (New Cause Historization Entries) | Start Date | End Date | Current Row | Remarks / Key Take-aways |
1 | Chicago | 1/1/18 | 12/1/18 | false | No change. |
1 | New York | 1/1/19 | 1/1/21 | false | Not current row anymore. Any end date for New York before 1/1/21 (start date for Boston) results in New York becoming a historical row. |
1 | Boston | 1/1/21 | MAX_DATE | true | Current row with default End Date. |
Scenario 3: Target table is Empty and Source Table Contains Duplicate Values for Cause-Historization field
...
Customer ID (Natural Key) | Address (Cause Historization Values) | Start Date (Temporal Field 1) |
1 | Chicago | 1/1/15 |
1 | Boston | 1/1/16 |
1 | Chicago | 1/1/18 |
1 | Chicago | 1/1/19 (or 1/1/14) |
1 | Boston | 1/1/20 |
Output 3: Target table (After SCD2 operation)
Customer ID (Natural Key) | Address (New Cause Historization Entries) | Start Date | End Date | Current Row | Remarks / Key Take-aways |
1 | Chicago | 1/1/15 | 1/1/16 | false | End date arrived based on the start date of the next entry (in ascending order). |
1 | Boston | 1/1/16 | 1/1/18 | false | End date arrived based on the start date of the next entry (in ascending order). |
1 | Chicago | 1/1/18 | 1/1/20 | false | Any start date for Chicago outside the range 1/1/18 - 1/1/20 will be ignored. |
1 | Boston | 1/1/20 | MAX_DATE | true | Current row with default End Date. |
| Info |
|---|
The SCD2 operation sorts the source data records in the ascending order of their Start Dates when there is no row numbering (sequence) specified in the source data. |
Scenario 4: Start Date in Source Row Earlier than Start Date of Current Row
Target table (Before)
Customer ID | Address | Start Date | End Date | Current Row |
1 | Chicago | 1/1/18 | 12/1/18 | false |
1 | New York | 1/1/19 | MAX_DATE | true |
Source/Update table (Incoming SCD2 Data)
Customer ID (Natural Key) | Address (Cause Historization Values) | Start Date (Temporal Field 1) |
1 | Orlando | 1/1/17 |
1 | Boston | 1/1/21 |
Output 4.1: Target table (After SCD2 operation) when Invalid rows are ignored
Customer ID (Natural Key) | Address (New Cause Historization Entries) | Start Date | End Date | Current Row | Remarks / Key Take-aways |
1 | Chicago | 1/1/18 | 12/1/18 | false | No change. |
1 | New York | 1/1/19 | 1/1/21 | false | Not current row anymore. Any end date for New York before 1/1/21 (start date for Boston) results in New York becoming a historical row. |
1 | Boston | 1/1/21 | MAX_DATE | true | Current row with default End Date. |
Output 4.2: Target table (After SCD2 operation) when Invalid rows are logged
Customer ID (Natural Key) | Address (New Cause Historization Entries) | Start Date | End Date | Current Row | Invalid | Remarks / Key Take-aways |
1 | Chicago | 1/1/18 | 12/1/18 | false | No change. | |
1 | New York | 1/1/19 | 1/1/21 | false | Not current row anymore. Any end date for New York before 1/1/21 (start date for Boston) results in New York becoming a historical row. | |
1 | Orlando | 1/1/17 | MIN_DATE | false | true | Invalid row logged with End Date defaulted to MIN_DATE. |
1 | Boston | 1/1/21 | MAX_DATE | true | Current row with default End Date. |