Coalesce Internal Macros

Unexpected Behavior

Editing internal macros can cause unexpected behavior.

Using Internal Macros

To use an internal macro, add the macro into the Macros in Build Settings. You can make any edits you want, but you need to keep the name of the macro the same.

get_source_transform()

Gets the source column transform for a node’s columns.

get_source_transform

{%- macro get_source_transform(col) -%}
    {% if col.hashDetails %}
        {{ hash(col.hashDetails.columns, algo=col.hashDetails.algorithm, datatype=col.dataType) }}
    {% elif col.transform | trim != '' %}
        {{ col.transform }}
    {% elif col.sourceColumns[0].node and col.sourceColumns[0].node.name and col.sourceColumns[0].column and col.sourceColumns[0].column.name %}
        "{{ col.sourceColumns[0].node.name }}"."{{ col.sourceColumns[0].column.name }}"
    {% else %}
        NULL
    {% endif %}
{% endmacro -%}
[12:09 PM] {% macro test_stage(stage_name, continue_on_failure=true, type="sql") %}
---
coalesce_stage_start
name: {{ stage_name }}
type: {{ type }}Test
continue_on_failure: {{ continue_on_failure }}
code:
{% endmacro -%}

stage()

Defines the individual stages (steps) on node SQL execution.

stage

{% macro stage(stage_name, continue_on_failure=true, type="sql") %}
---
coalesce_stage_start
name: {{ stage_name }}
type: {{ type }}
continue_on_failure: {{ continue_on_failure }}
code:
{% endmacro -%}

get_value_by_column_attributes()

Filters columns in a node to retrieve only ones that match a provided attribute.

get_value_by_column_attributes

{%- macro get_value_by_column_attribute(column_attribute, value="name") -%}
    {%- set filtered_columns_by_attribute = columns | selectattr(column_attribute) | list -%}
    {%- if filtered_columns_by_attribute | length > 0 -%}
        {{- (filtered_columns_by_attribute | first)[value] -}}
    {%- else -%}
        ## ERR: COLUMN_NOT_FOUND_WITH_ATTRIBUTE_{{ column_attribute }} ##
    {%- endif -%}
{%- endmacro -%}

hash()

Creates a hash of a certain column using a selected algorithm.

hash()

{%- macro hash(columns, algo='MD5', delimiter='||', datatype='CHAR(32)') -%}
    {% for col in columns %}
        {# set algo function #}
        {% set algo_start = algo + '(' %}
        {% set algo_end = ')' %}
        {% if algo | upper == 'SHA256' %}
            {% set algo_start = 'SHA2(' %}
            {% set algo_end = ', 256)' %}
        {% endif %}  
        {# end set algo function #}

        {%- if loop.first %}CAST( {{ algo_start }}{% endif -%}
            NVL(CAST(
                {%  if col is string %} 
                    {{ col }}
                {% elif col.transform | trim != '' %}
                    {{ col.transform }}
                {% elif col.sourceColumns[0] %} 
                    "{{ col.sourceColumns[0].node.name }}"."{{ col.sourceColumns[0].column.name }}"
                {% else %}
                    null
                {%  endif %} 
            AS VARCHAR), 'null')
        {%- if not loop.last %} || {% if delimiter != '' %} '{{ delimiter }}' || {% endif -%} {% endif -%}
        {%- if loop.last %}{{ algo_end }} AS {{ datatype }} ){% endif -%}
    {% endfor %}
{%- endmacro -%}

test_stage()

Use the test stage to expand your node testing capabilities.

Learn more in How to Use the Coalesce Test Stage Macro.

{%- macro get_source_transform(col) -%}
    {% if col.hashDetails %}
        {{ hash(col.hashDetails.columns, algo=col.hashDetails.algorithm, datatype=col.dataType) }}
    {% elif col.transform | trim != '' -%}
        {{- col.transform -}}
    {% elif col.sourceColumns[0].node and col.sourceColumns[0].node.name and col.sourceColumns[0].column and col.sourceColumns[0].column.name -%}
        "{{- col.sourceColumns[0].node.name }}"."{{ col.sourceColumns[0].column.name -}}"
    {%- else -%}
        NULL
    {% endif %}
{%- endmacro -%}

ref_no_link()

Creates a fully qualified name reference to the storage location mapping of another node without an explicit link in the node graph.

Learn more about Ref Functions.

ref_no_link

{%- macro ref_no_link(location_name, node_name) -%}
        {%- set foundMapping = { 'flag': false } -%}
        {%- for storage in nodeMetadata.mapping -%}
            {%- if nodeMetadata.mapping[storage].locationName ==  location_name -%}
                {%- if foundMapping.update({'flag': true}) -%}{%- endif -%}
                {{-  location_name | export_ref(node_name, ref_type='ref_no_link') -}}
                "{{- nodeMetadata.mapping[storage].database -}}"."{{- nodeMetadata.mapping[storage].schema -}}"."{{- node_name -}}"
            {%- endif -%}
        {%- endfor -%}
        {%- if not foundMapping.flag -%}
            {{-  location_name | invalid_export_ref(node_name, ref_type='ref_no_link') -}}
        {%- endif -%}
    {%- endmacro -%}

ref_link()

Resolves to a fully qualified name reference to the storage location mapping of a node with an explicit link in the node graph without creating a string in the rendered template.

Learn more about Ref Functions.

ref_link

{%- macro ref_link(location_name, node_name) -%}
        {%- set foundMapping = { 'flag': false } -%}
        {%- for storage in nodeMetadata.mapping -%}
            {%- if nodeMetadata.mapping[storage].locationName ==  location_name -%}
                {%- if foundMapping.update({'flag': true}) -%}{%- endif -%}
                {{-  location_name | export_ref(node_name, ref_type='ref_link') -}}
            {%- endif -%}
        {%- endfor -%}
        {%- if not foundMapping.flag -%}
            {{-  location_name | invalid_export_ref(node_name, ref_type='ref_link') -}}
        {%- endif -%}
    {%- endmacro -%}

ref()

Resolves to a fully qualified name reference to the storage location mapping of a node with an explicit link in the node graph.

Learn more about Ref Functions.

ref

{%- macro ref(location_name, node_name) -%}
        {%- set foundMapping = { 'flag': false } -%}
        {%- for storage in nodeMetadata.mapping -%}
            {%- if nodeMetadata.mapping[storage].locationName ==  location_name -%}
                {%- if foundMapping.update({'flag': true}) -%}{%- endif -%}
                {{-  location_name | export_ref(node_name, ref_type='ref') -}}
                "{{- nodeMetadata.mapping[storage].database -}}"."{{- nodeMetadata.mapping[storage].schema -}}"."{{- node_name -}}"
            {%- endif -%}
        {%- endfor -%}
        {%- if not foundMapping.flag -%}
            {{-  location_name | invalid_export_ref(node_name, ref_type='ref') -}}
        {%- endif -%}
    {%- endmacro -%}