Test validate_row_count_for_lattest_and_incremental src_product_management_product_history src_product_management_product_latest_incremental

test_validate_row_count_for_lattest_and_incremental_src_product_management_product_history_src_product_management_product_latest_incremental.sql

select
      count(*) as failures,
      count(*) != 0 as should_warn,
      count(*) != 0 as should_error
    from (
    WITH snap 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
 WHERE __load_ts <= (SELECT MAX(__load_ts) 
                       FROM dev_edw_dbt_incremental_model_test.dbt_src_product_management.src_product_management_product_latest_incremental) 
), 
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 dev_edw_dbt_incremental_model_test.dbt_src_product_management.src_product_management_product_latest_incremental
), 
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
    WHERE diff_count > 0
    ) dbt_internal_test