Skip to content

DBT Test: Trader Group Check Seipcs Brazil Soy V2 6 1 2022 With Exporter Exporter Group True Exporter Group Not In Unknown Processed Domestically Exporter Year

DBT test name: trader_group_check_seipcs_brazil_soy_v2_6_1_2022_with_exporter_EXPORTER_GROUP__True__EXPORTER_GROUP_NOT_IN_UNKNOWN_PROCESSED_DOMESTICALLY___EXPORTER__YEAR

DBT details

Description

No description

Details

Models

Macros

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

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 AS trader,

        EXPORTER_GROUP AS group_name,

        YEAR::INT AS yy

    FROM "memory"."main"."seipcs_brazil_soy_v2_6_1_2022_with_exporter"
     WHERE EXPORTER_GROUP NOT IN ('UNKNOWN', 'PROCESSED DOMESTICALLY') 
),

-- Reference traders under their default name and under each of their labels
reference_traders AS (
    SELECT name AS trader, trader_node_id, groups
    FROM "memory"."main"."postgres_traders"
    UNION ALL
    SELECT l.label AS trader, pg.trader_node_id, pg.groups
    FROM "memory"."main"."postgres_traders" AS pg
    CROSS JOIN UNNEST(pg.labels) AS l(label)
),

-- 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
        trader 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,

    mt.trader AS unmatched_trader,

    -- 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 ) )
    )


    AND fg.trader = mt.trader

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