Data validation example - Dual Test - DBT
21/02/2024 18:06
'=============================== WC_ASSET_CLIENT_H ================================'
'==================================== DELETED ====================================='
'================================ DATE COMPARISON ================================='
select Sum(IFF(Dual_Test.INTEGRATION_ID is null,0,1)) as Total_SDual_Test_Records,
Sum(IFF(sac.asset_id is null,0,1)) as Total_S_asset_Con_Records,
Sum(IFF(sa.row_id is null,0,1)) as Total_s_asset_Records
from (
select
INTEGRATION_ID,*
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'DELETED'
) Dual_Test
Left Join ONEDB.raw_flex_crm.s_asset_con sac on sac.row_id = Dual_Test.INTEGRATION_ID
Left join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
select sac._fivetran_deleted, Dual_Test.*
from (
select
*
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'DELETED'
) Dual_Test
inner Join ONEDB.raw_flex_crm.s_asset_con sac on sac.row_id = Dual_Test.INTEGRATION_ID
Left join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
'---///////////////////////////////////////////////////////////////////////////////---'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
INTEGRATION_ID
from "AP_PROD"."ORACLEOACS_crm"."WC_ASSET_CLIENT_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'DELETED'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_crm.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.ROW_ID in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'DELETED'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'=============================== WC_ASSET_CLIENT_H ================================'
'============================ Missing in destination ============================='
'=============================== DATE COMPARISON ================================='
select * from DEV_ONEDB.wh_analytics.WC_OPTY_CLIENT_H where INTEGRATION_ID = '1-7BDN8F5'
select sac._fivetran_deleted,sac.row_id
from ONEDB.raw_flex_crm.s_asset_con sac
Inner join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
where sa.type_cd = 'Fin Account'
and sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'Missing in destination'
)
select Sum(IFF(sac.asset_id is null,0,1)) as Total_S_asset_con_Records,
Sum(IFF(sa.row_id is null,0,1)) as Total_s_asset_Records
from ONEDB.raw_flex_crm.s_asset_con sac
Left join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
where sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'Missing in destination'
)
'---///////////////////////////////////////////////////////////////////////////////---'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_crm"."WC_ASSET_CLIENT_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'Missing in destination'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_crm.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'Missing in destination'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'=============================== WC_ASSET_CLIENT_H ================================'
'================================== PRIMARY_FLG ==================================='
'================================ DATE COMPARISON ================================='
select deleted, * from "AP_PROD"."ORACLEOACS_crm"."WC_ASSET_CLIENT_H"
where INTEGRATION_ID IN (select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG') AND deleted = 'Y'
select sac._fivetran_deleted, sac.CONTACT_ID, sa.PR_CON_ID, Dual_Test.*
from (
select
*
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
) Dual_Test
inner Join ONEDB.raw_flex_crm.s_asset_con sac on sac.row_id = Dual_Test.INTEGRATION_ID
Left join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
where sac.CONTACT_ID<>sa.PR_CON_ID
select * from ONEDB.raw_flex_crm.s_asset_con limit 10
Where sac._fivetran_deleted = True
select Sum(IFF(sac.asset_id is null,0,1)) as Total_S_asset_con_Records,
Sum(IFF(sa.row_id is null,0,1)) as Total_s_asset_Records
from ONEDB.raw_flex_crm.s_asset_con sac
Inner join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
where sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
)
'---///////////////////////////////////////////////////////////////////////////////---'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
INTEGRATION_ID
from "AP_PROD"."ORACLEOACS_crm"."WC_ASSET_CLIENT_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
)
),
Snowflake
as
(
select
sac.last_upd as sac_last_upd,
sa.last_upd as sa_last_upd,
sac.row_id
from ONEDB.raw_flex_crm.s_asset_con sac
Inner join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
where
sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
)
)
Select
Snowflake.row_id,
Snowflake.sac_last_upd as Snowflake_sac_Last_Update_Date,
Snowflake.sa_last_upd as Snowflake_sa_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
where SNOWFLAKE_SA_LAST_UPDATE_DATE
'----------------------------------------------------------------------------------------------------------'
select
sac.row_id,
sac._fivetran_deleted,
sac.last_upd,
sa._fivetran_deleted,
sa.last_upd,
*
from ONEDB.raw_flex_crm.s_asset_con sac
Inner join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
where
sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
)
and (sac._fivetran_deleted = True or sa._fivetran_deleted = True )
'=============================== WC_OPTY_CLIENT_H ================================'
'============================== Missing in source ================================'
'=============================== DATE COMPARISON ================================='
select _fivetran_deleted,row_id from ONEDB.raw_flex_crm.S_EVT_ACT
where row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_3f95d1caed3c2922f9c9fd01badd8dd0
where MISMATCH_REASON = 'Missing in source'
)
and _fivetran_deleted = True
'---///////////////////////////////////////////////////////////////////////////////---'
select _fivetran_deleted from ONEDB.raw_flex_crm.S_ACT_CONTACT
where row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_3f95d1caed3c2922f9c9fd01badd8dd0
where MISMATCH_REASON = 'Missing in source'
)
and _fivetran_deleted = TRUE
'----------------------------------------------------------------------------------------------------------'
select * from DEV_ONEDB.wh_analytics.WC_OPTY_CLIENT_H where DELETED = 'Y'
select * from "AP_PROD"."ORACLEOACS_crm"."WC_OPTY_CLIENT_H" where DELETED = 'Y'
select * from DEV_ONEDB.wh_analytics.WC_OPTY_CLIENT_H
where INTEGRATION_ID in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_3f95d1caed3c2922f9c9fd01badd8dd0
where MISMATCH_REASON = 'Missing in source'
)
'----------------------------------------------------------------------------------------------------------'
select * from "AP_PROD"."ORACLEOACS_crm"."WC_OPTY_CLIENT_H"
where INTEGRATION_ID in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in source'
)
'----------------------------------------------------------------------------------------------------------'
select
Sum(Iff(Dual_Test.INTEGRATION_ID is null,0,1)) as Total_Dual_Test_Records,
Sum(Iff(S_ACT_CONTACT.row_id is null,0,1)) as Total_S_ACT_CONTACT_Records,
Sum(Iff(S_EVT_ACT.row_id is null,0,1)) as Total_S_EVT_ACT_Records
from
(
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in source'
) Dual_Test
Inner join ONEDB.raw_flex_crm.S_ACT_CONTACT on Dual_Test.INTEGRATION_ID=S_ACT_CONTACT.row_id
Left join ONEDB.raw_flex_crm.S_EVT_ACT on S_ACT_CONTACT.activity_Id = S_EVT_ACT.row_id
'---///////////////////////////////////////////////////////////////////////////////---'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_crm"."WC_OPTY_CLIENT_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in source'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_crm.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in source'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'=============================== WC_ASSET_PERSON_H ================================'
'==================================== DELETED ====================================='
'================================ DATE COMPARISON ================================='
select Sum(IFF(Dual_Test.INTEGRATION_ID is null,0,1)) as Total_SDual_Test_Records,
Sum(IFF(sac.asset_id is null,0,1)) as Total_S_asset_Con_Records,
Sum(IFF(sa.row_id is null,0,1)) as Total_s_asset_Records
from (
select
INTEGRATION_ID,*
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'DELETED'
) Dual_Test
Left Join ONEDB.raw_flex_siebel.s_asset_con sac on sac.row_id = Dual_Test.INTEGRATION_ID
Left join ONEDB.raw_flex_siebel.s_asset sa on sa.row_id = sac.asset_id
select sac._fivetran_deleted, Dual_Test.*
from (
select
*
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'DELETED'
) Dual_Test
inner Join ONEDB.raw_flex_siebel.s_asset_con sac on sac.row_id = Dual_Test.INTEGRATION_ID
Left join ONEDB.raw_flex_siebel.s_asset sa on sa.row_id = sac.asset_id
'---///////////////////////////////////////////////////////////////////////////////---'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
INTEGRATION_ID
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_ASSET_PERSON_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'DELETED'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_siebel.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.ROW_ID in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'DELETED'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'=============================== WC_ASSET_PERSON_H ================================'
'============================ Missing in destination ============================='
'=============================== DATE COMPARISON ================================='
select * from DEV_ONEDB.wh_analytics.WC_OPTY_PERSON_H where INTEGRATION_ID = '1-7BDN8F5'
select sac._fivetran_deleted,sac.row_id
from ONEDB.raw_flex_siebel.s_asset_con sac
Inner join ONEDB.raw_flex_siebel.s_asset sa on sa.row_id = sac.asset_id
where sa.type_cd = 'Fin Account'
and sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'Missing in destination'
)
select Sum(IFF(sac.asset_id is null,0,1)) as Total_S_asset_con_Records,
Sum(IFF(sa.row_id is null,0,1)) as Total_s_asset_Records
from ONEDB.raw_flex_siebel.s_asset_con sac
Left join ONEDB.raw_flex_siebel.s_asset sa on sa.row_id = sac.asset_id
where sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'Missing in destination'
)
'---///////////////////////////////////////////////////////////////////////////////---'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_ASSET_PERSON_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'Missing in destination'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_siebel.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'Missing in destination'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'=============================== WC_ASSET_PERSON_H ================================'
'================================== PRIMARY_FLG ==================================='
'================================ DATE COMPARISON ================================='
select deleted, * from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_ASSET_PERSON_H"
where INTEGRATION_ID IN (select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG') AND deleted = 'Y'
select sac._fivetran_deleted, sac.CONTACT_ID, sa.PR_CON_ID, Dual_Test.*
from (
select
*
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
) Dual_Test
inner Join ONEDB.raw_flex_siebel.s_asset_con sac on sac.row_id = Dual_Test.INTEGRATION_ID
Left join ONEDB.raw_flex_siebel.s_asset sa on sa.row_id = sac.asset_id
where sac.CONTACT_ID<>sa.PR_CON_ID
select * from ONEDB.raw_flex_siebel.s_asset_con limit 10
Where sac._fivetran_deleted = True
select Sum(IFF(sac.asset_id is null,0,1)) as Total_S_asset_con_Records,
Sum(IFF(sa.row_id is null,0,1)) as Total_s_asset_Records
from ONEDB.raw_flex_siebel.s_asset_con sac
Inner join ONEDB.raw_flex_siebel.s_asset sa on sa.row_id = sac.asset_id
where sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
)
'---///////////////////////////////////////////////////////////////////////////////---'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
INTEGRATION_ID
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_ASSET_PERSON_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
)
),
Snowflake
as
(
select
sac.last_upd as sac_last_upd,
sa.last_upd as sa_last_upd,
sac.row_id
from ONEDB.raw_flex_siebel.s_asset_con sac
Inner join ONEDB.raw_flex_siebel.s_asset sa on sa.row_id = sac.asset_id
where
sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
)
)
Select
Snowflake.row_id,
Snowflake.sac_last_upd as Snowflake_sac_Last_Update_Date,
Snowflake.sa_last_upd as Snowflake_sa_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
where SNOWFLAKE_SA_LAST_UPDATE_DATE
'----------------------------------------------------------------------------------------------------------'
select
sac.row_id,
sac._fivetran_deleted,
sac.last_upd,
sa._fivetran_deleted,
sa.last_upd,
*
from ONEDB.raw_flex_siebel.s_asset_con sac
Inner join ONEDB.raw_flex_siebel.s_asset sa on sa.row_id = sac.asset_id
where
sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
)
and (sac._fivetran_deleted = True or sa._fivetran_deleted = True )
'=============================== WC_OPTY_PERSON_H ================================'
'============================== Missing in source ================================'
'=============================== DATE COMPARISON ================================='
select _fivetran_deleted,row_id from ONEDB.raw_flex_siebel.S_EVT_ACT
where row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_3f95d1caed3c2922f9c9fd01badd8dd0
where MISMATCH_REASON = 'Missing in source'
)
and _fivetran_deleted = True
'---///////////////////////////////////////////////////////////////////////////////---'
select _fivetran_deleted from ONEDB.raw_flex_siebel.S_ACT_CONTACT
where row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_3f95d1caed3c2922f9c9fd01badd8dd0
where MISMATCH_REASON = 'Missing in source'
)
and _fivetran_deleted = TRUE
'----------------------------------------------------------------------------------------------------------'
select * from DEV_ONEDB.wh_analytics.WC_OPTY_PERSON_H where DELETED = 'Y'
select * from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_PERSON_H" where DELETED = 'Y'
select * from DEV_ONEDB.wh_analytics.WC_OPTY_PERSON_H
where INTEGRATION_ID in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_3f95d1caed3c2922f9c9fd01badd8dd0
where MISMATCH_REASON = 'Missing in source'
)
'----------------------------------------------------------------------------------------------------------'
select * from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_PERSON_H"
where INTEGRATION_ID in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in source'
)
'----------------------------------------------------------------------------------------------------------'
select
Sum(Iff(Dual_Test.INTEGRATION_ID is null,0,1)) as Total_Dual_Test_Records,
Sum(Iff(S_ACT_CONTACT.row_id is null,0,1)) as Total_S_ACT_CONTACT_Records,
Sum(Iff(S_EVT_ACT.row_id is null,0,1)) as Total_S_EVT_ACT_Records
from
(
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in source'
) Dual_Test
Inner join ONEDB.raw_flex_siebel.S_ACT_CONTACT on Dual_Test.INTEGRATION_ID=S_ACT_CONTACT.row_id
Left join ONEDB.raw_flex_siebel.S_EVT_ACT on S_ACT_CONTACT.activity_Id = S_EVT_ACT.row_id
'---///////////////////////////////////////////////////////////////////////////////---'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_PERSON_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in source'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_siebel.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in source'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'=============================== WC_OPTY_PERSON_H ================================'
'============================ Missing in destination ============================='
'=============================== DATE COMPARISON ================================='
select _fivetran_deleted from ONEDB.raw_flex_siebel.S_ACT_CONTACT where row_id = '1-3RLT67E'
select * from DEV_ONEDB.wh_analytics.WC_OPTY_PERSON_H where INTEGRATION_ID = '1-7BDN8F5'
select _fivetran_deleted,row_id from ONEDB.raw_flex_siebel.S_ACT_CONTACT
where row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in destination'
)
and _fivetran_deleted = False
'---///////////////////////////////////////////////////////////////////////////////---'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_PERSON_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in destination'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_siebel.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in destination'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'=============================== WC_OPTY_PERSON_H ================================'
'============================== F2F_ADEQUATE_STEPS ==============================='
'=============================== DATE COMPARISON ================================='
select count(*) from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_PERSON_H"
4243918
4198408
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_PERSON_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in destination'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_siebel.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in destination'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
--where Snowflake.ACTIVE_CONTACT_last_upd >Oracle.extract_date
'=============================== WC_OPTY_PERSON_H ================================'
'============================== F2F_ADEQUATE_STEPS ==============================='
'=============================== DATE COMPARISON ================================='
select count(*) from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_PERSON_H"
4243918
4198408
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_PERSON_H"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'F2F_ADEQUATE_STEPS'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_siebel.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'F2F_ADEQUATE_STEPS'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'-------------------------------------------------------------------------------------------'
select
*
from ONEDB.raw_flex_siebel.S_ACT_CONTACT
inner join ONEDB.raw_flex_siebel.S_EVT_ACT on S_ACT_CONTACT.activity_Id = S_EVT_ACT.row_id
where
S_ACT_CONTACT.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'F2F_ADEQUATE_STEPS'
)
'---------------------------------------------------------------------------------------------------------'
select
S_ACT_CONTACT.*
from ONEDB.raw_flex_siebel.S_ACT_CONTACT
inner join ONEDB.raw_flex_siebel.S_EVT_ACT on S_ACT_CONTACT.activity_Id = S_EVT_ACT.row_id
where
S_ACT_CONTACT.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'DELETED'
)
and S_ACT_CONTACT._fivetran_deleted = true
'=============================== WC_OPTY_PERSON_H ================================'
'============================= F2F_LENDER_APPROVAL ==============================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_PERSON_H"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'F2F_LENDER_APPROVAL'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_siebel.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'F2F_LENDER_APPROVAL'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'-------------------------------------------------------------------------------------------'
select
*
from ONEDB.raw_flex_siebel.S_ACT_CONTACT
inner join ONEDB.raw_flex_siebel.S_EVT_ACT on S_ACT_CONTACT.activity_Id = S_EVT_ACT.row_id
where
S_ACT_CONTACT.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'F2F_LENDER_APPROVAL'
)
'=============================== WC_OPTY_PERSON_H ================================'
'=================================== DELETED ====================================='
'=============================== DATE COMPARISON ================================='
select Sum(IFF(Dual_Test.INTEGRATION_ID is null,0,1)) as Total_SDual_Test_Records,
Sum(IFF(sac.asset_id is null,0,1)) as Total_S_asset_Con_Records,
Sum(IFF(sa.row_id is null,0,1)) as Total_s_asset_Records
from (
select
*
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_3f95d1caed3c2922f9c9fd01badd8dd0
where MISMATCH_REASON = 'DELETED'
) Dual_Test
Left Join ONEDB.raw_flex_siebel.s_asset_con sac on sac.row_id = Dual_Test.INTEGRATION_ID
Left join ONEDB.raw_flex_siebel.s_asset sa on sa.row_id = sac.asset_id
'---------------------------------------------------------------------------------------------------------'
select sac._fivetran_deleted, Dual_Test.*
from (
select
*
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'DELETED'
) Dual_Test
inner Join ONEDB.raw_flex_siebel.s_asset_con sac on sac.row_id = Dual_Test.INTEGRATION_ID
Left join ONEDB.raw_flex_siebel.s_asset sa on sa.row_id = sac.asset_id
'---------------------------------------------------------------------------------------------------------'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_PERSON_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'DELETED'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_siebel.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'DELETED'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
order by 2
'---------------------------------------------------------------------------------------------------------'
select
Sum(Iff(Dual_Test.INTEGRATION_ID is null,0,1)) as Total_Dual_Test_Records,
Sum(Iff(S_ACT_CONTACT.row_id is null,0,1)) as Total_S_ACT_CONTACT_Records,
Sum(Iff(S_EVT_ACT.row_id is null,0,1)) as Total_S_EVT_ACT_Records
from
(
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'DELETED'
) Dual_Test
Inner join ONEDB.raw_flex_siebel.S_ACT_CONTACT on Dual_Test.INTEGRATION_ID=S_ACT_CONTACT.row_id
Left join ONEDB.raw_flex_siebel.S_EVT_ACT on S_ACT_CONTACT.activity_Id = S_EVT_ACT.row_id
--and S_ACT_CONTACT._fivetran_deleted = true
'---------------------------------------------------------------------------------------------------------'
'=============================== WC_OPTY_PERSON_H ================================'
'================================= PRIMARY_FLG ==================================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_PERSON_H"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'PRIMARY_FLG'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
o.last_upd as S_OPTY_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_siebel.S_OPTY o
left outer join ONEDB.raw_flex_siebel.S_EVT_ACT ea on o.PR_ACT_ID = ea.ROW_ID and ea.opty_id = o.row_id and o.pr_act_id = ea.row_id
inner join ONEDB.raw_flex_siebel.S_ACT_CONTACT ACTIVE_CONTACT on ACTIVE_CONTACT.activity_Id = ea.row_id
where
ACTIVE_CONTACT.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'PRIMARY_FLG'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Snowflake.S_OPTY_last_upd as Snowflake_S_OPTY_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id a
'----------------------------------------------------------------------------------------------------------'
select
S_ACT_CONTACT.row_id,
S_ACT_CONTACT._fivetran_deleted,
S_ACT_CONTACT.last_upd,
S_EVT_ACT._fivetran_deleted,
S_EVT_ACT.last_upd,
*
from ONEDB.raw_flex_siebel.S_ACT_CONTACT
inner join ONEDB.raw_flex_siebel.S_EVT_ACT on S_ACT_CONTACT.activity_Id = S_EVT_ACT.row_id
where
S_ACT_CONTACT.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'PRIMARY_FLG'
)
'----------------------------------------------------------------------------------------------------------'
select
o._fivetran_deleted,
o.last_upd,
o.*
from ONEDB.raw_flex_siebel.S_OPTY o
left outer join ONEDB.raw_flex_siebel.S_EVT_ACT ea on o.PR_ACT_ID = ea.ROW_ID and ea.opty_id = o.row_id and o.pr_act_id = ea.row_id
inner join ONEDB.raw_flex_siebel.S_ACT_CONTACT on S_ACT_CONTACT.activity_Id = ea.row_id
where S_ACT_CONTACT.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'PRIMARY_FLG'
)
and o._fivetran_deleted = true
'----------------------------------------------------------------------------------------------------------'
select
integration_id,
*
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'PRIMARY_FLG'
)
select
*
from DEV_ONEDB.WH_ANALYTICS.WC_PERSON_D
where WC_PERSON_D.row_wid in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'PRIMARY_FLG'
)
/*
select
--pr_con_id
*
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_D"
where pr_con_id = '1-4WJW636'
*/
select
integration_id,
*
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_D"
where row_wid in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'PRIMARY_FLG'
)
select
*
from DEV_ONEDB.WH_ANALYTICS.WC_PERSON_D
where WC_PERSON_D.row_wid in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'PRIMARY_FLG'
)
select
*
from DEV_ONEDB.WH_ANALYTICS.WC_OPTY_D
'=============================== WC_OPTY_PERSON_H ================================'
'================================== ANNUAL_EXP ==================================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_PERSON_H"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'ANNUAL_EXP'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
S_FN_INCM_EXP_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_siebel.S_ACT_CONTACT ACTIVE_CONTACT
-- left outer join DEV_ONEDB.CURATED_FLEX.Loan_Account_Contact sc on sc.row_id = PERSON.row_id
left join (
select
activity_id,
PR_CON_ID,
MAX(last_upd) as S_FN_INCM_EXP_last_upd,
sum(
case when FREQ_CD = 'Annually' then incm_exp_amt
when FREQ_CD = 'Monthly' then incm_exp_amt*12
when FREQ_CD = '4weekly' then incm_exp_amt/4*52
when FREQ_CD = 'Fortnightly' then incm_exp_amt*26
when FREQ_CD = 'Quarterly' then incm_exp_amt*4
else incm_exp_amt
end
) as ANNUAL_INCM
from
ONEDB.raw_flex_siebel.S_FN_INCM_EXP
group by
activity_id,
PR_CON_ID
) PERSON_INCOME on PERSON_INCOME.activity_id = ACTIVE_CONTACT.ACTIVITY_ID and PERSON_INCOME.PR_CON_ID = ACTIVE_CONTACT.con_id
where
ACTIVE_CONTACT.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'ANNUAL_EXP'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Snowflake.S_FN_INCM_EXP_last_upd AS Snowflake_S_FN_INCM_EXP_last_upd,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'=============================== WC_OPTY_PERSON_H ================================'
'================================= ANNUAL_INCM ==================================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_OPTY_PERSON_H"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'ANNUAL_INCM'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
S_FN_INCM_EXP_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_siebel.S_ACT_CONTACT ACTIVE_CONTACT
-- left outer join DEV_ONEDB.CURATED_FLEX.Loan_Account_Contact sc on sc.row_id = PERSON.row_id
left join (
select
activity_id,
PR_CON_ID,
MAX(last_upd) as S_FN_INCM_EXP_last_upd,
sum(
case when FREQ_CD = 'Annually' then incm_exp_amt
when FREQ_CD = 'Monthly' then incm_exp_amt*12
when FREQ_CD = '4weekly' then incm_exp_amt/4*52
when FREQ_CD = 'Fortnightly' then incm_exp_amt*26
when FREQ_CD = 'Quarterly' then incm_exp_amt*4
else incm_exp_amt
end
) as ANNUAL_INCM
from
ONEDB.raw_flex_siebel.S_FN_INCM_EXP
group by
activity_id,
PR_CON_ID
) PERSON_INCOME on PERSON_INCOME.activity_id = ACTIVE_CONTACT.ACTIVITY_ID and PERSON_INCOME.PR_CON_ID = ACTIVE_CONTACT.con_id
where
ACTIVE_CONTACT.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_dba169ad6047a661ed3beecd56551fd1
where MISMATCH_REASON = 'ANNUAL_INCM'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Snowflake.S_FN_INCM_EXP_last_upd AS Snowflake_S_FN_INCM_EXP_last_upd,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'============================================================================================='
select * from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_ASSET_PERSON_H" where integration_id = '1-12241J5';
select * from "ONEDB"."WH_ANALYTICS"."WC_ASSET_PERSON_H" where integration_id = '1-15JGMUP';
'==================================== PERSON ====================================='
'=============================== MKTG_VALID_EMAIL ================================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'MKTG_VALID_EMAIL'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'MKTG_VALID_EMAIL'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'=============================== MARITAL_STAT_CD ================================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'MARITAL_STAT_CD'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'MARITAL_STAT_CD'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'================================= EMAIL_STATUS =================================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'EMAIL_STATUS'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'EMAIL_STATUS'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'============================ HOUSEHOLD_CONTACT_FLG =============================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'HOUSEHOLD_CONTACT_FLG'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'HOUSEHOLD_CONTACT_FLG'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'============================== SUPPRESS_MAIL_FLG ================================'
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'SUPPRESS_MAIL_FLG'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'SUPPRESS_MAIL_FLG'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'=================================== BIRTH_DT ===================================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'BIRTH_DT'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'BIRTH_DT'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'========================== BROKER_DO_NOT_CONTACT_FLG ============================'
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'DELETED'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'DELETED'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'========================== BROKER_DO_NOT_CONTACT_FLG ============================'
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'BROKER_DO_NOT_CONTACT_FLG'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'BROKER_DO_NOT_CONTACT_FLG'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'================================ BEST_CALL_TIME ================================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'BEST_CALL_TIME'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'BEST_CALL_TIME'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'=============================== UNSUB_RATE_ALERT ================================'
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'UNSUB_RATE_ALERT'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
CAMP_CON.last_upd as CAMP_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left join (
select
CAMP_CON.CONTACT_ID,
max(CAMP_CON.LAST_UPD) as LAST_UPD
from
DEV_ONEDB.CURATED_FLEX.Campaign_Contact CAMP_CON
inner join ONEDB.raw_flex_siebel.S_CONTACT PERSON on CAMP_CON.CONTACT_ID = PERSON.ROW_ID
where
CAMP_CON.CAMP_NAME like 'SMART%' OR CAMP_CON.CAMP_NAME like 'Car Loans%'
group by
CAMP_CON.CONTACT_ID
)
CAMP_CON on CAMP_CON.CONTACT_ID = PERSON.ROW_ID
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'UNSUB_RATE_ALERT'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_CONTACT_Last_Update_Date,
Snowflake.CAMP_CON_last_upd as Snowflake_PERSON_CAMP_CON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'==================================== SEX_MF ===================================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'SEX_MF'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'SEX_MF'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'================================ NUM_DEPENDENTS ================================'
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'NUM_DEPENDENTS'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'NUM_DEPENDENTS'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'================================= UNSUB_SMART ==================================='
'=============================== DATE COMPARISON ================================='
UNSUB_RATE_ALERT
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'UNSUB_SMART'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
CAMP_CON.last_upd as CAMP_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left join (
select
CAMP_CON.CONTACT_ID,
max(CAMP_CON.LAST_UPD) as LAST_UPD
from
DEV_ONEDB.CURATED_FLEX.Campaign_Contact CAMP_CON
inner join ONEDB.raw_flex_siebel.S_CONTACT PERSON on CAMP_CON.CONTACT_ID = PERSON.ROW_ID
where
CAMP_CON.CAMP_NAME like 'SMART%' OR CAMP_CON.CAMP_NAME like 'Car Loans%'
group by
CAMP_CON.CONTACT_ID
)
CAMP_CON on CAMP_CON.CONTACT_ID = PERSON.ROW_ID
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'UNSUB_SMART'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_CONTACT_Last_Update_Date,
Snowflake.CAMP_CON_last_upd as Snowflake_PERSON_CAMP_CON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'================================= ST_ADDRESS ===================================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'ST_ADDRESS'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
ADDR_PER._fivetran_synced as ADDR_PER_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
left outer join ONEDB.raw_flex_siebel.s_addr_per ADDR_PER on ADDR_PER.row_id = PERSON.PR_PER_ADDR_ID
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'ST_ADDRESS'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Snowflake.ADDR_PER_last_upd,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'================================== SPOUSE_ID ===================================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'SPOUSE_ID'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
SPOUSE_REL._fivetran_synced as SPOUSE_REL_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
left outer join (
select contact_id, spouse_id, rel_type_cd, _fivetran_synced, rank() over (partition by contact_id order by created Desc, row_id) as rnk
from (
select pr.rel_party_id as contact_id, pr.party_id as spouse_id, pr.created, pr.row_id, rel_type_cd, _fivetran_synced, _fivetran_deleted
from ONEDB.raw_flex_siebel.s_party_rel pr
where pr.rel_type_cd in ('Spouse', 'Partner / De Facto')
and pr.rel_party_id = '1-M6AGVB'
) qualify rnk = 1
) SPOUSE_REL on SPOUSE_REL.contact_id = PERSON.row_id
-- where PERSON.row_id = '1-524UQ2'
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'SPOUSE_ID'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Snowflake.SPOUSE_REL_last_upd,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
where row_id = '1-524UQ2'
'==================================== PERSON ====================================='
'=============================== ACTIVE_LOAN_FLG ================================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'ACTIVE_LOAN_FLG'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
LOAN_REL.last_upd as LOAN_REL_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
left join (
select
distinct active_person.contact_id, ACTIVE_PERSON.last_upd
from (select * from ap_prod.oracleoacs_siebel.WC_ASSET_D where type_cd = 'Fin Account') wc_loan_d
inner join DEV_ONEDB.CURATED_FLEX.Loan_Account_Contact ACTIVE_PERSON on wc_loan_d.integration_id = active_person.asset_id
where wc_loan_d.ONEDB_discharge_dt is null
) LOAN_REL on LOAN_REL.CONTACT_ID = PERSON.ROW_ID
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'ACTIVE_LOAN_FLG'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Snowflake.LOAN_REL_last_upd,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'================================ EMAIL_STATUS =================================='
'=============================== DATE COMPARISON ================================='
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'EMAIL_STATUS'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
sc.last_upd as sc_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'EMAIL_STATUS'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'=============================== SMART_RCNT_MKT_DT ==============================='
'================================ DATE COMPARISON ================================'
;
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'SMART_RCNT_MKT_DT'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
CAMP_CON.last_upd as CAMP_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left join (
select
CAMP_CON.CONTACT_ID,
max(CAMP_CON.LAST_UPD) as LAST_UPD
from
DEV_ONEDB.CURATED_FLEX.Campaign_Contact CAMP_CON
inner join ONEDB.raw_flex_siebel.S_CONTACT PERSON on CAMP_CON.CONTACT_ID = PERSON.ROW_ID
where
CAMP_CON.CAMP_NAME like 'SMART%' OR CAMP_CON.CAMP_NAME like 'Car Loans%'
group by
CAMP_CON.CONTACT_ID
)
CAMP_CON on CAMP_CON.CONTACT_ID = PERSON.ROW_ID
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'SMART_RCNT_MKT_DT'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_CONTACT_Last_Update_Date,
Snowflake.CAMP_CON_last_upd as Snowflake_PERSON_CAMP_CON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'==================================== PERSON ====================================='
'=============================== TAM_CONTACT_STATUS ==============================='
'================================ DATE COMPARISON ================================'
;
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'TAM_CONTACT_STATUS'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
CAMP_CON.last_upd as CAMP_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left join (
select
con_id as CON_ID,
tam_contact_dt as TAM_CONTACT_DT,
tam_contact_status as TAM_CONTACT_STATUS,
last_upd
from (
select
s_act_contact.con_id as con_id,
s_evt_act.todo_plan_start_dt as tam_contact_dt,
decode(s_evt_act.done_flg,'Y','Done','') as tam_contact_status,
s_act_contact.last_upd
from ONEDB.raw_flex_siebel.s_act_contact
inner join ONEDB.raw_flex_siebel.s_evt_act on s_act_contact.activity_id = s_evt_act.row_id
inner join ONEDB.raw_flex_siebel.s_src on s_evt_act.src_id = s_src.row_id
where lower(s_src.name) like '%red alert%'
and con_id = '1-8XR70A'
union
select
s_act_contact.con_id as con_id,
s_evt_act.todo_plan_start_dt as tam_contact_dt,
decode(s_evt_act.done_flg,'Y','Done','') as tam_contact_status,
s_act_contact.last_upd
from ONEDB.raw_flex_siebel.s_act_contact
inner join ONEDB.raw_flex_siebel.s_evt_act on s_act_contact.activity_id = s_evt_act.row_id
where s_evt_act.x_ONEDB_tam_flg = 'Y'
and con_id = '1-8XR70A'
) sub
-- where con_id = '1-8XR70A'
)
CAMP_CON on CAMP_CON.CON_ID = PERSON.ROW_ID
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'TAM_CONTACT_STATUS'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_CONTACT_Last_Update_Date,
Snowflake.CAMP_CON_last_upd as Snowflake_PERSON_CAMP_CON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
-- where Snowflake.row_id = '1-BMHEG6'
'==================================== PERSON ===================================='
'=============================== RATE_ALERT_ST_DT ==============================='
'=============================== DATE COMPARISON ================================'
;
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'RATE_ALERT_ST_DT'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
CAMP_CON.last_upd as CAMP_CON_last_upd,
PERSON.row_id
from ONEDB.raw_flex_siebel.S_CONTACT PERSON
left join (
select
CAMP_DATES.CON_PER_ID,
NVL(TO_TIMESTAMP(MIN(MIN_DATE)), null ) as RATE_ALERT_ST_DT
from
(
select
cc.CON_PER_ID,
cc.OUTCOME_CD as OUTCOME_CD,
DECODE(IFF(CONTAINS(s.NAME, ' - '),1,0), 0, s.NAME, substr(s.NAME, 1, REGEXP_INSTR(s.NAME, ' - ') - 1)) as CAMP_NAME,
MIN(cc.CREATED) as MIN_DATE,
MAX(cc.CREATED) as MAX_DATE
from
ONEDB.raw_flex_siebel.S_CAMP_CON cc
left outer join ONEDB.raw_flex_siebel.S_SRC s on s.row_Id = cc.src_id
inner join ONEDB.raw_flex_siebel.S_CONTACT CONTACT on cc.CON_PER_ID = CONTACT.ROW_ID
where
(s.NAME like 'SMART%' OR s.NAME like 'Car Loans%')
and CON_PER_ID = '1-J8DTK'
and s._fivetran_deleted = false
and cc._fivetran_deleted = false
--and CONTAINS(UPPER(s.NAME), 'RATE ALERT') > 0 AND cc.OUTCOME_CD <> 'No Contact'
--and nvl(cc.OUTCOME_CD,'') <> 'No Contact'
group by
cc.CON_PER_ID, cc.OUTCOME_CD, CAMP_NAME
order by MIN_DATE
) CAMP_DATES
where UPPER(CAMP_NAME) like '%RATE ALERT%' AND (outcome_cd not in ('No Contact', '') and outcome_cd is not null)
and CON_PER_ID = '1-J8DTK'
group by
CAMP_DATES.CON_PER_ID
select
CON_PER_ID,
IFF(CONTAINS(UPPER(CAMP_NAME), 'RATE ALERT') > 0 AND OUTCOME_CD <> 'No Contact', TO_TIMESTAMP( MIN(MIN_DATE)), null ) as RATE_ALERT_ST_DT
from (
select
cc.CON_PER_ID,
nvl(cc.OUTCOME_CD,'') as OUTCOME_CD,
DECODE(IFF(CONTAINS(s.NAME, ' - '),1,0), 0, s.NAME, substr(s.NAME, 1, REGEXP_INSTR(s.NAME, ' - ') - 1)) as CAMP_NAME,
MIN(cc.CREATED) as MIN_DATE,
MAX(cc.CREATED) as MAX_DATE
from
ONEDB.raw_flex_siebel.S_CAMP_CON cc
left outer join ONEDB.raw_flex_siebel.S_SRC s on s.row_Id = cc.src_id
inner join ONEDB.raw_flex_siebel.S_CONTACT CONTACT on cc.CON_PER_ID = CONTACT.ROW_ID
where
(s.NAME like 'SMART%' OR s.NAME like 'Car Loans%')
and CON_PER_ID = '1-322D8GN'
and s._fivetran_deleted = false
and cc._fivetran_deleted = false
and CONTAINS(UPPER(s.NAME), 'RATE ALERT') > 0 AND cc.OUTCOME_CD <> 'No Contact'
group by
cc.CON_PER_ID, nvl(cc.OUTCOME_CD,''), DECODE(IFF(CONTAINS(s.NAME, ' - '),1,0), 0, s.NAME, substr(s.NAME, 1, REGEXP_INSTR(s.NAME, ' - ') - 1))
order by MIN_DATE
) CAMP_DATES
where CON_PER_ID = '1-322D8GN'
group by
CON_PER_ID, OUTCOME_CD, CAMP_NAME
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'RATE_ALERT_ST_DT'
)
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_CONTACT_Last_Update_Date,
Snowflake.CAMP_CON_last_upd as Snowflake_PERSON_CAMP_CON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
-- where Snowflake.row_id = '1-BMHEG6'
'==================================== PERSON ====================================='
'=============================== BRANDING_ORG_ID ================================'
'================================ DATE COMPARISON ================================'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_PERSON_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'BRANDING_ORG_ID'
)
),
Snowflake
as
(
select
PERSON.last_upd as PERSON_last_upd,
scx.last_upd as scx_CON_last_upd,
PERSON.row_id,
scx.attrib_46,
PERSON.x_ONEDB_pr_employed_by_org_id,
spn.ou_id
from ONEDB.raw_flex_siebel.s_emp_per PERSON
left join ONEDB.raw_flex_siebel.s_contact_x scx on scx.par_row_id = PERSON.row_id
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = PERSON.row_id
left join ONEDB.raw_flex_siebel.s_postn spn on spn.row_id = sc.pr_held_postn_id
where
PERSON.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_PERSON__c2a0c500166341fd6d0cb2bbfab09327
where MISMATCH_REASON = 'BRANDING_ORG_ID'
)
1-5PO87QJ
1-58TZTCJ
)
Select
Snowflake.row_id,
Snowflake.PERSON_last_upd as Snowflake_PERSON_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'================================================================================='
'============================= WC_EMPLOYEE_D ====================================='
'================================================================================='
select CURR_AVAIL_CD from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_EMPLOYEE_D" where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'CURR_AVAIL_CD'
)
'================================================================================='
'============================ HISTORICAL TRACE ==================================='
'================================================================================='
select * from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_POSITION_DH_SCD" where base_emp_id ='1-5ACQTG8'order by effective_from_dt
CELL_PH_NUM
'=================================== EMPLOYEE ===================================='
'==================================== LW_FLG ===================================='
'================================ DATE COMPARISON ================================'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_EMPLOYEE_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'LW_FLG'
)
),
Snowflake
as
(
select
EMPLOYEE.last_upd as EMPLOYEE_last_upd,
sc.last_upd as sc_CON_last_upd,
scx.last_upd as scx_CON_last_upd,
spn.last_upd as spn_CON_last_upd,
EMPLOYEE.row_id,
job_title
from ONEDB.raw_flex_siebel.s_emp_per EMPLOYEE
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = EMPLOYEE.row_id
left join ONEDB.raw_flex_siebel.s_contact_x scx on scx.par_row_id = EMPLOYEE.row_id
left join ONEDB.raw_flex_siebel.s_postn spn on spn.row_id = sc.pr_held_postn_id
where
EMPLOYEE.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'LW_FLG'
)
--and PERSON.row_id = '1-5DHYZ1P'
)
Select
Snowflake.row_id,
Snowflake.EMPLOYEE_last_upd,
Snowflake.sc_CON_last_upd,
Snowflake.scx_CON_last_upd,
Snowflake.spn_CON_last_upd,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'=================================== EMPLOYEE ===================================='
'================================== ALT_EMAIL_ADDR ==================================='
'================================ DATE COMPARISON ================================'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_EMPLOYEE_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'ALT_EMAIL_ADDR'
)
),
Snowflake
as
(
select
EMPLOYEE.last_upd as EMPLOYEE_last_upd,
sc.last_upd as sc_CON_last_upd,
EMPLOYEE.row_id,
job_title
from ONEDB.raw_flex_siebel.s_emp_per EMPLOYEE
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = EMPLOYEE.row_id
where
EMPLOYEE.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'ALT_EMAIL_ADDR'
)
-- and PERSON.row_id = '1-5DHYZ1P'
)
Select
Snowflake.row_id,
Snowflake.EMPLOYEE_last_upd as Snowflake_EMPLOYEE_Last_Update_Date,
Snowflake.sc_CON_last_upd,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'=================================== EMPLOYEE ===================================='
'================================== JOB_TITLE ==================================='
'================================ DATE COMPARISON ================================'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_EMPLOYEE_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'JOB_TITLE'
)
),
Snowflake
as
(
select
EMPLOYEE.last_upd as EMPLOYEE_last_upd,
sc.last_upd as sc_CON_last_upd,
EMPLOYEE.row_id,
job_title
from ONEDB.raw_flex_siebel.s_emp_per EMPLOYEE
left outer join ONEDB.raw_flex_siebel.s_contact sc on sc.row_id = EMPLOYEE.row_id
where
EMPLOYEE.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'JOB_TITLE'
)
-- and PERSON.row_id = '1-5DHYZ1P'
)
Select
Snowflake.row_id,
Snowflake.EMPLOYEE_last_upd as Snowflake_EMPLOYEE_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'=================================== EMPLOYEE ===================================='
'================================== EMAIL_ADDR ===================================='
'================================ DATE COMPARISON ================================'
With Oracle
As
(
select e.etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date, e.SSO_EMAIL, e2.SSO_EMAIL,
e.integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_EMPLOYEE_D" e
inner join "DEV_ONEDB"."WH_ANALYTICS"."WC_EMPLOYEE_D" e2 on e2.integration_Id = e.integration_Id
where e.integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'EMAIL_ADDR'
)
),
Snowflake
as
(
select
EMPLOYEE.last_upd as EMPLOYEE_last_upd,
EMPLOYEE.row_id
from ONEDB.raw_flex_siebel.S_CONTACT EMPLOYEE
where
EMPLOYEE.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'EMAIL_ADDR'
)
)
Select
Snowflake.row_id,
Snowflake.EMPLOYEE_last_upd as Snowflake_EMPLOYEE_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
Where Snowflake.EMPLOYEE_last_upd > Oracle.extract_date
'=================================== EMPLOYEE ===================================='
'================================== USER_TYPE ===================================='
'================================ DATE COMPARISON ================================'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_EMPLOYEE_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'USER_TYPE'
)
),
Snowflake
as
(
select
EMPLOYEE.last_upd as EMPLOYEE_last_upd,
EMPLOYEE.row_id
from ONEDB.raw_flex_siebel.S_EMP_PER EMPLOYEE
where
EMPLOYEE.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'USER_TYPE'
)
)
Select
Snowflake.row_id,
Snowflake.EMPLOYEE_last_upd as Snowflake_EMPLOYEE_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
Where Snowflake.EMPLOYEE_last_upd > Oracle.extract_date
--Where Snowflake.EMPLOYEE_last_upd > Oracle.extract_date
--left outer join cx_ONEDB_nccp_lic_join pr_lic_r on pr_lic_r.party_id = sep.row_id
-- pr_lic_r.end_dt_r as auth_exp_dt,
'=================================== EMPLOYEE ===================================='
'================================== LIC_EXP_DT =================================='
'================================ DATE COMPARISON ================================'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_EMPLOYEE_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'LIC_EXP_DT'
)
),
Snowflake
as
(
select
EMPLOYEE.last_upd as EMPLOYEE_last_upd,
pr_lic_r.last_upd as pr_lic_r_last_upd,
EMPLOYEE.row_id
from ONEDB.raw_flex_siebel.S_EMP_PER EMPLOYEE
inner join (
select
pr_lic.start_dt,
pr_lic.end_dt as end_dt_r,
pr_lic.LAST_UPD as pr_lic_LAST_UPD,
pr_lic.license_id,
pr_lic.party_id,
greatest(nvl( pr_lic._fivetran_synced, '1900-01-01'),nvl( pr_lic_r._fivetran_synced, '1900-01-01') ) as _fivetran_synced_join,
pr_lic_r.*
from ONEDB.raw_flex_siebel.cx_ONEDB_nccp_par pr_lic
inner join ONEDB.raw_flex_siebel.cx_ONEDB_nccp_lic pr_lic_r on pr_lic_r.row_id = pr_lic.license_id
where pr_lic.primary_flag = 'Y'
and pr_lic_r.type = 'License'
-- and pr_lic.party_id = '1-3MOSJVW'
) pr_lic_r on pr_lic_r.party_id = EMPLOYEE.row_id
where
EMPLOYEE.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'LIC_EXP_DT'
)
)
Select
Snowflake.row_id,
Snowflake.EMPLOYEE_last_upd as Snowflake_EMPLOYEE_Last_Update_Date,
Snowflake.pr_lic_r_last_upd,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
Where Snowflake.EMPLOYEE_last_upd > Oracle.extract_date
--left outer join cx_ONEDB_nccp_lic_join pr_lic_r on pr_lic_r.party_id = sep.row_id
-- pr_lic_r.end_dt_r as auth_exp_dt,
'=================================== EMPLOYEE ===================================='
'=============================== MARKETING_LEAD_FLG =============================='
'================================ DATE COMPARISON ================================'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_SIEBEL"."WC_EMPLOYEE_D"
where integration_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'MARKETING_LEAD_FLG'
)
),
Snowflake
as
(
select
EMPLOYEE.last_upd as EMPLOYEE_last_upd,
su.last_upd as USER_last_upd,
su.login,
HUB.last_upd as HUB_last_upd,
EMPLOYEE.row_id
from ONEDB.raw_flex_siebel.S_EMP_PER EMPLOYEE
left outer join ONEDB.raw_flex_siebel.S_USER su on su.row_id = EMPLOYEE.row_id
left join (
select ROW_NUMBER() Over (partition By flex_login Order By new_to_industry Desc nulls last, canonical_vid Desc nulls last, website_inquiry Desc nulls last) As Rank_Num,
*
from DEV_ONEDB.CURATED_HUBSPOT.Employee
) HUB on HUB.flex_login = su.LOGIN AND RANK_NUM = 1
where
EMPLOYEE.row_id in (
select
"integration_id"
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_EMPLOYE_ee067ec92dd6de9de45014da9ea7531d
where MISMATCH_REASON = 'MARKETING_LEAD_FLG'
)
)
Select
Snowflake.row_id,
Snowflake.EMPLOYEE_last_upd as Snowflake_EMPLOYEE_Last_Update_Date,
Snowflake.USER_last_upd as Snowflake_USER_Last_Update_Date,
Snowflake.HUB_last_upd,
login,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
Where Snowflake.USER_last_upd > Oracle.extract_date
'=============================== WC_ASSET_CLIENT_H ================================'
'==================================== DELETED ====================================='
'================================ DATE COMPARISON ================================='
select Sum(IFF(Dual_Test.INTEGRATION_ID is null,0,1)) as Total_SDual_Test_Records,
Sum(IFF(sac.asset_id is null,0,1)) as Total_S_asset_Con_Records,
Sum(IFF(sa.row_id is null,0,1)) as Total_s_asset_Records
from (
select
INTEGRATION_ID,*
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'DELETED'
) Dual_Test
Left Join ONEDB.raw_flex_crm.s_asset_con sac on sac.row_id = Dual_Test.INTEGRATION_ID
Left join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
select sac._fivetran_deleted, Dual_Test.*
from (
select
*
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'DELETED'
) Dual_Test
inner Join ONEDB.raw_flex_crm.s_asset_con sac on sac.row_id = Dual_Test.INTEGRATION_ID
Left join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
'---///////////////////////////////////////////////////////////////////////////////---'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
INTEGRATION_ID
from "AP_PROD"."ORACLEOACS_crm"."WC_ASSET_CLIENT_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'DELETED'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_crm.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.ROW_ID in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'DELETED'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'=============================== WC_ASSET_CLIENT_H ================================'
'============================ Missing in destination ============================='
'=============================== DATE COMPARISON ================================='
select * from DEV_ONEDB.wh_analytics.WC_OPTY_CLIENT_H where INTEGRATION_ID = '1-7BDN8F5'
select sac._fivetran_deleted,sac.row_id
from ONEDB.raw_flex_crm.s_asset_con sac
Inner join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
where sa.type_cd = 'Fin Account'
and sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'Missing in destination'
)
select Sum(IFF(sac.asset_id is null,0,1)) as Total_S_asset_con_Records,
Sum(IFF(sa.row_id is null,0,1)) as Total_s_asset_Records
from ONEDB.raw_flex_crm.s_asset_con sac
Left join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
where sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'Missing in destination'
)
'---///////////////////////////////////////////////////////////////////////////////---'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_crm"."WC_ASSET_CLIENT_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'Missing in destination'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_crm.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'Missing in destination'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
'=============================== WC_ASSET_CLIENT_H ================================'
'================================== PRIMARY_FLG ==================================='
'================================ DATE COMPARISON ================================='
select deleted, * from "AP_PROD"."ORACLEOACS_crm"."WC_ASSET_CLIENT_H"
where INTEGRATION_ID IN (select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG') AND deleted = 'Y'
select sac._fivetran_deleted, sac.CONTACT_ID, sa.PR_CON_ID, Dual_Test.*
from (
select
*
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
) Dual_Test
inner Join ONEDB.raw_flex_crm.s_asset_con sac on sac.row_id = Dual_Test.INTEGRATION_ID
Left join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
where sac.CONTACT_ID<>sa.PR_CON_ID
select * from ONEDB.raw_flex_crm.s_asset_con limit 10
Where sac._fivetran_deleted = True
select Sum(IFF(sac.asset_id is null,0,1)) as Total_S_asset_con_Records,
Sum(IFF(sa.row_id is null,0,1)) as Total_s_asset_Records
from ONEDB.raw_flex_crm.s_asset_con sac
Inner join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
where sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
)
'---///////////////////////////////////////////////////////////////////////////////---'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
INTEGRATION_ID
from "AP_PROD"."ORACLEOACS_crm"."WC_ASSET_CLIENT_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
)
),
Snowflake
as
(
select
sac.last_upd as sac_last_upd,
sa.last_upd as sa_last_upd,
sac.row_id
from ONEDB.raw_flex_crm.s_asset_con sac
Inner join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
where
sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
)
)
Select
Snowflake.row_id,
Snowflake.sac_last_upd as Snowflake_sac_Last_Update_Date,
Snowflake.sa_last_upd as Snowflake_sa_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id
where SNOWFLAKE_SA_LAST_UPDATE_DATE<ORACLE_LAST_UPDATE_DATE
'----------------------------------------------------------------------------------------------------------'
select
sac.row_id,
sac._fivetran_deleted,
sac.last_upd,
sa._fivetran_deleted,
sa.last_upd,
*
from ONEDB.raw_flex_crm.s_asset_con sac
Inner join ONEDB.raw_flex_crm.s_asset sa on sa.row_id = sac.asset_id
where
sac.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_ASSET_P_3dd79a38a2d15d7da1e54f063162ac85
where MISMATCH_REASON = 'PRIMARY_FLG'
)
and (sac._fivetran_deleted = True or sa._fivetran_deleted = True )
'=============================== WC_OPTY_CLIENT_H ================================'
'============================== Missing in source ================================'
'=============================== DATE COMPARISON ================================='
select _fivetran_deleted,row_id from ONEDB.raw_flex_crm.S_EVT_ACT
where row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_3f95d1caed3c2922f9c9fd01badd8dd0
where MISMATCH_REASON = 'Missing in source'
)
and _fivetran_deleted = True
'---///////////////////////////////////////////////////////////////////////////////---'
select _fivetran_deleted from ONEDB.raw_flex_crm.S_ACT_CONTACT
where row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_3f95d1caed3c2922f9c9fd01badd8dd0
where MISMATCH_REASON = 'Missing in source'
)
and _fivetran_deleted = TRUE
'----------------------------------------------------------------------------------------------------------'
select * from DEV_ONEDB.wh_analytics.WC_OPTY_CLIENT_H where DELETED = 'Y'
select * from "AP_PROD"."ORACLEOACS_crm"."WC_OPTY_CLIENT_H" where DELETED = 'Y'
select * from DEV_ONEDB.wh_analytics.WC_OPTY_CLIENT_H
where INTEGRATION_ID in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_3f95d1caed3c2922f9c9fd01badd8dd0
where MISMATCH_REASON = 'Missing in source'
)
'----------------------------------------------------------------------------------------------------------'
select * from "AP_PROD"."ORACLEOACS_crm"."WC_OPTY_CLIENT_H"
where INTEGRATION_ID in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in source'
)
'----------------------------------------------------------------------------------------------------------'
select
Sum(Iff(Dual_Test.INTEGRATION_ID is null,0,1)) as Total_Dual_Test_Records,
Sum(Iff(S_ACT_CONTACT.row_id is null,0,1)) as Total_S_ACT_CONTACT_Records,
Sum(Iff(S_EVT_ACT.row_id is null,0,1)) as Total_S_EVT_ACT_Records
from
(
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in source'
) Dual_Test
Inner join ONEDB.raw_flex_crm.S_ACT_CONTACT on Dual_Test.INTEGRATION_ID=S_ACT_CONTACT.row_id
Left join ONEDB.raw_flex_crm.S_EVT_ACT on S_ACT_CONTACT.activity_Id = S_EVT_ACT.row_id
'---///////////////////////////////////////////////////////////////////////////////---'
With Oracle
As
(
select etl_proc_wid/1440 + to_date('01-01-1970','dd-mm-yyyy') as extract_date,
integration_id
from "AP_PROD"."ORACLEOACS_crm"."WC_OPTY_CLIENT_H"
where integration_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in source'
)
),
Snowflake
as
(
select
ACTIVE_CONTACT.last_upd as ACTIVE_CONTACT_last_upd,
ACTIVE_CONTACT.last_upd as sc_CON_last_upd,
ACTIVE_CONTACT.row_id
from ONEDB.raw_flex_crm.S_ACT_CONTACT ACTIVE_CONTACT
where
ACTIVE_CONTACT.row_id in (
select
INTEGRATION_ID
from DEV_ONEDB.dbt_test__audit.dual_warehouse_test_WC_OPTY_PE_4e280d75455916c5c5824770948fb371
where MISMATCH_REASON = 'Missing in source'
)
)
Select
Snowflake.row_id,
Snowflake.ACTIVE_CONTACT_last_upd as Snowflake_ACTIVE_CONTACT_Last_Update_Date,
Oracle.extract_date as Oracle_Last_Update_Date
From Snowflake
Inner join Oracle on Oracle.integration_id = Snowflake.row_id