This is an issue with data that is updated in DWH_FIN_TRANSACTION_FACTS but not in DWH_FIN_TRANSACTION. So the foreign keys cannot connect to the parent key as they are missing.
Note: This may happen on other tables and dependent foreign keys
Example error on PostgreSQL:
ERROR 2021-01-26 06:06:51,422 [Dispatch Load Data Warehouse : [email protected] (tenant=clarity)] dwh.event ETL Job Failed. Log details below:
2021/01/26 06:06:51 - Fin Transactions? - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :
2021/01/26 06:06:51 - Fin Transactions? - Couldn't execute SQL: CALL DWH_ENABLE_CONSTRAINTS_SP(NULL,NULL);
2021/01/26 06:06:51 - Fin Transactions? -
2021/01/26 06:06:51 - Fin Transactions? - ERROR: ENCOUNTERED EXCEPTION WHILE ENABLING dwh_inv_asset_fk1. insert or update on table "dwh_inv_asset" violates foreign key constraint "dwh_inv_asset_fk1"
2021/01/26 06:06:51 - Fin Transactions? - Where: PL/pgSQL function dwh_enable_constraints_sp(character varying,character varying) line 43 at RAISE
Manually remove the values with query:
delete from DWH_FIN_TRANSACTION_FACTS where TRANSACTION_KEY not in
(select TRANSACTION_KEY from DWH_FIN_TRANSACTION)
Ensure nothing else is running on the application-level that updates the records last_updated_date as per the article below