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

Go to download
{%  if strategyTimestamp != ""  %}
MERGE INTO {{ tableFullName }} SL_EXISTING USING (
WITH 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
    -- )
    (
        SELECT {{ tableColumnsCsv }}
        FROM
        (
            SELECT {{ tableColumnsCsv }}, ROW_NUMBER() OVER (PARTITION BY {{ strategyKeyCsv }} ORDER BY {{ strategyTimestamp }} DESC) AS SL_ROW_NUMBER
            FROM ({{ selectStatement }}) AS SL_INCOMING
        ) AS SL_WITH_ROW_NUMBER
        WHERE SL_WITH_ROW_NUMBER.SL_ROW_NUMBER = 1
    )

{% endif %}
    -- All line present in the source with their key
    SELECT {{ renderStrategyKeyAsPseudo(strategyKey, 'SL_INCOMING') }}, SL_INCOMING.* FROM SL_INCOMING
    LEFT JOIN {{ tableFullName }} SL_EXISTING 
    ON ({{ strategyKeyJoinCondition }} 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 ({{ strategyKeyJoinCondition }} AND SL_EXISTING.{{strategyEndTs}} IS NULL)
    WHERE  SL_EXISTING.{{strategyTimestamp}} < SL_INCOMING.{{strategyTimestamp}}
    ) SL_INCOMING
ON ({{ renderStrategyJoinConditionWithPseudo('SL_INCOMING', 'SL_EXISTING', 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.') }}, CURRENT_TIMESTAMP(), NULL);

{%  else  %}
MERGE INTO {{ tableFullName }} SL_EXISTING USING (
WITH 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
    -- )
    (
        SELECT {{ tableColumnsCsv }}
        FROM
        (
            SELECT {{ tableColumnsCsv }}, ROW_NUMBER() OVER (PARTITION BY {{ strategyKeyCsv }} ORDER BY {{ strategyTimestamp }} DESC) AS SL_ROW_NUMBER
            FROM ({{ selectStatement }}) AS SL_INCOMING
        ) AS SL_WITH_ROW_NUMBER
        WHERE SL_WITH_ROW_NUMBER.SL_ROW_NUMBER = 1
    )


{% endif %}
    -- All line present in the source with their key
    SELECT {{ renderStrategyKeyAsPseudo(strategyKey, 'SL_INCOMING') }}, SL_INCOMING.* FROM SL_INCOMING
    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 ({{ strategyKeyJoinCondition }} AND SL_EXISTING.{{strategyEndTs}} IS NULL)
    ) SL_INCOMING
ON ({{ renderStrategyJoinConditionWithPseudo('SL_INCOMING', 'SL_EXISTING', 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.') }}, CURRENT_TIMESTAMP(), NULL);


{% endif %}