DBT Macro: Test Aggregate Match
DBT macro name: trase_duckdb.test_aggregate_match
DBT details
Description
No description
Details
Data Tests
Macros
slugify
{% test aggregate_match(
model,
compare_to,
group_on,
compare_on,
atol=0,
rtol=0.000001,
where_from=None,
where_to=None,
column_name=None
) %}
{#- 1. Parse `group_on` and `compare_on`. Both take the same three forms as
`reference_match`'s `match_on`, so they are parsed by the same loop. -#}
{% set group_columns = [] %}
{% set compare_columns = [] %}
{% for argument_name, entries, parsed in [
('group_on', group_on, group_columns),
('compare_on', compare_on, compare_columns)
] %}
{% for pair in entries %}
{% if (pair.model is defined) and (pair.ref is defined) %}
{% set columns = [pair.model, pair.ref] %}
{% elif pair is sequence and pair is not string and (pair|length) >= 2 %}
{% set columns = [pair[0], pair[1]] %}
{% elif pair is string %}
{% set columns = [pair, pair] %}
{% else %}
{{ exceptions.raise_compiler_error(
"aggregate_match: Each entry of `" ~ argument_name ~ "` must be {model: <col>, ref: <col>}, [model_col, ref_col], or <col> for same-name columns. Got: " ~ pair
) }}
{% endif %}
{% do parsed.append({
'model': columns[0],
'compare_to': columns[1],
'alias': dbt_utils.slugify(columns[0])
}) %}
{% endfor %}
{% endfor %}
{% if compare_columns | length == 0 %}
{{ exceptions.raise_compiler_error(
"aggregate_match: `compare_on` is empty, so there is nothing to compare."
) }}
{% endif %}
{#- Both lists are aliased to their slugified model-side name, so a name used in
both would collide inside the aggregates below. -#}
{% set group_aliases = group_columns | map(attribute='alias') | list %}
{% for column in compare_columns if column.alias in group_aliases %}
{{ exceptions.raise_compiler_error(
"aggregate_match: '" ~ column.alias ~ "' appears in both `group_on` and `compare_on`. A column can be grouped by or summed, not both."
) }}
{% endfor %}
{% set group_by = range(1, group_columns | length + 1) | join(', ') %}
with
{# 2. Aggregate each model to the `group_on` grain #}
model_aggregate as (
select
{% for column in group_columns %}
{{ column.model }} as {{ column.alias }},
{% endfor %}
{% for column in compare_columns %}
sum({{ column.model }}) as {{ column.alias }}{% if not loop.last %},{% endif %}
{% endfor %}
from {{ model }}
{% if where_from %}
where {{ where_from }}
{% endif %}
{% if group_columns %}
group by {{ group_by }}
{% endif %}
),
compare_to_aggregate as (
select
{% for column in group_columns %}
{{ column.compare_to }} as {{ column.alias }},
{% endfor %}
{% for column in compare_columns %}
sum({{ column.compare_to }}) as {{ column.alias }}{% if not loop.last %},{% endif %}
{% endfor %}
from {{ compare_to }}
{% if where_to %}
where {{ where_to }}
{% endif %}
{% if group_columns %}
group by {{ group_by }}
{% endif %}
),
{# 3. Line the two up. A group present on only one side comes back from the
full outer join as NULL, which coalesces to a total of 0 and so fails
against whatever the other side holds. #}
combined as (
select
{% for column in group_columns %}
coalesce(model_aggregate.{{ column.alias }}, compare_to_aggregate.{{ column.alias }}) as {{ column.alias }},
{% endfor %}
{% for column in compare_columns %}
coalesce(model_aggregate.{{ column.alias }}, 0) as {{ column.alias }}_model,
coalesce(compare_to_aggregate.{{ column.alias }}, 0) as {{ column.alias }}_compare_to,
coalesce(model_aggregate.{{ column.alias }}, 0)
- coalesce(compare_to_aggregate.{{ column.alias }}, 0) as {{ column.alias }}_difference{% if not loop.last %},{% endif %}
{% endfor %}
from model_aggregate
full outer join compare_to_aggregate
on
{% if group_columns %}
{# `is not distinct from` rather than `=`, so that groups keyed on a
NULL match each other instead of dropping out of the join #}
{% for column in group_columns %}
model_aggregate.{{ column.alias }} is not distinct from compare_to_aggregate.{{ column.alias }}{% if not loop.last %} and{% endif %}
{% endfor %}
{% else %}
{# no `group_on`: one row of grand totals on each side #}
true
{% endif %}
)
{# 4. Anything outside tolerance fails the test #}
select *
from combined
where
{% for column in compare_columns %}
{% if not loop.first %}or {% endif %}abs({{ column.alias }}_difference)
> {{ atol }} + {{ rtol }} * abs({{ column.alias }}_compare_to)
{% endfor %}
{% endtest %}