DBT Test: Check Bol Against Comtrade Pre Gold Cote Divoire Coffee 2020 Hs6 Alternative Quantity Civ 0901 2101 Mass Tonnes 2020

DBT test name: check_bol_against_comtrade_pre_gold_cote_divoire_coffee_2020_hs6__alternative_quantity__CIV__0901_2101__mass_tonnes__2020

DBT details

• Kind: generic (check_bol_against_comtrade)

• Test file: trase/data_pipeline/models/cote_divoire/coffee/trade/_schema_cote_divoire_coffee.yml

• Original dbt_docs

---

Description

No description

---

Details

Depends OnTest codeCompiled code

Models

• pre_gold_cote_divoire_coffee_2020
• comtrade_exports_year_exporter_hs6

Macros

• test_check_bol_against_comtrade
• get_where_subquery

{{ test_check_bol_against_comtrade(**_dbt_generic_test_kwargs) }}{{ config(warn_if=">=2",error_if=">=10",alias="check_bol_against_comtrade_pre_c2d25657ba1a4edb003a1d6e07a49db9") }}

-- Requires to set up an error/warning threshold when calling




WITH bol AS (
    SELECT
        SUM(mass_tonnes) AS total_bol_net_weight
    FROM "memory"."main"."pre_gold_cote_divoire_coffee_2020"

    WHERE (

        hs6 LIKE '0901%' OR

        hs6 LIKE '2101%' 

    )

),
comtrade_country AS (
    -- comtrade_weight_field defaults to net_weight_kg but can be set to
    -- alternative_quantity for countries that report weights in that field instead
    SELECT alternative_quantity AS weight_kg
    FROM "memory"."main"."comtrade_exports_year_exporter_hs6"
    WHERE year = 2020
    AND country_of_export_iso = 'CIV'
    AND (

        commodity_code LIKE '0901%' OR

        commodity_code LIKE '2101%' 

    )
),
comtrade_total AS (
    SELECT
        -- weight_kg is in kg (whether net_weight_kg or alternative_quantity); convert to tonnes
        SUM(weight_kg) / 1000 AS total_comtrade_net_weight
    FROM comtrade_country
),
-- Failure calculation thresholds only work against integers, so we round
failure_calculation AS (
    SELECT
        bol.total_bol_net_weight::int64 AS bol_net_weight,
        comtrade_total.total_comtrade_net_weight::int64 AS comtrade_net_weight,
        ABS(1 - (bol.total_bol_net_weight / NULLIF(comtrade_total.total_comtrade_net_weight, 0))) * 100 AS percentage_difference,
        COALESCE(
            ROUND(
                ABS(1 - (bol.total_bol_net_weight / NULLIF(comtrade_total.total_comtrade_net_weight, 0))) * 100
            )::int,
            100
        ) AS rounded_percentage_difference
    FROM bol, comtrade_total
)

SELECT * FROM failure_calculation