Getting Started with Node Type V2

This guide walks you through creating your first V2 Node, from setting up a V2 Node type to deploying a table in your warehouse.

Prerequisites

You need the following:

• A Coalesce Workspace connected to Snowflake.
• At least one source Node in your pipeline to SELECT from.

Step 1: Create a V2 Node Type

Before you can create a V2 Node, you need a V2 Node type. Node types are the templates that control how a Node deploys and runs, and they are shared across all Nodes of that type.

Go to Build Settings > Node Types and click the Create Node Type dropdown. Select the V2 option. This creates a Node type with version: 2 set. Give it a descriptive name, for example SQL Stage.

The Node type list includes a Version column so you can distinguish V1 and V2 types at a glance.

You can also duplicate an existing V2 Node type. The duplicate inherits version: 2 and the original's templates.

V2 Node types have no config items

V2 Node types cannot have config items. Coalesce enforces this at save time. All configuration is handled through SQL annotations in the Node file itself.

Step 2: Add a Node to Your Pipeline

In the Build interface, add a new Node to your graph and select the V2 Node type you just created as the Node type.

You can add it by right-clicking to attach downstream from an existing Node, or by using the + control to add a Node and then selecting your V2 Node type. If you add the Node from an existing upstream Node, the editor pre-populates with a starter SELECT that references it.

The center panel shows a SQL editor instead of the mapping grid.

Step 3: Write Your SQL

Write or paste a standard SELECT statement. Use {{ ref('LOCATION', 'NODE_NAME') }} to reference upstream Nodes. That is how Coalesce builds the dependency graph and tracks lineage.

@id("ea59b792-01e9-42a2-98c8-f760ce8caa76")
@nodeType("d75202b8-eefb-4aa5-a200-3e42276bb5e4")

SELECT
    "O_ORDERKEY" AS O_ORDERKEY,
    "O_CUSTKEY" AS O_CUSTKEY,
    "O_ORDERSTATUS" AS O_ORDERSTATUS,
    "O_TOTALPRICE" AS O_TOTALPRICE,
    "O_ORDERDATE" AS O_ORDERDATE,
    "O_ORDERPRIORITY" AS O_ORDERPRIORITY,
    "O_CLERK" AS O_CLERK,
    "O_SHIPPRIORITY" AS O_SHIPPRIORITY,
    "O_COMMENT" AS O_COMMENT
FROM {{ ref('SOURCE', 'ORDERS') }} "ORDERS"

When you save, Coalesce parses the SELECT and the Columns tab on the right updates to show every column inferred from your SQL with its name and data type.

After saving, check the Columns tab to confirm all expected columns appear with the correct data types. If a column's type shows as UNKNOWN, add an explicit cast in your SQL, for example CAST(value AS DECIMAL(12,2)).

Step 4: Configure the Node with Annotations

V2 Nodes have no config panel. All configuration is done through SQL annotations written directly in your SQL file. Annotations that apply to the whole Node go before the SELECT. Annotations that apply to a specific column go directly after that column expression in the SELECT clause.

@id("ea59b792-01e9-42a2-98c8-f760ce8caa76")
@nodeType("d75202b8-eefb-4aa5-a200-3e42276bb5e4")
@materializationType("table")
@insertStrategy("MERGE")
SELECT
    "O_ORDERKEY" AS O_ORDERKEY,
     "O_CUSTKEY" AS "O_CUSTKEY @isBusinessKey @notNull",
    "O_ORDERSTATUS" AS O_ORDERSTATUS,
    "O_TOTALPRICE" AS O_TOTALPRICE,
    "O_ORDERDATE" AS O_ORDERDATE,
    "O_ORDERPRIORITY" AS O_ORDERPRIORITY,
    "O_CLERK" AS O_CLERK,
    "O_SHIPPRIORITY" AS O_SHIPPRIORITY,
    "O_COMMENT" AS O_COMMENT
FROM {{ ref('SOURCE', 'ORDERS') }} "ORDERS"

See SQL Annotations Reference for the full list of annotations and how to use them in templates.

Step 5: Create and Run Templates

In Build Settings > Node Types, open your V2 Node type and configure Create and Run templates on the Node type. Those Jinja templates turn each Node's SELECT and annotations into warehouse DDL and DML. See SQL Annotations Reference for how annotations map to template variables and for optional branches such as tests.

Create Template

Create Template Example

{% if node.materializationType == 'table' %}
    {{ stage('Create Table') }}

    CREATE OR REPLACE TABLE {{ ref_no_link(node.location.name, node.name) }}
    (
        {% for col in columns %}
            "{{ col.name }}" {{ col.dataType }}
            {%- if not col.nullable or col.notNull %} NOT NULL
                {%- if col.defaultValue | length > 0 %} DEFAULT {{ col.defaultValue }}{% endif %}
            {% endif %}
            {%- if col.description | length > 0 %} COMMENT '{{ col.description | escape }}'{% endif %}
            {%- if not loop.last -%}, {% endif %}
        {% endfor %}
    )
    {%- if node.description | length > 0 %} COMMENT = '{{ node.description | escape }}'{% endif %}

{% elif node.materializationType == 'view' %}
    {{ stage('Create View') }}

    CREATE OR REPLACE VIEW {{ ref_no_link(node.location.name, node.name) }}
    (
        {% for col in columns %}
            "{{ col.name }}"
            {%- if col.description | length > 0 %} COMMENT '{{ col.description | escape }}'{% endif %}
            {%- if not loop.last -%}, {% endif %}
        {% endfor %}
    )
    {%- if node.description | length > 0 %} COMMENT = '{{ node.description | escape }}'{% endif %}
    AS
    {% for source in sources %}
        SELECT
        {% for col in source.columns %}
            {{ get_source_transform(col) }} AS "{{ col.name }}"
            {%- if not loop.last -%}, {% endif %}
        {% endfor %}
        {{ source.join }}
    {% endfor %}

{% endif %}

Run Template

The MERGE branch uses {{ source.cteString }} before each SELECT so CTEs from the Node SQL editor are included. If you author CTEs, keep that line inside the sources loop.

Run Template Example

{# --- Pre-SQL --- #}
{% if config.preSQL is defined and config.preSQL.parameters is defined -%}
    {% for sql in config.preSQL.parameters -%}
        {{ stage('Pre-SQL ' + loop.index|string) }}
        {{ sql }}
    {% endfor %}
{%- endif %}

{# --- Insert Strategy --- #}
{% if config.insertStrategy is defined and config.insertStrategy.parameters is defined %}
    {% if config.insertStrategy.parameters[0] == 'MERGE' %}
        {{ stage('Merge Data') }}
        MERGE INTO {{ ref_no_link(node.location.name, node.name) }} TGT
        USING (
        {% for source in sources %}
            {{ source.cteString }}
            SELECT
            {% for col in source.columns %}
                {{ get_source_transform(col) }} AS "{{ col.name }}"{% if not loop.last %}, {% endif %}
            {% endfor %}
            {{ source.join }}
        {% endfor %}
        ) SRC
        ON {% for col in columns if col.isBusinessKey %}{% if not loop.first %} AND {% endif %}TGT."{{ col.name }}" = SRC."{{ col.name }}"{% endfor %}
        WHEN MATCHED THEN UPDATE SET
            {% for col in columns if col.isChangeTracking %}
                TGT."{{ col.name }}" = SRC."{{ col.name }}"{% if not loop.last %}, {% endif %}
            {% endfor %}
        WHEN NOT MATCHED THEN INSERT (
            {% for col in columns %}
                "{{ col.name }}"{% if not loop.last %}, {% endif %}
            {% endfor %}
        ) VALUES (
            {% for col in columns %}
                SRC."{{ col.name }}"{% if not loop.last %}, {% endif %}
            {% endfor %}
        );

    {% elif config.insertStrategy.parameters[0] == 'TRUNCATE' %}
        {{ stage('Truncate Table') }}
        TRUNCATE TABLE {{ ref_no_link(node.location.name, node.name) }};

        {{ stage('Insert Data') }}
        INSERT INTO {{ ref_no_link(node.location.name, node.name) }}
        (
            {% for col in columns %}
                "{{ col.name }}"{% if not loop.last %}, {% endif %}
            {% endfor %}
        )
        {% for source in sources %}
            {{ source.cteString }}
            SELECT
            {% for col in source.columns %}
                {{ get_source_transform(col) }} AS "{{ col.name }}"{% if not loop.last %}, {% endif %}
            {% endfor %}
            {{ source.join }}
            {% if not loop.last %}UNION ALL{% endif %}
        {% endfor %};

    {% else %}
        {{ stage('Insert Data') }}
        INSERT INTO {{ ref_no_link(node.location.name, node.name) }}
        (
            {% for col in columns %}
                "{{ col.name }}"{% if not loop.last %}, {% endif %}
            {% endfor %}
        )
        {% for source in sources %}
            {{ source.cteString }}
            SELECT
            {% for col in source.columns %}
                {{ get_source_transform(col) }} AS "{{ col.name }}"{% if not loop.last %}, {% endif %}
            {% endfor %}
            {{ source.join }}
            {% if not loop.last %}UNION ALL{% endif %}
        {% endfor %};

    {% endif %}
{% else %}
    {{ stage('Insert Data') }}
    INSERT INTO {{ ref_no_link(node.location.name, node.name) }}
    (
        {% for col in columns %}
            "{{ col.name }}"{% if not loop.last %}, {% endif %}
        {% endfor %}
    )
    {% for source in sources %}
        {{ source.cteString }}
        SELECT
        {% for col in source.columns %}
            {{ get_source_transform(col) }} AS "{{ col.name }}"{% if not loop.last %}, {% endif %}
        {% endfor %}
        {{ source.join }}
        {% if not loop.last %}UNION ALL{% endif %}
    {% endfor %};

{% endif %}

{# --- Post-SQL --- #}
{% if config.postSQL is defined and config.postSQL.parameters is defined -%}
    {% for sql in config.postSQL.parameters -%}
        {{ stage('Post-SQL ' + loop.index|string) }}
        {{ sql }}
    {% endfor %}
{%- endif %}

{# --- Tests --- #}
{% if config.testsEnabled -%}
    {% if config.tests is defined and config.tests.parameters is defined -%}
        {% for test in config.tests.parameters -%}
            {{ test_stage(test) }}
            {{ test }}
        {% endfor %}
    {%- endif %}

    {% for column in columns -%}
        {% if column.tests is mapping and column.tests.parameters is defined -%}
            {% for test in column.tests.parameters -%}
                {{ test_stage(column.name + ": Test " + loop.index|string) }}
                {{ test }}
            {%- endfor %}
        {%- endif %}
    {%- endfor %}
{%- endif %}

Save the Node type. Coalesce does not infer behavior from annotations unless the template reads the matching metadata (for example col.isBusinessKey for the MERGE ON clause).

Step 6: Preview, Deploy, and Run

Once your SQL and Node type templates are saved, use the same workflow as any other Node:

1. Validate Select checks for parse errors without deploying.
2. Create deploys the Node and generates the DDL that creates the table or view in your warehouse.
3. Run executes the DML and populates the table.

After running, verify the following:

• DAG view: your {{ ref() }} calls should appear as upstream dependencies in the graph.
• Columns tab: all expected columns are listed with correct data types.
• Warehouse: query the target table to confirm the data looks right.

Full Example

Here is a complete Node that joins two upstream tables using CTEs, casts an ambiguous type, and configures a business key with an annotation:

@materializationType("table")

WITH orders_base AS (
    SELECT
        O_ORDERKEY AS order_key,
        O_CUSTKEY AS customer_key,
        O_ORDERSTATUS AS order_status,
        O_TOTALPRICE AS order_total_price,
        O_ORDERDATE AS order_date,
        TRIM(O_ORDERPRIORITY) AS order_priority
    FROM {{ ref('STAGING', 'STG_ORDERS') }} "ORDERS"
),

lineitem_base AS (
    SELECT
        L_ORDERKEY AS order_key,
        L_LINENUMBER::NUMBER AS line_number,
        L_QUANTITY AS quantity,
        L_EXTENDEDPRICE AS extended_price,
        L_DISCOUNT AS discount_percent,
        L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS discounted_price
    FROM {{ ref('STAGING', 'STG_LINEITEM') }} "LINEITEM"
)

SELECT
    o.order_key @isBusinessKey,
    li.line_number,
    o.customer_key,
    o.order_date,
    o.order_status,
    o.order_priority,
    li.quantity,
    li.extended_price,
    li.discount_percent,
    li.discounted_price
FROM orders_base o
INNER JOIN lineitem_base li
    ON o.order_key = li.order_key

After saving, the Columns tab lists all 10 columns with inferred types. The DAG shows dependencies on both STG_ORDERS and STG_LINEITEM. Create generates the table and Run populates it.

Best Practices

Use these practices while you author V2 Nodes:

• Always alias your columns. Explicit aliases like O_ORDERKEY AS order_key give Coalesce a clear, stable name to work with. Expressions without aliases may produce unexpected column names.
• Cast ambiguous types. If the Columns tab shows UNKNOWN for a data type, add an explicit cast in your SQL: value::TIMESTAMP_NTZ or CAST(value AS DECIMAL(12,2)).
• Build incrementally. Start with a basic SELECT and confirm columns parse correctly before adding CTEs, joins, or annotations. It is easier to debug a simple query than a complex one.
• Use CTEs instead of subqueries. {{ ref() }} calls inside deeply nested subqueries may not register as DAG dependencies. Using top-level CTEs ensures lineage and Job ordering stay accurate.

What's Next?

• SQL Annotations Reference for the full reference for all annotations.
• The V2 Editor for a walkthrough of every part of the interface.
• Node Type V2 for how V2 compares to V1, platform scope, and links across the section.
• Troubleshooting and FAQ for common parse, column, and lineage issues.