-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathis_internal.sql
117 lines (99 loc) · 6.69 KB
/
is_internal.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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
{% macro is_internal(email=none,ip_address=none) -%}
{{ adapter.dispatch('is_internal', 'census_utils') (email,ip_address) }}
{%- endmacro %}
{% macro default__is_internal(email=none,ip_address=none) -%}
case
when 1 = 2 then true
{% if email == none and ip_address == none %}
{%- set error_message = '
The `is_internal` macro was called without an email or ip_address, so it has nothing to evaluate. The {}.{} model triggered this warning. \
'.format(model.package_name, model.name) -%}
{%- do exceptions.raise_compiler_error(error_message) -%}
{%- endif %}
{% if var("internal_domain", none) == none and var("internal_email_relation", none) == none and var("internal_ip_relation", none) == none %}
{%- set error_message = '
The `is_internal` macro was called without an internal email domain, internal email relation, or internal ip relation defined in your dbt_project.yml, so it has nothing to compare to. The {}.{} model triggered this warning. \
'.format(model.package_name, model.name) -%}
{%- do exceptions.raise_compiler_error(error_message) -%}
{%- endif %}
{% if email %}
{%- set exclusion = var("internal_email_column", 'email_address') ~ ' is not null' -%}
{% if var("internal_email_relation", false) %}
{%- set internal_email = dbt_utils.get_column_values(table=ref(var("internal_email_relation")), column=var("internal_email_column", 'email_address'), where=exclusion) -%}
when array_contains({{ email }}::variant, {{internal_email}}) then true
{%- endif %}
{% if var("internal_domain", false) %}
when {{ census_utils.extract_email_domain(email) }} in {{ var("internal_domain") }} then true
{%- endif %}
{%- endif %}
{% if ip_address and var("internal_ip_relation", false) %}
{%- set exclusion = var("internal_ip_column", 'ip_address') ~ ' is not null' -%}
{%- set internal_ip = dbt_utils.get_column_values(table=ref(var("internal_ip_relation")), column=var("internal_ip_column", 'ip_address'), where=exclusion) -%}
when array_contains({{ ip_address }}::variant, {{internal_ip}}) then true
{%- endif %}
else false end
{%- endmacro %}
{% macro bigquery__is_internal(email=none,ip_address=none) -%}
case
when 1 = 2 then true
{% if email == none and ip_address == none %}
{%- set error_message = '
The `is_internal` macro was called without an email or ip_address, so it has nothing to evaluate. The {}.{} model triggered this warning. \
'.format(model.package_name, model.name) -%}
{%- do exceptions.raise_compiler_error(error_message) -%}
{%- endif %}
{% if var("internal_domain", none) == none and var("internal_email_relation", none) == none and var("internal_ip_relation", none) == none %}
{%- set error_message = '
The `is_internal` macro was called without an internal email domain, internal email relation, or internal ip relation defined in your dbt_project.yml, so it has nothing to compare to. The {}.{} model triggered this warning. \
'.format(model.package_name, model.name) -%}
{%- do exceptions.raise_compiler_error(error_message) -%}
{%- endif %}
{% if email %}
{%- set exclusion = var("internal_email_column", 'email_address') ~ ' is not null' -%}
{% if var("internal_email_relation", false) %}
{%- set internal_email = dbt_utils.get_column_values(table=ref(var("internal_email_relation")), column=var("internal_email_column", 'email_address'), where=exclusion) -%}
when {{ email }} in unnest({{ internal_email }}) then true
{%- endif %}
{% if var("internal_domain", false) %}
when {{ census_utils.extract_email_domain(email) }} in {{ var("internal_domain") }} then true
{%- endif %}
{%- endif %}
{% if ip_address and var("internal_ip_relation", false) %}
{%- set exclusion = var("internal_ip_column", 'ip_address') ~ ' is not null' -%}
{%- set internal_ip = dbt_utils.get_column_values(table=ref(var("internal_ip_relation")), column=var("internal_ip_column", 'ip_address'), where=exclusion) -%}
when {{ ip_address }} in unnest({{internal_ip}}) then true
{%- endif %}
else false end
{% endmacro %}
{% macro redshift__is_internal(email=none,ip_address=none) -%}
case
when 1 = 2 then true
{% if email == none and ip_address == none %}
{%- set error_message = '
The `is_internal` macro was called without an email or ip_address, so it has nothing to evaluate. The {}.{} model triggered this warning. \
'.format(model.package_name, model.name) -%}
{%- do exceptions.raise_compiler_error(error_message) -%}
{%- endif %}
{% if var("internal_domain", none) == none and var("internal_email_relation", none) == none and var("internal_ip_relation", none) == none %}
{%- set error_message = '
The `is_internal` macro was called without an internal email domain, internal email relation, or internal ip relation defined in your dbt_project.yml, so it has nothing to compare to. The {}.{} model triggered this warning. \
'.format(model.package_name, model.name) -%}
{%- do exceptions.raise_compiler_error(error_message) -%}
{%- endif %}
{% if email %}
{%- set exclusion = var("internal_email_column", 'email_address') ~ ' is not null' -%}
{% if var("internal_email_relation", false) %}
{%- set internal_email = dbt_utils.get_column_values(table=ref(var("internal_email_relation")), column=var("internal_email_column", 'email_address'), where=exclusion)|join(',') -%}
when charindex({{ email }},'{{internal_email}}') > 0 then true
{%- endif %}
{% if var("internal_domain", false) %}
when {{ census_utils.extract_email_domain(email) }} in {{ var("internal_domain") }} then true
{%- endif %}
{%- endif %}
{% if ip_address and var("internal_ip_relation", false) %}
{%- set exclusion = var("internal_ip_column", 'ip_address') ~ ' is not null' -%}
{%- set internal_ip = dbt_utils.get_column_values(table=ref(var("internal_ip_relation")), column=var("internal_ip_column", 'ip_address'), where=exclusion)|join(',') -%}
when charindex({{ ip_address }}, '{{internal_ip}}') > 0 then true
{%- endif %}
else false end
{% endmacro %}