{{
config(
materialized = 'table',
unique_key = 'ROW_WID',
comment='Warehouse Layer WC_CAR_CLIENT_H',
tags = ['analytics']
)
}}
with
CAR_CLIENT_HISTORICAL AS (
select * from {{ ref('WC_CAR_CLIENT_HISTORICAL') }}
),
LOAN_ACCOUNT_CONTACT as (
select * from {{ ref('curfl_Loan_Account_Contact') }}
),
WC_CAR_CLIENT_H_LIVE as (
Select
{{ generate_surrogate_key(['1', 'LOAN_ACCOUNT_CONTACT.ROW_ID']) }} AS ROW_WID,
{{ var('etl_proc_wid') }} AS ETL_PROC_WID,
1 AS DATASOURCE_NUM_ID,
LOAN_ACCOUNT_CONTACT.ROW_ID as INTEGRATION_ID,
{{ generate_surrogate_key(['1', 'LOAN_ACCOUNT_CONTACT.CAR_ID']) }} as CAR_WID,
{{ generate_surrogate_key(['1', 'LOAN_ACCOUNT_CONTACT.CONTACT_ID']) }}as CLIENT_WID,
LOAN_ACCOUNT_CONTACT.PRIMARY_FLAG as PRIMARY_FLG,
'N' as DELETED
From LOAN_ACCOUNT_CONTACT
),
WC_CAR_CLIENT_H as (
Select
ROW_WID,
ETL_PROC_WID,
DATASOURCE_NUM_ID,
INTEGRATION_ID,
CAR_WID,
CLIENT_WID,
PRIMARY_FLG,
DELETED
From WC_CAR_CLIENT_H_LIVE
Union
Select
ROW_WID,
ETL_PROC_WID,
DATASOURCE_NUM_ID,
INTEGRATION_ID,
CAR_WID,
CLIENT_WID,
PRIMARY_FLG,
IFF(CAR_CLIENT_HISTORICAL.DATASOURCE_NUM_ID = 1, 'Y',CAR_CLIENT_HISTORICAL.DELETED) AS DELETED
From CAR_CLIENT_HISTORICAL
Where NOT EXISTS (SELECT
1
FROM WC_CAR_CLIENT_H_LIVE
WHERE WC_CAR_CLIENT_H_LIVE.DATASOURCE_NUM_ID = CAR_CLIENT_HISTORICAL.DATASOURCE_NUM_ID
AND WC_CAR_CLIENT_H_LIVE.INTEGRATION_ID = CAR_CLIENT_HISTORICAL.INTEGRATION_ID
)
)
Select
ROW_WID,
ETL_PROC_WID,
DATASOURCE_NUM_ID,
INTEGRATION_ID,
CAR_WID,
CLIENT_WID,
PRIMARY_FLG,
DELETED
From WC_CAR_CLIENT_H
============================ YML ========================================
version: 2
models:
- name: WC_CAR_CLIENT_H
description: "Client car dimension. Contains information for car client"
columns:
- name: ROW_WID
description: ""
tests:
- unique
- not_null
- name: INTEGRATION_ID
description: ""
tests:
- unique
- not_null