Documentation index: llms.txt. This page is also available as markdown: append .md to this URL or send Accept: text/markdown.
SQL Annotations Reference
SQL annotations are how you configure a SQL Node. You write @name or @name("value") directives in the Node's SQL file alongside your SELECT. Configuration that a YAML Node captures in the config panel, a SQL Node captures as annotations:
@materializationType("table")
@writeMode("append")
SELECT
"N_NATIONKEY" AS "N_NATIONKEY" @notNull,
"N_NAME" AS "N_NAME" @description("Nation name")
FROM {{ ref('SRC', 'NATION') }} "NATION"
The flow from annotation to warehouse:
- You annotate the SQL. Node-level annotations go above the leading
WITHorSELECT; column-level annotations follow a column in the outermostSELECT. - Coalesce hydrates the values into Node metadata. Node-level values land on
config, column-level values land on the column, and reserved annotations set Node and column fields directly. - Templates render the SQL. The Node Type's Create and Run templates read the metadata and emit DDL and DML.
Every section below details one part of that flow. Annotations come in two kinds:
- A small set of Reserved Annotations that behave the same on every SQL Node Type and are validated by Coalesce.
- Node-Type-Defined Annotations: everything else. Each SQL Node Type declares its own annotations in its definition and consumes them in its templates, so the available set depends on the Node Type. The Annotations panel on each Node lists what its type supports.
Syntax
An annotation is @ followed by a name, with optional parentheses holding one or more values:
@disableTests
@materializationType("table")
@tests("SELECT 1 FROM {{ this }} WHERE N_REGIONKEY IS NULL", true, "Before")
- A bare annotation such as
@disableTestsproduces the valuetrue. - A parameterized annotation such as
@writeMode("append")produces{ parameters: ["append"] }. Parameters are positional; there are no named parameters. - Parameter values are literals: quoted strings, numbers, or the unquoted boolean values
trueandfalse. Each keeps its type, so@tests("...", true, "Before")produces{ parameters: ["...", true, "Before"] }. Negative numbers are not supported. - Annotations are part of the SQL grammar, not comments. Text that looks like an annotation inside a
--or/* */comment is ignored. - Jinja inside a string parameter is not evaluated when the annotation is parsed. It is stored as text and rendered later by whichever template consumes the value, so
@preSQL("DELETE FROM {{ this }} WHERE LOAD_DATE < CURRENT_DATE()")works as expected.
Quote a boolean and you get a string
@disableTests(false) produces the boolean false. @disableTests("false") produces the string "false", which Jinja treats as true. Write boolean values unquoted, or use bare flag annotations and treat presence as on.
Annotation Names
Names follow the SQL identifier rules of the parser: letters, digits, and underscores, not starting with a digit, with no hyphens. Names that are SQL keywords, such as unique, as, or primary, fail to parse; that is why Coalesce's own test annotation is @uniqueness rather than @unique.
Reserved annotation names are matched case-insensitively. Node-type-defined names are case-sensitive: write them exactly as the Node Type declares them.
Placement
Annotations are valid in exactly two places.
Node-level: above the leading WITH or SELECT. These configure the Node:
@materializationType("table")
@writeMode("append")
SELECT ...
Column-level: after a column expression, and its alias if present, in the outermost SELECT. These configure the column, and a column can carry several:
SELECT
"N_NATIONKEY" AS "N_NATIONKEY" @notNull @not_null @min_max("0", "24"),
"N_NAME" AS "N_NAME" @description("Nation name"),
"N_COMMENT" AS "N_COMMENT"
FROM {{ ref('SRC', 'NATION') }} "NATION"
Annotations anywhere else, including inside CTEs or subqueries, do not parse as annotations.
Reserved Annotations
Reserved annotations work on every SQL Node Type. A Node Type cannot redefine them: a declaration that reuses a reserved name is ignored and the reserved behavior wins.
Node-Level Reserved Annotations
| Annotation | Sets | Behavior |
|---|---|---|
@id("...") | Node ID | Managed by Coalesce. Never change it. Coalesce adds it when the Node is first saved. A missing @id, or one that does not match the Node, is an error. |
@nodeType("...") | Node Type ID | Managed by Coalesce. Holds the ID of the Node's type, not its display name. Changing it to a different valid SQL Node Type ID in your Workspace switches the Node to that type on save. Pointing it at a YAML Node Type or an unknown ID is an error. |
@description("...") | Node description | Optional. Defaults to an empty description. |
@materializationType("...") | Materialization | Optional. Exactly "table" or "view", lowercase; defaults to "table" when omitted. Other values are an error. On a Node Type that sets deployStrategy: advanced, this annotation is not reserved and the Node Type defines its own allowed values. |
Column-Level Reserved Annotations
| Annotation | Sets | Behavior |
|---|---|---|
@description("...") | Column description | Optional. Defaults to an empty description. |
@notNull | Column nullability | Marks the column NOT NULL in the DDL the Create Template emits. Takes no argument; omit the annotation to leave the column nullable. |
@defaultValue("...") | Column default | Optional. The default value emitted in DDL. Quote it to match the column's data type: @defaultValue("0") for a number, @defaultValue("'NA'") for a string. |
@notNull is not a test
Reserved @notNull is a DDL constraint. Node Types can also define data quality tests with similar names, such as @not_null in Coalesce's Work Node Type, which check loaded rows instead. Both can appear on the same column.
How Reserved Annotations Are Validated
Coalesce validates reserved annotations as you type and again when it plans a deploy:
- In the SQL editor, an invalid reserved annotation is underlined, and hovering shows the message.
- In the Problems panel, each invalid annotation appears as an issue under the Node.
- On deploy, a Node with an invalid reserved annotation blocks the deployment until it is fixed.
coa validatereports the same issues.
The checks: a reserved annotation may appear only once (later duplicates are flagged and the first is used); it must have the right number of arguments (@nodeType requires an argument, @notNull does not take an argument); @materializationType must be one of its allowed values; @nodeType must be a valid SQL Node Type ID; and @id must match the Node's own ID.
Node-type-defined annotations are not validated. A misspelled or undeclared annotation parses fine and hydrates under whatever name you wrote, with no warning. See Undeclared annotations have no effect below.
Node-Type-Defined Annotations
Every annotation beyond the reserved set is defined by the Node Type. Coalesce's Work Node Type, for example, defines @writeMode, @disableTests, @tests, @preSQL, and @postSQL at the node level, and column tests such as @not_null, @uniqueness, @min_max, and @freshness. Coalesce ships no built-in behavior for any of these names: they take effect only because the Node Type's templates consume their values.
A SQL Node Type declares its annotations in its definition so that they appear, with descriptions and copyable snippets, in the Annotations panel on every Node of that type. See Defining a SQL Node Type for the declaration format.
How Values Hydrate
| Annotation | Written | Hydrates to |
|---|---|---|
| Node-level, bare | @disableTests | config.disableTests = true |
| Node-level, parameterized | @writeMode("append") | config.writeMode = { parameters: ["append"] } |
| Node-level, repeatable | @preSQL("...") twice | config.preSQL = [{ parameters: ["..."] }, { parameters: ["..."] }] |
| Column-level, bare | @not_null | column.not_null = true |
| Column-level, parameterized | @min_max("0", "24") | column.min_max = { parameters: ["0", "24"] } |
Node-level values live under the config object. Column-level values are spread directly onto the column object when the template context is built, so templates read column.not_null, never column.config.not_null. Reserved annotations set real Node and column fields (node.materializationType, column.description, column.nullable, column.defaultValue) and never appear under config.
Rules that follow from the hydration:
- A bare annotation is always
true, even when the Node Type declares parameters for it. A template that readsconfig.disableTests.parameters[0]breaks on the bare form; test for presence instead. - Repeatable annotations (declared with
allowsMultiple) always hydrate as an array, one entry per occurrence, even with a single occurrence, so templates can loop unconditionally. Without that flag, repeating an annotation keeps only the last occurrence. - Declared defaults are documentation. A parameter's
defaultshows in the panel and pre-fills snippets, but nothing applies it at runtime. Templates supply their own fallback, for exampleconfig.writeMode.parameters[0] if config.writeMode is defined else 'truncateInsert'.
Undeclared annotations have no effect
Coalesce accepts any annotation name and hydrates its value, but a value nothing consumes changes nothing. If you write @somename, or misspell a declared annotation, the Node deploys and runs as if the annotation were absent, with no error or warning. When an option you set seems ignored, first check the Annotations panel to confirm the exact name the Node Type supports.
The Annotations Panel
When a Node Type declares annotations, each of its Nodes shows the read-only Annotations panel in place of the Config tab, listing what you can write in the SQL:
- General: Node properties that live outside the SQL, such as the Storage Location.
- Node Annotations: written above the leading
WITHorSELECT. - Column Annotations: written after
AS <alias>on a column in the outermostSELECT.
Each row shows the annotation's signature (optional parameters carry a trailing ?), tags for reserved, required, and repeatable, its description, its parameters with their types or allowed values and defaults, and a copy button that produces a paste-ready line. @id has no copy button because Coalesce manages it. The panel is for discovery: values are set in the SQL, not in the panel.
A Complete Worked Example
This example is Coalesce's Work Node Type from the Base Node Types - SQL package for Snowflake, reproduced from the package source: the annotation declarations, the Create and Run templates, and the macros the Run template calls. Two edits were made for the docs. Four lines in the declarations that the definition parser rejects are corrected (type and default on the disableTests declaration, boolean options values, and an empty nested parameters key), and the templates' license header comment is omitted.
1. Declare the Annotations in the Node Metadata Spec
annotations:
node:
- name: writeMode
description: >-
Controls how data is written to the target table.
𝘁𝗿𝘂𝗻𝗰𝗮𝘁𝗲𝗜𝗻𝘀𝗲𝗿𝘁 clears the table before loading, replacing its contents entirely;
𝗮𝗽𝗽𝗲𝗻𝗱 inserts the new rows alongside whatever is already there.
If not specified in the SQL, defaults to 𝘁𝗿𝘂𝗻𝗰𝗮𝘁𝗲𝗜𝗻𝘀𝗲𝗿𝘁.
𝗜𝗴𝗻𝗼𝗿𝗲𝗱 𝗼𝗻 𝘃𝗶𝗲𝘄𝘀.
isRequired: false
parameters:
- name: mode
type: string
description: Whether to 𝗧𝗥𝗨𝗡𝗖𝗔𝗧𝗘 the target table before inserting, or 𝗔𝗣𝗣𝗘𝗡𝗗 to it.
isRequired: true
default: truncateInsert
example: append
options:
- truncateInsert
- append
- name: disableTests
description: >-
Controls whether configured tests are skipped.
Useful while developing a node — skip node-level and column-level tests without deleting them, so you can iterate on the SQL first and turn tests back on once the logic is settled.
isRequired: false
- name: tests
description: >-
Node-level data quality test.
Runs the given 𝗾𝘂𝗲𝗿𝘆𝗦𝗤𝗟 against the target and fails the test if it returns any records — write the query so it selects only the rows that violate your condition.
Runs 𝗕𝗲𝗳𝗼𝗿𝗲 or 𝗔𝗳𝘁𝗲𝗿 the load per 𝗿𝘂𝗻𝗢𝗿𝗱𝗲𝗿, and either halts or continues the run on failure per 𝗰𝗼𝗻𝘁𝗶𝗻𝘂𝗲𝗢𝗻𝗙𝗮𝗶𝗹𝘂𝗿𝗲.
Skipped entirely when 𝗱𝗶𝘀𝗮𝗯𝗹𝗲𝗧𝗲𝘀𝘁𝘀 is set.
allowsMultiple: true
parameters:
- name: querySQL
type: string
description: SQL statement to execute as a validation test.
isRequired: true
example: 'SELECT 1'
- name: continueOnFailure
type: boolean
description: Determines whether execution continues when the test fails.
isRequired: false
default: true
options:
- "true"
- "false"
- name: runOrder
type: string
description: Determines whether the test is executed before or after the load operation.
isRequired: false
default: After
options:
- Before
- After
- name: preSQL
description: >-
SQL statement to execute before the data load operation — e.g. staging setup, dropping temp tables, or capturing pre-load state.
Repeat the annotation to run multiple statements in the order they appear.
𝗜𝗴𝗻𝗼𝗿𝗲𝗱 𝗼𝗻 𝘃𝗶𝗲𝘄𝘀.
allowsMultiple: true
parameters:
- name: querySQL
type: string
description: Query to be executed.
isRequired: true
example: 'SELECT 1'
- name: postSQL
description: >-
SQL statement to execute after the data load operation — e.g. updating dependent tables, cleaning up temp objects, or logging load completion.
Repeat the annotation to run multiple statements in the order they appear.
𝗜𝗴𝗻𝗼𝗿𝗲𝗱 𝗼𝗻 𝘃𝗶𝗲𝘄𝘀.
allowsMultiple: true
parameters:
- name: querySQL
type: string
description: Query to be executed.
isRequired: true
example: 'SELECT 1'
column:
- name: inHash
description: >-
Marks a column as an input to a generated hash key, grouped by 𝗵𝗮𝘀𝗵𝗡𝗮𝗺𝗲 and ordered within that group by 𝗵𝗮𝘀𝗵𝗢𝗿𝗱𝗲𝗿.
Mark every column that should feed a given hash with the same 𝗵𝗮𝘀𝗵𝗡𝗮𝗺𝗲, then call the 𝐠𝐞𝐭_𝐡𝐚𝐬𝐡("<hashName>") macro elsewhere in the SELECT to produce the actual hash column from those marked columns.
𝗦𝗲𝗲 𝗱𝗼𝗰𝘂𝗺𝗲𝗻𝘁𝗮𝘁𝗶𝗼𝗻 𝗳𝗼𝗿 𝗱𝗲𝘁𝗮𝗶𝗹𝘀.
allowsMultiple: true
parameters:
- name: hashName
isRequired: true
type: string
description: Group hash name shared by every column feeding the same hash.
example: GH_COL
- name: hashOrder
isRequired: true
type: number
description: Position of this column within its hash group, lowest first.
example: 1
- name: not_null
description: >-
🚦 Column-level data quality test.
Fails on rows where the column is 𝗡𝗨𝗟𝗟.
Presence enables the test — remove the annotation to turn it off.
Runs 𝗔𝗳𝘁𝗲𝗿 the load and continues the run on failure.
- name: uniqueness
description: >-
🚦 Column-level data quality test.
Fails when a value appears on more than one row.
Runs 𝗔𝗳𝘁𝗲𝗿 the load and continues the run on failure.
- name: empty
description: >-
🚦 Column-level data quality test.
Fails on rows where the column trims to the empty string;
𝗡𝗨𝗟𝗟 values pass this test and are instead caught by not_null.
Runs 𝗔𝗳𝘁𝗲𝗿 the load and continues the run on failure.
- name: accepted_values
description: >-
🚦 Column-level data quality test.
Fails on rows whose value is outside the allow list.
Repeat the annotation once per permitted value.
Each value is pasted into the SQL verbatim, so quote it to match the column's data
type — a number as accepted_values("<num>"), a string as accepted_values("'<string>'").
Runs 𝗔𝗳𝘁𝗲𝗿 the load and continues the run on failure.
allowsMultiple: true
parameters:
- name: value
type: string
isRequired: true
- name: rejected_values
description: >-
🚦 Column-level data quality test.
Fails on rows whose value is in the deny list.
Repeat the annotation once per forbidden value. Each value is pasted into the SQL verbatim, so quote it to match the column's data type — a number as rejected_values("<num>"), a string as
rejected_values("'<string>'").
Runs 𝗔𝗳𝘁𝗲𝗿 the load and continues the run on failure.
allowsMultiple: true
parameters:
- name: value
type: string
isRequired: true
example: "'<string>'"
- name: min_max
description: >-
🚦 Column-level data quality test.
Fails on rows outside the inclusive range.
Both bounds are pasted into the SQL verbatim, so write a number as "0" and a date as "DATE '2026-01-01'".
Runs 𝗔𝗳𝘁𝗲𝗿 the load and continues the run on failure.
parameters:
- name: min
type: string
description: Inclusive lower bound the column value must not fall below.
isRequired: true
example: "0"
- name: max
type: string
description: Inclusive upper bound the column value must not exceed.
isRequired: true
example: "100"
- name: min_value
description: >-
🚦 Column-level data quality test.
Fails on rows below the bound.
Pasted into the SQL verbatim — see min_max.
Runs 𝗔𝗳𝘁𝗲𝗿 the load and continues the run on failure.
parameters:
- name: min
type: string
description: Inclusive lower bound the column value must not fall below.
isRequired: true
example: "0"
- name: max_value
description: >-
🚦 Column-level data quality test.
Fails on rows above the bound.
Pasted into the SQL verbatim — see min_max.
Runs 𝗔𝗳𝘁𝗲𝗿 the load and continues the run on failure.
parameters:
- name: max
type: string
description: Inclusive upper bound the column value must not exceed.
isRequired: true
example: "100"
- name: freshness
description: >-
🚦 Column-level data quality test.
Checks that the data is fresh — fails when the column's value is older than the given interval, or when the table has no rows at all.
𝗡𝗼𝘁𝗲: on a DATE column the value is truncated to midnight.
Runs 𝗔𝗳𝘁𝗲𝗿 the load and continues the run on failure.
parameters:
- name: interval
description: >-
How far back from now the newest value is allowed to be, expressed in the unit given by 𝘂𝗻𝗶𝘁.
type: number
isRequired: true
example: 1
- name: unit
type: string
options: [SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR]
default: DAY
- name: relative_time
description: >-
🚦 Column-level data quality test.
Compares this column against another date/time column on the same node using the
given operator.
E.g. @relative_time("<=", "END_DATE") fails any row where this column's value is not less than or equal to END_DATE.
A row with either side 𝗡𝗨𝗟𝗟 is left out of the check and passes.
Runs 𝗔𝗳𝘁𝗲𝗿 the load and continues the run on failure.
parameters:
- name: operator
type: string
description: Comparison operator used to compare this column against the other column.
isRequired: true
options: ["<", "<=", ">", ">=", "=", "<>"]
example: "="
- name: column
type: string
description: Name of the other date/time column on this node to compare against.
isRequired: true
example: "<other_column>"
2. Create Template
The Create Template branches on the reserved node.materializationType and uses the reserved column fields nullable, defaultValue, and description:
{# == Node Type Name : Work == #}
{# == Node Type Description : This node creates work table or view == #}
{#---------------------------------------------------------------------------------------------#}
{%- if node.materializationType == 'table' %}
{# CreateSQL for Table #}
{{ stage('Create ' + node.materializationType ) }}
CREATE OR REPLACE {{ node.materializationType }} {{ ref_no_link(node.location.name, node.name) }}
(
{%- for col in columns %}
"{{ col.name }}" {{ col.dataType }}
{%- if col.nullable == false %} NOT NULL {%- endif %}
{%- if col.defaultValue %} DEFAULT {{ col.defaultValue }} {%- endif %}
{%- if col.description | trim | length > 0 %} COMMENT '{{ col.description | escape }}' {%- endif %}
{%- if not loop.last -%}, {%- endif %}
{%- endfor %}
)
{%- if node.description | trim | length > 0 %} COMMENT = '{{ node.description | escape }}' {%- endif %}
{%- elif node.materializationType == 'view' %}
{# CreateSQL for View #}
{{ stage('Create ' + node.materializationType ) }}
CREATE OR REPLACE {{ node.materializationType }} {{ ref_no_link(node.location.name, node.name) }}
(
{%- for col in columns %}
"{{ col.name }}"
{%- if col.description | trim | length > 0 %} COMMENT '{{ col.description | escape }}' {%- endif %}
{%- if not loop.last -%}, {%- endif %}
{%- endfor %}
)
{%- if node.description | length > 0 %} COMMENT = '{{ node.description | escape }}' {%- endif %}
AS
{{ sources[0].cteString }}
SELECT {{ sources[0].selectModifier | default('', true) }}
{%- for col in sources[0].columns %}
{{ get_source_transform(col) }} AS "{{ col.name }}"
{%- if not loop.last -%}, {%- endif %}
{%- endfor %}
{{ sources[0].join }}
{%- endif %}
3. Run Template
The Run Template reads node-level annotations from config and delegates the tests to the run_tests macro. Note the guards: sql is mapping and sql.parameters is defined skips a bare @preSQL written without an argument, and the writeMode fallback supplies the default because declared defaults are not applied at runtime.
{# == Node Type Name : Work == #}
{# == Node Type Description : Loads the table with custom SQL logic == #}
{#---------------------------------------------------------------------------------------------#}
{# == To run data quality tests before data insertion == #}
{{ run_tests('Before') }}
{%- if node.materializationType == 'table' %}
{#== Pre-SQL ==#}
{%- set ns = namespace(cnt=0) %}
{%- for sql in config.preSQL %}
{%- if sql is mapping and sql.parameters is defined %}
{%- set ns.cnt = ns.cnt + 1 %}
{{ stage('Pre-SQL ' ~ ns.cnt )}}
{{ sql.parameters[0] }}
{%- endif %}
{%- endfor %}
{# == Determine write mode: truncateInsert (default) or append == #}
{%- if config.writeMode is defined and config.writeMode.parameters is defined and config.writeMode.parameters | length > 0 %}
{%- set writeMode = config.writeMode.parameters[0] | trim %}
{%- else %}
{%- set writeMode = 'truncateInsert' %}
{%- endif %}
{# == Set insert mode keyword based on writeMode == #}
{%- if writeMode | lower == 'append' %}
{%- set insertMode = '' %}
{%- else %}
{%- set insertMode = 'OVERWRITE' %}
{%- endif %}
{# == Insert data from sources into Work table == #}
{{ stage('Load ' + node.materializationType + ' using Insert') }}
INSERT {{ insertMode }} INTO {{ ref_no_link(node.location.name, node.name) }}
(
{%- for col in sources[0].columns %}
"{{ col.name }}"
{%- if not loop.last -%},{%- endif %}
{%- endfor %}
)
{{ sources[0].cteString }}
(
SELECT {{ sources[0].selectModifier | default('', true) }}
{%- for col in sources[0].columns %}
{{ get_source_transform(col) }} AS "{{ col.name }}"
{%- if not loop.last -%}, {%- endif %}
{%- endfor %}
{{ sources[0].join }}
)
{#== Post-SQL ==#}
{%- set ns = namespace(cnt=0) %}
{%- for sql in config.postSQL %}
{%- if sql is mapping and sql.parameters is defined %}
{%- set ns.cnt = ns.cnt + 1 %}
{{ stage('Post-SQL ' ~ ns.cnt ) }}
{{ sql.parameters[0] }}
{%- endif %}
{%- endfor %}
{%- else %}
{{ stage('Load Skipped for View') }}
-- The node {{node.name}} is materialized as View. Therefore, a Load operation is not supported.
SELECT 1 AS INFO_MESSAGE WHERE FALSE
{%- endif %}
{# == To run data quality tests after data insertion == #}
{{ run_tests('After') }}
4. Macros
The package ships three macros. run_tests renders the node-level tests from config.tests and the column-level tests from each column's annotations; get_hash builds a hash expression from the columns marked @inHash; get_boolean_config reads a flag written either bare or with a boolean argument.
{# ========================================================================
Purpose : Executes data quality tests associated with the node
and its columns, based on the configured run order.
Supports running tests either before or after data
insertion, and respects the test continuation
behavior on failure.
Input Parameters : runOrder (String)
- Determines when the tests should be executed.
Expected values: 'Before' or 'After'
Returns : Rendered SQL statements for executing the applicable
node-level and column-level data quality tests.
No output is generated if tests are disabled.
========================================================================= #}
{%- macro run_tests(runOrder) -%}
{%- set columnContinueOnFailure = true -%}
{%- if get_boolean_config("disableTests") != "true" -%}
{%- if runOrder == "After" -%}
{%- for col in columns -%}
{%- set colName = '"' + col.name + '"' -%}
{%- set accepted = col.accepted_values if (col.accepted_values is sequence and col.accepted_values is not mapping) else [] -%}
{%- set rejected = col.rejected_values if (col.rejected_values is sequence and col.rejected_values is not mapping) else [] -%}
{%- set target = ref(node.location.name, node.name) -%}
{%- if col.not_null is defined -%}
{{ test_stage(col.name + ': not_null', columnContinueOnFailure) }}
SELECT {{ colName }}
FROM {{ target }}
WHERE {{ colName }} IS NULL
{%- endif -%}
{%- if col.uniqueness is defined -%}
{{ test_stage(col.name + ': uniqueness', columnContinueOnFailure) }}
SELECT {{ colName }}
FROM {{ target }}
GROUP BY {{ colName }}
HAVING COUNT(*) > 1
{%- endif -%}
{%- if col.empty is defined -%}
{{ test_stage(col.name + ': empty', columnContinueOnFailure) }}
SELECT {{ colName }}
FROM {{ target }}
WHERE {{ colName }} IS NOT NULL AND TRIM({{ colName }}) = ''
{%- endif -%}
{%- if accepted | length > 0 -%}
{{ test_stage(col.name + ': accepted_values', columnContinueOnFailure) }}
SELECT {{ colName }}
FROM {{ target }}
WHERE {{ colName }} IS NOT NULL
AND {{ colName }} NOT IN ({%- for v in accepted -%} {{ v.parameters[0] }} {%- if not loop.last -%}, {%- endif -%}{%- endfor -%})
{%- endif -%}
{%- if rejected | length > 0 -%}
{{ test_stage(col.name + ': rejected_values', columnContinueOnFailure) }}
SELECT {{ colName }}
FROM {{ target }}
WHERE {{ colName }} IN ({%- for v in rejected -%} {{ v.parameters[0] }} {%- if not loop.last -%}, {%- endif -%}{%- endfor -%})
{%- endif -%}
{%- if col.min_max is defined and col.min_max.parameters is defined -%}
{{ test_stage(col.name + ': min_max', columnContinueOnFailure) }}
SELECT {{ colName }}
FROM {{ target }}
WHERE {{ colName }} IS NOT NULL
AND ({{ colName }} < {{ col.min_max.parameters[0] }} OR {{ colName }} > {{ col.min_max.parameters[1] }})
{%- endif -%}
{%- if col.min_value is defined and col.min_value.parameters is defined -%}
{{ test_stage(col.name + ': min_value', columnContinueOnFailure) }}
SELECT {{ colName }}
FROM {{ target }}
WHERE {{ colName }} IS NOT NULL AND {{ colName }} < {{ col.min_value.parameters[0] }}
{%- endif -%}
{%- if col.max_value is defined and col.max_value.parameters is defined -%}
{{ test_stage(col.name + ': max_value', columnContinueOnFailure) }}
SELECT {{ colName }}
FROM {{ target }}
WHERE {{ colName }} IS NOT NULL AND {{ colName }} > {{ col.max_value.parameters[0] }}
{%- endif -%}
{%- if col.freshness is defined and col.freshness.parameters is defined -%}
{{ test_stage(col.name + ': freshness', columnContinueOnFailure) }}
SELECT MAX(CAST({{ colName }} AS DATETIME)) AS "LATEST_VALUE"
FROM {{ target }}
HAVING MAX(CAST({{ colName }} AS DATETIME)) IS NULL
OR MAX(CAST({{ colName }} AS DATETIME)) < DATEADD({{ col.freshness.parameters[1] | default('DAY', true) }}, -{{ col.freshness.parameters[0] }}, CURRENT_TIMESTAMP())
{%- endif -%}
{%- if col.relative_time is defined and col.relative_time.parameters is defined -%}
{%- set other = '"' + col.relative_time.parameters[1] + '"' -%}
{{ test_stage(col.name + ': relative_time', columnContinueOnFailure) }}
SELECT {{ colName }}, {{ other }}
FROM {{ target }}
WHERE {{ colName }} IS NOT NULL AND {{ other }} IS NOT NULL
AND NOT ({{ colName }} {{ col.relative_time.parameters[0] }} {{ other }})
{%- endif -%}
{%- endfor -%}
{%- endif -%}
{%- set ns = namespace(cnt=0) -%}
{%- for test in config.tests | default([]) -%}
{%- if test is mapping and test.parameters is defined -%}
{%- if test.parameters[2] | default("After") == runOrder -%}
{%- set ns.cnt = ns.cnt + 1 -%}
{{ test_stage(
('Pre Load Test ' if runOrder == 'Before' else 'Post Load Test ') ~ ns.cnt,
test.parameters[1] | default(true)
) }}
{{ test.parameters[0] }}
{%- endif -%}
{%- endif -%}
{%- endfor -%}
{%- endif -%}
{%- endmacro -%}
{#===================================================================================================
Description:
Generates a deterministic hash value by combining and hashing all column values associated with the given `hash_name` using the `@inHash` annotation.
Input: `hash_name` (string) - The hash group identifier used in `@inHash("<hash_name>")` to group columns for hashing.
Output: - Returns a SQL expression of type `STRING`:
- A SHA1 hash of concatenated column values
- Returns `NULL` if no columns are mapped to the given hash group
====================================================================================================#}
{%- macro get_hash(hash_name, algo='SHA1', delimiter='||') -%}
{%- set cols = namespace(items=[]) -%}
{%- set algo_upper = algo | upper -%}
{# Collect columns participating in this hash #}
{%- for col in sources[0].columns -%}
{%- if col.inHash is defined -%}
{%- for p in col.inHash -%}
{%- set parts = p.parameters[0] %}
{%- set position = p.parameters[1] | int -%}
{%- set hash = p.parameters[0] -%}
{%- if hash == hash_name -%}
{%- set cols.items = cols.items + [{
"position": position,
"column": get_source_transform(col)
}] -%}
{%- endif -%}
{%- endfor -%}
{%- endif -%}
{%- endfor -%}
{%- if cols.items | length == 0 -%}
NULL
{%- else -%}
{%- set sorted_cols = cols.items | sort(attribute='position') -%}
{# Define algo wrappers #}
{%- set algo_start = algo_upper + '(' -%}
{%- set algo_end = ')' -%}
{%- if algo_upper == 'SHA256' -%}
{%- set algo_start = 'SHA2(' -%}
{%- set algo_end = ', 256)' -%}
{%- endif -%}
CAST(
{{ algo_start }}
{%- for c in sorted_cols -%}
NVL(CAST({{ c.column }} AS VARCHAR), 'null')
{%- if not loop.last %}
|| {%- if delimiter != '' %} '{{ delimiter }}' || {% endif -%}
{%- endif -%}
{%- endfor -%}
{{ algo_end }}
AS STRING)
{%- endif -%}
{%- endmacro -%}
{#===================================================================================================
get_boolean_config(flag)
Description:
Extracts the boolean value of a configuration flag, supporting both
annotation formats:
- @flag
- @flag(true) / @flag(false)
Input:
flag: Name of the boolean configuration flag, e.g. "truncateBefore",
"testsEnabled", or "clusterDefined".
Examples:
@truncateBefore
-> config["truncateBefore"] = true
-> Output: true
@truncateBefore(true)
-> config["truncateBefore"] = { parameters: [true] }
-> Output: true
@truncateBefore(false)
-> config["truncateBefore"] = { parameters: [false] }
-> Output: false
No annotation
-> config["truncateBefore"] is not defined
-> Output: empty
Usage:
{% if get_boolean_config("truncateBefore") | trim | lower == "true" %}
...
{% endif %}
====================================================================================================#}
{%- macro get_boolean_config(flag) -%}
{%- if config[flag] is defined -%}
{%- if config[flag] is boolean -%}
{{- config[flag] | trim | lower -}}
{%- elif config[flag].parameters is defined and config[flag].parameters|length > 0 -%}
{{- config[flag].parameters[0] | trim | lower -}}
{%- endif -%}
{%- endif -%}
{%- endmacro -%}
5. Set the Values in the Node SQL
@id("87e9ebb0-856f-43b7-b7ba-efe100a16742")
@nodeType("707")
@description("V2 Work node demonstrating every supported annotation")
@writeMode("append")
@tests("SELECT 1 FROM {{ this }} GROUP BY N_NATIONKEY HAVING COUNT(*) > 1", false)
@tests("SELECT 1 FROM {{ this }} WHERE N_REGIONKEY IS NULL", true, "Before")
SELECT DISTINCT
"N_NATIONKEY" AS "N_NATIONKEY" @notNull @not_null @uniqueness @min_value("0") @max_value("100") @inHash("GH_COL1", 2) @description("Nation key"),
"N_NAME" AS "N_NAME" @not_null @empty @accepted_values("'ALGERIA'") @accepted_values("'ARGENTINA'") @inHash("GH_COL1", 1) @description("Nation name"),
"N_REGIONKEY" AS "N_REGIONKEY" @defaultValue("0"),
{{ get_hash("GH_COL1") }} AS "NATION_HASH"
FROM {{ ref('SRC', 'NATION') }} "NATION"
This produces:
node.materializationType = "table"(reserved; defaulted because the annotation is omitted)node.description = "V2 Work node demonstrating every supported annotation"(reserved)config.writeMode = { parameters: ["append"] }config.tests = [{ parameters: ["SELECT 1 FROM ... HAVING COUNT(*) > 1", false] }, { parameters: ["SELECT 1 FROM ... IS NULL", true, "Before"] }]config.disableTestsis undefined, sorun_testsruns the testssources[0].selectModifier = "DISTINCT"- On
N_NATIONKEY:column.nullable = falseandcolumn.description = "Nation key"(reserved);column.not_null = true,column.uniqueness = true,column.min_value = { parameters: ["0"] },column.max_value = { parameters: ["100"] },column.inHash = [{ parameters: ["GH_COL1", 2] }] - On
N_NAME:column.accepted_values = [{ parameters: ["'ALGERIA'"] }, { parameters: ["'ARGENTINA'"] }]andcolumn.inHash = [{ parameters: ["GH_COL1", 1] }] - On
N_REGIONKEY:column.defaultValue = "0"(reserved)
Accessing Annotation Values in Templates
- Bare annotations produce
true. Use them directly:{% if config.disableTests %}for node-level,{% if col.not_null is defined %}for column-level. - Parameterized annotations produce
{ parameters: [...] }. Read a value withconfig.writeMode.parameters[0], guarding withis definedand supplying your own default. - Repeatable annotations produce an ordered array, one
{ parameters: [...] }entry per occurrence. Loop with{% for entry in config.preSQL | default([]) %}. - Reserved annotations set real fields:
node.materializationType,node.description,col.description,col.nullable,col.defaultValue. sources[0].selectModifiercarries any modifier the author wrote afterSELECT, such asDISTINCTorTOP 10. It is undefined when there is none, so read it assources[0].selectModifier | default('', true).
Quoting and Escaping
Annotation string values can be wrapped in either double or single quotes:
@materializationType("table")
@writeMode('append')
When a value contains quotes, wrap it in the opposite quote style. This matters for case-sensitive identifiers inside a test or SQL statement:
@preSQL("SELECT 1 FROM {{ this }} GROUP BY N_NAME HAVING COUNT(*) > 1")
@preSQL('SELECT 1 FROM {{ this }} GROUP BY "N_Name" HAVING COUNT(*) > 1')
Values that templates paste into SQL verbatim, such as test bounds and default values, must be quoted for the column's data type: @min_max("0", "24") for numbers, @accepted_values("'ALGERIA'") for a string.
What's Next?
- Node Types Overview for how annotations fit the overall model.
- Getting Started with SQL Nodes to create your first SQL Node.
- Defining a SQL Node Type to declare annotations on your own Node Type.
- The SQL Node Editor for a tour of the SQL Node editing experience.
- Troubleshooting and FAQ for parse, column, and annotation issues.