I have three pkgs.
Organization (runs first)
Department (runs second)
Employee(runs third)
Source file for Organization will have OrganizationID + DepartmentID + EmployeeID + ErrorFlag
i.e. 1 1 1
now, DepartmentID and EmployeeID cannot repeat for another OrganizationID
i.e. 1st record = 1 1 1 and 5th record = 4 1 1 (this cannot happen).
If that happens, I want to mark that 4 1 1 record as True for ErrorFlag column.
I want to then apply same logic for Department where 1 emp cannot be in multiple dept.
Can I get some ideas on how to approach that in ETL? I want to mark the 2nd+ instance of duplicate foreign values to True.
Thank you