DBT Test: Check Bol Against Comtrade Gold India Trade Coffee 2020 Ind 0901 2101 Mass Tonnes 2020

DBT test name: check_bol_against_comtrade_gold_india_trade_coffee_2020_IND__0901_2101__mass_tonnes__2020

DBT details

• Kind: generic (check_bol_against_comtrade)

• Test file: trase/data_pipeline/models/india/trade/bol/coffee/_schema_india_coffee.yml

• Original dbt_docs

---

Description

No description

---

Details

Depends OnTest codeCompiled code

Models

• gold_india_trade_coffee_2020

Sources

• comtrade

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

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


WITH bol AS (
    SELECT
        SUM(mass_tonnes) AS total_bol_net_weight
    FROM "trase_production"."main_india_coffee"."gold_india_trade_coffee_2020"
),
comtrade_country AS (
    SELECT netWgt
    FROM
        read_parquet('s3://trase-storage/world/trade/statistical_data/comtrade/comtrade_monthly/*/*/*.parquet', hive_partitioning = true)
    WHERE refYear = 2020
    AND reporterISO = 'IND'
    AND (

        cmdCode LIKE '0%' OR

        cmdCode LIKE '9%' OR

        cmdCode LIKE '0%' OR

        cmdCode LIKE '1%' OR

        cmdCode LIKE ',%' OR

        cmdCode LIKE '2%' OR

        cmdCode LIKE '1%' OR

        cmdCode LIKE '0%' OR

        cmdCode LIKE '1%' 

    )
),
comtrade_total as (
    select
        -- Sum the net weight and convert it to tonnes
        sum(NetWgt)/1000 as total_comtrade_net_weight
    from
        comtrade_country
),
-- Failure calculation thresholds seem to only work against integers, therefore we round them
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 / comtrade_total.total_comtrade_net_weight))*100 as percentage_difference,
        ROUND(
            (abs(1 - (bol.total_bol_net_weight / comtrade_total.total_comtrade_net_weight))*100)
        )::int as rounded_percentage_difference
    from
        bol,
        comtrade_total
)

select * from failure_calculation