-
Notifications
You must be signed in to change notification settings - Fork 0
/
clean.sql
38 lines (33 loc) · 1.9 KB
/
clean.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
{%- macro clean(field, destination='facebook',type='name') %}
{%- if type == 'name' -%}
trim(lower({{ field }}))
{%- elif type == 'email' and destination == 'facebook' -%}
trim(lower({{ field }}))
{%- elif type == 'email' and destination == 'google' -%}
trim(lower(
case when {{ census_utils.extract_email_domain(field) }} = 'gmail.com' or {{ census_utils.extract_email_domain(field) }} = 'googlemail.com'
then replace(replace({{ field }}, {{ census_utils.extract_email_domain(field) }},''),'.','') || {{ census_utils.extract_email_domain(field) }}
else {{ field }}
end
))
{%- elif type == 'country' and destination == 'google' -%}
{{ census_utils.get_country_code(field) }}
{%- elif type == 'country' and destination == 'facebook' -%}
lower({{ census_utils.get_country_code(field) }})
{%- elif type == 'city' and destination == 'facebook' -%}
regexp_replace(translate(lower({{ field }}), 'ůțąðěřšžųłşșýźľňèéëêēėęàáâäæãåāîïíīįìôöòóœøōõûüùúūñńçćč','utaoerszutssyzlneeeeeeaaaaaaaaiiiiiioooooooouuuuunnccc'),'[^a-z]','')
{%- elif type == 'city' and destination == 'google' -%}
trim(lower({{ field }}))
{%- elif type == 'zip' and destination == 'facebook' -%}
{# Google can accept 9 digit zip codes but Facebook cannot. #}
trim(left({{ field }}, 5))
{%- elif type == 'zip' and destination == 'google' -%}
trim({{ field }})
{% else %}
{%- set error_message = '
Warning: the `clean` macro does not accept the combination of destination {} and type {}. The {}.{} model triggered this warning. \
'.format(destination, type, model.package_name, model.name) -%}
{%- do exceptions.raise_compiler_error(error_message) -%}
{{ field }}
{% endif %}
{%- endmacro -%}