coalesce.ioTake documentation survey

Coalesce Internal Macros

Suggest Edits

In addition to custom macros that can be defined by the user, there are several “Internal” macros in Coalesce you can use or edit.

🚧

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.

{%- 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.

{% 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.

{%- 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.

{%- 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 -%}

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.

{%- 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.


{%- 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.

{%- 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 -%}

Updated 10 days ago