SQL Annotations Reference

SQL annotations are how you set options on each V2 Node. Instead of using the mapping grid config panel on every Node, you write @name or @name("value") directives in the Node SQL file alongside your SELECT.

For an annotation to change deployed SQL, the Node type must declare it in the YAML definition, Create and Run templates must consume the hydrated metadata, and the Node SQL or Options must set the value. See Declare Annotations in the Node Type Definition for the full checklist.

The annotation parser accepts @name syntax for any identifier you choose. A small number of names are Reserved Annotations with built-in system behavior. Everything else is a Custom Annotations. Custom names work the same way as @truncateBefore or @preSQL: declare the name in the node type definition, read it in templates, then set it in Node SQL or Options.

Syntax

A boolean annotation has no parameters and produces a true value:

@truncateBefore
@testsEnabled
@isBusinessKey

A parameterized annotation takes one or more quoted values in parentheses:

@materializationType("table")
@insertStrategy("MERGE")
@preSQL("ALTER SESSION SET TIMEZONE = 'UTC'")

Placement

Node-Level Placement

Put these annotations before the SELECT clause. They set Node configuration in config.*:

@materializationType("table")
@truncateBefore
SELECT ...

Column-Level Placement

Add these after a column expression in the SELECT list. They set column properties in column.*:

SELECT
    ID AS id @isBusinessKey,
    NAME AS name @isChangeTracking,
    STATUS AS status
FROM {{ ref('WORK', 'STG_CUSTOMERS') }}

A column can have multiple annotations:

SELECT
    ID AS id @isBusinessKey @notNull,
    NAME AS name @isChangeTracking @notNull,
    ...

Reserved Annotations

These annotations have built-in system behavior. @id and @nodeType are auto-populated by Coalesce when a Node is created. You never need to write them manually, and you should not edit them. @materializationType is a reserved keyword.

Annotation	Level	Routes to	Behavior
`@id("...")`	Node	`node.id`	Auto-populated. Never change. Modifying it breaks the Node's identity, version control history, and any references to it.
`@nodeType("...")`	Node	`node.nodeType`	Auto-populated. Only change this if you are intentionally pointing the Node at a different valid Node type ID in your Workspace.
`@materializationType("...")`	Node	`node.materializationType`	Controls how the Node is materialized using `"table"` or `"view"`. Also available as `config.materializationType.parameters[0]` in templates.

Custom Annotations

Everything beyond the three reserved annotations is custom. That includes familiar names such as @truncateBefore and @preSQL, and author-defined names such as @myLoadMode("full") or @regionCode("US").

Coalesce does not ship special-case behavior for custom names. If you add @somename in SQL but omit somename from the node type definition, Coalesce does not treat it as a registered option. The annotation may not hydrate into config.* or column metadata, and templates cannot rely on it. The same rule applies to built-in-sounding names like truncateBefore and preSQL, and to names you invent for your Node type.

See Declare Annotations in the Node Type Definition for the config item and template steps.

Node-Level Fields in Config

Top-level annotations land in config.*:

@truncateBefore          -- config.truncateBefore = true
@insertStrategy("MERGE") -- config.insertStrategy = { parameters: ['MERGE'] }
@preSQL("ALTER SESSION SET TIMEZONE = 'UTC'")  -- config.preSQL = { parameters: ['ALTER SESSION...'] }

Column-Level Fields on the Column Object

Column annotations are flattened onto the column object at the root level and are accessible directly as column.isBusinessKey:

SELECT
    ID AS id @isBusinessKey,        -- column.isBusinessKey = true
    NAME AS name @isChangeTracking, -- column.isChangeTracking = true
    STATUS AS status @notNull       -- column.notNull = true
FROM {{ ref('WORK', 'STG_CUSTOMERS') }}

Parameterized column annotations produce { parameters: [...] } on the column metadata object:

SELECT
    ID AS id @partitionBy('HASH', '16'),   -- column.partitionBy = { parameters: ['HASH', '16'] }
    AMOUNT AS amount @precision('12', '2') -- column.precision = { parameters: ['12', '2'] }
FROM {{ ref('WORK', 'STG_TRANSACTIONS') }}

@isBusinessKey, @isChangeTracking, @notNull, and @isUnique are custom annotations. The system does not enforce constraints automatically. They work when the node type definition declares the matching column config or mapping column and your templates check for them.

Author-Defined Annotation Names

You can introduce new annotation names for a custom V2 Node type. Pick any valid identifier, declare it in the node type YAML, wire it in templates, then use it in Node SQL.

Node-level example: a dropdown in the definition with attributeName: myLoadMode maps to @myLoadMode("full") in SQL and to config.myLoadMode in templates.

  - displayName: Load Mode
    attributeName: myLoadMode
    type: dropdownSelector
    default: incremental
    options:
    - incremental
    - full

@myLoadMode("full")

SELECT
    id AS id
FROM {{ ref('STAGING', 'STG_ORDERS') }}

{% if config.myLoadMode is defined and config.myLoadMode.parameters[0] == 'full' %}
    {{ stage('Truncate Table') }}
    TRUNCATE IF EXISTS {{ ref_no_link(node.location.name, node.name) }};
{% endif %}

Column-level custom names follow the same rule: add a mappingColumns entry or column selector in the definition, then reference column.yourAttributeName in the Create template. See Node Config Options for config item types you can bind to annotations.

Common Annotation Examples

Annotation	Level	Type	Hydrated to	Purpose
`@isBusinessKey`	Column	Boolean	`column.isBusinessKey`	Business/primary key columns
`@isChangeTracking`	Column	Boolean	`column.isChangeTracking`	SCD change detection columns
`@notNull`	Column	Boolean	`column.notNull`	NOT NULL constraint in DDL
`@isUnique`	Column	Boolean	`column.isUnique`	Unique constraint columns
`@isSurrogateKey`	Column	Boolean	`column.isSurrogateKey`	Surrogate key columns
`@truncateBefore`	Node	Boolean	`config.truncateBefore`	Truncate table before INSERT
`@testsEnabled`	Node	Boolean	`config.testsEnabled`	Enable test execution
`@insertStrategy("...")`	Node	Parameterized	`config.insertStrategy.parameters`	Insert strategy: `INSERT`, `UNION`, `UNION ALL`, `MERGE`
`@preSQL("...")`	Node	Parameterized	`config.preSQL.parameters`	SQL to run before the main query
`@postSQL("...")`	Node	Parameterized	`config.postSQL.parameters`	SQL to run after the main query
`@nodeTests("...")`	Node	Parameterized	`config.nodeTests.parameters`	Node-level data quality tests
`@columnTests("...")`	Column	Parameterized	`column.columnTests.parameters`	Column-level data quality tests

Declare Annotations in the Node Type Definition

Before you use an annotation in Node SQL, add a matching entry to the node type YAML config block in Build Settings > Node Types. The attributeName must match the annotation name without @. That rule applies to every annotation you use, including names you define yourself: @myLoadMode requires attributeName: myLoadMode. Coalesce uses the definition to validate annotations, apply defaults, sync Options in the UI, and hydrate config.* or column fields for templates.

This applies to node-level options such as truncateBefore, preSQL, insertStrategy, and testsEnabled, and to column-level flags your templates read, such as isBusinessKey, notNull, or custom mapping columns. Reserved annotations @id and @nodeType are auto-populated; @materializationType uses the materializationSelector config item when you expose it.

Work through this checklist for each option:

Add the config item to the node type definition (toggle, dropdown, text box, column selector, or other type from Node Config Options).
Add Jinja in the Create and/or Run template that reads the hydrated field.
Set the value in Node SQL with @annotationName, or in Options when the definition exposes the control.

The Getting Started with Node Type V2 guide includes a full definition plus Create and Run templates that wire Truncate Before, Pre-SQL, Post-SQL, and related options.

Example fragment for @truncateBefore:

  - displayName: Truncate Before
    attributeName: truncateBefore
    type: toggleButton
    default: true

Example fragment for @preSQL:

  - displayName: Pre-SQL
    attributeName: preSQL
    type: textBox
    syntax: sql
    isRequired: false

@insertStrategy accepts INSERT, UNION, UNION ALL, and MERGE only. Full replace loads use @truncateBefore, or Truncate Before in Options, not @insertStrategy("TRUNCATE").

Using Annotations in Templates

You can define any annotation name and reference it in your Create and Run templates. Below are complete templates that consume the annotations from the Full Example below.

Create Template

This template uses the reserved node.materializationType field to branch between table and view DDL, and the custom col.notNull field to add NOT NULL constraints:

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

This template consumes @preSQL, @postSQL, @truncateBefore, @insertStrategy, @isBusinessKey, @isChangeTracking, @testsEnabled, and @tests. It assumes the node type definition declares the matching config items. See Declare Annotations in the Node Type Definition.

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

{# --- Truncate before insert --- #}
{% if config.truncateBefore %}
    {{ stage('Truncate Table') }}
    TRUNCATE IF EXISTS {{ ref_no_link(node.location.name, node.name) }};
{% 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 %}
        );

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

Full Example

A complete SQL file using reserved and custom annotations.

Reserved Node ID and Node Type Fields

@id and @nodeType are managed by Coalesce automatically. Never modify @id, because changing it breaks the Node's identity. Don't change @nodeType unless you're pointing the Node at a different valid Node type ID in your Workspace.
@materializationType is a reserved annotation that controls how the Node is deployed.
All other annotations are custom. Declare each one in the node type definition, reference it in Create and Run templates, then set it in Node SQL. Custom annotations have no effect unless a template consumes them.

@id("98245936-8e90-468e-a1ed-e3a18e3ec941")
@nodeType("fd252eae-7b6d-4061-91cd-291ceaa52be1")
@materializationType("table")
@insertStrategy("MERGE")
@truncateBefore
@preSQL("ALTER SESSION SET TIMEZONE = 'UTC'")
@postSQL("ALTER SESSION UNSET TIMEZONE")
@testsEnabled
@tests("SELECT * FROM {{ this }} WHERE C_NAME IS NULL", "SELECT * FROM {{ this }} WHERE C_ACCTBAL < 0")

SELECT
    "C_CUSTKEY" AS C_CUSTKEY @isBusinessKey @notNull,
    "C_NAME" AS C_NAME @isChangeTracking @notNull,
    "C_ADDRESS" AS C_ADDRESS @isChangeTracking,
    "C_NATIONKEY" AS C_NATIONKEY,
    "C_PHONE" AS C_PHONE @tests('SELECT * FROM {{ this }} WHERE C_PHONE IS NULL') @notNull,
    "C_ACCTBAL" AS C_ACCTBAL,
    "C_MKTSEGMENT" AS C_MKTSEGMENT,
    "C_COMMENT" AS C_COMMENT
FROM {{ ref('SRC', 'CUSTOMER') }} "CUSTOMER"

This produces:

node.materializationType = "table" - Reserved; controls deployment
config.insertStrategy = { parameters: ['MERGE'] } - Custom; consumed by templates
config.truncateBefore = true - Custom; consumed by templates
config.preSQL = { parameters: ['ALTER SESSION SET TIMEZONE = ...'] } - Custom; consumed by templates
config.postSQL = { parameters: ['ALTER SESSION UNSET TIMEZONE'] } - Custom; consumed by templates
config.testsEnabled = true - Custom; consumed by templates
config.tests = { parameters: ['SELECT * FROM ...', 'SELECT * FROM ...'] } - Custom; consumed by templates
column.isBusinessKey = true on C_CUSTKEY
column.isChangeTracking = true on C_NAME, C_ADDRESS
column.notNull = true on C_CUSTKEY, C_NAME, C_PHONE
column.tests = { parameters: ['SELECT * FROM ...'] } on C_PHONE

Accessing Annotation Values in Templates

The format for accessing annotation values depends on the annotation type:

Boolean annotations using names such as @testsEnabled and @truncateBefore produce true. Use directly: {% if config.testsEnabled %}.
Parameterized annotations such as @insertStrategy("MERGE") produce { parameters: ['MERGE'] }. Access the value with config.insertStrategy.parameters[0].

Quoting and Escaping

Annotation values can be wrapped in either double quotes or single quotes. Both styles are valid:

@materializationType("table")
@insertStrategy('MERGE')

You can also pass unquoted numbers and the Boolean literals true and false:

@threshold(100)
@enabled(true)

When your annotation value contains quotes, use the opposite quote style to wrap it:

@preSQL("ALTER SESSION SET TIMEZONE = 'UTC'")

Complex quoting scenarios, including nested quotes and multi-statement @preSQL strings with semicolons, may have edge cases. Test your specific use case before you rely on it in production.

What's Next?

Node Type V2 Overview for the hub page on SQL-first Nodes.
Getting Started with Node Type V2 to create your first V2 Node type and Node.
The V2 Editor for a tour of the V2 editing experience.
Upgrading from V1 to V2 Node Types if you are migrating from deprecated inputMode: 'sql'.
Troubleshooting and FAQ for parse, column, and template issues.

Syntax​

Placement​

Node-Level Placement​

Column-Level Placement​

Reserved Annotations​

Custom Annotations​

Node-Level Fields in Config​

Column-Level Fields on the Column Object​

Author-Defined Annotation Names​

Common Annotation Examples​

Declare Annotations in the Node Type Definition​

Using Annotations in Templates​

Create Template​

Run Template​

Full Example​

Accessing Annotation Values in Templates​

Quoting and Escaping​

What's Next?​