/*
This macro is to used as a post hook for WC_PERSON_D
The purpose is to update the DELETED flag in dw table is the source records has been updated to deleted.
To check whether a source table exist, see below
https://discourse.getdbt.com/t/writing-packages-when-a-source-table-may-or-may-not-exist/1487
For update statement in DBT, see below
https://stackoverflow.com/questions/67870208/how-to-use-update-statement-in-dbt
*/
{% macro update_person_delete_flg() %}
{%- set source_relation = adapter.get_relation(
database=source('edw_modeled', 'WC_PERSON_D').database,
schema=source('edw_modeled', 'WC_PERSON_D').schema,
identifier=source('edw_modeled', 'WC_PERSON_D').name) -%}
{% set table_exists = source_relation is not none %}
{% if table_exists %}
{% set query %}
update {{ source('edw_modeled', 'WC_PERSON_D') }} as WC_PERSON_D
set WC_PERSON_D.DELETED = 'Y'
from (
select
distinct S_CONTACT.ROW_ID as INTEGRATION_ID
from {{ source('flex_siebel', 'S_CONTACT') }} as S_CONTACT
where S_CONTACT._FIVETRAN_SYNCED >= {{ latest_refresh_date() }} AND S_CONTACT._FIVETRAN_DELETED = TRUE
) as EXP_PERSON_DELETED_FLG
where EXP_PERSON_DELETED_FLG.INTEGRATION_ID = WC_PERSON_D.INTEGRATION_ID
{% endset %}
{% do run_query(query) %}
{% endif %}
{% endmacro %}