Skip to content

DBT Test: Trader Group Check Gold Colombia Cd Coffee 2020 Exporter Group Name Year

DBT test name: trader_group_check_gold_colombia_cd_coffee_2020_exporter_group_name__year

DBT details

Description

No description

Details

Models

Macros

{{ test_trader_group_check(**_dbt_generic_test_kwargs) }}{{ config(alias="trader_group_check_gold_colomb_7b8b29df1b50f2e2d226a774eefcee62") }}

WITH
-- Model slice under test: distinct ([trader,] group_name, year|date) rows from the model.
-- Optional row_condition narrows the rows considered by the test.
model_traders AS (
    SELECT DISTINCT

        exporter_group_name AS group_name,

        year::INT AS yy

    FROM "memory"."main"."gold_colombia_cd_coffee_2020"

),

reference_traders AS (
    SELECT name, trader_node_id, groups
    FROM "memory"."main"."postgres_traders"
),

-- Reference unnest: each company_group entry becomes a row with parsed period.
-- time_start/time_end used for date matching; year_start/year_end for year matching.
flattened_groups AS (
    SELECT
        name AS trader,
        json_extract_string(t.company_group, '$.group')::VARCHAR AS group_name,
        -- Raw timestamps from reference (nullable when not present)
        CASE
            WHEN json_extract_string(t.company_group, '$.time_start') = 'null' THEN NULL
            ELSE json_extract_string(t.company_group, '$.time_start')::TIMESTAMP
        END AS time_start,
        CASE
            WHEN json_extract_string(t.company_group, '$.time_end') = 'null' THEN NULL
            ELSE json_extract_string(t.company_group, '$.time_end')::TIMESTAMP
        END AS time_end,
        -- Year boundaries for matching:
        -- year_start NULL => open start (applies from the beginning)
        -- year_end NULL   => open end   (applies indefinitely)
        CASE
            WHEN json_extract_string(t.company_group, '$.time_start') = 'null' THEN NULL
            ELSE EXTRACT(YEAR FROM json_extract_string(t.company_group, '$.time_start')::TIMESTAMP)
        END AS year_start,
        CASE
            WHEN json_extract_string(t.company_group, '$.time_end') = 'null' THEN NULL
            ELSE EXTRACT(YEAR FROM json_extract_string(t.company_group, '$.time_end')::TIMESTAMP)
        END AS year_end
    FROM
        reference_traders AS pg
    CROSS JOIN UNNEST(groups) AS t(company_group)
)
-- Return only non-matches: LEFT JOIN + IS NULL (anti-join).
-- The join allows a match when:
--   1) No period defined at all within a group (applies to all years),
--   2) Bounded window: year_start <= yy <= year_end (or open end),
--   3) Open start: year_start is NULL and yy <= year_end (or open end).
SELECT DISTINCT
    mt.group_name AS unmatched_group_name,

    -- Aggregate all unmatched years/dates per (group[, trader]) for context

    ARRAY_AGG(DISTINCT mt.yy ORDER BY mt.yy DESC) AS unmatched_years

FROM model_traders AS mt
LEFT JOIN
    flattened_groups AS fg
ON mt.group_name = fg.group_name

    AND (
        -- 1) No period at all: applies to any year
        ( fg.time_start IS NULL AND fg.time_end IS NULL )
        OR
        -- 2) Bounded window or open end: [year_start, year_end]
        ( mt.yy >= fg.year_start
            AND ( mt.yy <= fg.year_end OR fg.year_end IS NULL ) )
        OR
        -- 3) Open start: (-inf, year_end]
        ( fg.year_start IS NULL
            AND ( mt.yy <= fg.year_end OR fg.year_end IS NULL ) )
    )


WHERE fg.group_name IS NULL
GROUP BY ALL
ORDER BY unmatched_group_name