DBT Macro: Test Timeseries Outlier

DBT macro name: trase_duckdb.test_timeseries_outlier

DBT details

• Macro file: trase/data_pipeline/tests/generic/timeseries_outlier.sql

• Original dbt_docs

---

Description

No description

---

Details

Referenced ByMacro code

Data Tests

• brazil_soy_v2_7_0_supply_chain_volume_timeseries_outlier
• brazil_soy_v2_7_0_supply_chain_volume_product_timeseries_outlier
• brazil_soy_v2_7_0_supply_chain_fob_timeseries_outlier
• brazil_soy_v2_7_0_supply_chain_soy_deforestation_timeseries_outlier
• brazil_soy_v2_7_0_supply_chain_co2_gross_emissions_timeseries_outlier
• brazil_soy_v2_7_0_supply_chain_co2_net_emissions_timeseries_outlier
• brazil_soy_v2_7_0_supply_chain_land_use_timeseries_outlier
• brazil_soy_v2_7_0_supply_chain_soy_deforestation_def_year_timeseries_outlier
• brazil_soy_v2_7_0_supply_chain_co2_gross_emissions_def_year_timeseries_outlier
• brazil_soy_v2_7_0_supply_chain_co2_net_emissions_def_year_timeseries_outlier
• brazil_soy_v2_7_0_supply_chain_soy_deforestation_planting_year_timeseries_outlier
• brazil_soy_v2_7_0_supply_chain_co2_gross_emissions_planting_year_timeseries_outlier
• brazil_soy_v2_7_0_supply_chain_co2_net_emissions_planting_year_timeseries_outlier

{% test timeseries_outlier(model, expression, max_relative_change, group_by=[], time_column='year', exclude_periods=[], from_period=none) %}
with series as (
    select
        {%- for column in group_by %}
        {{ column }},
        {%- endfor %}
        {{ time_column }} as time_period,
        {{ expression }} as value
    from {{ model }}
    group by all
),

neighbours as (
    select
        *,
        lag(time_period) over periods as previous_period,
        lag(value) over periods as previous_value,
        lead(time_period) over periods as next_period,
        lead(value) over periods as next_value
    from series
    where value is not null
    window periods as (
        {% if group_by %}partition by {{ group_by | join(', ') }}{% endif %}
        order by time_period
    )
),

baselines as (
    select
        *,
        (
            coalesce(previous_value, next_value) + coalesce(next_value, previous_value)
        ) / 2 as baseline
    from neighbours
)

select
    *,
    value / nullif(baseline, 0) - 1 as relative_change
from baselines
where
    baseline is not null
    and abs(value - baseline) > {{ max_relative_change }} * abs(baseline)
    {%- if exclude_periods %}
    and time_period not in ({{ exclude_periods | join(', ') }})
    {%- endif %}
    {%- if from_period is not none %}
    and time_period >= {{ from_period }}
    {%- endif %}
{% endtest %}