DBT Test: Check Id Palm Unknown Country Seipcs Indonesia Palm Oil 2021 Km 0 Mgd 30 Patched

DBT test name: check_id_palm_unknown_country_seipcs_indonesia_palm_oil_2021_km_0_mgd_30_patched_

DBT details

• Kind: generic (check_id_palm_unknown_country)

• 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_unknown_country
• get_where_subquery

{{ test_check_id_palm_unknown_country(**_dbt_generic_test_kwargs) }}{{ config(alias="check_id_palm_unknown_country__a445135e89525d7ae7924bbac0783f55") }}

/*
 * This test fails if any flows of Indonesia palm oil going to UNKNOWN do not
 * have the correct values. For speed the test will return only return a single row for
 * each test with a placeholder value.
 */

/* ------------------------------------------------------------------------------------
Our first test is that the country column contains the value
UNKNOWN at least once
TODO - could run this test once per year?
------------------------------------------------------------------------------------- */
    select 'no such country=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 country = 'UNKNOWN'
    )
/* ------------------------------------------------------------------------------------
Our second test is that the importer etc. have specific values if and only if the key is
'DOMESTIC'
------------------------------------------------------------------------------------- */
union all
(
    select 'some columns have incorrect values' as error
    from "trase_production"."main_indonesia_palm"."seipcs_indonesia_palm_oil_2021_km_0_mgd_30_patched"
    where
        year >= 2014 and
        (
            -- if country is UNKNOWN then importer, exporter and refinery should be too
            country = 'UNKNOWN' and not
            (
                      importer = 'UNKNOWN' and
                importer_group = 'UNKNOWN' and
                      exporter = 'UNKNOWN' and
                exporter_group = 'UNKNOWN' and
                      refinery = 'UNKNOWN' and
             refinery_trase_id = 'ID-PALM-REFINERY-X' and
                refinery_group = 'UNKNOWN'
            )
        )
    limit 1
)