Generate row rank using event timestamp column with DBT incremental materialization

generate_row_rank_using_event_ts_incremental.sql

{#
- this macro creates incremental tables based
ON history DATA - it uses is_incremental() macro TO wrap valid SQL that filters THE rows - Filter will ONLY be applied
ON an incremental run - it uses unique_key PARAMETER which ensures THE existing ROW IS updated IN target TABLE #}

{%- macro generate_row_rank_using_event_ts_incremental(model, keycolumn) -%}
    
    {%- set columns = adapter.get_columns_in_relation(model) -%}
    {{ config(materialized = var('incremental'), unique_key = '__uuid') }}

    WITH ranked_data AS (
        SELECT
            ROW_NUMBER() over (
                PARTITION BY {{ keycolumn }}
                ORDER BY
                    __event_ts DESC
            ) AS row_rank,
            *
          FROM {{ model }}
        
        {% if is_incremental() -%}
         WHERE __load_ts > ( SELECT MAX(__load_ts)
                               FROM {{ this }} )
        {% endif -%}
    )
    SELECT
        {%- for column in columns %}
            "{{ column.name }}" {%- if not loop.last -%},
            {%- endif -%}
        {%- endfor -%},
        HASH(
            {{ keycolumn }}
        ) AS __uuid
     FROM
        ranked_data
    WHERE
        row_rank = 1
    ORDER BY __load_ts desc
{% endmacro %}