Test validate_row_rank_and_incremental_query_result src_product_management_product_history src_product_management_product_latest_incremental

test_validate_row_rank_and_incremental_query_result_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 ranked_rows 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) 
), 
row_rank_as_one AS
(
SELECT HASH_AGG(*) row_rank_hash_agg_value
  FROM(
  SELECT "PRODUCT_ID" ,"PRODUCT_NAME" ,"PRICE_PER_UNIT" ,"BASIC_UNIT" ,"IS_STOCK_LIMITED" ,"IS_ACTIVE_FOR_SALE" ,"__EVENT_TS" ,"__LOAD_TS" 
    FROM ranked_rows
   WHERE row_rank = 1 
  )
), 
incremental_rows AS (
SELECT HASH_AGG(*) incremental_hash_agg_value
  FROM (
  SELECT 
    "PRODUCT_ID" ,"PRODUCT_NAME" ,"PRICE_PER_UNIT" ,"BASIC_UNIT" ,"IS_STOCK_LIMITED" ,"IS_ACTIVE_FOR_SALE" ,"__EVENT_TS" ,"__LOAD_TS" 
  FROM dev_edw_dbt_incremental_model_test.dbt_src_product_management.src_product_management_product_latest_incremental
  )
), 
final as (
SELECT
    row_rank_hash_agg_value,
    incremental_hash_agg_value,
    CASE WHEN row_rank_hash_agg_value = incremental_hash_agg_value THEN 0 ELSE 1 END result
  FROM row_rank_as_one
  CROSS JOIN incremental_rows
)
SELECT * 
  FROM final
    WHERE result > 0
    ) dbt_internal_test