DBT Test: Check Trader Groups Diet Trase Coffee 2020 External Exporter Group 2020

DBT test name: check_trader_groups_diet_trase_coffee_2020_external_exporter_group__2020

DBT details

• Kind: generic (check_trader_groups)

• Test file: trase/data_pipeline/models/diet_trase/_schema.yml

• Original dbt_docs

---

Description

No description

---

Details

Depends OnTest codeCompiled code

Models

• diet_trase_coffee_2020_external
• postgres_traders

Macros

• test_check_trader_groups
• get_where_subquery

{{ test_check_trader_groups(**_dbt_generic_test_kwargs) }}{{ config(severity="warn",alias="check_trader_groups_diet_trase_d19056da85eccf369dbf114d73310262") }}

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 AS group_name
    FROM "trase_production"."main"."diet_trase_coffee_2020_external"
),
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