72 lines
2.4 KiB
SQL
72 lines
2.4 KiB
SQL
{% snapshot m_DWH_FXCD_NH_F_COUNTRY %}
|
|
{{
|
|
config(
|
|
target_schema='dw_rar',
|
|
alias = 'NH_F_COUNTRY_MARS',
|
|
unique_key=['COUNTRY_ID'],
|
|
strategy='check',
|
|
check_cols=['ENTITY_ID',
|
|
'COUNTRY_GDP',
|
|
'NCB_USD_LAMBDA',
|
|
'OF_FLAG',
|
|
'FR_FLAG',
|
|
'EU_FLAG',
|
|
'EUROSYSTEM_FLAG',
|
|
'FR_PORTF_SHARE_OPTOUT',
|
|
'FR_LIMIT_CALC_OPTOUT',
|
|
'COUNTRY_COMMENT',
|
|
'OF_LMT_AMNT',
|
|
'OF_MANUAL_FLAG',
|
|
'FR_LMT_AMNT',
|
|
'FR_MANUAL_FLAG',
|
|
'USD_PORTFOLIO_EUR_SIZE',
|
|
'JPY_PORTFOLIO_EUR_SIZE',
|
|
'CAPITAL_KEY_AMNT',
|
|
'LAMBDA_MANUAL_FLAG',
|
|
'CNY_PORTFOLIO_EUR_SIZE',
|
|
'CHANGE_DESCRIPTION'],
|
|
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_COUNTRY"]
|
|
)
|
|
}}
|
|
SELECT
|
|
(SELECT COALESCE(MAX(A_KEY), 0) FROM {{ this }}) + ROW_NUMBER() OVER (ORDER BY 1) as A_KEY,
|
|
{{ get_workflow_history_key() }} AS A_DWH_LOAD_SET_FK,
|
|
COUNTRY_ID,
|
|
ENTITY_ID,
|
|
COUNTRY_GDP,
|
|
NCB_USD_LAMBDA,
|
|
{{clean_boolean_fxcd('OF_FLAG')}} AS OF_FLAG,
|
|
{{clean_boolean_fxcd('FR_FLAG')}} AS FR_FLAG,
|
|
{{clean_boolean_fxcd('EU_FLAG')}} AS EU_FLAG,
|
|
{{clean_boolean_fxcd('EUROSYSTEM_FLAG')}} AS EUROSYSTEM_FLAG,
|
|
{{clean_boolean_fxcd('FR_PORTF_SHARE_OPTOUT')}} AS FR_PORTF_SHARE_OPTOUT,
|
|
{{clean_boolean_fxcd('FR_LIMIT_CALC_OPTOUT')}} AS FR_LIMIT_CALC_OPTOUT,
|
|
COUNTRY_COMMENT,
|
|
OF_LMT_AMNT,
|
|
{{clean_boolean_fxcd('OF_MANUAL_FLAG')}} AS OF_MANUAL_FLAG,
|
|
FR_LMT_AMNT,
|
|
{{clean_boolean_fxcd('FR_MANUAL_FLAG')}} AS FR_MANUAL_FLAG,
|
|
USD_PORTFOLIO_EUR_SIZE,
|
|
JPY_PORTFOLIO_EUR_SIZE,
|
|
CAPITAL_KEY_AMNT,
|
|
{{clean_boolean_fxcd('LAMBDA_MANUAL_FLAG')}} AS LAMBDA_MANUAL_FLAG,
|
|
CNY_PORTFOLIO_EUR_SIZE,
|
|
CHANGE_DESCRIPTION
|
|
FROM {{ source('ods', 'F_COUNTRY') }}
|
|
WHERE
|
|
A_WORKFLOW_HISTORY_KEY = {{ filter_workflow_history_max_key("w_ODS_FXCD_F_COUNTRY",get_main_task_name(model.name))}}
|
|
{% endsnapshot %}
|
|
|