Load Date Warehouse job fails with the following error message.
ERROR: ENCOUNTERED EXCEPTION WHILE ENABLING dwh_inv_project_fk1. insert or update on table "dwh_inv_project" violates foreign key constraint "dwh_inv_project_fk1"
The foreign key constraint dwh_inv_project_fk1 is a constraint on investment_key of the dwh_inv_project table, that refers to investment_key of dwh_inv_investment table. So, this error message comes up when there is a record in dwh_inv_project whose parent record is not present in dwh_inv_investment table.
It was noticed that a process was running in loop that was updating an Idea record continuously, thereby changing the last_updated_date on that record in the inv_investments table. Since the clarity_updated_date was greater than the load start date, the relevant record was being left out resulting in a mismatch.
The process had an incorrect logic in the UPDATE trigger that was corrected. This resulted in it not getting triggered unnecessarily and thus not updating the inv_investments row continuously.
Query to identify this issue would be as below. It should return at least one record.
where investment_key not in (