autogen macro example

_loading_source.yml

version: 2


sources:
  - name: csv_load
    database: adinsmoor_sandbox_dev
    schema: loading_layer
    tables:
      - name: sw_line_input      
      - name: recipe    

build_mapping_sql.sql

{% macro build_mapping_sql( mapping_ref ) %}

{%- set mapping_query %}
    select distinct
    column_names, attribute_id
    from {{ mapping_ref }}
{% endset -%}

{%- set results = run_query(mapping_query) -%}

{%- if execute -%}
    {% set res_list = results.rows %}
{%- else -%}
    {% set res_list = [] %}
{%- endif -%}

{% for row in res_list %}
    {%- if row.COLUMN_NAMES is none -%}
        {{ row.ATTRIBUTE_ID }}
    {%- else -%}
        {{ row.COLUMN_NAMES }} as {{ row.ATTRIBUTE_ID.split('common_')[1] }}
    {%- endif -%}
    {% if not loop.last %},{% endif %}
{% endfor %}
{% endmacro %}

build_mapping_sql_case.sql

{% macro build_mapping_sql_case( mapping_ref ) %}
{#- this query self joins the recipe to look for rows with the same attribute name, 
    indicating there are values to map as well -#}
{%- set mapping_query %}
    select * 
    from {{ source('csv_load', 'recipe') }} recipe
    join ( 
        select ATTRIBUTE_ID, count(ATTRIBUTE_ID) as attribute_count from {{ source('csv_load', 'recipe') }} group by ATTRIBUTE_ID 
    ) r2 on recipe.ATTRIBUTE_ID = r2.ATTRIBUTE_ID
    order by recipe.ATTRIBUTE_ID
{% endset -%}

{%- set results = run_query(mapping_query) -%}

{%- if execute -%}
    {% set res_list = results.rows %}
{%- else -%}
    {% set res_list = [] %}
{%- endif -%}

{#- this looks quite complex, but its basically looping through the query results above 
    and building sql statements
    the |groupby() is to step through each attribute_id (column_name to map)
    if the column has a count over 1 then it has values we need to deal with a case statement -#}
{% for row_group in res_list|groupby('ATTRIBUTE_ID') %}
    {% for row in row_group.list %}
        {%- if row.ATTRIBUTE_COUNT > 1 %}
            {%- if loop.first %} CASE {% endif %}
                WHEN {{ row.COLUMN_NAMES }} = '{{ row.VALUE_MAPPINGS_OLD }}' THEN '{{ row.VALUE_MAPPINGS_NEW }}' 
            {%- if loop.last %} 
                END AS 
                {% if row.COLUMN_NAMES is none -%}
                    {{ row.ATTRIBUTE_ID }}
                {%- else -%}
                    {{ row.ATTRIBUTE_ID.split('common_')[1] }}
                {%- endif -%}
            {%- endif %}
        {% else %}
            {%- if row.COLUMN_NAMES is none -%}
                {{ row.ATTRIBUTE_ID }}
            {%- else -%}
                {{ row.COLUMN_NAMES }} as {{ row.ATTRIBUTE_ID.split('common_')[1] }}
            {%- endif -%}
        {%- endif %}
    {%- endfor %}  
    {%- if not loop.last %},{% endif %}
{%- endfor %}    
{% endmacro %}

stg_sw_line.sql

select
{{ build_mapping_sql (source('csv_load', 'recipe'))}}
from {{ source('csv_load', 'sw_line_input') }}

stg_sw_line_w_case.sql

select
{{ build_mapping_sql_case (source('csv_load', 'recipe'))}}
from {{ source('csv_load', 'sw_line_input') }}