Handling Duplicate Records in SCD Type 2 Dimension Nodes

Issue

When using SCD Type 2 dimension nodes, you may encounter errors when business keys aren't unique enough for a new record to be inserted, instead resulting in an error.

Solution

The error occurs when multiple rows in the source data match the same business key in the target table. This prevents the merge operation from determining which record should replace the existing one in the target.

• Re-evaluate and adjust business or merge keys:
  • Consider using composite keys. For example FIRST_NAME + LAST_NAME instead of just LAST_NAME.
  • Use the ID column as the business key.
• Set a session variable in the Pre-SQL:
  • alter session set ERROR_ON_NONDETERMINISTIC_MERGE = true;
  • This may result in data loss since it will pick records at random to include or exclude in the merges.