DBT Test: Check Id Palm Unknown Country Indonesia Palm Oil Sei Pcs V1 3 Seipcs 2021

DBT test name: check_id_palm_unknown_country_indonesia_palm_oil_sei_pcs_v1_3_seipcs_2021_

DBT details

• Kind: generic (check_id_palm_unknown_country)

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

• Original dbt_docs

---

Description

No description

---

Details

Depends OnTest codeCompiled code

Models

• indonesia_palm_oil_sei_pcs_v1_3_seipcs_2021

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__59107b604943553cf1e5173f0ceeb3ce") }}

/*
 * 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 "memory"."main"."indonesia_palm_oil_sei_pcs_v1_3_seipcs_2021"
        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 "memory"."main"."indonesia_palm_oil_sei_pcs_v1_3_seipcs_2021"
    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'
            )
        )
)