forked from dbt-labs/dbt-utils
-
Notifications
You must be signed in to change notification settings - Fork 1
/
mutually_exclusive_ranges.sql
97 lines (80 loc) · 3.49 KB
/
mutually_exclusive_ranges.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
{% test mutually_exclusive_ranges(model, lower_bound_column, upper_bound_column, partition_by=None, gaps='allowed', zero_length_range_allowed=False) %}
{{ return(adapter.dispatch('test_mutually_exclusive_ranges', 'dbt_utils')(model, lower_bound_column, upper_bound_column, partition_by, gaps, zero_length_range_allowed)) }}
{% endtest %}
{% macro default__test_mutually_exclusive_ranges(model, lower_bound_column, upper_bound_column, partition_by=None, gaps='allowed', zero_length_range_allowed=False) %}
{% if gaps == 'not_allowed' %}
{% set allow_gaps_operator='=' %}
{% set allow_gaps_operator_in_words='equal_to' %}
{% elif gaps == 'allowed' %}
{% set allow_gaps_operator='<=' %}
{% set allow_gaps_operator_in_words='less_than_or_equal_to' %}
{% elif gaps == 'required' %}
{% set allow_gaps_operator='<' %}
{% set allow_gaps_operator_in_words='less_than' %}
{% else %}
{{ exceptions.raise_compiler_error(
"`gaps` argument for mutually_exclusive_ranges test must be one of ['not_allowed', 'allowed', 'required'] Got: '" ~ gaps ~"'.'"
) }}
{% endif %}
{% if not zero_length_range_allowed %}
{% set allow_zero_length_operator='<' %}
{% set allow_zero_length_operator_in_words='less_than' %}
{% elif zero_length_range_allowed %}
{% set allow_zero_length_operator='<=' %}
{% set allow_zero_length_operator_in_words='less_than_or_equal_to' %}
{% else %}
{{ exceptions.raise_compiler_error(
"`zero_length_range_allowed` argument for mutually_exclusive_ranges test must be one of [true, false] Got: '" ~ zero_length_range_allowed ~"'.'"
) }}
{% endif %}
{% set partition_clause="partition by " ~ partition_by if partition_by else '' %}
with window_functions as (
select
{% if partition_by %}
{{ partition_by }} as partition_by_col,
{% endif %}
{{ lower_bound_column }} as lower_bound,
{{ upper_bound_column }} as upper_bound,
lead({{ lower_bound_column }}) over (
{{ partition_clause }}
order by {{ lower_bound_column }}
) as next_lower_bound,
row_number() over (
{{ partition_clause }}
order by {{ lower_bound_column }} desc
) = 1 as is_last_record
from {{ model }}
),
calc as (
-- We want to return records where one of our assumptions fails, so we'll use
-- the `not` function with `and` statements so we can write our assumptions nore cleanly
select
*,
-- For each record: lower_bound should be < upper_bound.
-- Coalesce it to return an error on the null case (implicit assumption
-- these columns are not_null)
coalesce(
lower_bound {{ allow_zero_length_operator }} upper_bound,
false
) as lower_bound_{{ allow_zero_length_operator_in_words }}_upper_bound,
-- For each record: upper_bound {{ allow_gaps_operator }} the next lower_bound.
-- Coalesce it to handle null cases for the last record.
coalesce(
upper_bound {{ allow_gaps_operator }} next_lower_bound,
is_last_record,
false
) as upper_bound_{{ allow_gaps_operator_in_words }}_next_lower_bound
from window_functions
),
validation_errors as (
select
*
from calc
where not(
-- THE FOLLOWING SHOULD BE TRUE --
lower_bound_{{ allow_zero_length_operator_in_words }}_upper_bound
and upper_bound_{{ allow_gaps_operator_in_words }}_next_lower_bound
)
)
select * from validation_errors
{% endmacro %}