DBT Test: Reference Match Indonesia Wood Pulp V3 2 0 Spatial Entities Ref Indonesia Wood Pulp V3 2 0 Spatial Entities Left Year Parent Trase Id Trase Id

DBT test name: reference_match_indonesia_wood_pulp_v3_2_0_spatial_entities_ref_indonesia_wood_pulp_v3_2_0_spatial_entities___left__year___parent_trase_id_trase_id_

DBT details

• Kind: generic (reference_match)

• Test file: trase/data_pipeline/models/indonesia/wood_pulp/data_packages/indonesia_wood_pulp_v3_2_0/_schema.yml

• Original dbt_docs

• Tags: indonesia, indonesia_wood_pulp_v3_2_0_data_package, mock_model, wood_pulp

---

Description

No description

---

Details

Depends OnTest codeCompiled code

Models

• indonesia_wood_pulp_v3_2_0_spatial_entities

Macros

• test_reference_match
• get_where_subquery

{{ test_reference_match(**_dbt_generic_test_kwargs) }}{{ config(tags=['mock_model', 'indonesia', 'wood_pulp', 'indonesia_wood_pulp_v3_2_0_data_package'],alias="reference_match_indonesia_wood_125b24f1a11ec3a6d429cbd7c35a2b53") }}

    with unioned as (
        -- 2. Union distinct key combinations from both models
        -- Each row is tagged with its source ('model' or 'compare_to_model') to track its origin.

        -- Select distinct keys from the primary model
      select distinct

          year as year,

          parent_trase_id as parent_trase_id
        ,
        'model' as source_table
      from "memory"."main"."indonesia_wood_pulp_v3_2_0_spatial_entities"


      union all

      -- Select distinct keys from the comparison model
      select distinct

          year as year,

          trase_id as parent_trase_id
        ,
        'compare_to_model' as source_table
      from "memory"."main"."indonesia_wood_pulp_v3_2_0_spatial_entities"

    ),

    grouped as (
      -- 3. Group by the key columns to find unique occurrences
      -- Keys that appear only once (`occurrences = 1`) represent a mismatch between the models.
      select

          year,

          parent_trase_id,

        count(*) as occurrences,
        min(source_table) as source_table -- If occurrences = 1, this reveals the single source table
      from unioned
      group by

          year,

          parent_trase_id

    )

    -- 4. Select and classify the discrepancies based on the join_type
    -- The test fails if this query returns any rows.
    select

        year,

        parent_trase_id,

      case
        when source_table = 'compare_to_model'
        then 'missing from model' -- The key was only in the reference model
        when source_table = 'model'
        then 'missing from reference' -- The key was only in the primary model
      end as error_type
    from grouped
    where occurrences = 1

        -- For a 'left' join test, we only care if rows from the main `model` are missing from `compare_to`.
        -- These are rows that appear only once and have `source_table = 'model'`.
      and source_table = 'model'