-
-
Notifications
You must be signed in to change notification settings - Fork 117
/
expect_column_values_to_be_increasing.sql
54 lines (48 loc) · 1.42 KB
/
expect_column_values_to_be_increasing.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
{% test expect_column_values_to_be_increasing(model, column_name,
sort_column=None,
strictly=True,
row_condition=None,
group_by=None) %}
{%- set sort_column = column_name if not sort_column else sort_column -%}
{%- set operator = ">" if strictly else ">=" -%}
with all_values as (
select
{{ sort_column }} as sort_column,
{%- if group_by -%}
{{ group_by | join(", ") }},
{%- endif %}
{{ column_name }} as value_field
from {{ model }}
{% if row_condition %}
where {{ row_condition }}
{% endif %}
),
add_lag_values as (
select
sort_column,
{%- if group_by -%}
{{ group_by | join(", ") }},
{%- endif %}
value_field,
lag(value_field) over
{%- if not group_by -%}
(order by sort_column)
{%- else -%}
(partition by {{ group_by | join(", ") }} order by sort_column)
{%- endif %} as value_field_lag
from
all_values
),
validation_errors as (
select
*
from
add_lag_values
where
value_field_lag is not null
and
not (value_field {{ operator }} value_field_lag)
)
select *
from validation_errors
{% endtest %}