Skip to content

DBT Test: Check Bol Against Comtrade Gold Colombia Cd Coffee 2020 Col 0901 2101 Mass Tonnes 2020

DBT test name: check_bol_against_comtrade_gold_colombia_cd_coffee_2020_COL__0901_2101__mass_tonnes__2020

DBT details

Description

No description

Details

Models

Macros

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

-- 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"."gold_colombia_cd_coffee_2020"

),
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 net_weight_kg AS weight_kg
    FROM "memory"."main"."comtrade_exports_year_exporter_hs6"
    WHERE year = 2020
    AND country_of_export_iso = 'COL'
    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