DBT Test: Check Id Palm Missing Importer Seipcs Indonesia Palm Oil 2021 Km 0 Mgd 30 Patched

DBT test name: check_id_palm_missing_importer_seipcs_indonesia_palm_oil_2021_km_0_mgd_30_patched_

DBT details

• Kind: generic (check_id_palm_missing_importer)

• Test file: trase/data_pipeline/models/indonesia/palm_oil/sei_pcs/_schema.yml

• Original dbt_docs

---

Description

No description

---

Details

Depends OnTest codeCompiled code

Models

• seipcs_indonesia_palm_oil_2021_km_0_mgd_30_patched

Macros

• test_check_id_palm_missing_importer
• get_where_subquery

{{ test_check_id_palm_missing_importer(**_dbt_generic_test_kwargs) }}{{ config(alias="check_id_palm_missing_importer_130826c3bb9397e2b6720f96d5a3b89c") }}

/* This test checks that we are using UNKNOWN for missing values of the importer. We
   assume that we have at least _some_ missing values in the data, so the test also
   fails if it cannot find the UNKNOWN importer
*/

/* ------------------------------------------------------------------------------------
Our first test is that the importer column contains the value UNKNOWN at least once.
TODO - could run this test once per year?
------------------------------------------------------------------------------------- */
    select 'no such row with importer=UNKNOWN' as error
    where not exists (
        select 1
        from "trase_production"."main_indonesia_palm"."seipcs_indonesia_palm_oil_2021_km_0_mgd_30_patched"
        where importer = 'UNKNOWN'
    )
/* ------------------------------------------------------------------------------------
Our second test is as above, but for importer_group
------------------------------------------------------------------------------------- */
union all
    select 'no such row with importer_group=UNKNOWN' as error
    where not exists (
        select 1
        from "trase_production"."main_indonesia_palm"."seipcs_indonesia_palm_oil_2021_km_0_mgd_30_patched"
        where importer_group = 'UNKNOWN'
    )
/* ------------------------------------------------------------------------------------
Our third test is that there are no rows where importer or importer_group take other
missing values (i.e. null or NONE)
------------------------------------------------------------------------------------- */
union all
(
    select 'some rows have importer/importer_group null or NONE' as error
    from "trase_production"."main_indonesia_palm"."seipcs_indonesia_palm_oil_2021_km_0_mgd_30_patched"
    where
        importer       = 'NONE' or
        importer       is null  or
        importer_group = 'NONE' or
        importer_group is null
    limit 1
)
/* ------------------------------------------------------------------------------------
Our fourth test is that the UNKNOWN importer has importer group UNKNOWN
------------------------------------------------------------------------------------- */
union all
(
    select 'some rows have importer UNKNOWN but importer_group not UNKNOWN' as error
    from "trase_production"."main_indonesia_palm"."seipcs_indonesia_palm_oil_2021_km_0_mgd_30_patched"
    where importer = 'UNKNOWN' and (importer_group is null or importer_group <> 'UNKNOWN')
    limit 1
)