Base Node Types - SQL

‹ View all packages

Package ID: @coalesce/snowflake/base-node-types-sql

Supported Platform:

Latest Version: 2.3.0 (October 12, 2026)

annotationscomplex sql queriesdata-quality-testseltincrementalloadsql-firstsql-interfacestage

Overview

SQL-first Node Types for staging flows with inline annotations, automatic metadata extraction, dependency handling authoring experience in Coalesce.

Installation

1. Copy the Package ID: @coalesce/snowflake/base-node-types-sql
2. In Coalesce, open the Workspace where you wish to install the package.
3. Go to the Build Setting of the Workspace, tab Packages, and click the Install button on the top right of the page.
4. Paste the Package ID, and proceed with the installation process.

Description

SQL-First NodeTypes

The SQL-first nodes is a transformation tool within Coalesce that lets developers write custom, hand-coded SQL instead of using the standard graphical column-mapping interface. It is ideal for complex transformations, advanced window functions, or multi-step logic that is difficult to represent with the standard UI, and ships with a built-in library of column- and node-level data quality tests. While it provides maximum flexibility, it shifts the responsibility of column definition and logic maintenance to the SQL author.

Coalesce Base Node Types - SQL Package

The Coalesce Base Node Types - SQL Package includes:

• Work
• Dimension
• Fact

Concepts shared across every node type — quote style, hash columns, data quality tests, known limitations, and usage examples — are documented once in Common Reference and linked to from each node type below.

Node Type Comparison

A side-by-side view of which annotations each node type supports, so it's easy to see what a given node type is missing compared to the others.

Node Annotations Matrix

Annotation	Work	Dimension	Fact
@writeMode	✅	✅	✅
@mergeStrategy	➖	✅ * changeTracking * lastModified * upsert	✅ * changeTracking * lastModified * upsert * allColumnMatch
@zeroKey (node-level)	➖	✅	➖
@disableTests	✅	✅	✅
@tests	✅	✅	✅
@preSQL	✅	✅	✅
@postSQL	✅	✅	✅
@tag (node-level)	✅	✅	✅
@materializationType	table / transient table / view	table / transient table / view	table / transient table / view
Target Load	INSERT (OVERWRITE)	(TRUNCATE) MERGE	(TRUNCATE) MERGE/INSERT

Column Annotations Matrix

Annotation	Work	Dimension	Fact
@id (required)	➖	✅	✅
@isBusinessKey	➖	✅ (required)	✅ (conditional) ³
@isChangeTracking	➖	✅	➖
@lastModifiedTracking	➖	✅	✅
@zeroKey (column-level)	➖	✅	➖
@isSurrogateKey	➖	✅	➖
@isSystemVersion	➖	✅	➖
@isSystemCurrentFlag	➖	✅	➖
@isSystemCreateDate	➖	✅	✅
@isSystemUpdateDate	➖	✅	✅
@isSystemEndDate	➖	✅	➖
@tag (column-level)	✅	✅	✅
@clusterKey	✅	✅	✅
all column tests	✅	✅	✅

See Column-Level Data Quality Tests for the shared quality-test annotations in the last row.

³ Fact: required for an explicit @mergeStrategy of changeTracking, lastModified or upsert; optional otherwise — with neither @mergeStrategy nor @isBusinessKey the node is a plain insert, and allColumnMatch ignores it. See Fact Load Behaviour.

Work

The Work node is a general-purpose transformation node within Coalesce, used to materialize intermediate or staging-layer tables and views as part of a larger transformation pipeline. It sits between raw source data and downstream modeled objects, giving developers a flexible landing point to shape, clean, and validate data — complete with a built-in library of column- and node-level data quality tests — before it flows further into the pipeline.

Work Node Configuration

The Work Node type has three configuration groups:

• General
• Node Annotations
• Column Annotations

Work General Options

Property	Description
Storage Location	Storage Location where the Work table or view will be created
Node Type	Read-only: the node type's name and ID — Work (SQLWork). Set from @nodeType when the node is created; the ID has a copy button.

Work Node Annotations

Annotation	Summary
@id(id) (reserved)	Unique identifier for the node. Static and auto-generated when the node is created — not meant to be edited.
@nodeType(type) (reserved)	Identifies the node's type. Set automatically based on the node type chosen when the node is created.
@description(text) (reserved)	Node-level description. Can be edited via this annotation or in the node description field below the node name in the UI. Example: @description("Table description")
@materializationType(type) (reserved)	Table, transient table or view
@deployDisabled (reserved)	Excludes this node from deployment.
@tag(name, value, storageLocation?) (repeatable)	Applies a Snowflake tag to this table/view.
@writeMode("truncateInsert | append")	How each run writes to the target: truncateInsert or append Not specified in the SQL editor → defaults to truncateInsert.
@disableTests ²	Controls whether configured tests are skipped.
@tests(querySQL, continueOnFailure?, runOrder?) ² (repeatable)	Node-level data quality test.
@preSQL(querySQL) (repeatable)	SQL statement to execute before the data load operation.
@postSQL(querySQL) (repeatable)	SQL statement to execute after the data load operation.

Work Column Annotations

Annotation	Summary
@notNull (reserved)	Marks column as NOT NULL. Note: Ignored on Views. Example: @notNull
@description(<text>) (reserved)	Adds column description. Example: @description("timestamp column")
@defaultValue(<value>) (reserved)	Adds default value.
@inHash("<hashName>", <hashOrder>) ¹ (repeatable)	Marks a column as an input to a generated hash key.
@tag(name, value, storageLocation?) (repeatable)	Applies a Snowflake tag to this column.
@clusterKey(position, expression?) (repeatable)	Marks a column as part of the table's clustering key, in the order given by position (lowest first).

Details for each annotation are in the Annotation Reference.

Note: Quote style matters for case-sensitive identifiers when writing @tests, @preSQL, and @postSQL — see Quote Style for Case-Sensitive Identifiers.

🚦 The full set of column-level data quality tests (@not_null, @uniqueness, @empty, @accepted_values, @rejected_values, @min_max, @min_value, @max_value, @freshness, @relative_time) applies to Work columns exactly as described in Column-Level Data Quality Tests.

---

Work Deployment

Every deployment of a Work node first runs these checks, which stop the deployment before any change in Snowflake:

Stage	Description
Unsupported Materialization Type: <node>	@materializationType is not table, transient table or view
Duplicate Tags Found: <node or column>	A duplicate tag: the same tag (same name and location) applied more than once on the node or on one column. Repeating @tag for different tags is fine.

Work Initial Deployment

When deployed for the first time into an Environment, the Work Node executes one of these stages, depending on its materialization type:

Stage	Description
Create table	Executes a CREATE OR REPLACE TABLE statement, with column and table tags, comments and the cluster key
Create transient table	Same as above, as a TRANSIENT TABLE
Create view	Executes a CREATE OR REPLACE VIEW statement, with column and view tags and comments

Work Redeployment

After the Work Node has been deployed for the first time into a target Environment, subsequent deployments either alter the existing object, recreate it, or only record a metadata update when nothing in the object changed (for example tests, @writeMode, @preSQL/@postSQL, a transform or join on a table, or a data type change that leaves a view's SQL unchanged):

Stage	Description
Metadata Update-Default	The node changed, but nothing in Snowflake needs to change

Altering the Work Tables

Changes to columns, the table description, the cluster key, tags, or the table name or location alter the Work Table, whether made individually or all together. The changes are made on an internal clone of the table, so a failure leaves the live table untouched.

The following stages are executed, in this order (each one only when needed):

Stage	Description
Clone Table	Creates an internal clone of the table (<table>_COALESCE_INTERNAL_TABLE), with its data, cluster key, tags and grants
Warning: Cluster Key Change	Non-failing message when a clustered column is renamed or dropped (see note below)
Drop Cluster Key	Drops the cluster key when a clustered column is renamed
Alter Column	Changes data types, nullability, column descriptions and defaults, in one statement
Add Column	Adds new columns, with their tags and comments
Rename Column	Renames a column whose @id is unchanged, one stage per column
Recluster Table | Drop Cluster Key	Applies the new cluster key, or drops it when no @clusterKey remains
Delete Column	Drops removed columns
Edit Table Description	Updates the table comment
Unset Column Tags: &lt;column> | Set Column Tags: &lt;column>	Removes and sets column tags; a changed tag value is only a set
Unset Table Tags | Set Table Tags	Removes and sets table tags; a changed tag value is only a set
Swap Cloned Table	Swaps the altered clone with the live table
Rename Table	Instead of the swap, when the table is renamed or moved: the clone is renamed to the new name/location
Delete Table	Drops the internal clone after a swap, or the original table after a rename

Note: A column is renamed only when its @id stays the same. If the @id changes too, the existing column is dropped and a new column is created, which loses its data — give columns a stable @id to rename them safely.

Note — clustered columns: Snowflake does not allow renaming or dropping a column while it is part of the cluster key, so these are handled on the clone, starting with a non-failing Warning: Cluster Key Change stage that lists the renamed and dropped clustered columns and the new key:

• Renaming a clustered column (same @id) — the cluster key is dropped, the column is renamed, and the table is reclustered with the new name.
• Dropping a clustered column — the table is reclustered on the remaining @clusterKey columns (or the cluster key is dropped when none remain), and then the column is dropped.

Note: Snowflake only allows widening a column's data type (for example VARCHAR(25) → VARCHAR(100)) and only sequence values for SET DEFAULT. Other data type or default changes fail on the clone with Snowflake's error, leaving the live table untouched.

Recreating the Work Tables

If the materialization type changes (table ↔ view, or table ↔ transient table), the existing object is dropped and recreated:

Stage	Description
Drop table | Drop transient table | Drop view	Drops the existing object
Create table | Create transient table | Create view	Recreates the node with the new materialization type

Altering and Recreating the Work Views

A view is changed in place only when its tags change; any other change to the view's SQL (columns, transforms, joins, descriptions) recreates it:

Stage	Description
Unset Column Tags: <column> | Set Column Tags: <column> | Unset View Tags | Set View Tags	Only tags changed: the view is altered in place
Create view	The view definition changed: CREATE OR REPLACE VIEW with the new definition
Drop view + Create view	The view is renamed or moved: the old view is dropped and the new one created
Metadata Update-Default	Only tests changed (@tests, column tests such as @not_null, or @disableTests) — they are not part of the view's SQL, so the view is not recreated or altered; only the node metadata is updated

Removing a Work Node

If a Work Node of materialization type table is deleted from a SQL Workspace, that SQL Workspace is committed to Git and that commit deployed to a higher-level Environment, then the Work Table in the target Environment will be dropped.

This is executed in two stages:

Stage	Description
Delete Table	Drops the internal clone, if one was left behind by a failed deployment
Drop table	Drops the target table in Snowflake

If a Work Node of materialization type view is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher-level Environment, then the Work View in the target Environment will be dropped.

The stage executed:

Stage	Description
Drop view	Drops the existing Work view from the target Environment

---

Dimension

The Dimension node is a specialized transformation node within Coalesce, used to materialize dimension tables that track descriptive attributes over time. It merges incoming source data into the target using a configurable change-detection strategy — plain upsert, column-based change tracking, or a last-modified timestamp — and, depending on that strategy and the columns marked on the node, produces either an SCD Type 1 (overwrite-in-place) or SCD Type 2 (versioned history) dimension, complete with a built-in library of column- and node-level data quality tests.

Dimension Node Configuration

The Dimension Node type has three configuration groups:

• General
• Node Annotations
• Column Annotations

Dimension General Options

Property	Description
Storage Location	Storage Location where the Dimension table or view will be created
Node Type	Read-only: the node type's name and ID — Dimension (SQLDimension). Set from @nodeType when the node is created; the ID has a copy button.

Dimension Node Annotations

Annotation	Summary
@id(id) (reserved)	Unique identifier for the node. Static and auto-generated when the node is created — not meant to be edited.
@nodeType(type) (reserved)	Identifies the node's type. Set automatically based on the node type chosen when the node is created.
@description(text) (reserved)	Node-level description. Can be edited via this annotation or in the node description field below the node name in the UI. Example: @description("Table description")
@materializationType(type) (reserved)	Table, transient table or view
@deployDisabled (reserved)	Excludes this node from deployment.
@tag(name, value, storageLocation?) (repeatable)	Applies a Snowflake tag to this table/view.
@writeMode("truncateInsert | append")	How each run writes to the target: truncateInsert or append Not specified in the SQL editor → defaults to append.
@mergeStrategy("upsert | changeTracking | lastModified")	Chooses how this dimension decides a row has changed, and therefore which column annotations it requires.
@zeroKey(surrogateKeyValue, stringValue?, timestampValue?, booleanValue?)	Inserts a default "zero key" record into the target — a placeholder row used to catch unresolved foreign key lookups.
@disableTests ²	Controls whether configured tests are skipped.
@tests(querySQL, continueOnFailure?, runOrder?) ² (repeatable)	Node-level data quality test.
@preSQL(querySQL) (repeatable)	SQL statement to execute before the data load operation.
@postSQL(querySQL) (repeatable)	SQL statement to execute after the data load operation.

Dimension Column Annotations

Column annotations shared with Work and Fact

Annotation	Summary
@id("<value>")	Stable column ID for lineage tracking.
@notNull (reserved)	Marks column as NOT NULL. Note: Ignored on Views. Example: @notNull
@description(<text>) (reserved)	Adds column description. Example: @description("timestamp column")
@defaultValue(<value>) (reserved)	Adds default value.
@inHash("<hashName>", <hashOrder>) ¹ (repeatable)	Marks a column as an input to a generated hash key.
@tag(name, value, storageLocation?) (repeatable)	Applies a Snowflake tag to this column.
@clusterKey(position, expression?) (repeatable)	Marks a column as part of the table's clustering key, in the order given by position (lowest first).

Dimension-specific column annotations

Annotation	Summary
@isBusinessKey (required)	Marks a column as part of the business key used to match existing rows during the merge. Note: Ignored on Views. Example: @isBusinessKey
@lastModifiedTracking(scdType?)	Marks the column used to detect newer source rows for an incremental load.
@isChangeTracking	Marks a column to be watched for changes that decide SCD type.
@zeroKey(value)	Adds a custom zero key value (ghost record) to this column, overriding the node-level @zeroKey defaults for it.
@isSurrogateKey	Marks this column as the node's surrogate key.
@isSystemVersion	Marks this column as the SCD version number, incremented each time a business key gets a new version.
@isSystemCurrentFlag	Marks this column as the SCD "is current" flag — 'Y' on the active version of a row, overwritten to 'N' when it's expired.
@isSystemCreateDate	Marks this column as the timestamp a row (or, under SCD Type 2, a specific row version) was first created.
@isSystemUpdateDate	Marks this column as the timestamp a row was last updated.
@isSystemEndDate	Marks this column as the SCD Type 2 expiration timestamp — a far-future sentinel while the row is current, set to the actual expiry time once superseded.

Shared annotations link to the Annotation Reference; the Dimension-specific ones are described in Dimension Annotation Details.

Note: Quote style matters for case-sensitive identifiers when writing @tests, @preSQL, and @postSQL — see Quote Style for Case-Sensitive Identifiers.

🚦 The full set of column-level data quality tests (@not_null, @uniqueness, @empty, @accepted_values, @rejected_values, @min_max, @min_value, @max_value, @freshness, @relative_time) applies to Dimension columns exactly as described in Column-Level Data Quality Tests.

Dimension Annotation Details

@mergeStrategy (Dimension)

@mergeStrategy("upsert | changeTracking | lastModified")

Chooses how this dimension decides a row has changed, and therefore which column annotations it requires.

Value	Description
upsert	No change detection at all; a matched row is always updated, an unmatched row is inserted. Doesn't assert any SCD type of its own — if the upstream SELECT/CTE already implements SCD1/SCD2 logic, this strategy just merges that result through as-is. The SELECT/CTE should return one row per business key (de-duplicate upstream). System columns are optional; any that are present are written exactly as the SELECT/CTE computes them. @lastModifiedTracking/@isChangeTracking columns aren't used — if present, a warning is raised and they're ignored.
changeTracking	Compares columns marked @isChangeTracking (or, if none are marked, every plain attribute column) to detect a change. SCD Type 2 if any column is marked @isChangeTracking, otherwise SCD Type 1. Doesn't use @lastModifiedTracking — if present, a warning is raised and it's ignored.
lastModified	Compares a single @lastModifiedTracking timestamp column against the target's stored value. Requires exactly one column marked @lastModifiedTracking — a validation check flags it if none (or more than one) is present. SCD Type 1 or 2 comes from that column's own scdType parameter. Doesn't use @isChangeTracking — if present, a warning is raised and it's ignored.

• Not specified in the SQL editor → defaults to changeTracking - SCD Type 1.
• The value is not case-sensitive; any other value is flagged by a validation check.
• Every strategy merges on the business key, so the SELECT/CTE must return exactly one row per business key, with no NULL business key — see Duplicate or NULL Business Keys for a Before test that checks this.
• Under changeTracking / lastModified the load writes fixed system column values — see System column values.
• Note: Ignored on Views.

Example:

@mergeStrategy("lastModified")

@zeroKey (Dimension node)

@zeroKey(surrogateKeyValue, stringValue?, timestampValue?, booleanValue?)

Inserts a default "zero key" record into the target — a placeholder row used to catch unresolved foreign key lookups.

Parameter	Description
surrogateKeyValue	(required) Value used for the zero record's surrogate key column only.
stringValue	(optional) Default value for string/varchar columns, pasted into the SQL verbatim — include your own quotes. Default "'UNKNOWN'".
timestampValue	(optional) Default value for date/time/timestamp columns. Default "1900-01-01 00:00:00".
booleanValue	(optional) Default value for boolean columns. Default true.

• Numeric columns (integer, decimal, float) always get 0; override any column with a column-level @zeroKey(value).
• A string value longer than its column (for example 'UNKNOWN' in a VARCHAR(4)) raises a non-failing Warning: Zero Key Value Too Long test stage that lists each such column; Snowflake then rejects the value and the Zero Key Record stage fails. Set a shorter value on the column with @zeroKey("'<value>'"), or widen the column.
• Not specified in the SQL editor → the zero record is not inserted.
• Note: Ignored on Views.

Example:

@zeroKey(0, "'UNKNOWN'", "1900-01-01 00:00:00", true)

@isChangeTracking (Dimension)

@isChangeTracking

Marks a column to be watched for changes that decide SCD type.

• Marked on any column → SCD Type 2 — a change expires the existing row and inserts a new version.
• Not marked on any column → SCD Type 1 — a change updates the row in place.
• Under SCD Type 2, a change in a column not marked @isChangeTracking updates the current row in place — no new version is created.
• Note: Ignored on Views.

Example:

@isChangeTracking

@zeroKey (Dimension column)

@zeroKey(value)

Adds a custom zero key value (ghost record) to this column, overriding the node-level @zeroKey defaults for it.

Parameter	Description
value	Zero key value for this column, pasted into the SQL verbatim.

• The value is pasted into the SQL verbatim, so quote it to match the column's data type.
• A string value longer than the column's declared length raises the non-failing Warning: Zero Key Value Too Long stage (see @zeroKey (Dimension node)).
• Note: Ignored unless @zeroKey is enabled at the node level, and ignored on Views.

Example:

@zeroKey("'N/A'")                                   -- string: quotes inside the annotation
@zeroKey("-1")                                      -- number
@zeroKey("0.00")                                    -- decimal
@zeroKey("'1900-01-01'")                            -- date (or "TO_DATE('1900-01-01')")
@zeroKey("TO_TIMESTAMP('1900-01-01 00:00:00')")     -- timestamp
@zeroKey(false)                                     -- boolean (unquoted)
@zeroKey("PARSE_JSON('{}')")                        -- variant

@isSurrogateKey (Dimension)

@isSurrogateKey

Marks this column as the node's surrogate key.

• Optional for SCD Type 1 — the merge logic joins and detects changes entirely off the business key.
• Recommended for SCD Type 2 — a stable surrogate key per version is the standard way to identify a versioned row, though it isn't enforced by the merge itself.
• Required only when the node-level @zeroKey is set, since the zero/ghost record is matched against the target by surrogate key value.
• Note: Ignored on Views. Agentic creation only — manual creation adds this column automatically.
• Expected expression: 0 AS "<NODE_NAME>_KEY" @isSurrogateKey — type the node name yourself, e.g. 0 AS "DIM_CUSTOMER_KEY" @isSurrogateKey. A {{NODE_NAME}} placeholder is not filled in and would create a column named _KEY.

@isSystemVersion (Dimension)

@isSystemVersion

Marks this column as the SCD version number, incremented each time a business key gets a new version.

• Required for SCD Type 2 — shows which version of a row this is; each time a business key's row changes, the expired row is kept and a new row is inserted with this value incremented by 1.
• Optional for SCD Type 1 — new-vs-existing is detected off the business key alone, so this column can be dropped entirely; if kept, it is always written as 1 and never incremented.
• Optional for upsert — if kept, it is written exactly as the SELECT/CTE computes it.
• Note: Ignored on Views. Agentic creation only — manual creation adds this column automatically.
• Expected expression: 1 AS "SYSTEM_VERSION" @isSystemVersion

@isSystemCurrentFlag (Dimension)

@isSystemCurrentFlag

Marks this column as the SCD "is current" flag — 'Y' on the active version of a row, overwritten to 'N' when it's expired.

• Required for SCD Type 2 — used to filter the target down to each business key's one current row before comparing it against the incoming source row.
• Optional for SCD Type 1 — safe to omit; if kept, it is always written as 'Y'.
• Optional for upsert — if kept, it is written exactly as the SELECT/CTE computes it (e.g. 'N' for a row the CTE is expiring).
• Note: Ignored on Views. Agentic creation only — manual creation adds this column automatically.
• Expected expression: 'Y' AS "SYSTEM_CURRENT_FLAG" @isSystemCurrentFlag

@isSystemEndDate (Dimension)

@isSystemEndDate

Marks this column as the SCD Type 2 expiration timestamp — a far-future sentinel while the row is current, set to the actual expiry time once superseded.

• Required for SCD Type 2 — this is what makes a version's active window queryable; omitting it leaves expired versions with no expiry marker.
• Optional for SCD Type 1 (no versioning, so nothing ever expires) — safe to omit entirely.
• Optional for upsert — if kept, it is written exactly as the SELECT/CTE computes it.
• Note: Ignored on Views. Agentic creation only — manual creation adds this column automatically.
• Expected expression: CAST('2999-12-31 00:00:00' AS TIMESTAMP) AS "SYSTEM_END_DATE" @isSystemEndDate

---

Dimension SCD Type & System Column Requirements

@mergeStrategy picks the change-detection strategy; which SCD type it resolves to (and therefore which system columns are required) depends on that strategy and, for changeTracking/lastModified, on which columns are marked:

@mergeStrategy	Resolves to	Driven by
upsert	Whatever SCD shape the upstream SELECT/CTE already implements — Coalesce doesn't detect or enforce SCD1/SCD2 here, it just merges the result through as-is. Out of the box (plain SELECT, one row per business key) this behaves as an SCD1 overwrite; SCD2 needs the CTE to do the versioning — see note ²	User's own SQL logic, not any column annotation
lastModified	SCD1 or SCD2, from that column's own scdType parameter	@lastModifiedTracking(scdType)
changeTracking (default)	SCD2 if any column is marked, otherwise SCD1	@isChangeTracking

Requirement level of each column annotation, by resolved strategy/SCD type:

Annotation	Category	SCD1 (changeTracking / lastModified)	SCD2 (changeTracking / lastModified)	upsert
@isBusinessKey	Key	🔴	🔴	🔴
@isSurrogateKey	Key	⚪	🟡	⚪
@isSystemCreateDate	System column	🔴	🔴	⚪
@isSystemUpdateDate	System column	🔴	🔴	⚪
@isSystemVersion	System column	⚪	🔴	⚪
@isSystemCurrentFlag	System column	⚪	🔴	⚪
@isSystemEndDate	System column	⚪	🔴	⚪

Legend: 🔴 Required · 🟡 Recommended · ⚪ Optional¹

• ¹ upsert never reads the target back to compare, so no system column is needed for change detection. Any system column that is present is written exactly as the SELECT/CTE computes it — on insert and on every update — except @isSystemCreateDate, which keeps its first-inserted value. The default columns the SQL editor generates (SYSTEM_VERSION → 1, SYSTEM_CURRENT_FLAG → 'Y', SYSTEM_END_DATE → '2999-12-31', create/update date → current timestamp) therefore still load as expected.
  
• ² SCD2 with upsert: the merge matches on the @isBusinessKey column(s) only, so to keep history mark a key that is unique per version as @isBusinessKey (e.g. a hash of the natural key and effective date, or your own version key — not the @isSurrogateKey identity column), and have the CTE return both rows: the existing version re-emitted with its expiry values (SYSTEM_CURRENT_FLAG → 'N', SYSTEM_END_DATE → now), which updates the old row, and the new version with a new key, which is inserted.
  
• @lastModifiedTracking/@isChangeTracking on an upsert node are never read — a warning is raised and they're ignored.
  
• Marking @isChangeTracking on any column is itself what makes the node SCD2 under changeTracking.
  

System column values

Under changeTracking and lastModified the load writes fixed values into the system columns. The expression the SELECT gives a system column is only used to derive the column's datatype — its value is ignored:

Column	Value written
@isSystemVersion	1 on a new key; previous version + 1 on a new SCD2 version
@isSystemCurrentFlag	'Y' on the current row; 'N' on an expired SCD2 row
@isSystemCreateDate	CURRENT_TIMESTAMP on first insert and on each new SCD2 version, then kept
@isSystemUpdateDate	CURRENT_TIMESTAMP whenever the row is inserted, changed, or expired as an old SCD2 version
@isSystemEndDate	'2999-12-31 00:00:00' on the current row; the expiry time (one millisecond before the load) on an expired SCD2 row

To use different values (e.g. 'Yes'/'No' flags or another end-date sentinel), use upsert and compute them in the SELECT/CTE — upsert writes system columns exactly as the SELECT/CTE computes them.

---

Dimension Deployment

Dimension Initial Deployment

Every deployment of a Dimension Node first runs these checks before any change in Snowflake:

Stage	Description
Unsupported Materialization Type: <node>	@materializationType is not table, transient table or view — stops the deployment and the run
Duplicate Tags Found: <node or column>	A duplicate tag: the same tag (same name and location) applied more than once on the node or on one column — stops the deployment. Repeating @tag for different tags is fine.
Duplicate Column Names | Duplicate Column IDs | Missing Column IDs | Missing Business Key | Invalid configuration: … | Missing Required System Columns | Surrogate Key Not Set/Using Custom Surrogate Key Logic | Warning: Zero Key Value Too Long	Dimension validation checks (table only), rendered only when the annotations have a problem — see Dimension Load. A required problem stops the deployment; a recommendation does not.

When deployed for the first time into an Environment, the Dimension Node executes one of these stages, depending on its materialization type:

Stage	Description
Create table	Executes a CREATE OR REPLACE TABLE statement, with the @isSurrogateKey column as an identity column, column and table tags, comments and the cluster key
Create transient table	Same as above, as a TRANSIENT TABLE
Create view	Executes a CREATE OR REPLACE VIEW statement, with column and view tags and comments — see Dimension View

Dimension View

A Dimension Node of materialization type view is a plain CREATE OR REPLACE VIEW over the node's SELECT:

• None of the Dimension validation checks run — @isBusinessKey, system columns, @id and @mergeStrategy aren't required or checked.
• There is no merge; the run shows Load Skipped for View. @mergeStrategy, @writeMode, @zeroKey, @preSQL, @postSQL and the tracking annotations are ignored.
• Any system column (surrogate key, version, current flag, create/update/end date) is selected as a typed NULL, keeping the column's datatype.
• @tag annotations (node and column) apply to the view; @clusterKey is ignored.
• Column-level data quality tests and node-level @tests still run.

Dimension Load

Every deployment of a Dimension Node of materialization type table runs its configured data quality tests, then merges data into the target using the stages below, depending on the resolved @mergeStrategy and SCD type:

Stage	Description
Pre Load Test <n>	Node-level @tests(..., "Before") tests, in the order they appear.
Duplicate Column Names | Duplicate Column IDs | Missing Column IDs | Missing Business Key | Invalid configuration: … | Missing Required System Columns | Surrogate Key Not Set/Using Custom Surrogate Key Logic | Warning: Zero Key Value Too Long	Validation checks — rendered only when the node's annotations have a problem (the SELECT returns the same column name twice, two columns share an @id value, a column without @id, no @isBusinessKey, an unrecognised or conflicting @mergeStrategy/tracking column, a missing system column, a surrogate key that is only recommended (non-failing), or a zero key string longer than its column (non-failing). Each one flags the problem; see the individual annotations for what triggers it.
Check NULL values for <column> column	Pre-load check for @mergeStrategy("lastModified") — flags a NULL @lastModifiedTracking value in the source.
Pre-SQL <n>	Each @preSQL statement, in the order they appear.
Truncate table	Executed only when @writeMode("truncateInsert") is set — clears the target before the merge.
Load table Using Merge - Zero Key Record	Executed only when node-level @zeroKey is set and a surrogate key column exists — merges the placeholder "zero key" row into the target.
Load table Using Merge - Upsert	Executed for @mergeStrategy("upsert") — merges source rows straight through with no change detection: every matched row is updated from the SELECT/CTE (system columns included, @isSystemCreateDate excepted) and every unmatched row is inserted.
Load table Using Merge - Last Modified Comparison - SCD1 | SCD2	Executed for @mergeStrategy("lastModified") — compares the @lastModifiedTracking column against the target to detect changes, then updates in place (SCD1) or versions the row (SCD2) per that column's scdType.
Load table Using Merge - Change Tracking - SCD1 | SCD2	Executed for @mergeStrategy("changeTracking") (the default) — compares @isChangeTracking columns (or, absent any, every plain attribute column) against the target, then updates in place (SCD1) or versions the row (SCD2).
Post-SQL <n>	Each @postSQL statement, in the order they appear.
<column>: <test> | Post Load Test <n>	Column-level data quality tests and node-level @tests(..., "After") tests, run after the load.

Note: For a transient table the stage names read transient table instead of table (for example Load transient table Using Merge - Upsert).

Dimension Redeployment

After the Dimension Node has been deployed for the first time into a target Environment, subsequent deployments either alter the existing object, recreate it, or only record a metadata update when nothing in the object changed (for example tests, @writeMode, @mergeStrategy, @preSQL/@postSQL, or a transform or join on a table):

Stage	Description
Metadata Update-Default	The node changed, but nothing in Snowflake needs to change

Altering the Dimension Tables

Changes to columns, the table description, the cluster key, tags, or the table name or location alter the Dimension Table, whether made individually or all together. The changes are made on an internal clone of the table, so a failure leaves the live table untouched.

The following stages are executed, in this order (each one only when needed):

Stage	Description
Clone Table	Creates an internal clone of the table (<table>_COALESCE_INTERNAL_TABLE), with its data, cluster key, tags and grants
Warning: Cluster Key Change	Non-failing message when a clustered column is renamed or dropped (see note below)
Drop Cluster Key	Drops the cluster key when a clustered column is renamed
Alter Column	Changes data types, nullability, column descriptions and defaults, in one statement
Add Column	Adds new columns, with their tags and comments (@isSurrogateKey is ignored on an added column — see Known Limitations)
Rename Column	Renames a column whose @id is unchanged, one stage per column
Recluster Table | Drop Cluster Key	Applies the new cluster key, or drops it when no @clusterKey remains
Delete Column	Drops removed columns
Edit Table Description	Updates the table comment
Unset Column Tags: &lt;column> | Set Column Tags: &lt;column>	Removes and sets column tags; a changed tag value is only a set
Unset Table Tags | Set Table Tags	Removes and sets table tags; a changed tag value is only a set
Swap Cloned Table	Swaps the altered clone with the live table
Rename Table	Instead of the swap, when the table is renamed or moved: the clone is renamed to the new name/location
Delete Table	Drops the internal clone after a swap, or the original table after a rename

Note: Every Dimension column carries a required @id, so renaming a column (same @id) is a true RENAME COLUMN that keeps its data.

Note — clustered columns: Snowflake does not allow renaming or dropping a column while it is part of the cluster key, so these are handled on the clone, starting with a non-failing Warning: Cluster Key Change stage that lists the renamed and dropped clustered columns and the new key:

• Renaming a clustered column (same @id) — the cluster key is dropped, the column is renamed, and the table is reclustered with the new name.
• Dropping a clustered column — the table is reclustered on the remaining @clusterKey columns (or the cluster key is dropped when none remain), and then the column is dropped.

Note: Snowflake only allows widening a column's data type (for example VARCHAR(25) → VARCHAR(100)) and only sequence values for SET DEFAULT. Other data type or default changes fail on the clone with Snowflake's error, leaving the live table untouched.

Recreating the Dimension Tables

If the materialization type changes (table ↔ view, or table ↔ transient table), the existing object is dropped and recreated:

Stage	Description
Drop table | Drop transient table | Drop view	Drops the existing object
Create table | Create transient table | Create view	Recreates the node with the new materialization type

Altering and Recreating the Dimension Views

A view is changed in place only when its tags change; any other change to the view's SQL (columns, transforms, joins, descriptions) recreates it:

Stage	Description
Unset Column Tags: <column> | Set Column Tags: <column> | Unset View Tags | Set View Tags	Only tags changed: the view is altered in place
Create view	The view definition changed: CREATE OR REPLACE VIEW with the new definition
Drop view + Create view	The view is renamed or moved: the old view is dropped and the new one created
Metadata Update-Default	Only tests changed (@tests, column tests such as @not_null, or @disableTests) — they are not part of the view's SQL, so the view is not recreated or altered; only the node metadata is updated

Removing a Dimension Node

If a Dimension Node of materialization type table is deleted from a SQL Workspace, that SQL Workspace is committed to Git and that commit deployed to a higher-level Environment, then the Dimension Table in the target Environment will be dropped.

This is executed in two stages:

Stage	Description
Delete Table	Drops the internal clone, if one was left behind by a failed deployment
Drop table	Drops the target table in Snowflake

If a Dimension Node of materialization type view is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher-level Environment, then the Dimension View in the target Environment will be dropped.

The stage executed:

Stage	Description
Drop view	Drops the existing Dimension view from the target Environment

---

Fact

The Fact node is a specialized transformation node within Coalesce, used to materialize fact tables that hold measurable business events. It loads incoming source data into the target in one of three ways: a plain insert when neither @mergeStrategy nor a business key is set, an SCD Type 1 merge on the business key (changeTracking or lastModified), or a merge with no change detection (upsert, or allColumnMatch, which inserts only rows that don't already exist). Facts never keep versioned (SCD Type 2) history and have no surrogate key or zero key record. Like the other node types, it comes with a built-in library of column- and node-level data quality tests.

Fact Node Configuration

The Fact Node type has three configuration groups:

• General
• Node Annotations
• Column Annotations

Fact General Options

Property	Description
Storage Location	Storage Location where the Fact table or view will be created
Node Type	Read-only: the node type's name and ID — Fact (SQLFact). Set from @nodeType when the node is created; the ID has a copy button.

Fact Node Annotations

Annotation	Summary
@id(id) (reserved)	Unique identifier for the node. Static and auto-generated when the node is created — not meant to be edited.
@nodeType(type) (reserved)	Identifies the node's type. Set automatically based on the node type chosen when the node is created.
@description(text) (reserved)	Node-level description. Can be edited via this annotation or in the node description field below the node name in the UI. Example: @description("Table description")
@materializationType(type) (reserved)	Table, transient table or view
@deployDisabled (reserved)	Excludes this node from deployment.
@tag(name, value, storageLocation?) (repeatable)	Applies a Snowflake tag to this table/view.
@writeMode("truncateInsert | append")	How each run writes to the target: truncateInsert or append Not specified in the SQL editor → defaults to append.
@mergeStrategy("changeTracking | lastModified | upsert | allColumnMatch")	Chooses how rows are matched against the target, and therefore which column annotations are required.
@disableTests ²	Controls whether configured tests are skipped.
@tests(querySQL, continueOnFailure?, runOrder?) ² (repeatable)	Node-level data quality test.
@preSQL(querySQL) (repeatable)	SQL statement to execute before the data load operation.
@postSQL(querySQL) (repeatable)	SQL statement to execute after the data load operation.

Fact Column Annotations

Annotation	Summary
@id("<value>")	Stable column ID for lineage tracking.
@notNull (reserved)	Marks column as NOT NULL. Note: Ignored on Views. Example: @notNull
@description(<text>) (reserved)	Adds column description. Example: @description("timestamp column")
@defaultValue(<value>) (reserved)	Adds default value.
@inHash("<hashName>", <hashOrder>) ¹ (repeatable)	Marks a column as an input to a generated hash key.
@tag(name, value, storageLocation?) (repeatable)	Applies a Snowflake tag to this column.
@clusterKey(position, expression?) (repeatable)	Marks a column as part of the table's clustering key, in the order given by position (lowest first).
@isBusinessKey	Marks a column as part of the business key used to match existing rows during the merge.
@lastModifiedTracking	Marks the column used to detect newer source rows for an incremental load (SCD Type 1).
@isSystemCreateDate	Marks this column as the timestamp a row was first created.
@isSystemUpdateDate	Marks this column as the timestamp a row was last updated.

Shared annotations link to the Annotation Reference; the Fact-specific ones are described in Fact Annotation Details.

Note: Quote style matters for case-sensitive identifiers when writing @tests, @preSQL, and @postSQL — see Quote Style for Case-Sensitive Identifiers.

🚦 The full set of column-level data quality tests (@not_null, @uniqueness, @empty, @accepted_values, @rejected_values, @min_max, @min_value, @max_value, @freshness, @relative_time) applies to Fact columns exactly as described in Column-Level Data Quality Tests.

Fact Annotation Details

@mergeStrategy (Fact)

@mergeStrategy("changeTracking | lastModified | upsert | allColumnMatch")

Chooses how rows are matched against the target, and therefore which column annotations are required.

Value	Description
changeTracking	SCD Type 1. Compares every plain attribute column against the target; a changed row is overwritten in place, a new key is inserted. Doesn't use @lastModifiedTracking — if present, a warning is raised and it's ignored.
lastModified	SCD Type 1. Compares a single @lastModifiedTracking column against the target's stored value; a newer value overwrites the row in place. Requires exactly one column marked @lastModifiedTracking — a validation check flags it if none (or more than one) is present.
upsert	No change detection at all; a matched row is always updated, an unmatched row is inserted. The SELECT/CTE should return one row per business key (de-duplicate upstream). System columns are optional; any that are present are written exactly as the SELECT/CTE computes them. @lastModifiedTracking isn't used — if present, a warning is raised and it's ignored.
allColumnMatch	Treats every non-system column as the business key: a source row is inserted only if no target row matches it on all of those columns, and an existing matching row is never updated. @isBusinessKey isn't needed — if present, a warning is raised, it's ignored and all columns are used for the comparison. @lastModifiedTracking isn't used either — a warning is raised and it's ignored. System columns are optional, are left out of the comparison, and are written exactly as the SELECT/CTE computes them.

• Not specified in the SQL editor → defaults to changeTracking - SCD Type 1 when a column is marked @isBusinessKey, otherwise the node is loaded as a plain insert.
• The value is not case-sensitive; any other value is flagged by a validation check.
• When set, @mergeStrategy always takes priority: changeTracking, lastModified and upsert merge on the business key, so without an @isBusinessKey column a validation check fails and the load stops. allColumnMatch needs no @isBusinessKey.
• Note: Ignored on Views.

Example:

@mergeStrategy("allColumnMatch")

@isBusinessKey (Fact)

@isBusinessKey

Marks a column as part of the business key used to match existing rows during the merge.

• Optional when @mergeStrategy isn't set — if no column is marked, there is no merge and every source row is simply inserted.
• Required for an explicit changeTracking, lastModified or upsert — without it the load stops with a validation error.
• Ignored under @mergeStrategy("allColumnMatch"), which uses every non-system column as the key (a warning is raised).
• Note: Ignored on Views.

Example:

@isBusinessKey

---

Fact Load Behaviour

How a Fact table is loaded depends on @mergeStrategy and on whether any column is marked @isBusinessKey:

@isBusinessKey	@mergeStrategy	Load
none	not set	Plain insert (default without a business key)
none	changeTracking / lastModified / upsert	❌ Validation error — the load stops
marked	not set, or changeTracking	SCD1 merge on the business key (default with a business key)
marked	lastModified	SCD1 merge on the business key
marked	upsert	Merge on the business key
any (ignored, warning if marked)	allColumnMatch	Merge on every non-system column

Requirement level of each column annotation, by load:

Annotation	changeTracking / lastModified	upsert	allColumnMatch	Plain insert
@isBusinessKey	🔴	🔴	⚪¹	—
@lastModifiedTracking	🔴 for lastModified only	—	—	—
@isSystemCreateDate	🔴	⚪	🟡	🟡
@isSystemUpdateDate	🔴	⚪	⚪	⚪

Legend: 🔴 Required · 🟡 Recommended · ⚪ Optional · — Not used

• ¹ Ignored if present — every non-system column is used as the business key and a warning is raised.
• allColumnMatch is meant for factless fact tables (no measures — the row itself is the fact), used for event tracking and coverage/relationship tracking. See Fact All Column Match.
• Under changeTracking / lastModified the load writes fixed values into the system columns: @isSystemCreateDate gets CURRENT_TIMESTAMP on first insert and is then kept; @isSystemUpdateDate gets CURRENT_TIMESTAMP whenever the row is inserted or changed. The expression the SELECT gives them is only used to derive the column's datatype.
• Under upsert, allColumnMatch and plain insert, system columns are written exactly as the SELECT/CTE computes them. @isSystemCreateDate keeps its first-inserted value on an upsert update.
• Business key matching (including every column under allColumnMatch) uses plain equality, so a NULL never matches — see Duplicate or NULL Business Keys.

---

Fact Deployment

Fact Initial Deployment

Every deployment of a Fact Node first runs these checks before any change in Snowflake:

Stage	Description
Unsupported Materialization Type: <node>	@materializationType is not table, transient table or view — stops the deployment and the run
Duplicate Tags Found: <node or column>	A duplicate tag: the same tag (same name and location) applied more than once on the node or on one column — stops the deployment. Repeating @tag for different tags is fine.
Duplicate Column Names | Duplicate Column IDs | Missing Column IDs | Invalid configuration: No Business Key | Invalid configuration: … | Missing Required System Columns	Fact validation checks (table only), rendered only when the annotations have a problem — see Fact Load. A required problem stops the deployment; a recommendation does not.

When deployed for the first time into an Environment, the Fact Node executes one of these stages, depending on its materialization type:

Stage	Description
Create table	Executes a CREATE OR REPLACE TABLE statement, with column and table tags, comments and the cluster key
Create transient table	Same as above, as a TRANSIENT TABLE
Create view	Executes a CREATE OR REPLACE VIEW statement, with column and view tags and comments — see Fact View

Fact View

A Fact Node of materialization type view is a plain CREATE OR REPLACE VIEW over the node's SELECT:

• None of the Fact validation checks run — @isBusinessKey, system columns, @id and @mergeStrategy aren't required or checked.
• There is no load; the run shows Load Skipped for View. @mergeStrategy, @writeMode, @preSQL, @postSQL and @lastModifiedTracking are ignored.
• @isSystemCreateDate / @isSystemUpdateDate columns are selected as a typed NULL, keeping the column's datatype.
• @tag annotations (node and column) apply to the view; @clusterKey is ignored.
• Column-level data quality tests and node-level @tests still run.

Fact Load

Every deployment of a Fact Node of materialization type table runs its configured data quality tests, then loads data into the target using the stages below:

Stage	Description
Pre Load Test <n>	Node-level @tests(..., "Before") tests, in the order they appear.
Duplicate Column Names | Duplicate Column IDs | Missing Column IDs | Invalid configuration: … | Missing Required System Columns	Validation checks — rendered only when the node's annotations have a problem (the SELECT returns the same column name twice, two columns share an @id value, a column without @id, an unrecognised or conflicting @mergeStrategy/tracking column, or a missing system column).
Check NULL values for <column> column	Pre-load check for @mergeStrategy("lastModified") — flags a NULL @lastModifiedTracking value in the source.
Pre-SQL <n>	Each @preSQL statement, in the order they appear.
Truncate table	Executed only when @writeMode("truncateInsert") is set — clears the target before the load.
Load table using Insert	Executed when no @mergeStrategy is set and no column is marked @isBusinessKey — inserts every source row with INSERT INTO … SELECT.
Load table Using Merge - Change Tracking - SCD1	Executed for @mergeStrategy("changeTracking"), and by default when no @mergeStrategy is set and a column is marked @isBusinessKey — compares every plain attribute column against the target, then updates changed rows in place and inserts new keys.
Load table Using Merge - Last Modified Comparison - SCD1	Executed for @mergeStrategy("lastModified") — compares the @lastModifiedTracking column against the target, then updates newer rows in place and inserts new keys.
Load table Using Merge - Upsert	Executed for @mergeStrategy("upsert") — every matched row is updated from the SELECT/CTE (system columns included, @isSystemCreateDate excepted) and every unmatched row is inserted.
Load table Using Merge - All Column Match	Executed for @mergeStrategy("allColumnMatch") — matches on every non-system column and inserts only rows that don't already exist; nothing is updated.
Post-SQL <n>	Each @postSQL statement, in the order they appear.
<column>: <test> | Post Load Test <n>	Column-level data quality tests and node-level @tests(..., "After") tests, run after the load.

Note: For a transient table the stage names read transient table instead of table (for example Load transient table Using Merge - Upsert).

Fact Redeployment

After the Fact Node has been deployed for the first time into a target Environment, subsequent deployments either alter the existing object, recreate it, or only record a metadata update when nothing in the object changed (for example tests, @writeMode, @mergeStrategy, @preSQL/@postSQL, or a transform or join on a table):

Stage	Description
Metadata Update-Default	The node changed, but nothing in Snowflake needs to change

Altering the Fact Tables

Changes to columns, the table description, the cluster key, tags, or the table name or location alter the Fact Table, whether made individually or all together. The changes are made on an internal clone of the table, so a failure leaves the live table untouched.

The following stages are executed, in this order (each one only when needed):

Stage	Description
Clone Table	Creates an internal clone of the table (<table>_COALESCE_INTERNAL_TABLE), with its data, cluster key, tags and grants
Warning: Cluster Key Change	Non-failing message when a clustered column is renamed or dropped (see note below)
Drop Cluster Key	Drops the cluster key when a clustered column is renamed
Alter Column	Changes data types, nullability, column descriptions and defaults, in one statement
Add Column	Adds new columns, with their tags and comments
Rename Column	Renames a column whose @id is unchanged, one stage per column
Recluster Table | Drop Cluster Key	Applies the new cluster key, or drops it when no @clusterKey remains
Delete Column	Drops removed columns
Edit Table Description	Updates the table comment
Unset Column Tags: &lt;column> | Set Column Tags: &lt;column>	Removes and sets column tags; a changed tag value is only a set
Unset Table Tags | Set Table Tags	Removes and sets table tags; a changed tag value is only a set
Swap Cloned Table	Swaps the altered clone with the live table
Rename Table	Instead of the swap, when the table is renamed or moved: the clone is renamed to the new name/location
Delete Table	Drops the internal clone after a swap, or the original table after a rename

Note: Every Fact column carries a required @id, so renaming a column (same @id) is a true RENAME COLUMN that keeps its data.

Note — clustered columns: Snowflake does not allow renaming or dropping a column while it is part of the cluster key, so these are handled on the clone, starting with a non-failing Warning: Cluster Key Change stage that lists the renamed and dropped clustered columns and the new key:

• Renaming a clustered column (same @id) — the cluster key is dropped, the column is renamed, and the table is reclustered with the new name.
• Dropping a clustered column — the table is reclustered on the remaining @clusterKey columns (or the cluster key is dropped when none remain), and then the column is dropped.

Note: Snowflake only allows widening a column's data type (for example VARCHAR(25) → VARCHAR(100)) and only sequence values for SET DEFAULT. Other data type or default changes fail on the clone with Snowflake's error, leaving the live table untouched.

Recreating the Fact Tables

If the materialization type changes (table ↔ view, or table ↔ transient table), the existing object is dropped and recreated:

Stage	Description
Drop table | Drop transient table | Drop view	Drops the existing object
Create table | Create transient table | Create view	Recreates the node with the new materialization type

Altering and Recreating the Fact Views

A view is changed in place only when its tags change; any other change to the view's SQL (columns, transforms, joins, descriptions) recreates it:

Stage	Description
Unset Column Tags: <column> | Set Column Tags: <column> | Unset View Tags | Set View Tags	Only tags changed: the view is altered in place
Create view	The view definition changed: CREATE OR REPLACE VIEW with the new definition
Drop view + Create view	The view is renamed or moved: the old view is dropped and the new one created
Metadata Update-Default	Only tests changed (@tests, column tests such as @not_null, or @disableTests) — they are not part of the view's SQL, so the view is not recreated or altered; only the node metadata is updated

Removing a Fact Node

If a Fact Node of materialization type table is deleted from a SQL Workspace, that SQL Workspace is committed to Git and that commit deployed to a higher-level Environment, then the Fact Table in the target Environment will be dropped.

This is executed in two stages:

Stage	Description
Delete Table	Drops the internal clone, if one was left behind by a failed deployment
Drop table	Drops the target table in Snowflake

If a Fact Node of materialization type view is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher-level Environment, then the Fact View in the target Environment will be dropped.

The stage executed:

Stage	Description
Drop view	Drops the existing Fact view from the target Environment

---

Common Reference

Concepts and reusable building blocks that apply across every node type in this package.

Annotation Reference

Annotations shared by Work, Dimension and Fact, described once. Where a node type behaves differently, its variant is shown under its own label. Node-type-specific annotations (for example @mergeStrategy or the system columns) are described in each node type's section. Annotations shared only by Dimension and Fact (for example @lastModifiedTracking and the create/update date system columns) are here too, with each node type's differences labelled.

Node Annotations

@materializationType

@materializationType(type) (reserved)

table/view/transient table.

Value	Description
table	Permanent table (default).
transient table	Transient table.
view	View.

• Use lowercase table, transient table or view — case and extra spaces are ignored ("Table " reads as table); any other value stops the deploy and the run with an Unsupported Materialization Type check.
• Not specified in the SQL editor → defaults to table.

Example:

@materializationType("view")

@tag (node)

@tag(name, value, storageLocation?)

(repeatable) Applies a Snowflake tag to this table/view.

Parameter	Description
name	Name of the tag (must already exist in Snowflake). Case-sensitive: PII and pii are different tags.
value	Value to assign to the tag.
storageLocation	(optional) storage location the tag lives in; defaults to this node's own location.

• Prerequisite: Tags must first be created in Snowflake before they can be used in Coalesce.
• Repeat the annotation to apply several different tags. A duplicate — the same tag (same name and location) applied twice, even with a different value — is not allowed and stops the deploy with a Duplicate Tags Found check.
• ✅ @tag("PII", "true") @tag("OWNER", "DATA_TEAM") — two different tags
• ✅ @tag("PII", "true", "TGTTAGDIR") @tag("PII", "true", "SRCTAGDIR") — two different tags (same name, different storage locations)
• ❌ @tag("PII", "true") @tag("PII", "false") — the same tag twice

Example:

@tag("PII", "true")
@tag("COST_CENTRE", "FIN", "GOVERNANCE")

How tag changes deploy (node and column tags alike)

A tag is identified by its name plus its location, with a missing location resolved to the node's own; the value is what is attached to it.

Change	What runs
New node / table or view recreated	Tags are created inline: WITH TAG (...) on the column and on the table or view
Same name, location and value	Nothing for tags
Value changed	Set only — SET TAG overwrites the value
Tag added	Set only
Tag removed	Unset only
Name or location changed (a different tag)	Unset the old tag, then Set the new one
Annotation text changed but the same tag (e.g. the node's own location written explicitly)	No tag stage — Metadata Update-Default only
Node moved to another location	Tags without a location follow the node (unset, then set in the new location); tags with an explicit location stay

• Stage order: column tags (Unset Column Tags: <column> / Set Column Tags: <column>) before node tags (Unset Table|View Tags / Set Table|View Tags); unset before set; all changed tags of one column or node in one statement.
• Tables are changed on the internal clone (ALTER TABLE <clone> ... TAG) before it is swapped in; the clone keeps the existing tags.
• Views are changed in place (ALTER VIEW IF EXISTS ... TAG) when only tags changed; any other change to the view recreates it with all its tags.
• Values are compared as text (5 = "5"); tag names are compared exactly (case-sensitive).

@writeMode

@writeMode("truncateInsert | append")

• Note: Ignored on Views.

Work:

Value	Description
truncateInsert	Replaces the table's contents entirely via a single INSERT OVERWRITE INTO statement (atomic — no separate truncate step).
append	Inserts the new rows via INSERT INTO, alongside whatever is already there.

• Not specified in the SQL editor → defaults to truncateInsert.

Example:

@writeMode("append")

Dimension / Fact:

Value	Description
truncateInsert	Clears the table before loading, replacing its contents entirely.
append	Dimension: Inserts the new rows via merge, alongside whatever is already there. Fact: Loads the new rows alongside whatever is already there.

• Not specified in the SQL editor → defaults to append.

Example:

@writeMode("truncateInsert")

@disableTests

@disableTests²

Controls whether configured tests are skipped.

• Specified in the SQL editor → all node- and column-level tests are skipped.
• Not specified in the SQL editor → tests run normally.
• To turn tests back on, remove the annotation. Useful while developing a node — iterate on the SQL first, then re-enable once the logic is settled.

Example:

@disableTests

@tests

@tests(querySQL, continueOnFailure?, runOrder?)²

(repeatable) Node-level data quality test.

² Node level tests are performed only when disableTests is OFF.

@tests("<querySQL>", <continueOnFailure>, "<runOrder>")

Parameter	Description
querySQL	SQL statement to execute as a validation test. The test fails if the query returns any records.
continueOnFailure	(optional) true(default) or false. Marks whether the run is meant to continue when the test fails.
runOrder	(optional) Before or After(default). Determines whether the test is executed before or after the load operation.

• Runs querySQL against the target; fails if it returns any records.
• Skipped entirely when @disableTests is set.

Example:

@tests("SELECT 1 FROM {{ this }} GROUP BY N_NATIONKEY HAVING COUNT(*) > 1", false, "After")  -- runs After the load, continueOnFailure=false

More examples:

@tests("SELECT 1 FROM {{ this }} GROUP BY N_COMMENT HAVING COUNT(*) > 1", true, "Before")  -- runs Before the load, continueOnFailure=true
@tests("SELECT 1 FROM {{ this }} GROUP BY N_COMMENT HAVING COUNT(*) > 1", false)  -- runs After the load (default), continueOnFailure=false
@tests("SELECT 1 FROM {{ this }} GROUP BY N_COMMENT HAVING COUNT(*) > 1")  -- runs After the load (default), continueOnFailure=true (default)

@preSQL

@preSQL(querySQL)

(repeatable) SQL statement to execute before the data load operation.

Parameter	Description
querySQL	SQL statement to execute before the data load operation.

• Repeat the annotation to run multiple statements, in the order they appear.
• Note: Ignored on Views.

Example:

@preSQL("DELETE FROM {{ this }} WHERE N_LOAD_DATE < DATEADD(DAY, -90, CURRENT_DATE())")

@postSQL

@postSQL(querySQL)

(repeatable) SQL statement to execute after the data load operation.

Parameter	Description
querySQL	SQL statement to execute after the data load operation.

• Repeat the annotation to run multiple statements, in the order they appear.
• Note: Ignored on Views.

Example:

@postSQL("INSERT INTO {{ ref('AUDIT', 'LOAD_LOG') }} (TABLE_NAME, LOAD_TS) VALUES ('WRK_NATION', CURRENT_TIMESTAMP())")

Column Annotations

@id (column)

@id("<value>")

Stable column ID for lineage tracking.

Parameter	Description
value	Six hexadecimal digits, unique within the node, e.g. 110e75.

• Required on every column — the SQL editor generates a fresh @id("<value>") for each column it creates, and a validation check flags any column that is missing one.
• Not for manual editing — value is a stable identifier generated once at creation time.
• Column added later: make sure it gets its own @id too. If you're editing by hand, add a new, unique 6-hex-digit @id("<value>") along with the column; an agent can generate one for you. Don't reuse a value already on the node — a repeated value fails the Duplicate Column IDs check.
• Duplicate column names: if the SELECT returns the same column name twice, Coalesce renames the extra copy (SYSTEM_CREATE_DATE → SYSTEM_CREATE_DATE1) and drops its @id; this is reported by the Duplicate Column Names check rather than as a missing ID.

Example:

@id("110e75")

@defaultValue

@defaultValue(<value>) (reserved)

Adds default value.

Parameter	Description
value	Default value for the column — quoted to match the column's data type (see below).

• Quote to match the column's data type -
• number: defaultValue("<num>")
• string: defaultValue("'<string>'")
• Note: Ignored on Views.

Example:

@defaultValue("20")
@defaultValue("'NA'")

@inHash

@inHash("<hashName>", <hashOrder>)¹

(repeatable) Marks a column as an input to a generated hash key.

Parameter	Description
hashName	Columns sharing the same value are grouped together into the same hash.
hashOrder	This column's position within that group.

• Call get_hash("<hashName>") elsewhere in the SELECT to produce the hash column from the marked columns.

Example:

<col_name> AS <col_name> @inHash("GH_COL", 1),
{{ get_hash('GH_COL') }}::STRING AS "GH_COL"

get_hash() macro

¹ The hash transformation uses the reusable get_hash() macro:

{{ get_hash("<hash_name>", "<algo>", "<delimiter>") }}

Parameter	Description
hash_name	Hash name used across columns to identify the columns included in the hash.
algo	(optional) Hashing algorithm to use. Supported values include SHA1, SHA256 and MD5. Defaults to SHA1.
delimiter	(optional) Delimiter used to separate column values when generating the hash. Defaults to || and can be customized.

get_hash() examples:

Using hash macro(default-SHA1)

<col_name> AS <col_name> @inHash("GH_COL", 1),
{{ get_hash('GH_COL') }}::STRING AS "GH_COL"

Using hash macro(MD5)

<col_name> AS <col_name> @inHash("GH_COL", 1),
{{ get_hash('GH_COL', 'MD5') }}::STRING AS "GH_COL"

Using hash macro(SHA256)

<col_name> AS <col_name> @inHash("GH_COL", 1),
{{ get_hash('GH_COL', 'SHA256') }}::STRING AS "GH_COL"

Using hash macro(algo=SHA256, delimiter='~' )

<col_name> AS <col_name> @inHash("GH_COL", 1),
{{ get_hash('GH_COL', algo='SHA256', delimiter='~') }}::STRING AS "GH_COL"

Using multiple keys hash macro

<col_name1> AS <col_name1> @inHash("GH_COL", 1),
<col_name2> AS <col_name2> @inHash("GH_COL", 2),
{{ get_hash('GH_COL') }}::STRING AS "GH_COL_COMBINED"

Using multiple hash macros

<col_name1> AS <col_name1> @inHash("GH_COL1", 1) @inHash("GH_COL2", 2),
<col_name2> AS <col_name2> @inHash("GH_COL1", 2),
<col_name3> AS <col_name3> @inHash("GH_COL2", 1),
{{ get_hash('GH_COL1') }}::STRING AS "GH_COL_COMBINED1",
{{ get_hash('GH_COL2', delimiter='~') }}::STRING AS "GH_COL_COMBINED2"

Using explicit expression:

CAST(
  SHA1(
    NVL(CAST(<col_name> AS VARCHAR), 'null')
  ) AS STRING
)::STRING AS "GH_Key"

@tag (column)

@tag(name, value, storageLocation?)

(repeatable) Applies a Snowflake tag to this column.

Parameter	Description
name	Name of the tag (must already exist in Snowflake). Case-sensitive: PII and pii are different tags.
value	Value to assign to the tag.
storageLocation	(optional) storage location the tag lives in; defaults to this node's own location.

• Prerequisite: Tags must first be created in Snowflake before they can be used in Coalesce.
• Repeat the annotation to apply several different tags to the column. A duplicate — the same tag (same name and location) applied twice on the column, even with a different value — is not allowed and stops the deploy with a Duplicate Tags Found check.
• ✅ @tag("PII", "true") @tag("OWNER", "DATA_TEAM") — two different tags
• ✅ @tag("PII", "true", "TGTTAGDIR") @tag("PII", "true", "SRCTAGDIR") — two different tags (same name, different storage locations)
• ❌ @tag("PII", "true") @tag("PII", "false") — the same tag twice

Example:

@tag("PII", "true")

• Column tags deploy exactly like node tags — see How tag changes deploy under @tag (node).

@clusterKey

@clusterKey(position, expression?)

(repeatable) Marks a column as part of the table's clustering key, in the order given by position (lowest first).

Parameter	Description
position	Position of this column in the clustering key (lowest first).
expression	(optional) Expression to cluster on instead of the plain column, e.g. trunc("REGION_KEY", -5).

• Leave out expression to cluster on the column as-is, or add one to cluster on a transformed value instead.
• Inside the expression, write a double quote twice (""REGION_KEY"") — a backslash does not escape it; see Quote Style for Case-Sensitive Identifiers.
• Note: Ignored on Views.

Example:

@clusterKey(1)
@clusterKey(2, "trunc(""REGION_KEY"", -5)")

@lastModifiedTracking

Dimension: @lastModifiedTracking(scdType?) · Fact: @lastModifiedTracking

Marks the column used to detect newer source rows for an incremental load.

• Datatype — DATE/TIME or any incrementing NUMERIC type.
• Can only be applied to one column.
• Must not be NULL in the source — a pre-load check flags it, since a NULL value can never compare as newer: a row loaded with a NULL tracking value is never updated again.
• Note: Ignored on Views.

Dimension:

• Only this column decides a change — other columns that change without a newer tracking value are never applied, even under SCD Type 1.

Parameter	Description
scdType	(optional) 1 overwrites the row in place, 2 expires the row and inserts a new version. Not specified → defaults to 1.

Fact:

Marks the column used to detect newer source rows for an incremental load (SCD Type 1).

• Only this column decides a change — other columns that change without a newer tracking value are never applied.
• Used only by @mergeStrategy("lastModified").

Example:

-- Dimension
@lastModifiedTracking(2)
-- Fact
@lastModifiedTracking

@isSystemCreateDate

@isSystemCreateDate

Marks this column as the timestamp a row was first created.

• Note: Ignored on Views. Agentic creation only — manual creation adds this column automatically.
• Expected expression: CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS "SYSTEM_CREATE_DATE" @isSystemCreateDate

Dimension:

Marks this column as the timestamp a row (or, under SCD Type 2, a specific row version) was first created.

• Required unless @mergeStrategy is upsert — under changeTracking/lastModified it's read back from the target and preserved unchanged on every row after its initial insert. Under upsert, no change detection reads the target at all, so this column isn't required; if kept, it is written from the SELECT/CTE on insert and never overwritten on update.

Fact:

• Required for changeTracking/lastModified — read back from the target and preserved unchanged on every row after its initial insert.
• Recommended for allColumnMatch and plain insert — rows are only ever inserted, so it's the record of when each row was loaded; it is written exactly as the SELECT/CTE computes it.
• Optional for upsert — if kept, it is written from the SELECT/CTE on insert and never overwritten on update.

@isSystemUpdateDate

@isSystemUpdateDate

Marks this column as the timestamp a row was last updated.

• Note: Ignored on Views. Agentic creation only — manual creation adds this column automatically.
• Expected expression: CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS "SYSTEM_UPDATE_DATE" @isSystemUpdateDate

Dimension:

• Required for SCD Type 2 — read back from the target and refreshed to the current timestamp whenever a row is inserted as a new version or its prior current version is expired.
• Required for SCD Type 1 — recomputed to the current timestamp on every inserted or changed row, so every row change carries an audit trail of when it was last updated.
• Optional for upsert — if kept, it is written from the SELECT/CTE on every insert and update.

Fact:

• Required for changeTracking/lastModified — set to the current timestamp on every inserted or changed row.
• Optional for upsert, allColumnMatch and plain insert — if kept, it is written exactly as the SELECT/CTE computes it.

Quote Style for Case-Sensitive Identifiers

Quote style matters for case-sensitive identifiers wherever raw SQL is pasted into an annotation, e.g. @tests, @preSQL, @postSQL.

Default — outer "..." double quotes, identifier unquoted:

@preSQL(" SELECT 1 FROM {{ this }} GROUP BY N_NAME HAVING COUNT(*) > 1 ")

If the column name's casing must be preserved exactly, swap the outer quotes to '...' single quotes, and wrap the identifier itself in "..." double quotes:

@preSQL(' SELECT 1 FROM {{ this }} GROUP BY "N_Name" HAVING COUNT(*) > 1 ')

Or keep the outer "..." double quotes and double each inner quote — "" inside the annotation string becomes a single " in the SQL. Use this form when the SQL also needs single quotes, e.g. a {{ ref('...', '...') }} or a string literal:

@tests("SELECT ""Nation_Key"" FROM {{ ref('SRC', 'NATION') }} GROUP BY ""Nation_Key"" HAVING COUNT(*) > 1", false, "Before")  -- runs Before the load, continueOnFailure=false

A backslash (\") does not escape a quote inside an annotation string and fails with a syntax error.

Column-Level Data Quality Tests

🚦 Applicable only when @disableTests is not set. Each runs After the load and continues the run on failure. Not specified in the SQL editor → test is off.

Property	Description
@not_null	Fails on rows where the column is NULL. Example: @not_null
@uniqueness	Fails when a value appears on more than one row. Example: @uniqueness
@empty	Fails on rows where the column trims to the empty string. NULL values pass this test — they're caught by @not_null instead. Example: @empty
@accepted_values("<value>", ...)	Fails on rows whose value is outside the allow list. NULL values pass. Repeatable — every occurrence on the column is merged into one list. Each call takes a comma-separated list or separate arguments — accepted_values("1, 3, 5") accepted_values("1", "3", "5") accepted_values("1") accepted_values("3, 5") Each value is pasted into the SQL verbatim, so write it as a valid SQL value for the column's data type — numeric: with or without double quotes string: single quotes inside double quotes, "'<string>'" boolean: true/false, with or without double quotes date/time: a date/time literal or expression in double quotes, with any literal inside it in single quotes, e.g. "'2026-01-01'" or "DATE '2026-01-01'" Example: @accepted_values("'ALGERIA', 'ARGENTINA'")
@rejected_values("<value>", ...)	Fails on rows whose value is in the deny list. NULL values pass. Same syntax and quoting rules as accepted_values. Example: @rejected_values("'NA'")
@min_max("<min>", "<max>")	Fails on rows outside the inclusive range. NULL values pass. Applies to numeric and date/time columns only. Each bound is pasted into the SQL verbatim, so write it as a valid SQL value for the column's data type — positive numeric (integer, decimal or float): with or without double quotes negative numeric (integer, decimal or float): double quotes required date/time: a date/time expression in double quotes, with any literal inside it in single quotes, e.g. "DATE '2026-01-01'" or "CURRENT_TIMESTAMP" Example: @min_max("0", "4")
@min_value("<min>")	Fails on rows below the bound. NULL values pass. Value formatting — see min_max. Example: @min_value("0")
@max_value("<max>")	Fails on rows above the bound. NULL values pass. Value formatting — see min_max. Example: @max_value("100")
@freshness(<interval>, "<unit>")	Fails when the newest value in the column is older than the given interval, or the table is empty. interval — how far back from now the newest value is allowed to be, expressed in the unit given by unit. unit — SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, or YEAR (defaults to DAY). Note: on a DATE column the value is truncated to midnight. Example: @freshness(7, "DAY")
@relative_time("<operator>", "<other_column>")	Compares this column against another date/time column on the same node. e.g. @relative_time("<=", "END_DATE") fails rows where this column's value is not <= END_DATE. Either side NULL → row is skipped (passes). Both columns should have the same datatype — e.g. comparing a TIMESTAMP_TZ with a TIMESTAMP_NTZ reads the NTZ value in the session's time zone and can give unexpected results. Example: @relative_time("<", "L_M_2")

---

Notes & Supported SQL Functionality

• Verify that all column datatypes are successfully resolved before creating the object. Columns with an UNKNOWN datatype may cause stage generation or runtime failures. This typically happens when:

  • a CTE builds a column through a UNION/UNION ALL inside a derived table — wrap the column in an explicit CAST(... AS <type>) in the final SELECT;
  • the final SELECT reads a CTE by its bare name (FROM "CUST", "CUST"."C_NAME") — give the CTE a table alias and qualify the columns with it (FROM "CUST" "CU", "CU"."C_NAME"), and every column then resolves to its real datatype. Nodes downstream of an UNKNOWN column inherit it, so fix it at the first node.

  The create dry-run still reports passed with UNKNOWN in the DDL, so check the rendered column list rather than the status.

• Any keyword that is valid immediately after SELECT is accepted in the final SELECT clause (right after any CTEs) — for example DISTINCT or ALL. This does not extend to keywords like DEFAULT that, while valid SQL keywords elsewhere, don't fit in a SELECT clause.

  SELECT DISTINCT TOP 10
       "N_REGIONKEY" AS "N_REGIONKEY",
       "N_NAME" AS "N_NAME"
  FROM {{ ref('SRC', 'NATION') }} "NATION"

• Multi-Source Joins & Enrichment: The ability to reference and join multiple upstream nodes (e.g., Joining ORDERS and CUSTOMER) within a single stage to flatten data or create enriched wide tables while maintaining full lineage for every source.

• Conditional Logic via CASE Statements: Support for complex business rules and data categorization using standard CASE WHEN syntax to create derived columns based on multiple logical conditions.

• Flexible Projection (SELECT * with Expressions): Enhanced projection capabilities that allow for selecting all columns from a source (SELECT *) while simultaneously appending new calculated expressions, timestamps, or metadata in the same statement.
  Note: Column-level annotations (e.g. @not_null, @inHash) can only be attached to columns that are explicitly listed in the SELECT clause — they cannot be applied to columns pulled in via SELECT *.

• Nested Subqueries: Support for correlated and non-correlated subqueries within SELECT, FROM, or WHERE clauses, enabling granular filtering and complex lookups that don't require separate nodes.

• Common Table Expressions (CTEs): Support for standard WITH clauses to break down complex, multi-step transformation logic into readable, modular blocks. Coalesce tracks lineage through each CTE and back to the source tables.

• Recursive CTEs: Full support for WITH RECURSIVE logic, enabling the transformation of hierarchical data and the programmatic generation of data sequences within a single node.

• If a CTE is referenced in templates that may include joins, always use a table alias and qualify all column references with that alias. This prevents ambiguous column errors and ensures the template remains extensible as additional joins are introduced.

---

Known Limitations

Users should be aware of the following technical constraints when using SQL-first nodes:

• Parsable SQL Only: The node only supports SQL that can be fully parsed by the platform’s engine. Non-standard SQL or vendor-specific "semantic views" that bypass standard parsing will not work.

• SELECT Statements Only:
  This node only supports data retrieval and transformation logic. DML or DDL commands such as CREATE, MERGE, DELETE, UPDATE, or TRUNCATE are not supported and will cause execution failures.

• Support for UNION, and UNION ALL:
  UNION, and UNION ALL are fully supported when used within Common Table Expressions (CTEs). While these keywords can also be used in standard SELECT statements without generating an error, they may not be parsed correctly by the platform. As a result, subsequent clauses (such as JOINs) may be interpreted as part of a standard join structure, causing the generated SQL to differ from the intended query and potentially leading to inconsistent data loads. To ensure the SQL is parsed and executed as expected, always implement these operations inside a CTE.

• Other Keywords:
  GROUP BY, ORDER BY and HAVING clauses can be included as part of the join query and will be parsed and processed accordingly.

• Reserved Keywords as Annotation Names:
  Avoid naming custom annotations after words that are reserved keywords in the platform's SQL grammar — e.g. UNIQUE, AS, PRIMARY. The parser may fail to parse such annotations and throw a validation error.

• Switching Between YAML and SQL Node Types:
  Converting an existing YAML (.yml) node to a SQL (.sql) node, or vice versa, is not supported.

• Adding @isSurrogateKey to a Deployed Dimension:
  Snowflake can only add an identity column to an empty table, so when a redeploy adds a new column marked @isSurrogateKey, Coalesce ignores @isSurrogateKey and adds it as a regular column. The load never writes the surrogate key (it relies on identity), so the column stays NULL for existing and new rows. To get a working surrogate key, recreate the table — for example drop it and deploy the node again — so it is created with the identity column. A surrogate key present from the first deployment is created as identity as usual.

• Adding a NOT NULL Column Without a Default:
  Snowflake can't add a NOT NULL column without a DEFAULT to a table that already holds rows, so a redeploy that adds a column with @notNull and no @defaultValue fails at the Add Column stage ("Non-nullable column cannot be added to non-empty table"). The failure is left visible on purpose. Add the column without @notNull (or with a @defaultValue) first, load it, then add @notNull in a later deployment.

• Changing @nodeType on a Deployed Node:
  The switch deploys as a metadata update — the existing table is kept, not rebuilt for the new type. Work/Fact → Dimension overwrites every row via the zero-key merge (no identity surrogate key), Dimension → Fact rewrites every row and its surrogate keys, and → Work appends duplicates on each run; only Work → Fact is safe. Drop and redeploy the table when changing a node's type.

• Large or High-Precision Numbers in Test Values:
  An unquoted number in an annotation is read as a floating-point value, so integers beyond roughly 15–16 digits and decimals with more than about 16 significant digits are rounded before the test runs (e.g. 12345678901234567890 becomes 1.2345678901234567e+19). Wrap such values in double quotes — @accepted_values("12345678901234567890") — to pass them through exactly.

• Commas Inside String Values in accepted_values/rejected_values:
  Values are split on every comma, trimmed, and re-joined with ", ". A string written with exactly one space after each comma, like "'4, 5'", is tested as written; any other spacing is normalised, so "'A,B'" is tested as 'A, B'. Commas between arguments of a function call (e.g. "TO_DATE('15/06/2026','DD/MM/YYYY')") are unaffected.

• truncateInsert with SCD Type 2:
  @writeMode("truncateInsert") empties the table before every load, so an SCD Type 2 dimension (changeTracking with an @isChangeTracking column, or lastModified with @lastModifiedTracking(2)) loses all its history on each run — every row is reloaded as version 1. The combination runs without error, but it defeats the purpose of SCD Type 2; use the default append for versioned dimensions.

• Keys Deleted from the Source:
  A business key that disappears from the source is left as it is in the target — under SCD Type 2 its current row stays current (SYSTEM_CURRENT_FLAG = 'Y', open SYSTEM_END_DATE), and under SCD Type 1 the row is kept unchanged. Deletes are never detected, expired or flagged; handle them separately (e.g. with @postSQL) if the dimension needs to reflect removed keys.

• System Columns Added to a Loaded Table:
  Adding @isSystemCreateDate / @isSystemUpdateDate to a table that already holds rows (for example when switching a node from upsert to changeTracking) adds the columns with NULL in every existing row. @isSystemCreateDate is carried over from the target on every later update, so it stays NULL for those rows; @isSystemUpdateDate is only filled once a row actually changes. Backfill them once (e.g. with @postSQL) or reload with @writeMode("truncateInsert") if they need values.

• Duplicate or NULL Business Keys:
  Every strategy merges on the business key, so the SELECT/CTE must return exactly one row per key, with no NULL key. Otherwise a duplicated key is inserted more than once on a first load and later merges fail with "Duplicate row detected during DML action", and a NULL key never matches, so that row is re-inserted on every run. De-duplicate upstream (e.g. QUALIFY ROW_NUMBER() OVER (PARTITION BY <business key> ORDER BY <timestamp> DESC) = 1). To check it before each load, add a Before node-level test — see Duplicate or NULL Business Key Check.

  • NULL business key — inserted instead of updated: Dimension and Fact expect every business key to be non-NULL. Matching uses plain equality and NULL = NULL is never true, so a source row with a NULL business key never matches its target row: instead of updating it, every run inserts it again as a new row (and an SCD2 Dimension never expires the old one). Under allColumnMatch the same applies to a NULL in any compared column. Filter or default NULL keys upstream, e.g. WHERE <business key> IS NOT NULL or COALESCE(<business key>, '<placeholder>').

---

Usage Examples

Common ways to use the SQL nodes. The SQL patterns under Work Examples (CTEs, window functions, DISTINCT) work the same way in a Dimension or Fact node.

Work Examples

• Sample Node with Annotations
• Sample Node with DISTINCT
• Basic Transformation and Cleaning
• Aggregate Functions inside a CTE
• Using CTEs
• Multi-CTE Transformation with Window Functions
• Recursive CTE
• Recursive CTE Date Series
• CTE for Multi-Source Combine

Data Quality Test Examples

• Column Tests by Datatype
• Node-Level Tests

Dimension Examples

• Change Tracking SCD1
• Change Tracking SCD2
• Last Modified SCD1
• Last Modified SCD2
• Upsert
• Zero Key Record
• Dimension as a View
• Duplicate or NULL Business Key Check
• Upsert with Custom SCD Logic

Fact Examples

• Fact Plain Insert
• Fact Change Tracking SCD1
• Fact Last Modified SCD1
• Fact Upsert
• Fact All Column Match

Work Examples

Sample Node with Annotations

@writeMode("append")
@tests("SELECT 1 FROM {{ this }} GROUP BY N_NATIONKEY HAVING COUNT(*) > 1")  -- runs After the load (default), continueOnFailure=true (default)
@tests("SELECT 1 FROM {{ this }} WHERE N_REGIONKEY IS NULL", true, "Before")  -- runs Before the load, continueOnFailure=true
@description("Table description")
@preSQL("DELETE FROM {{ this }} WHERE N_LOAD_DATE < DATEADD(DAY, -90, CURRENT_DATE())")
@postSQL("INSERT INTO {{ ref('AUDIT', 'LOAD_LOG') }} (TABLE_NAME, LOAD_TS) VALUES ('WRK_NATION', CURRENT_TIMESTAMP())")
SELECT
    "N_NATIONKEY"                             AS "N_NATIONKEY"         @not_null @uniqueness @min_value(0) @max_value(100)  @accepted_values("1, 2, 3") @inHash("GH_COL1", 2),
    "N_NAME"                                  AS "N_NAME"              @not_null @empty @accepted_values("'ALGERIA', 'ARGENTINA'") @inHash("GH_COL1", 1),
    "N_REGIONKEY"                             AS "N_REGIONKEY"         @min_max(0, 4) @notNull @defaultValue("20"),
    "N_COMMENT"                               AS "N_COMMENT"           @rejected_values("'NA'"),
    "LAST_MODIFIED"                           AS L_M_1                 @freshness(7, "DAY") @relative_time("<", "L_M_2") @description("timestamp column"),
    "LAST_MODIFIED"                           AS L_M_2,
    CAST({{ get_hash('GH_COL1') }} AS STRING) AS "GH_COL1"             @description("Hash Column")
FROM {{ ref('SOURCE_DATA', 'NATION') }} "NATION"

Sample Node with DISTINCT

@writeMode("append")
@description("Table description")
SELECT DISTINCT
    "N_NATIONKEY"                            AS "N_NATIONKEY",
    "N_NAME"                                 AS "N_NAME",
    "N_REGIONKEY"                            AS "N_REGIONKEY",
    "N_COMMENT"                              AS "N_COMMENT",
    "LAST_MODIFIED"                          AS L_M                   @freshness(7, "DAY")
FROM {{ ref('SOURCE_DATA', 'NATION') }} "NATION"

Basic Transformation and Cleaning

Standard pattern for renaming columns and handling nulls.

SELECT
    "O_ORDERKEY"                             AS "O_ORDERKEY",
    "O_CUSTKEY"                              AS "O_CUSTKEY",
    UPPER("O_ORDERSTATUS")                   AS "O_ORDERSTATUS",
    COALESCE("O_TOTALPRICE", 0)              AS "O_TOTALPRICE",
    "O_ORDERDATE"                            AS "O_ORDERDATE"
FROM {{ ref('SRC', 'ORDERS') }} "ORDERS"
WHERE "O_ORDERSTATUS" != 'F'

Aggregate Functions inside a CTE

WITH "ORDER_COUNTS" AS (
    SELECT
        MOD("ORDER_ID", 25) AS "NATION_KEY",
        COUNT(*) AS "ORDER_COUNT"
    FROM {{ ref('SRC', 'ORDERS_TEST') }}
    GROUP BY MOD("ORDER_ID", 25)
)
SELECT
    "NATION_KEY"                             AS "NATION_KEY",
    "ORDER_COUNT"                            AS "ORDER_COUNT"
FROM "ORDER_COUNTS"

Using CTEs

For more complex, multi-step logic

WITH PRIORITY_COUNTS AS (
    SELECT 
        "O_ORDERPRIORITY" AS "O_ORDERPRIORITY",
        COUNT(*) AS ORDER_COUNT
    FROM {{ ref('SRC', 'ORDERS') }}
    GROUP BY 1
)
SELECT * FROM PRIORITY_COUNTS

Multi-CTE Transformation with Window Functions

Complex transformations that would otherwise require multiple nodes can be written as a single SQL statement. Coalesce tracks lineage through each CTE and down to the source tables

WITH ORDERED_ORDERS AS (
-- CTE 1: Rank every order for each customer by date
SELECT
O_CUSTKEY,
O_ORDERKEY,
O_ORDERDATE,
O_TOTALPRICE,
O_ORDERSTATUS,
ROW_NUMBER() OVER (
PARTITION BY O_CUSTKEY
ORDER BY O_ORDERDATE ASC, O_ORDERKEY ASC
) AS ORDER_RANK
FROM {{ ref('SRC', 'ORDERS') }}
),
FIRST_ORDERS AS (
-- CTE 2: Filter to keep only the first order (rank 1) for each customer
SELECT
O_CUSTKEY,
O_ORDERKEY AS FIRST_ORDER_ID,
O_ORDERDATE AS FIRST_PURCHASE_DATE,
O_TOTALPRICE AS FIRST_ORDER_VALUE,
O_ORDERSTATUS
FROM ORDERED_ORDERS
WHERE ORDER_RANK = 1
)
-- Final Select: Add metadata and return the results
SELECT
F.O_CUSTKEY,
F.FIRST_ORDER_ID,
F.FIRST_PURCHASE_DATE,
F.FIRST_ORDER_VALUE,
F.O_ORDERSTATUS @notNull,
CURRENT_TIMESTAMP() AS REFRESHED_AT,
'Initial Customer Purchase' AS RECORD_TYPE
FROM FIRST_ORDERS F

Recursive CTE

WITH RECURSIVE "NATION_MANAGERS" AS (
    SELECT
        "N_NATIONKEY" AS "NATION_ID",
        "N_NAME" AS "NATION_NAME",
        CASE WHEN "N_NATIONKEY" = 0 THEN NULL ELSE "N_NATIONKEY" - 1 END AS "MANAGER_ID"
    FROM {{ ref('SRC', 'NATION_TEST') }}
),
"NATION_CHAIN" AS (
    SELECT
        "NATION_ID",
        "NATION_NAME",
        "MANAGER_ID",
        1 AS "ORG_LEVEL",
        CAST("NATION_NAME" AS STRING) AS "PATH"
    FROM "NATION_MANAGERS"
    WHERE "MANAGER_ID" IS NULL

    UNION ALL

    SELECT
        "N"."NATION_ID",
        "N"."NATION_NAME",
        "N"."MANAGER_ID",
        "C"."ORG_LEVEL" + 1,
        "C"."PATH" || ' -> ' || "N"."NATION_NAME"
    FROM "NATION_MANAGERS" "N"
    JOIN "NATION_CHAIN" "C" ON "N"."MANAGER_ID" = "C"."NATION_ID"
)
SELECT
    "NC"."NATION_ID"                         AS "NATION_ID",
    "NC"."NATION_NAME"                       AS "NATION_NAME",
    "NC"."ORG_LEVEL"                         AS "ORG_LEVEL",
    "NC"."PATH"                              AS "PATH",
    COUNT("O"."ORDER_ID")                    AS "ORDER_COUNT"
FROM "NATION_CHAIN" "NC"
JOIN {{ ref('SRC', 'ORDERS_TEST') }} "O" ON "NC"."NATION_ID" = MOD("O"."ORDER_ID", 25)
GROUP BY "NC"."NATION_ID", "NC"."NATION_NAME", "NC"."ORG_LEVEL", "NC"."PATH"

Recursive CTE Date Series

WITH RECURSIVE RCTE_FNL AS (
    SELECT TO_DATE('2025-01-01') AS "date_s"
    UNION ALL
    SELECT DATEADD(day, 1, "date_s") AS "date_s"
    FROM RCTE_FNL
    where "date_s" < TO_DATE('2025-01-10')
  )
SELECT "date_s"
FROM RCTE_FNL

CTE for Multi-Source Combine

WITH ALL_NATIONS AS (
    SELECT *
    FROM {{ ref('SOURCE_DATA', 'NATION_COPY1') }}
    UNION
    SELECT *
    FROM {{ ref('SOURCE_DATA', 'NATION_COPY2') }}
)
SELECT * FROM ALL_NATIONS

Data Quality Test Examples

Column Tests by Datatype

Each column test runs After the load, continues the run on failure, and lets NULL values pass (except @not_null). Values are pasted into the SQL as written, so quote them to match the column's datatype.

SELECT
    "CUSTOMER_ID"                            AS "CUSTOMER_ID"         @not_null @uniqueness @min_value("1"),
    "NAME"                                   AS "NAME"                @not_null @empty,
    "SEGMENT"                                AS "SEGMENT"             @accepted_values("'RETAIL', 'CORPORATE'") @accepted_values("'SMB'"),
    "STATUS"                                 AS "STATUS"              @rejected_values("'DELETED'", "'TEST'"),
    "BALANCE"                                AS "BALANCE"             @min_max("-1000", "100000"),
    "DISCOUNT"                               AS "DISCOUNT"            @min_value("0") @max_value("0.5"),
    "IS_ACTIVE"                              AS "IS_ACTIVE"           @accepted_values(true),
    "SIGNUP_DATE"                            AS "SIGNUP_DATE"         @min_max("DATE '2000-01-01'", "CURRENT_DATE"),
    "CREATED_AT"                             AS "CREATED_AT"          @max_value("CURRENT_TIMESTAMP"),
    "UPDATED_AT"                             AS "UPDATED_AT"          @freshness(1, "DAY") @relative_time(">=", "CREATED_AT"),
    "OPEN_TIME"                              AS "OPEN_TIME"           @min_max("'08:00:00'", "'18:00:00'")
FROM {{ ref('SRC', 'CUSTOMER') }}

• Number — @min_value("1"), a negative bound in double quotes "-1000", a decimal "0.5".
• String — single quotes inside double quotes; @accepted_values can repeat and the lists are merged.
• Boolean — true/false. Date/time — a literal or expression, e.g. "DATE '2000-01-01'", "CURRENT_TIMESTAMP", "'08:00:00'".
• @relative_time compares two columns of the same datatype.
• The same annotations work on a Dimension or Fact node — add an @id("<value>") to each column there.

Node-Level Tests

A node-level test fails when its query returns any rows. Before tests usually read the source; After tests read the loaded target through {{ this }}.

@tests("SELECT CUSTOMER_ID FROM {{ ref('SRC', 'CUSTOMER') }} GROUP BY CUSTOMER_ID HAVING COUNT(*) > 1", false, "Before")  -- runs Before the load, continueOnFailure=false
@tests("SELECT 1 FROM {{ ref('SRC', 'CUSTOMER') }} HAVING COUNT(*) = 0", true, "Before")  -- runs Before the load, continueOnFailure=true
@tests("SELECT 1 FROM {{ this }} WHERE BALANCE < 0 AND STATUS = 'ACTIVE'")  -- runs After the load (default), continueOnFailure=true (default)
@tests("SELECT 1 FROM {{ this }} HAVING COUNT(*) <> (SELECT COUNT(*) FROM {{ ref('SRC', 'CUSTOMER') }})", true, "After")  -- runs After the load, continueOnFailure=true
SELECT
    "CUSTOMER_ID"                            AS "CUSTOMER_ID",
    "BALANCE"                                AS "BALANCE",
    "STATUS"                                 AS "STATUS"
FROM {{ ref('SRC', 'CUSTOMER') }}

• Line 1 — duplicate keys in the source, before the load. Line 2 — an empty source. Line 3 — a business rule on the loaded rows (After by default). Line 4 — the target row count matches the source.
• continueOnFailure is the second argument.
• @disableTests on the node skips every node-level and column-level test.

Dimension Examples

Change Tracking SCD1

The default strategy. Any change in a non-key column overwrites the row.

SELECT
    "CUSTOMER_ID"                            AS "CUSTOMER_ID"         @id("a10001") @isBusinessKey,
    "NAME"                                   AS "NAME"                @id("a10002"),
    "CITY"                                   AS "CITY"                @id("a10003"),
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_CREATE_DATE"  @id("a10008") @isSystemCreateDate,
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_UPDATE_DATE"  @id("a10009") @isSystemUpdateDate
FROM {{ ref('SRC', 'CUSTOMER') }}

Change Tracking SCD2

A change in a column marked @isChangeTracking keeps the old row and adds a new version. Other columns are updated in place.

@mergeStrategy("changeTracking")
SELECT
    "CUSTOMER_ID"                            AS "CUSTOMER_ID"         @id("b10001") @isBusinessKey,
    "NAME"                                   AS "NAME"                @id("b10002"),
    "CITY"                                   AS "CITY"                @id("b10003") @isChangeTracking,
    1                                        AS "SYSTEM_VERSION"      @id("b10005") @isSystemVersion,
    'Y'                                      AS "SYSTEM_CURRENT_FLAG" @id("b10006") @isSystemCurrentFlag,
    CAST('2999-12-31 00:00:00' AS TIMESTAMP) AS "SYSTEM_END_DATE"     @id("b10007") @isSystemEndDate,
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_CREATE_DATE"  @id("b10008") @isSystemCreateDate,
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_UPDATE_DATE"  @id("b10009") @isSystemUpdateDate
FROM {{ ref('SRC', 'CUSTOMER') }}

Last Modified SCD1

A row is overwritten only when its @lastModifiedTracking value is newer than the stored one.

@mergeStrategy("lastModified")
SELECT
    "CUSTOMER_ID"                            AS "CUSTOMER_ID"         @id("c10001") @isBusinessKey,
    "NAME"                                   AS "NAME"                @id("c10002"),
    "UPDATED_AT"                             AS "UPDATED_AT"          @id("c10003") @lastModifiedTracking(1),
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_CREATE_DATE"  @id("c10008") @isSystemCreateDate,
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_UPDATE_DATE"  @id("c10009") @isSystemUpdateDate
FROM {{ ref('SRC', 'CUSTOMER') }}

Last Modified SCD2

Same as above, but a newer value keeps the old row and adds a new version.

@mergeStrategy("lastModified")
SELECT
    "CUSTOMER_ID"                            AS "CUSTOMER_ID"         @id("d10001") @isBusinessKey,
    "NAME"                                   AS "NAME"                @id("d10002"),
    "UPDATED_AT"                             AS "UPDATED_AT"          @id("d10003") @lastModifiedTracking(2),
    1                                        AS "SYSTEM_VERSION"      @id("d10005") @isSystemVersion,
    'Y'                                      AS "SYSTEM_CURRENT_FLAG" @id("d10006") @isSystemCurrentFlag,
    CAST('2999-12-31 00:00:00' AS TIMESTAMP) AS "SYSTEM_END_DATE"     @id("d10007") @isSystemEndDate,
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_CREATE_DATE"  @id("d10008") @isSystemCreateDate,
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_UPDATE_DATE"  @id("d10009") @isSystemUpdateDate
FROM {{ ref('SRC', 'CUSTOMER') }}

Upsert

No change detection: existing keys are updated, new keys inserted. System columns are optional.

@mergeStrategy("upsert")
SELECT
    "CUSTOMER_ID"                            AS "CUSTOMER_ID"         @id("e10001") @isBusinessKey,
    "NAME"                                   AS "NAME"                @id("e10002"),
    "CITY"                                   AS "CITY"                @id("e10003")
FROM {{ ref('SRC', 'CUSTOMER') }}

Zero Key Record

Adds a placeholder row for unmatched lookups. Needs a surrogate key column.

@zeroKey(0)
SELECT
    0                                        AS "CUSTOMER_KEY"        @id("f10000") @isSurrogateKey,
    "CUSTOMER_ID"                            AS "CUSTOMER_ID"         @id("f10001") @isBusinessKey,
    "NAME"                                   AS "NAME"                @id("f10002") @zeroKey("'NA'"),
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_CREATE_DATE"  @id("f10008") @isSystemCreateDate,
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_UPDATE_DATE"  @id("f10009") @isSystemUpdateDate
FROM {{ ref('SRC', 'CUSTOMER') }}

Dimension as a View

No business key or system columns needed; nothing is merged.

@materializationType("view")
SELECT
    "CUSTOMER_ID"                            AS "CUSTOMER_ID"         @id("0a0001"),
    "NAME"                                   AS "NAME"                @id("0a0002")
FROM {{ ref('SRC', 'CUSTOMER') }}

Duplicate or NULL Business Key Check

Every merge strategy matches rows on the business key, so the source must return exactly one row per key, with no NULL key. A Before test runs ahead of the load and fails if the source returns a duplicated or NULL key.

@mergeStrategy("changeTracking")
@tests("SELECT N_NATIONKEY, N_NAME FROM {{ ref('SOURCE_DATA', 'NATION') }} GROUP BY N_NATIONKEY, N_NAME HAVING COUNT(*) > 1 OR N_NATIONKEY IS NULL OR N_NAME IS NULL", false, "Before")  -- runs Before the load, continueOnFailure=false
SELECT
    0                                        AS "DIM_NATION_KEY"      @id("a1b2c3") @isSurrogateKey,
    "N_NATIONKEY"                            AS "N_NATIONKEY"         @id("b2c3d4") @isBusinessKey,
    "N_NAME"                                 AS "N_NAME"              @id("c3d4e5") @isBusinessKey,
    "N_REGIONKEY"                            AS "N_REGIONKEY"         @id("d4e5f6"),
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_CREATE_DATE"  @id("e5f6a7") @isSystemCreateDate,
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_UPDATE_DATE"  @id("f6a7b8") @isSystemUpdateDate
FROM {{ ref('SOURCE_DATA', 'NATION') }} "NATION"

• The test query must read the same source, CTE and filters as the node's SELECT, so it checks the rows that are actually loaded. List every @isBusinessKey column in the GROUP BY and the IS NULL checks.
• For a case-sensitive column name, double each inner quote — ""Nation_Key"" — see Quote Style for Case-Sensitive Identifiers.

Upsert with Custom SCD Logic

upsert writes each row exactly as the SELECT computes it, so the SELECT can implement its own change logic. This example keeps the previous CITY (SCD3 style): the SELECT joins the source to the node's own table, and when the city changes, the old value moves to CITY_PREV.

@mergeStrategy("upsert")
WITH "EXISTING" AS (
    -- Current rows already in this dimension
    SELECT "CUSTOMER_ID", "CITY", "CITY_PREV", "CITY_CHANGED_DATE"
    FROM {{ ref('TARGET', 'DIM_CUSTOMER') }}
)
SELECT
    "SRC"."CUSTOMER_ID"                      AS "CUSTOMER_ID"         @id("0b0001") @isBusinessKey,
    "SRC"."NAME"                             AS "NAME"                @id("0b0002"),
    "SRC"."CITY"                             AS "CITY"                @id("0b0003"),
    CASE WHEN NOT EQUAL_NULL("SRC"."CITY", "EXISTING"."CITY") AND "EXISTING"."CUSTOMER_ID" IS NOT NULL
         THEN "EXISTING"."CITY"
         ELSE "EXISTING"."CITY_PREV" END     AS "CITY_PREV"           @id("0b0004"),
    CASE WHEN NOT EQUAL_NULL("SRC"."CITY", "EXISTING"."CITY") AND "EXISTING"."CUSTOMER_ID" IS NOT NULL
         THEN CURRENT_TIMESTAMP
         ELSE "EXISTING"."CITY_CHANGED_DATE" END AS "CITY_CHANGED_DATE" @id("0b0005"),
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_CREATE_DATE"  @id("0b0008") @isSystemCreateDate,
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_UPDATE_DATE"  @id("0b0009") @isSystemUpdateDate
FROM {{ ref('SRC', 'CUSTOMER') }} "SRC"
LEFT JOIN "EXISTING" ON "SRC"."CUSTOMER_ID" = "EXISTING"."CUSTOMER_ID"

• New customer — no match in EXISTING, so CITY_PREV and CITY_CHANGED_DATE are NULL.
• City changed — the old city moves to CITY_PREV and CITY_CHANGED_DATE is stamped.
• City unchanged — the stored CITY_PREV and CITY_CHANGED_DATE are kept.
• @isSystemCreateDate keeps its first-insert value on update; every other column is overwritten from the SELECT.

Fact Examples

Fact Plain Insert

No @mergeStrategy and no @isBusinessKey column, so every source row is inserted with no matching. System columns are optional and written as the SELECT computes them. Adding @mergeStrategy("changeTracking"), "lastModified" or "upsert" here without a business key would stop the load with a validation error.

SELECT
    "ORDER_ID"                               AS "ORDER_ID"            @id("1a0001"),
    "CUSTOMER_ID"                            AS "CUSTOMER_ID"         @id("1a0002"),
    "AMOUNT"                                 AS "AMOUNT"              @id("1a0003"),
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_CREATE_DATE"  @id("1a0008") @isSystemCreateDate
FROM {{ ref('SRC', 'ORDERS') }}

Fact Change Tracking SCD1

The default strategy. Any change in a non-key column overwrites the row; new keys are inserted.

SELECT
    "ORDER_ID"                               AS "ORDER_ID"            @id("1b0001") @isBusinessKey,
    "STATUS"                                 AS "STATUS"              @id("1b0002"),
    "AMOUNT"                                 AS "AMOUNT"              @id("1b0003"),
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_CREATE_DATE"  @id("1b0008") @isSystemCreateDate,
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_UPDATE_DATE"  @id("1b0009") @isSystemUpdateDate
FROM {{ ref('SRC', 'ORDERS') }}

Fact Last Modified SCD1

A row is overwritten only when its @lastModifiedTracking value is newer than the stored one.

@mergeStrategy("lastModified")
SELECT
    "ORDER_ID"                               AS "ORDER_ID"            @id("1c0001") @isBusinessKey,
    "STATUS"                                 AS "STATUS"              @id("1c0002"),
    "UPDATED_AT"                             AS "UPDATED_AT"          @id("1c0003") @lastModifiedTracking,
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_CREATE_DATE"  @id("1c0008") @isSystemCreateDate,
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_UPDATE_DATE"  @id("1c0009") @isSystemUpdateDate
FROM {{ ref('SRC', 'ORDERS') }}

Fact Upsert

No change detection: existing keys are updated, new keys inserted. System columns are optional.

@mergeStrategy("upsert")
SELECT
    "ORDER_ID"                               AS "ORDER_ID"            @id("1d0001") @isBusinessKey,
    "STATUS"                                 AS "STATUS"              @id("1d0002"),
    "AMOUNT"                                 AS "AMOUNT"              @id("1d0003")
FROM {{ ref('SRC', 'ORDERS') }}

Fact All Column Match

Every non-system column is compared; a row is inserted only if an identical row doesn't already exist. Nothing is ever updated, and no @isBusinessKey is needed.

allColumnMatch is how you build a factless fact table — a table with no measures, where the existence of a row is the fact itself. Typical uses:

• Event tracking — records that something happened, e.g. a student attended a class or a customer visited a page on a date.
• Coverage / relationship tracking — records which combinations are valid or in effect, e.g. which products are on promotion in which stores, so you can also find what didn't happen (promoted products that didn't sell).

@mergeStrategy("allColumnMatch")
SELECT
    "ORDER_ID"                               AS "ORDER_ID"            @id("1e0001"),
    "PRODUCT_ID"                             AS "PRODUCT_ID"          @id("1e0002"),
    "QUANTITY"                               AS "QUANTITY"            @id("1e0003"),
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP)     AS "SYSTEM_CREATE_DATE"  @id("1e0008") @isSystemCreateDate
FROM {{ ref('SRC', 'ORDER_LINES') }}

• SYSTEM_CREATE_DATE is left out of the comparison, so re-running the load doesn't insert the same row again.
• Matching uses plain equality — a row with a NULL in any compared column never matches and is inserted on every run.
• Two identical source rows that aren't in the target yet are both inserted — add DISTINCT to the SELECT if that's not wanted.

Tags and Cluster Key

Tags and cluster keys work the same on Work, Dimension and Fact tables (on a view, tags apply and @clusterKey is ignored). The tags must already exist in Snowflake.

@materializationType("table")
@tag("COST_CENTRE", "FIN")
@tag("OWNER", "DATA_TEAM")
@tag("DATA_DOMAIN", "SALES", "GOVERNANCE")
SELECT
    "N_NATIONKEY"  AS "N_NATIONKEY"  @clusterKey(1),
    "N_NAME"       AS "N_NAME"       @tag("PII", "false") @tag("OWNER", "DATA_TEAM"),
    "N_REGIONKEY"  AS "N_REGIONKEY"  @clusterKey(2, "trunc(""N_REGIONKEY"", -1)"),
    "N_COMMENT"    AS "N_COMMENT"    @tag("PII", "true")
FROM {{ ref('SOURCE_DATA', 'NATION') }} "NATION"

On the first deployment this creates the table with every tag and the cluster key inline (<DB>.<SCHEMA> is the node's own location, <GOV_DB>.<GOV_SCHEMA> the GOVERNANCE location):

CREATE OR REPLACE TABLE "<DB>"."<SCHEMA>"."WRK_NATION" (
    "N_NATIONKEY"  NUMBER(38,0),
    "N_NAME"       VARCHAR(25)  WITH TAG ("<DB>"."<SCHEMA>"."PII" = 'false', "<DB>"."<SCHEMA>"."OWNER" = 'DATA_TEAM'),
    "N_REGIONKEY"  NUMBER(38,0),
    "N_COMMENT"    VARCHAR(152) WITH TAG ("<DB>"."<SCHEMA>"."PII" = 'true')
)
CLUSTER BY ("N_NATIONKEY", trunc("N_REGIONKEY", -1))
WITH TAG ("<DB>"."<SCHEMA>"."COST_CENTRE" = 'FIN', "<DB>"."<SCHEMA>"."OWNER" = 'DATA_TEAM',
          "<GOV_DB>"."<GOV_SCHEMA>"."DATA_DOMAIN" = 'SALES')

• COST_CENTRE, OWNER and PII have no location, so they resolve to the node's own location; DATA_DOMAIN lives in the GOVERNANCE storage location.
• N_NAME carries two different tags; the same tag twice on one column (or on the node) would stop the deploy with Duplicate Tags Found.
• OWNER on the table and OWNER on N_NAME are separate tag assignments (one on the table, one on the column), so both are allowed.
• The cluster key is ordered by position: the plain N_NATIONKEY column first, then the trunc(...) expression. Inside the expression, double quotes are doubled (""N_REGIONKEY"").
• On a later deployment, changing a tag value runs only Set Column Tags / Set Table Tags, and changing the cluster key runs Recluster Table — see How tag changes deploy and Altering the Work Tables.

---

Code

Work

• Node definition
• Create Template
• Run Template

Dimension

• Node definition
• Create Template
• Run Template

Fact

• Node definition
• Create Template
• Run Template

Macro

• Macro

---

Versions

Available versions of the package.

Version #	Release Date	Notes
2.3.0	October 12, 2026	Adds @tag, @clusterKey and transient tables; redeploys use clone-alter-swap-delete, not live table.
2.2.0	October 05, 2026	1) New Fact node: Insert, SCD1 Merge, Factless capability

2. Renamed Work and Dimension nodetypeIDs | | 2.1.0 | September 29, 2026 | New Dimension node: business key merge, SCD1/SCD2 history, upsert and zero-key rows | | 2.0.1 | September 25, 2026 | accepted_vales and rejected_values support lists as input. disabTests, continueOnFailure updated. | | 2.0.0 | September 04, 2026 | Added Work node type supporting annotations directly in their spec compatible with new version. |