Compare lattest based on history and incremental data row count

compare_lattest_and_incremental_row_count.sql

{#
- This macro generate row counts for lattest data based on history data and incremental data 
- Compares both the row counts and outputs the diffrence
#}

{%- macro compare_lattest_and_incremental_row_count(history_model, incremental_model) -%}
{%- set columns = adapter.get_columns_in_relation(history_model) -%}
WITH snap AS
(
SELECT
    ROW_NUMBER() OVER (
        PARTITION BY {% for column in columns if column.name.endswith('_ID') -%}
            "{{ column.name }}" {% if not loop.last -%}, 
            {%- endif %}
        {%- endfor %}
        ORDER BY __event_ts DESC
    ) AS row_rank,
    *
  FROM {{ history_model }}
 WHERE __load_ts <= (SELECT MAX(__load_ts) 
                       FROM {{ incremental_model }}) 
), 
ranked_rows_count AS (
SELECT COUNT(*) row_rank_count
  FROM snap
 WHERE row_rank = 1),
incremental_rows_count AS (
SELECT COUNT(*) incremental_row_count
  FROM {{ incremental_model }}
), 
final as (
SELECT
    row_rank_count,
    incremental_row_count,
    abs(row_rank_count - incremental_row_count) AS diff_count
  FROM ranked_rows_count
  CROSS JOIN incremental_rows_count
)
SELECT * 
  FROM final
{% endmacro %}