SQL Annotations Reference

SQL annotations are how you configure V2 Nodes. Instead of setting materialization type, insert strategy, preSQL and postSQL, and other options in a config panel, you write them as @name or @name("value") directives directly in your SQL file. The transformation, the columns, and the configuration all live in one place.

The annotation parser is fully generic: any @name is valid syntax. A small number of names are Reserved Annotations with built-in system behavior. Everything else is Custom Annotations. Those custom names land in the Node's metadata and only have meaning if your templates consume them.

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. The parser accepts any name, with no restrictions. Custom annotations land in the Node's metadata and only do something if your templates check for them.

This includes the annotations that Coalesce's default templates commonly use. They aren't special. They work because the default templates look for them, not because the system processes them automatically.

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 doesn't enforce constraints automatically. They work because Coalesce Node type templates check for them.

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

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, @insertStrategy, @isBusinessKey, @isChangeTracking, @testsEnabled, and @tests:

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

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. You can use any name you want as long as your Create and Run templates reference it. 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")
@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.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​

Common Annotation Examples​

Using Annotations in Templates​

Create Template​

Run Template​

Full Example​

Accessing Annotation Values in Templates​

Quoting and Escaping​

What's Next?​