47 lines
1.9 KiB
SQL
47 lines
1.9 KiB
SQL
{% snapshot m_DWH_FXCD_NH_F_ELIGIBILITY_ISSUER %}
|
|
{{
|
|
config(
|
|
target_schema='dw_rar',
|
|
alias = 'NH_F_ELIGIBILITY_ISSUER_MARS',
|
|
unique_key=['ELIGIBILITY_ISSUER_ID'],
|
|
strategy='check',
|
|
check_cols=[
|
|
'ELIGIBILITY_ISSUER_NAME',
|
|
'ART_101_FLAG',
|
|
'RATING_THRESHOLD',
|
|
'DEFINITION',
|
|
'RATING_UPLIFT_FACTOR',
|
|
'ELIG_FLAG'
|
|
],
|
|
hard_deletes='invalidate',
|
|
dbt_valid_to_current="TIMESTAMP '9999-12-31 00:00:00'",
|
|
snapshot_meta_column_names={
|
|
'dbt_valid_from': 'A_VALID_FROM',
|
|
'dbt_valid_to': 'A_VALID_TO',
|
|
'dbt_updated_at': 'UPDATED_AT',
|
|
'dbt_scd_id': 'SCD_ID'
|
|
},
|
|
column_types={
|
|
'A_VALID_FROM': 'DATE',
|
|
'A_VALID_TO': 'DATE'
|
|
},
|
|
tags=["m_DWH_FXCD_NH_F_ELIGIBILITY_ISSUER"]
|
|
)
|
|
}}
|
|
|
|
SELECT DISTINCT
|
|
(SELECT COALESCE(MAX(A_KEY), 0) FROM {{ this }}) + ROW_NUMBER() OVER (ORDER BY 1) as A_KEY,
|
|
CAST({{ get_workflow_history_key() }} AS NUMBER(38,0)) AS A_DWH_LOAD_SET_FK,
|
|
CAST(ELIGIBILITY_ISSUER_ID AS NUMBER(28,0)) AS ELIGIBILITY_ISSUER_ID,
|
|
CAST(ELIGIBILITY_ISSUER_NAME AS VARCHAR2(200 CHAR)) AS ELIGIBILITY_ISSUER_NAME,
|
|
CAST({{ clean_boolean_fxcd('ART_101_FLAG') }} AS CHAR(1 CHAR)) AS ART_101_FLAG,
|
|
CAST(RATING_THRESHOLD AS VARCHAR2(4 CHAR)) AS RATING_THRESHOLD,
|
|
CAST(DEFINITION AS VARCHAR2(50 CHAR)) AS DEFINITION,
|
|
CAST(NULL AS CHAR(1 BYTE)) AS RATING_TERM_TYPE, -- Not mapped in Informatica target, so excluded from check_cols
|
|
CAST(RATING_UPLIFT_FACTOR AS NUMBER(28,0)) AS RATING_UPLIFT_FACTOR,
|
|
CAST({{ clean_boolean_fxcd('ELIG_FLAG') }} AS VARCHAR2(1 CHAR)) AS ELIG_FLAG
|
|
FROM
|
|
{{ source('ods', 'F_ELIGIBILITY_ISSUER') }}
|
|
WHERE
|
|
A_WORKFLOW_HISTORY_KEY = {{ filter_workflow_history_max_key("w_ODS_FXCD_F_ELIGIBILITY_ISSUER",get_main_task_name(model.name))}}
|
|
{% endsnapshot %} |