DBT Test: Check Trader Groups Gold Uganda Coffee 2020 Exporter Group Name 2020

DBT test name: check_trader_groups_gold_uganda_coffee_2020_exporter_group_name__2020

DBT details

• Kind: generic (check_trader_groups)

• Test file: trase/data_pipeline/models/uganda/trade/bol/2020/gold/_schema.yml

• Original dbt_docs

---

Description

No description

---

Details

Depends OnTest codeCompiled code

Models

• gold_uganda_coffee_2020
• postgres_traders

Macros

• test_check_trader_groups
• get_where_subquery

{{ test_check_trader_groups(**_dbt_generic_test_kwargs) }}{{ config(alias="check_trader_groups_gold_ugand_fbf8c4611933e92e4035a0c9c61010ed") }}

WITH trader_groups AS (
    SELECT
        UNNEST(groups) AS company_group
    FROM "trase_production"."main"."postgres_traders"
),
flattened_groups AS (
    SELECT
        json_extract_string(company_group, '$.group')::VARCHAR AS group_name,
        CASE
            WHEN json_extract_string(company_group, '$.time_start') = 'null' THEN NULL
            ELSE json_extract_string(company_group, '$.time_start')::TIMESTAMP
        END AS time_start,
        CASE
            WHEN json_extract_string(company_group, '$.time_end') = 'null' THEN NULL
            ELSE json_extract_string(company_group, '$.time_end')::TIMESTAMP
        END AS time_end
    FROM
        trader_groups
),
model_groups AS (
    SELECT
        DISTINCT exporter_group_name AS group_name
    FROM "trase_production"."main_uganda_coffee"."gold_uganda_coffee_2020"
),
matching_groups AS (
    SELECT
        model.group_name AS model_group_name,
        fg.group_name AS official_group_name
    FROM model_groups AS model
    LEFT JOIN 
        flattened_groups AS fg
    ON model.group_name = fg.group_name
    WHERE
        ( fg.time_start IS NULL AND fg.time_end IS NULL )
    OR
        ( YEAR(fg.time_start) <= 2020
            AND ( YEAR(fg.time_end) >= 2020 OR YEAR(fg.time_end) IS NULL ) )
)

SELECT model_group_name AS unmatched_group_name 
FROM matching_groups
WHERE 
    official_group_name IS NULL
    OR model_group_name IS NULL
    OR LEN(model_group_name) = 0