templates.write-strategies.postgresql.scd2.j2 Maven / Gradle / Ivy

Go to download

{%  if strategyTimestamp != ""  %}
CREATE TEMPORARY VIEW SL_INCOMING AS 
{% if strategyOn == 'TARGET' %}
    {{ selectStatement }};
{% else %} {# strategyOn == 'SOURCE_AND_TARGET' #}
        SELECT {{ tableColumnsCsv }}
        FROM ({{ selectStatement }}) AS SL_INCOMING
        QUALIFY ROW_NUMBER() OVER (PARTITION BY {{ strategyKeyCsv }} ORDER BY {{ strategyTimestamp }} DESC) = 1;
{% endif %}


CREATE TEMPORARY VIEW SL_INCOMING_ALL AS
    SELECT {{ renderStrategyKeyAsPseudo(strategyKey, 'SL_INCOMING') }}, SL_INCOMING.* FROM SL_INCOMING
    LEFT JOIN {{ tableFullName }} SL_EXISTING 
    ON ({{ renderStrategyKeyJoinCondition('SL_INCOMING','SL_EXISTING',strategyKey) }} AND SL_EXISTING.{{strategyEndTs}} IS NULL)
    WHERE  {{ renderStrategyKeyIsNull('SL_EXISTING', strategyKey) }} OR SL_EXISTING.{{strategyTimestamp}} < SL_INCOMING.{{strategyTimestamp}}
    UNION ALL
    -- All line present in the source and target that are current in the target
    SELECT {{ renderStrategyKeyAsNullPseudo(strategyKey) }}, SL_INCOMING.* FROM SL_INCOMING
    INNER JOIN {{ tableFullName }} SL_EXISTING
    ON ({{ renderStrategyKeyJoinCondition('SL_INCOMING','SL_EXISTING',strategyKey) }} AND SL_EXISTING.{{strategyEndTs}} IS NULL)
    WHERE  SL_EXISTING.{{strategyTimestamp}} < SL_INCOMING.{{strategyTimestamp}};


MERGE INTO {{ tableFullName }} USING SL_INCOMING_ALL
ON ({{ renderStrategyJoinConditionWithPseudo('SL_INCOMING_ALL', '{{ tableFullName }}', strategyKey) }})
WHEN MATCHED THEN UPDATE SET {{ strategyEndTs }} = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT ({{ renderColumnsWithoutScd2(tableColumnNames, "") }}, {{ quote }}{{ strategyStartTs }}{{ quote }}, {{ quote }}{{ strategyEndTs }}{{ quote }} )
    VALUES ({{ renderColumnsWithoutScd2(tableColumnNames, 'SL_INCOMING_ALL.') }}, CURRENT_TIMESTAMP, NULL);

{%  else  %}
CREATE TEMPORARY VIEW SL_INCOMING AS 
{% if strategyOn == 'TARGET' %}
    {{ selectStatement }};
{% else %} {# strategyOn == 'SOURCE_AND_TARGET' #}
        SELECT {{ tableColumnsCsv }}
        FROM ({{ selectStatement }}) AS SL_INCOMING
        QUALIFY ROW_NUMBER() OVER (PARTITION BY {{ strategyKeyCsv }} ORDER BY {{ strategyTimestamp }} DESC) = 1;
{% endif %}


CREATE TEMPORARY VIEW SL_INCOMING_ALL AS
    SELECT {{ renderStrategyKeyAsPseudo(strategyKey, 'SL_INCOMING') }}, SL_INCOMING.* FROM SL_INCOMING
        UNION ALL
    SELECT {{ renderStrategyKeyAsNullPseudo(strategyKey) }}, SL_INCOMING.* FROM SL_INCOMING
    INNER JOIN {{ tableFullName }} SL_EXISTING
    ON ({{ renderStrategyKeyJoinCondition('SL_INCOMING','SL_EXISTING',strategyKey) }} AND SL_EXISTING.{{strategyEndTs}} IS NULL);


MERGE INTO {{ tableFullName }} USING {{ tableFullName }}_INCOMING
ON ({{ renderStrategyJoinConditionWithPseudo('SL_INCOMING_ALL', '{{ tableFullName }}', strategyKey) }})
WHEN MATCHED THEN
    UPDATE SET {{ strategyEndTs }} = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT ({{ renderColumnsWithoutScd2(tableColumnNames, "") }}, {{ quote }}{{ strategyStartTs }}{{ quote }}, {{ quote }}{{ strategyEndTs }}{{ quote }} )
    VALUES ({{ renderColumnsWithoutScd2(tableColumnNames, 'SL_INCOMING_ALL.') }}, CURRENT_TIMESTAMP, NULL);

{% endif %}

DROP VIEW IF EXISTS SL_INCOMING_ALL;
DROP VIEW IF EXISTS SL_INCOMING;
DROP TABLE IF EXISTS {{ tableFullName }}_INCOMING;