Skip to content

DBT Test: Database Contains All Indonesian Provinces

DBT test name: database_contains_all_indonesian_provinces

DBT details

Description

No description

Details

Models

Macros

{{ test_reference_match(**_dbt_generic_test_kwargs) }}

    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

          trase_id::text as trase_idtext,

          name as name
        ,
        'model' as source_table
      from "trase_production"."main"."postgres_regions_without_geometry"

      where country = 'INDONESIA' and level = 3


      union all

      -- Select distinct keys from the comparison model
      select distinct

          'ID-' || prov_code::int::text as trase_idtext,

          prov as name
        ,
        'compare_to_model' as source_table
      from "trase_production"."main"."indonesia_kabupaten_boundaries_simplified_2023"

    ),

    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

          trase_idtext,

          name,

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

          trase_idtext,

          name

    )

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

        trase_idtext,

        name,

      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 'right' join test, we only care if rows from `compare_to` are missing from the main `model`.
        -- These are rows that appear only once and have `source_table = 'compare_to_model'`.
      and source_table = 'compare_to_model'