vvgsrk

{%- macro generate_row_rank_using_event_ts(model, keycolumn) -%}
{%- set columns = adapter.get_columns_in_relation(model) -%}
WITH latest_data_changes AS
(
SELECT
    ROW_NUMBER() OVER (
        PARTITION BY {{ keycolumn }}  
        ORDER BY __event_ts DESC
    ) AS row_rank,
    *

vvgsrk

{#
- 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') }}

vvgsrk

WITH source AS (
    SELECT
        *
    FROM
        {{ source(
            'product_management',
            'product_history'
        ) }}
),
renamed AS (

vvgsrk

{{ generate_row_rank_using_event_ts(ref('src_product_management_product_history'), 'product_id') }}

vvgsrk

{{ generate_row_rank_using_event_ts_incremental(ref('src_product_management_product_history'), 'product_id') }}

vvgsrk

WITH ranked_data AS (
    SELECT
        ROW_NUMBER() over (
            PARTITION BY product_id
            ORDER BY
                __event_ts DESC
        ) AS row_rank,
        *
      FROM dev_edw_dbt_incremental_model_test.dbt_src_product_management.src_product_management_product_history
    )

vvgsrk

SELECT *
  FROM {{ ref('src_product_management_product_latest_incremental') }}

vvgsrk

create or replace transient table dev_edw_dbt_incremental_model_test.dbt_src_product_management.src_product_management_product_latest_incremental  as
      (
    WITH ranked_data AS (
        SELECT
            ROW_NUMBER() over (
                PARTITION BY product_id
                ORDER BY
                    __event_ts DESC
            ) AS row_rank,
            *

vvgsrk

create or replace temporary table dev_edw_dbt_incremental_model_test.dbt_src_product_management.src_product_management_product_latest_incremental__dbt_tmp  as
      (
    WITH ranked_data AS (
        SELECT
            ROW_NUMBER() over (
                PARTITION BY product_id
                ORDER BY
                    __event_ts DESC
            ) AS row_rank,
            *

vvgsrk

merge into dev_edw_dbt_incremental_model_test.dbt_src_product_management.src_product_management_product_latest_incremental as DBT_INTERNAL_DEST
        using dev_edw_dbt_incremental_model_test.dbt_src_product_management.src_product_management_product_latest_incremental__dbt_tmp as DBT_INTERNAL_SOURCE
        on 
            DBT_INTERNAL_SOURCE.__uuid = DBT_INTERNAL_DEST.__uuid       
    
    when matched then update set
        "PRODUCT_ID" = DBT_INTERNAL_SOURCE."PRODUCT_ID","PRODUCT_NAME" = DBT_INTERNAL_SOURCE."PRODUCT_NAME","PRICE_PER_UNIT" = DBT_INTERNAL_SOURCE."PRICE_PER_UNIT","BASIC_UNIT" = DBT_INTERNAL_SOURCE."BASIC_UNIT","IS_STOCK_LIMITED" = DBT_INTERNAL_SOURCE."IS_STOCK_LIMITED","IS_ACTIVE_FOR_SALE" = DBT_INTERNAL_SOURCE."IS_ACTIVE_FOR_SALE","__EVENT_TS" = DBT_INTERNAL_SOURCE."__EVENT_TS","__LOAD_TS" = DBT_INTERNAL_SOURCE."__LOAD_TS","__UUID" = DBT_INTERNAL_SOURCE."__UUID"

    when not matched then insert
        ("PRODUCT_ID", "PRODUCT_NAME", "PRICE_PER_UNIT", "BASIC_UNIT", "IS_STOCK_LIMITED", "IS_