Skip to main content

Documentation index: llms.txt. This page is also available as markdown: append .md to this URL or send Accept: text/markdown.

Fabric Base Node Types - SQL

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/fabric/fabric-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.

Fabric Base Node Types - SQL Package

The Fabric Base Node Types - SQL Package includes:

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​

AnnotationWorkDimensionFact
@materializationTypetable / viewtable / viewtable / view
@writeMode✅✅✅
@mergeStrategy➖✅
* changeTracking
* lastModified
* upsert
✅
* changeTracking
* lastModified
* upsert
* allColumnMatch
@zeroKey (node-level)➖✅➖
@disableTests✅✅✅
@tests✅✅✅
@preSQL✅✅✅
@postSQL✅✅✅
Target Load(TRUNCATE) INSERT(TRUNCATE) MERGE(TRUNCATE) MERGE/INSERT

Column Annotations Matrix​

AnnotationWorkDimensionFact
@id (recommended)✅✅✅
@isBusinessKey➖✅
(required)
✅
(conditional) ³
@isChangeTracking➖✅➖
@lastModifiedTracking➖✅
(scdType?)
✅
@zeroKey (column-level)➖✅➖
@isSurrogateKey➖✅➖
@isSystemVersion➖✅➖
@isSystemCurrentFlag➖✅➖
@isSystemCreateDate➖✅✅
@isSystemUpdateDate➖✅✅
@isSystemEndDate➖✅➖
@clusterKey✅✅✅
@inHash✅✅✅
all column tests✅✅✅
  • ³ Optional when @mergeStrategy isn't set — without it the Fact is loaded as a plain insert. Required for an explicit changeTracking, lastModified or upsert; ignored under allColumnMatch.
  • @id is not enforced: a column without one raises the non-failing Warning: Missing Column IDs on Dimension and Fact, and on every node type it is matched by name on redeploy, so renaming it loses its data.

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


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:

Work General Options​

PropertyDescription
Storage LocationStorage Location (Fabric Warehouse and schema) where the Work table or view will be created
Node TypeRead-only: the node type's name and ID — Work (SQLWork). Set from @nodeType when the node is created.

Work Node Annotations​

AnnotationSummary
@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)Not supported in Fabric. Renders a warning stage if a node description is added.
@materializationType(type) (reserved)table or view
@deployDisabled (reserved)Excludes this node from deployment.
@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​

AnnotationSummary
@notNull (reserved)Marks column as NOT NULL.
Note: Ignored on Views.
Example: @notNull
@description(<text>) (reserved)Not supported in Fabric. Renders a warning stage if a column description is added.
@defaultValue(<value>) (reserved)Not supported in Fabric. Renders a warning stage if a default value is added.
@inHash("<hashName>", <hashOrder>) ¹ (repeatable)Marks a column as an input to a generated hash key.
@clusterKey(position?)Marks a column as part of the table's CLUSTER BY key (up to 4 columns), in the order given by the optional position (lowest first); columns without a position follow in SELECT order.

Details for each annotation are in the Annotation Reference.

🚦 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 before any change in Fabric. A failing check stops the deployment; a warning does not:

StageDescription
Unsupported Materialization Type: &lt;node>@materializationType is not table or view (Fabric has no transient table) — stops the deployment and the run
Warning: Not Supported in FabricNon-failing — a node description, column description or @defaultValue is set; Fabric has none of these, so they are ignored
Invalid configuration: clusterKey | Invalid configuration: clusterKey Order | Invalid configuration: clusterKey Data Type | Warning: clusterKey on Long Text ColumnTable only — see Clustering. More than 4 columns, a repeated or invalid order number, or a BIT / (MAX) column stops the deployment; a CHAR/VARCHAR longer than 32 only warns.
Warning: Column Change Not AppliedNon-failing, redeploy only — a NOT NULL change, or a column added as NOT NULL, that Fabric cannot apply to an existing table

Work Initial Deployment​

When deployed for the first time into an environment the Work node of materialization type table will execute the below stages:

StageDescription
Drop tableDrops any existing table — Fabric has no CREATE OR REPLACE TABLE
Create tableExecutes a CREATE TABLE statement, with NOT NULL and CLUSTER BY

When deployed for the first time into an environment the Work node of materialization type view will execute the below stages:

StageDescription
Drop Stage ViewDrops any existing view
Create Stage ViewExecutes a CREATE VIEW statement ([schema].[name] — Fabric's CREATE VIEW takes no database name)

Work Load​

StageDescription
Pre Load Test <n>Node-level @tests(..., "Before") tests, in the order they appear.
Pre-SQL <n>Each @preSQL statement, in the order they appear.
Truncate tableExecuted unless @writeMode("append") is set — clears the target before the load.
Load table using InsertInserts every source row with INSERT INTO … SELECT.
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.
Load Skipped for ViewFor a view there is no load — only the tests run.

Work Redeployment​

After the Work node has been deployed for the first time into a target environment, subsequent deployments alter the Work table, rebuild it, or recreate the Work view. Only what changed is applied; when nothing in the table itself changed, a Metadata Update-Default stage is recorded.

Altering the Work Tables​

A few types of column or table changes will alter the Work table in the target environment, whether these changes are made individually or all together:

  • Changing table names
  • Dropping existing columns
  • Altering column data types
  • Adding new columns
  • Renaming Columns
  • Adding, removing or reordering @clusterKey columns

Fabric has no ALTER COLUMN or RENAME COLUMN, so every alter runs on a zero-copy clone of the table, which is then swapped in. The following stages are executed:

StageDescription
Clone TableCreates an internal clone of the table (CREATE TABLE … AS CLONE OF)
Delete Column | Alter Column | Rename Column | Add ColumnApplied to the clone. A dropped, retyped or renamed column rebuilds the clone with CREATE TABLE AS SELECT; an added column uses ALTER TABLE … ADD
Rename Clone Table | Rename TableUpon successful completion of all updates, the clone replaces the main table (Rename Table when the node was renamed in the same location), ensuring that no data is lost
Rebuild Table With Cluster Key | Rebuild Table Without Cluster KeyReplaces the rename step when the cluster key changed, or the table is clustered — the table is recreated from the clone with CREATE TABLE … WITH (CLUSTER BY …) AS SELECT, keeping its data
Delete TableDrops the internal clone (and the old table, when renamed or moved)
Warning: Column Change Not AppliedNon-failing — a NOT NULL change Fabric cannot apply to an existing table

Note: A column is matched by its @id, so a column renamed with the same @id keeps its data. A Work column without an @id is matched by name: renaming it drops it and adds a new one, which loses its data. Fabric names are case-sensitive, so a case-only change (e.g. N_NAME → n_Name) is also applied as a rename.

Altering and Recreating the Work Views​

If any of the following changes are detected, the view is recreated by dropping it first and then creating it again:

  • Join clause
  • Adding transformation
  • Changes in configuration like adding distinct
  • Renaming the node or moving it to another location

The following stages are executed:

StageDescription
Drop Stage ViewRemoves the existing view
Create Stage ViewCreates the view with the updated definition

Switching a node between table and view first drops the old object (Delete Table / Delete View), then creates the new one.

Removing a Work Node​

If a Work Node 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 or view in the target Environment will be dropped:

StageDescription
Delete Table | Delete ViewDrops the target table or view in Fabric

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 column — 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:

Dimension General Options​

PropertyDescription
Storage LocationStorage Location (Fabric Warehouse and schema) where the Dimension table or view will be created
Node TypeRead-only: the node type's name and ID — Dimension (SQLDimension). Set from @nodeType when the node is created.

Dimension Node Annotations​

AnnotationSummary
@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)Not supported in Fabric. Renders a warning stage if a node description is added.
@materializationType(type) (reserved)table or view
@deployDisabled (reserved)Excludes this node from deployment.
@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

AnnotationSummary
@id("<value>")Stable column ID for lineage tracking.
@notNull (reserved)Marks column as NOT NULL.
Note: Ignored on Views.
Example: @notNull
@description(<text>) (reserved)Not supported in Fabric. Renders a warning stage if a column description is added.
@defaultValue(<value>) (reserved)Not supported in Fabric. Renders a warning stage if a default value is added.
@inHash("<hashName>", <hashOrder>) ¹ (repeatable)Marks a column as an input to a generated hash key.
@clusterKey(position?)Marks a column as part of the table's CLUSTER BY key (up to 4 columns), in the order given by the optional position (lowest first); columns without a position follow in SELECT order.

Dimension-specific column annotations

AnnotationSummary
@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
@isChangeTrackingMarks a column to be watched for changes that decide SCD type.
@lastModifiedTracking(scdType?)Marks the column used to detect newer source rows under @mergeStrategy("lastModified"); scdType 1 (default) overwrites, 2 versions the row.
@zeroKey(value)Adds a custom zero key value (ghost record) to this column, overriding the node-level @zeroKey defaults for it.
@isSurrogateKeyMarks this column as the node's surrogate key.
@isSystemVersionMarks this column as the SCD version number, incremented each time a business key gets a new version.
@isSystemCurrentFlagMarks this column as the SCD "is current" flag — 'Y' on the active version of a row, overwritten to 'N' when it's expired.
@isSystemCreateDateMarks this column as the timestamp a row (or, under SCD Type 2, a specific row version) was first created.
@isSystemUpdateDateMarks this column as the timestamp a row was last updated.
@isSystemEndDateMarks 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.

🚦 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.

ValueDescription
upsertNo 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. @isChangeTracking / @lastModifiedTracking columns aren't used — if present, a warning is raised and they're ignored.
changeTrackingCompares 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. A @lastModifiedTracking column isn't used — a warning is raised and it's ignored.
lastModifiedCompares a single @lastModifiedTracking column against the target's stored value — a row changes only when the source value is newer. SCD Type 1 or 2 comes from that column's scdType (@lastModifiedTracking(1) / (2), default 1). Requires exactly one @lastModifiedTracking column — none, or more than one, stops the load. @isChangeTracking isn't used — 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 and lastModified the load writes fixed system column values — see System column values.
  • Note: Ignored on Views.

Example:

@mergeStrategy("changeTracking")
@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.

ParameterDescription
surrogateKeyValue(required) Value used for the zero record's surrogate key column only, pasted into the SQL verbatim — write it as a value of the key's type: the surrogate key is an integer (BIGINT) column, so give a number, e.g. "-1" or 0.
stringValue(optional) Default value for CHAR/VARCHAR columns, pasted into the SQL verbatim — include your own quotes. Default "'UNKNOWN'".
timestampValue(optional) Default value for DATE/TIME/DATETIME2 columns. Default "1900-01-01 00:00:00".
booleanValue(optional) Default value for BIT columns (true → 1, false → 0). Default true.
  • Requires an integer @isSurrogateKey column (e.g. CAST(0 AS BIGINT) AS [DIM_CUSTOMER_KEY] @isSurrogateKey) — the zero record is matched by its key value. Real rows are numbered from the current highest key + 1, so use a low value such as @zeroKey("-1") or @zeroKey(0).
  • Numeric columns always get 0; override any column with a column-level @zeroKey(value).
  • A value longer than its VARCHAR(n) / CHAR(n) column raises a non-failing Warning: Zero Key Value Too Long stage, and the Zero Key Record stage then fails. Use a shorter value or widen the column. VARCHAR with no length holds 1 character.
  • Positive numbers and booleans can be unquoted (@zeroKey(0), @zeroKey(false); a boolean is written to BIT as 1/0). Quote negative numbers (@zeroKey("-1")), strings (@zeroKey("'N/A'")) and dates (@zeroKey("CAST('1900-01-01' AS DATETIME2(6))")).
  • Not specified in the SQL editor → the zero record is not inserted.
  • Note: Ignored on Views.

Example:

@zeroKey("-1", "'UNKNOWN'", "1900-01-01 00:00:00", true) -- BIGINT surrogate key, all values set
@zeroKey -- defaults: key 0, 'UNKNOWN', 1900-01-01 00:00:00, BIT 1
@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.

ParameterDescription
valueZero 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 value longer than the column's declared VARCHAR(n) / CHAR(n) 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") -- negative number: always quoted
@zeroKey("0.00") -- decimal
@zeroKey("CAST('1900-01-01' AS DATE)") -- date
@zeroKey("CAST('1900-01-01 00:00:00' AS DATETIME2(6))") -- datetime2
@zeroKey(false) -- BIT: written as 0
@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.
  • The table has no IDENTITY column (it could not take the zero record's own key value). The load generates the key instead: each new row, or new SCD Type 2 version, gets ROW_NUMBER() + the current highest key. Existing rows keep their key.
    • The column must be an integer type (BIGINT recommended), otherwise Invalid configuration: Surrogate Key Data Type stops the deployment.
    • Under upsert, keys are numbered across every incoming row but only used for new ones, so the sequence can have gaps.
  • Note: Ignored on Views. Agentic creation only — manual creation adds this column automatically.
  • Expected expression: CAST(0 AS BIGINT) AS [<NODE_NAME>_KEY] @id("<6 hex digits>") @isSurrogateKey — type the node name yourself, e.g. DIM_CUSTOMER_KEY. The SELECT expression only sets the column's datatype; its value is ignored.
@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: CAST('Y' AS VARCHAR(1)) 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 DATETIME2(6)) 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:

@mergeStrategyResolves toDriven by
upsertWhatever 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
changeTracking (default)SCD2 if any column is marked, otherwise SCD1@isChangeTracking
lastModifiedSCD1 or SCD2, from the column's scdType@lastModifiedTracking(scdType)

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

AnnotationCategorySCD1 (changeTracking / lastModified(1))SCD2 (changeTracking / lastModified(2))upsert
@isBusinessKeyKey🔴🔴🔴
@lastModifiedTrackingTracking🔴 lastModified only🔴 lastModified only—
@isSurrogateKeyKey⚪🟡⚪
@isSystemCreateDateSystem column🔴🔴⚪
@isSystemUpdateDateSystem column🔴🔴⚪
@isSystemVersionSystem column⚪🔴⚪
@isSystemCurrentFlagSystem column⚪🔴⚪
@isSystemEndDateSystem 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.
  • ² 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), 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.
  • @isChangeTracking / @lastModifiedTracking columns on an upsert node are never read — a warning is raised and they're ignored.
  • Under lastModified only the tracking column decides a change: other columns that change without a newer tracking value are not applied, and an SCD2 node never updates the current row in place.
  • @isSurrogateKey, whenever present, must be an integer type (BIGINT recommended); the load generates its values — see @isSurrogateKey.

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:

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

All timestamps are written as DATETIME2(6).

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


Dimension Deployment​

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

StageDescription
Unsupported Materialization Type: &lt;node>@materializationType is not table or view — stops the deployment and the run
Warning: Not Supported in FabricA node description, column description or @defaultValue is set — Fabric has none of these, so they are ignored. Does not stop the deployment.
Duplicate Column Names | Duplicate Column IDs | Warning: Missing Column IDs | Missing Business Key | Invalid configuration: mergeStrategy | Invalid configuration: Last Modified Column | Invalid configuration: lastModifiedTracking scdType | Invalid configuration: Surrogate Key Data Type | Missing Required System Columns | Surrogate Key Not Set/Using Custom Surrogate Key Logic | Warning: Zero Key Value Too LongDimension validation checks (table only), rendered only when the annotations have a problem — see Dimension Load. A required problem stops the deployment; a recommendation or a stage starting with Warning: does not. Invalid configuration: mergeStrategy stops the deployment for an unknown strategy or for lastModified without a @lastModifiedTracking column, but only warns when a tracking annotation the strategy never reads is set (@isChangeTracking / @lastModifiedTracking under upsert, @lastModifiedTracking under changeTracking, @isChangeTracking under lastModified) — it is ignored and the load continues. Invalid configuration: Last Modified Column stops it when more than one column is marked @lastModifiedTracking; Invalid configuration: lastModifiedTracking scdType when scdType is not 1 or 2.
Invalid configuration: clusterKey | Invalid configuration: clusterKey Order | Invalid configuration: clusterKey Data Type | Warning: clusterKey on Long Text Column@clusterKey checks (table only): more than 4 columns, a repeated or invalid order number, or a BIT / (MAX) column stops the deployment; a CHAR/VARCHAR longer than 32 only warns.
Warning: Column Change Not AppliedRedeploy only: a NOT NULL change, or a column added as NOT NULL, that Fabric cannot apply to an existing table. Does not stop the deployment.

Dimension Initial Deployment​

When deployed for the first time into an environment the Dimension node will execute the below stages:

StageDescription
Drop table / Create tableTable: Fabric has no CREATE OR REPLACE TABLE, so any existing table is dropped first; then a CREATE TABLE statement runs, with NOT NULL and CLUSTER BY
Drop Stage View / Create Stage ViewView: drops any existing view, then runs a CREATE VIEW statement — see Dimension View

Dimension View​

A Dimension Node of materialization type view is a plain DROP VIEW IF EXISTS + CREATE VIEW over the node's SELECT. Fabric's CREATE VIEW takes no database name, so the view is created as [schema].[name]. On a redeploy it is only recreated when its definition, name or location changed; switching a node from table to view drops the table first.

  • None of the Dimension validation checks run — @isBusinessKey, system columns, @id and @mergeStrategy aren't required or checked. Only Unsupported Materialization Type and Warning: Not Supported in Fabric can appear.
  • There is no merge; the run shows Load Skipped for View. @mergeStrategy, @writeMode, @zeroKey, @preSQL, @postSQL, @clusterKey and the tracking annotations are ignored.
  • Any system column (surrogate key, version, current flag, create/update/end date) is selected as a typed NULL (CAST(NULL AS <type>)), keeping the column's datatype.
  • Descriptions and @defaultValue aren't supported in Fabric and are ignored, the same as for tables.
  • Column-level data quality tests and node-level @tests still run.

Dimension Load​

Every run 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:

StageDescription
Pre Load Test <n>Node-level @tests(..., "Before") tests, in the order they appear.
Validation checksThe checks listed under Dimension Deployment, rendered only when the node's annotations have a problem.
Check NULL values for <column> columnPre-load check for @mergeStrategy("lastModified") — stops the run if the @lastModifiedTracking column is NULL in any source row, since a NULL can never compare as newer.
Pre-SQL <n>Each @preSQL statement, in the order they appear.
Truncate tableExecuted only when @writeMode("truncateInsert") is set — clears the target before the merge.
Load table Using Merge - Zero Key RecordExecuted 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 - UpsertExecuted 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 - Last Modified Comparison - SCD1 | SCD2Executed for @mergeStrategy("lastModified") — a row is changed only when its @lastModifiedTracking value is newer than the stored one; SCD1 (@lastModifiedTracking / (1)) overwrites it in place, SCD2 (@lastModifiedTracking(2)) expires the current row and inserts a new version.
Load table Using Merge - Change Tracking - SCD1 | SCD2Executed for @mergeStrategy("changeTracking") (the default) — SCD1 compares every plain attribute column and updates the row in place; SCD2 (any @isChangeTracking column) expires the current row and inserts a new version when an @isChangeTracking column changes, and updates the current row in place when only a plain attribute column changes.
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.

New surrogate keys are generated during the merge as ROW_NUMBER() + the current highest key.

Dimension Redeployment​

Redeployment works exactly like the Work node — see Altering the Work Tables and Altering and Recreating the Work Views; the create stages read Create table / Create Stage View. A table is altered on a clone (Clone Table → column changes → Rename Clone Table / Rename Table → Delete Table); with @clusterKey the final step is Rebuild Table With Cluster Key instead. A metadata update (Metadata Update-Default) is recorded when only tests, @writeMode, @mergeStrategy, @zeroKey, @preSQL/@postSQL or a transform or join on a table changed.

Note: Fabric has no RENAME COLUMN. A column renamed with the same @id is applied by rebuilding the table with the new name, so its data is kept. A column without an @id is matched by name, so renaming it runs as Delete Column + Add Column and its values are lost (Warning: Missing Column IDs). Fabric names are case-sensitive, so a case-only change (e.g. N_NAME → n_Name) is also applied as a rename.

Removing a Dimension Node​

If a Dimension Node 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 or view in the target Environment will be dropped (Delete Table / Delete View) — the same stages as Removing a Work Node.


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 four 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), a merge with no change detection (upsert), or an insert of only the rows that don't already exist (allColumnMatch). 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:

Fact General Options​

PropertyDescription
Storage LocationStorage Location (Fabric Warehouse and schema) where the Fact table or view will be created
Node TypeRead-only: the node type's name and ID — Fact (SQLFact). Set from @nodeType when the node is created.

Fact Node Annotations​

AnnotationSummary
@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.
Not supported in Fabric — ignored, and the deployment shows Warning: Not Supported in Fabric.
@materializationType(type) (reserved)table or view
@deployDisabled (reserved)Excludes this node from deployment.
@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​

AnnotationSummary
@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.
Not supported in Fabric — ignored, and the deployment shows Warning: Not Supported in Fabric.
@defaultValue(<value>) (reserved)Adds default value.
Not supported in Fabric — ignored, and the deployment shows Warning: Not Supported in Fabric.
@clusterKey(position?)Marks a column as part of the table's CLUSTER BY key (up to 4 columns), in the order given by the optional position (lowest first); columns without a position follow in SELECT order.
@inHash("<hashName>", <hashOrder>) ¹ (repeatable)Marks a column as an input to a generated hash key.
@isBusinessKeyMarks a column as part of the business key used to match existing rows during the merge.
@lastModifiedTrackingMarks the column used to detect newer source rows under @mergeStrategy("lastModified") (SCD Type 1).
@isSystemCreateDateMarks this column as the timestamp a row was first created.
@isSystemUpdateDateMarks 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: Quoting matters when writing @tests, @preSQL, and @postSQL — see Quoting Inside Annotations.

🚦 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.

ValueDescription
changeTrackingSCD 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.
lastModifiedSCD 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 — none, or more than one, stops the load.
upsertNo 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.
allColumnMatchTreats 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 fails the validation check Invalid configuration: mergeStrategy and stops the deployment and the run.
  • 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@mergeStrategyLoad
nonenot setPlain insert (default without a business key)
nonechangeTracking / lastModified / upsert❌ Validation error — the load stops
markednot set, or changeTrackingSCD1 merge on the business key (default with a business key)
markedlastModifiedSCD1 merge on the business key, driven by the @lastModifiedTracking column
markedupsertMerge on the business key
any (ignored, warning if marked)allColumnMatchMerge on every non-system column — inserts only new rows

Requirement level of each column annotation, by load:

AnnotationchangeTracking / lastModifiedupsertallColumnMatchPlain 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 SYSDATETIME() on first insert and is then kept; @isSystemUpdateDate gets SYSDATETIME() whenever the row is inserted or changed. Both are written as DATETIME2(6). 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 — that row is inserted again on every run. See Duplicate or NULL Business Keys.


Fact Deployment​

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

StageDescription
Unsupported Materialization Type: &lt;node>@materializationType is not table or view — stops the deployment and the run
Warning: Not Supported in FabricNon-failing — a node description, column description or @defaultValue is set; Fabric has none of these, so they are ignored
Duplicate Column Names | Duplicate Column IDs | Warning: Missing Column IDs | Invalid configuration: mergeStrategy | Invalid configuration: No Business Key | Invalid configuration: Last Modified Column | Warning: lastModifiedTracking Parameter Ignored | Missing Required System ColumnsFact validation checks (table only), rendered only when the annotations have a problem. A required problem stops the deployment; a stage starting with Warning: does not. Invalid configuration: mergeStrategy stops the deployment for an unknown strategy or for lastModified without a @lastModifiedTracking column, but only warns when an annotation the strategy never reads is set (@isBusinessKey under allColumnMatch, @lastModifiedTracking under changeTracking / upsert / allColumnMatch); Invalid configuration: No Business Key likewise only warns for @lastModifiedTracking on a plain-insert node. Warning: lastModifiedTracking Parameter Ignored — a Fact is always SCD Type 1, so @lastModifiedTracking takes no parameter.
Invalid configuration: clusterKey | Invalid configuration: clusterKey Order | Invalid configuration: clusterKey Data Type | Warning: clusterKey on Long Text ColumnTable only — see Clustering. More than 4 columns, a repeated or invalid order number, or a BIT / (MAX) column stops the deployment; a CHAR/VARCHAR longer than 32 only warns.
Warning: Column Change Not AppliedNon-failing, redeploy only — a NOT NULL change, or a column added as NOT NULL, that Fabric cannot apply to an existing table

Fact Initial Deployment​

StageDescription
Drop table / Create tableTable: Fabric has no CREATE OR REPLACE TABLE, so any existing table is dropped first; then a CREATE TABLE statement runs, with NOT NULL and CLUSTER BY
Drop Stage View / Create Stage ViewView: drops any existing view, then runs a CREATE VIEW statement — see Fact View

Fact View​

A Fact Node of materialization type view is a plain DROP VIEW IF EXISTS + CREATE VIEW over the node's SELECT. Fabric's CREATE VIEW takes no database name, so the view is created as [schema].[name]:

  • 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, @clusterKey and @lastModifiedTracking are ignored.
  • @isSystemCreateDate / @isSystemUpdateDate columns are selected as a typed NULL, keeping the column's datatype.
  • Descriptions and @defaultValue aren't supported in Fabric and are ignored, the same as for tables.
  • Column-level data quality tests and node-level @tests still run.

Fact Load​

StageDescription
Pre Load Test <n>Node-level @tests(..., "Before") tests, in the order they appear.
Validation checksThe checks listed under Fact Deployment, rendered only when the node's annotations have a problem.
Check NULL values for <column> columnPre-load check for @mergeStrategy("lastModified") — stops the run if the @lastModifiedTracking column is NULL in any source row.
Pre-SQL <n>Each @preSQL statement, in the order they appear.
Truncate tableExecuted only when @writeMode("truncateInsert") is set — clears the target before the load.
Load table Using InsertExecuted 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 - SCD1Executed for @mergeStrategy("changeTracking"), and by default when no @mergeStrategy is set and a column is marked @isBusinessKey.
Load table Using Merge - Last Modified Comparison - SCD1Executed for @mergeStrategy("lastModified") — a row is overwritten only when its @lastModifiedTracking value is newer than the stored one.
Load table Using Merge - UpsertExecuted for @mergeStrategy("upsert").
Load table Using Merge - All Column MatchExecuted 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.

Fact Redeployment​

Redeployment works exactly like the Work node — see Altering the Work Tables and Altering and Recreating the Work Views; the create stages read Create table / Create Stage View. A table is altered on a clone (Clone Table → column changes → Rename Clone Table / Rename Table → Delete Table); with @clusterKey the final step is Rebuild Table With Cluster Key instead. A metadata update (Metadata Update-Default) is recorded when nothing in the table itself changed.

Note: Fabric has no RENAME COLUMN. A column renamed with the same @id is applied by rebuilding the table with the new name, so its data is kept. A column without an @id is matched by name, so renaming it runs as Delete Column + Add Column and its values are lost (Warning: Missing Column IDs). Fabric names are case-sensitive, so a case-only change (e.g. N_NAME → n_Name) is also applied as a rename.

Removing a Fact Node​

If a Fact Node 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 or view in the target Environment will be dropped (Delete Table / Delete View) — the same stages as Removing a Work Node.

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 Annotations​

@materializationType​

@materializationType(type) (reserved)

ValueDescription
tableTable (default).
viewView.
  • Fabric has no transient table — any value other than table or view stops the deploy and the run with an Unsupported Materialization Type check. The value is not case-sensitive.
  • Not specified in the SQL editor → defaults to table.

Example:

@materializationType("view")
@writeMode​

@writeMode("truncateInsert | append")

  • Note: Ignored on Views.

Work:

ValueDescription
truncateInsertClears the table (Truncate table stage) and then inserts the new rows (INSERT INTO … SELECT), replacing its contents entirely.
appendInserts 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:

ValueDescription
truncateInsertClears the table before loading, replacing its contents entirely.
appendDimension: 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>")
ParameterDescription
querySQLSQL statement to execute as a validation test. The test fails if the query returns any records. Use {TARGET} for this node's table and {TARGET_SCHEMA} for its database and schema; reference any other node with {{ ref('<location>', '<node>') }}.
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.

Example:

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

More examples:

@tests("SELECT 1 FROM {TARGET} GROUP BY [N_COMMENT] HAVING COUNT(*) > 1", true, "Before") -- runs Before the load, continueOnFailure=true
@tests("SELECT 1 FROM {TARGET} GROUP BY [N_COMMENT] HAVING COUNT(*) > 1", false) -- runs After the load (default), continueOnFailure=false
@tests("SELECT 1 FROM {TARGET} 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.

  • Repeat the annotation to run multiple statements, in the order they appear.
  • Use {TARGET} for this node's table and {TARGET_SCHEMA} for its database and schema; reference any other node with {{ ref('<location>', '<node>') }}.
  • Note: Ignored on Views.

Example:

@preSQL("DELETE FROM {TARGET} WHERE [N_LOAD_DATE] < DATEADD(DAY, -90, CAST(SYSDATETIME() AS DATE))")
@postSQL​

@postSQL(querySQL)

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

  • Repeat the annotation to run multiple statements, in the order they appear.
  • Use {TARGET} for this node's table and {TARGET_SCHEMA} for its database and schema; reference any other node with {{ ref('<location>', '<node>') }}.
  • Note: Ignored on Views.

Example:

@postSQL("INSERT INTO {TARGET_SCHEMA}.[LOAD_LOG] ([TABLE_NAME], [LOAD_TS]) VALUES ('WRK_NATION', SYSDATETIME())")

Column Annotations​

@id (column)​

@id("<value>")

Stable column ID for lineage tracking.

ParameterDescription
valueSix hexadecimal digits, unique within the node, e.g. 110e75.
  • Expected on every Dimension and Fact column — the SQL editor generates a fresh @id("<value>") for each column it creates. A column without one raises the non-failing Warning: Missing Column IDs: it is matched by name on redeploy, so renaming it drops and re-adds the column and its values are lost.
  • Not for manual editing — value is a stable identifier generated once at creation time.
  • Column added later: give it its own new, unique 6-hex-digit @id. 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 a column DEFAULT.

  • Not supported in Fabric — Fabric Warehouse has no column defaults. The annotation is reserved so it can't be removed, but it is ignored: no DEFAULT is written, and the deployment shows the non-failing Warning: Not Supported in Fabric.
@clusterKey​

@clusterKey(position?)

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

  • position is optional — columns without one follow the numbered columns, in SELECT order. A position must be a positive whole number, used once per node; a repeated or invalid one stops the deploy with Invalid configuration: clusterKey Order.
  • Fabric clusters on at most 4 columns — more stops the deploy with Invalid configuration: clusterKey.
  • Plain columns only — compute an expression as its own column in the SELECT and cluster on that.
  • BIT and (MAX) columns can't be clustered — the deploy stops with Invalid configuration: clusterKey Data Type. Only the first 32 characters of a CHAR/VARCHAR feed the clustering statistics, so a longer text column raises the non-failing Warning: clusterKey on Long Text Column. Other types are left to Fabric to accept or reject when the table is created.
  • ⚠️ Clustering can only be set when a table is created. On redeployment, a cluster key change (or any column change on a clustered table) rebuilds the table with a full copy of its data (Rebuild Table With Cluster Key); the data is kept.
  • Note: Ignored on Views.

Example:

@clusterKey(1)
@clusterKey(2)
@clusterKey
@inHash​

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

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

ParameterDescription
hashNameColumns sharing the same value are grouped together into the same hash.
hashOrderThis 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),
CAST({{ get_hash('GH_COL') }} AS VARCHAR(128)) AS [GH_COL]

get_hash() macro

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

{{ get_hash("<hash_name>", "<algo>", "<delimiter>") }}
ParameterDescription
hash_nameHash name used across columns to identify the columns included in the hash.
algo(optional) Hashing algorithm to use (T-SQL HASHBYTES). Supported values are SHA2_256, SHA2_512, SHA1 and MD5; SHA256 and SHA512 are accepted as aliases. Defaults to SHA2_256.
delimiter(optional) Delimiter used to separate column values when generating the hash. Defaults to `
  • The columns are cast to VARCHAR(8000) (FLOAT/REAL with full precision), a NULL becomes 'null', they are joined in hashOrder with the delimiter, hashed with HASHBYTES, and returned as a lowercase hex string (64 characters for SHA2_256, 128 for SHA2_512, 40 for SHA1, 32 for MD5).
  • Wrap the call in CAST(... AS VARCHAR(128)). The SQL parser can't infer a datatype through the macro, so a bare {{ get_hash(...) }} column has no known datatype and fails at create.
  • algo is not case-sensitive ('md5' = 'MD5'). delimiter='' joins the values with no delimiter.
  • When no column is marked with the hash name, get_hash() returns NULL. The hash name is matched exactly, so get_hash('gh_col') does not pick up @inHash("GH_COL", 1).

get_hash() examples:

Using hash macro(default-SHA2_256)

<col_name> AS <col_name> @inHash("GH_COL", 1),
CAST({{ get_hash('GH_COL') }} AS VARCHAR(128)) AS [GH_COL]

Using hash macro(MD5)

<col_name> AS <col_name> @inHash("GH_COL", 1),
CAST({{ get_hash('GH_COL', 'MD5') }} AS VARCHAR(128)) AS [GH_COL]

Using hash macro(SHA1)

<col_name> AS <col_name> @inHash("GH_COL", 1),
CAST({{ get_hash('GH_COL', 'SHA1') }} AS VARCHAR(128)) AS [GH_COL]

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

<col_name> AS <col_name> @inHash("GH_COL", 1),
CAST({{ get_hash('GH_COL', algo='SHA2_256', delimiter='~') }} AS VARCHAR(128)) AS [GH_COL]

Using hash macro(SHA2_512, no delimiter)

<col_name1> AS <col_name1> @inHash("GH_COL", 1),
<col_name2> AS <col_name2> @inHash("GH_COL", 2),
CAST({{ get_hash('GH_COL', 'SHA2_512', '') }} AS VARCHAR(128)) 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),
CAST({{ get_hash('GH_COL') }} AS VARCHAR(128)) 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),
CAST({{ get_hash('GH_COL1') }} AS VARCHAR(128)) AS [GH_COL_COMBINED1],
CAST({{ get_hash('GH_COL2', delimiter='~') }} AS VARCHAR(128)) AS [GH_COL_COMBINED2]

Using explicit expression:

LOWER(CONVERT(VARCHAR(128),
HASHBYTES('SHA2_256',
ISNULL(CAST(<col_name> AS VARCHAR(8000)), 'null')
), 2)
) AS [GH_Key]
@lastModifiedTracking​

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

Marks the column used to detect newer source rows for an incremental load — used only by @mergeStrategy("lastModified").

  • Datatype — DATE / TIME / DATETIME2 or any incrementing numeric type.
  • Can only be applied to one column — more than one stops the load with Invalid configuration: Last Modified Column.
  • Must not be NULL in the source — the pre-load Check NULL values for <column> column stage stops the run, since a NULL value can never compare as newer.
  • Only this column decides a change — other columns that change without a newer tracking value are never applied.
  • Under any other strategy (or on a plain-insert Fact) it is ignored with a warning.
  • Note: Ignored on Views.

Dimension:

ParameterDescription
scdType(optional) 1 overwrites the row in place, 2 expires the row and inserts a new version. Not specified → defaults to 1. Any other value stops the load with Invalid configuration: lastModifiedTracking scdType.

Fact: takes no parameter — a Fact is always SCD Type 1. A parameter raises the non-failing Warning: lastModifiedTracking Parameter Ignored.

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(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_CREATE_DATE] @isSystemCreateDate

Dimension: 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.

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(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_UPDATE_DATE] @isSystemUpdateDate

Dimension: required for SCD Type 1 and Type 2 — recomputed to the current timestamp on every inserted, changed or expired row. Optional for upsert.

Fact: required for changeTracking / lastModified; optional for upsert, allColumnMatch and plain insert.

Quoting Inside Annotations​

Raw SQL pasted into @tests, @preSQL and @postSQL is wrapped in the annotation's "..." double quotes. Write Fabric identifiers in square brackets, not double quotes, so the two never clash:

  • Use square brackets for identifiers ([N_NAME]) and single quotes for string literals ('F'), exactly as in T-SQL.
  • Fabric object and column names are case-sensitive — [N_NAME] and [n_name] are different columns, so the case must match exactly. Text comparisons are case-sensitive too ('F' = 'f' is false).
  • A positive number or a boolean argument can be written with or without quotes (@clusterKey(1) is the same as @clusterKey("1")). A negative number, text and SQL must be quoted. An unquoted negative number (@zeroKey(-1)) breaks the parse of every annotation on the node.
@tests("SELECT 1 FROM {TARGET} WHERE [N_NAME] = 'UNKNOWN'", false, "After")
@preSQL("DELETE FROM {TARGET} WHERE [O_ORDERSTATUS] = 'F'")

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.

PropertyDescription
@not_nullFails on rows where the column is NULL.
Example: @not_null
@uniquenessFails when a value appears on more than one row.
Example: @uniqueness
@emptyFails 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 one value or a comma-separated list —
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>'" (case-sensitive)
BIT: 1/0, 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 "CAST('2026-01-01' AS DATE)"
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 literal or expression in double quotes, with any literal inside it in single quotes, e.g. "'2026-01-01'" or "SYSDATETIME()"
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).
The other column's name must match its case exactly. Different date/time types (e.g. DATE and DATETIME2) are compared after T-SQL's implicit conversion.
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. The create dry-run still reports passed with UNKNOWN in the DDL, so check the rendered column list rather than the status.

  • Datatype Compatibility: If the defaulted datatype is not compatible with Microsoft Fabric Warehouse, use CAST() to explicitly convert the value to a Fabric-compatible datatype (e.g. VARCHAR(n) instead of NVARCHAR, DATETIME2(6) instead of DATETIME, DECIMAL(p,s) instead of MONEY).

  • 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
    [N_REGIONKEY] AS [N_REGIONKEY],
    [N_NAME] AS [N_NAME]
    FROM {{ ref('SRC', 'NATION') }} [NATION]
  • SELECT * is not currently supported in the outer/main SELECT statement — specify the column names explicitly. Inside a CTE, SELECT * can be used.

  • 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.

  • 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. T-SQL does not allow a CTE inside INSERT … SELECT or MERGE … USING, so the node's CTEs are placed before the generated INSERT / MERGE statement.

  • Nodes with No Upstream Source: A node's SELECT can generate its own rows entirely in SQL — e.g. a calendar / date dimension built from a numbers CTE, with no {{ ref() }} at all. See Date Dimension Generated in SQL.

  • Pivot and Unpivot: PIVOT / UNPIVOT as keywords are not supported by the Coalesce SQL parser for Fabric. Until they are, write a pivot with GROUP BY and conditional aggregation (MAX(CASE WHEN …) / SUM(CASE WHEN …)), and an unpivot as a UNION ALL of one SELECT per column (see Known Limitations). See Pivot without PIVOT and Unpivot without UNPIVOT.

  • Recursive CTEs are not supported by Fabric Warehouse — see Known Limitations for a non-recursive way to build a date series.

  • 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. T-SQL does not allow GROUP BY 1 (a column position) — repeat the column or expression instead.

  • No Recursive CTEs:
    Fabric Warehouse rejects a CTE that references itself ("Msg 8768: Recursive CTEs are unsupported in this version of Synapse SQL"). Build hierarchies with a fixed number of self-joins instead, and generate a date or number series from a small numbers CTE — see Date Series without Recursion.

  • No PIVOT / UNPIVOT:
    PIVOT and UNPIVOT as keywords are not currently supported by the Coalesce SQL parser for Fabric — anywhere in the query, including inside a CTE or after a sub-query, and even with every output column listed and cast. Validation fails with errors such as "Expected EOF or ';', but found 'PIVOT'" or "Unexpected 'FOR'". (Fabric itself runs them; it's the parser that rejects them.) Until the parser supports them, use GROUP BY with conditional aggregation for a pivot and UNION ALL for an unpivot — see Pivot without PIVOT and Unpivot without UNPIVOT.

  • 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.

  • Case Sensitivity:
    Fabric Warehouse is case-sensitive for both names and data:

    • [N_NAME] and [n_name] are different columns, and [WRK_customer] and [WRK_CUSTOMER] are different tables — using the wrong case fails with "Invalid column name" / "Invalid object name". Match the exact case in @tests, @preSQL, @postSQL and @relative_time.
    • 'Mark' = 'mark' is false, so business key matching, change detection, @accepted_values / @rejected_values and hashes all depend on exact casing — 'Mark' and 'mark' are two different business keys, and a case-only change in an attribute counts as a change. Standardise the case in the SELECT (e.g. UPPER(LTRIM(RTRIM([NAME])))) when the source is inconsistent.
    • A case-only column change (e.g. N_NAME → n_Name) is a real rename in Fabric: it runs Rename Column and keeps the data.
  • Cluster Key Changes Rebuild the Table:
    Fabric sets data clustering only when a table is created. A @clusterKey change — or any column change on a clustered table — rebuilds the table with a full copy of its data (Rebuild Table With Cluster Key). The data is kept, but the copy takes time and capacity on a large table.

  • Column Changes Rebuild the Table:
    Fabric has no ALTER COLUMN or RENAME COLUMN. Every alter runs on a zero-copy clone (CREATE TABLE … AS CLONE OF); a dropped, retyped or renamed column is applied by rebuilding the clone with CREATE TABLE AS SELECT, and only an added column uses ALTER TABLE … ADD. The clone is then swapped in for the live table.

  • NOT NULL on an Existing Table:
    Fabric cannot change a column's nullability or add a NOT NULL column to an existing table. A redeploy that adds or removes @notNull, or adds a new @notNull column, raises the non-failing Warning: Column Change Not Applied; the column keeps its current nullability until the table is recreated (e.g. change the node to a view and back).

  • Descriptions and Defaults Not Supported:
    Fabric Warehouse has no table / column descriptions and no column defaults. @description and @defaultValue are reserved annotations, so they stay, but they are ignored and the deployment shows the non-failing Warning: Not Supported in Fabric. Compute a fallback value in the SELECT (e.g. COALESCE([COL], 'NA')) instead of a default.

  • Surrogate Keys Are Generated by the Load:
    The table has no IDENTITY column. New @isSurrogateKey values are ROW_NUMBER() + the current highest key, computed in the merge. Don't run two loads into the same Dimension at the same time — they can generate the same key.

  • Changing @nodeType on a Deployed Node:
    The switch deploys as a metadata update — the existing table is kept, not rebuilt for the new type. Drop and redeploy the table when changing a node's type.

  • truncateInsert with SCD Type 2:
    @writeMode("truncateInsert") empties the table before every load, so an SCD Type 2 dimension loses all its history on each run — every row is reloaded as version 1. 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, and under SCD Type 1 the row is kept unchanged. Deletes are never detected; 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 adds the columns with NULL in every existing row. Backfill them once (e.g. with @postSQL) or reload with @writeMode("truncateInsert") if they need values.

  • Duplicate or NULL Business Keys:
    Every strategy except allColumnMatch merges on the business key, so the SELECT/CTE must return exactly one row per key, with no NULL key. Otherwise a duplicated key fails the merge with "The MERGE statement attempted to UPDATE or DELETE the same row more than once", and a NULL key never matches, so that row is re-inserted on every run. De-duplicate upstream — T-SQL has no QUALIFY, so rank the rows in a CTE and keep the first:

    WITH [RANKED] AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY [CUSTOMER_ID] ORDER BY [UPDATED_AT] DESC) AS [RN]
    FROM {{ ref('SRC', 'CUSTOMER') }}
    )
    SELECT ... FROM [RANKED] [R] WHERE [R].[RN] = 1

    To check it before each load, add a Before node-level test — see Duplicate or NULL Business Key Check.


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, and @clusterKey works the same on every node type.

Work Examples

Data Quality Test Examples

Dimension Examples

Fact Examples

Fabric Table Options

Work Examples​

Sample Node with Annotations​
@writeMode("append")
@tests("SELECT 1 FROM {TARGET} GROUP BY [N_NATIONKEY] HAVING COUNT(*) > 1") -- runs After the load (default), continueOnFailure=true (default)
@tests("SELECT 1 FROM {TARGET} WHERE [N_REGIONKEY] IS NULL", true, "Before") -- runs Before the load, continueOnFailure=true
@preSQL("DELETE FROM {TARGET} WHERE [N_LOAD_DATE] < DATEADD(DAY, -90, CAST(SYSDATETIME() AS DATE))")
@postSQL("INSERT INTO {TARGET_SCHEMA}.[LOAD_LOG] ([TABLE_NAME], [LOAD_TS]) VALUES ('WRK_NATION', SYSDATETIME())")
SELECT
[N_NATIONKEY] AS [N_NATIONKEY] @not_null @uniqueness @min_value(0) @max_value(100) @accepted_values("1, 2, 3") @inHash("GH_COL1", 2) @clusterKey(1),
[N_NAME] AS [N_NAME] @not_null @empty @accepted_values("'ALGERIA', 'ARGENTINA'") @inHash("GH_COL1", 1),
COALESCE([N_REGIONKEY], 20) AS [N_REGIONKEY] @min_max(0, 4) @notNull,
[N_COMMENT] AS [N_COMMENT] @rejected_values("'NA'"),
[LAST_MODIFIED] AS [L_M_1] @freshness(7, "DAY") @relative_time("<", "L_M_2"),
[LAST_MODIFIED] AS [L_M_2],
CAST([LAST_MODIFIED] AS DATE) AS [N_LOAD_DATE] @clusterKey(2),
CAST({{ get_hash('GH_COL1') }} AS VARCHAR(128)) AS [GH_COL1]
FROM {{ ref('SOURCE_DATA', 'NATION') }} [NATION]
Sample Node with DISTINCT​
@writeMode("append")
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],
CAST(UPPER([O_ORDERSTATUS]) AS VARCHAR(1)) AS [O_ORDERSTATUS],
CAST(COALESCE([O_TOTALPRICE], 0) AS DECIMAL(15,2)) 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
[ORDER_ID] % 25 AS [NATION_KEY],
COUNT(*) AS [ORDER_COUNT]
FROM {{ ref('SRC', 'ORDERS_TEST') }}
GROUP BY [ORDER_ID] % 25
)
SELECT
[OC].[NATION_KEY] AS [NATION_KEY],
[OC].[ORDER_COUNT] AS [ORDER_COUNT]
FROM [ORDER_COUNTS] [OC]
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 [O_ORDERPRIORITY]
)
SELECT
[PC].[O_ORDERPRIORITY] AS [O_ORDERPRIORITY],
[PC].[ORDER_COUNT] AS [ORDER_COUNT]
FROM [PRIORITY_COUNTS] [PC]
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] @clusterKey,
[F].[FIRST_ORDER_VALUE],
[F].[O_ORDERSTATUS] @notNull,
CAST(SYSDATETIME() AS DATETIME2(6)) AS [REFRESHED_AT],
CAST('Initial Customer Purchase' AS VARCHAR(50)) AS [RECORD_TYPE]
FROM [FIRST_ORDERS] [F]
Date Series without Recursion​

Fabric has no recursive CTEs, so a date spine is built from a numbers CTE: ten digits cross-joined four times give the numbers 0–9999, and each number is added as days to the start date.

WITH [DIGITS] AS (
SELECT [N] FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS [V]([N])
),
[NUMBERS] AS (
SELECT [A].[N] + 10 * [B].[N] + 100 * [C].[N] + 1000 * [D].[N] AS [N]
FROM [DIGITS] [A]
CROSS JOIN [DIGITS] [B]
CROSS JOIN [DIGITS] [C]
CROSS JOIN [DIGITS] [D]
),
[DATE_BOUNDS] AS (
SELECT
MIN([O_ORDERDATE]) AS [MIN_DATE],
MAX([O_ORDERDATE]) AS [MAX_DATE]
FROM {{ ref('SRC', 'ORDERS') }}
),
[DATE_SPINE] AS (
SELECT DATEADD(DAY, [NM].[N], [DB].[MIN_DATE]) AS [ORDER_DATE]
FROM [NUMBERS] [NM]
CROSS JOIN [DATE_BOUNDS] [DB]
WHERE [NM].[N] <= DATEDIFF(DAY, [DB].[MIN_DATE], [DB].[MAX_DATE])
),
[DAILY_ORDERS] AS (
SELECT
[O_ORDERDATE],
COUNT(*) AS [ORDER_COUNT],
SUM([O_TOTALPRICE]) AS [ORDER_TOTAL_VALUE]
FROM {{ ref('SRC', 'ORDERS') }}
GROUP BY [O_ORDERDATE]
)
SELECT
[ds].[ORDER_DATE] AS [ORDER_DATE] @notNull @clusterKey,
[n].[N_NATIONKEY] AS [N_NATIONKEY] @notNull,
[n].[N_NAME] AS [N_NAME],
COALESCE([do].[ORDER_COUNT], 0) AS [ORDER_COUNT],
CAST(COALESCE([do].[ORDER_TOTAL_VALUE], 0) AS FLOAT) AS [ORDER_TOTAL_VALUE]
FROM [DATE_SPINE] [ds]
CROSS JOIN {{ ref('SRC', 'NATION') }} [n]
LEFT JOIN [DAILY_ORDERS] [do] ON [do].[O_ORDERDATE] = [ds].[ORDER_DATE]
  • Four digit columns cover up to 10,000 days (about 27 years); add a fifth CROSS JOIN for longer ranges.
  • For a fixed range, replace [DATE_BOUNDS] with literals, e.g. DATEADD(DAY, [N], CAST('2025-01-01' AS DATE)) with WHERE [N] <= DATEDIFF(DAY, '2025-01-01', '2025-12-31').
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
[AN].[N_NATIONKEY] AS [N_NATIONKEY],
[AN].[N_NAME] AS [N_NAME],
[AN].[N_REGIONKEY] AS [N_REGIONKEY]
FROM [ALL_NATIONS] [AN]
  • In T-SQL a bare UNION removes duplicates; use UNION ALL to keep them.
  • SELECT * works inside the CTE; the outer SELECT lists its columns.
Pivot without PIVOT​

PIVOT as a keyword is not supported by the Coalesce SQL parser for Fabric. Until it is, turn rows into columns with GROUP BY and conditional aggregation: one MAX(CASE WHEN …) (or SUM / COUNT) per output column, grouped by the row key. This returns one row per ID with one column per survey question:

SELECT
CAST([ID] AS INT) AS [ID],
CAST(MAX(CASE WHEN [ATTR] = 'RNPS - Driver Appreciation experience'
THEN ISNULL([ANSWER_LABEL], [ANSWER]) END) AS VARCHAR(500)) AS [RNPS - Driver Appreciation experience],
CAST(MAX(CASE WHEN [ATTR] = 'Vehicle Condition'
THEN ISNULL([ANSWER_LABEL], [ANSWER]) END) AS VARCHAR(500)) AS [Vehicle Condition]
FROM {{ ref('SRC', 'PIVOT_REPRO') }} [PIVOT_REPRO]
GROUP BY [ID]

Counting instead of picking a value — the number of each DML_FLAG per city:

WITH [PIVOTED] AS (
SELECT
[EMP_CITY],
SUM(CASE WHEN [DML_FLAG] = 'I' THEN 1 ELSE 0 END) AS [INSERT_COUNT],
SUM(CASE WHEN [DML_FLAG] = 'U' THEN 1 ELSE 0 END) AS [UPDATE_COUNT],
SUM(CASE WHEN [DML_FLAG] = 'D' THEN 1 ELSE 0 END) AS [DELETE_COUNT]
FROM {{ ref('SRC', 'EMP_SOURCE') }}
GROUP BY [EMP_CITY]
)
SELECT
[PV].[EMP_CITY] AS [EMP_CITY] @uniqueness,
CAST([PV].[INSERT_COUNT] AS INT) AS [INSERT_COUNT] @min_value("0"),
CAST([PV].[UPDATE_COUNT] AS INT) AS [UPDATE_COUNT] @min_value("0"),
CAST([PV].[DELETE_COUNT] AS INT) AS [DELETE_COUNT] @min_value("0")
FROM [PIVOTED] [PV]
  • Add one CASE column per pivoted value; the values are compared case-sensitively ('I' ≠ 'i').
  • MAX keeps one value per key and column (like PIVOT (MAX(...))); use SUM / COUNT to aggregate.
Unpivot without UNPIVOT​

UNPIVOT as a keyword is not supported by the Coalesce SQL parser for Fabric. Until it is, turn columns into rows with a UNION ALL of one SELECT per column, inside a CTE:

WITH [UNPIVOTED] AS (
SELECT [EMP_ID], 'EMP_NAME' AS [ATTRIBUTE], CAST([EMP_NAME] AS VARCHAR(100)) AS [ATTR_VALUE]
FROM {{ ref('SRC', 'EMP_SOURCE') }}
WHERE [EMP_NAME] IS NOT NULL
UNION ALL
SELECT [EMP_ID], 'EMP_CITY', CAST([EMP_CITY] AS VARCHAR(100))
FROM {{ ref('SRC', 'EMP_SOURCE') }}
WHERE [EMP_CITY] IS NOT NULL
)
SELECT
[UP].[EMP_ID] AS [EMP_ID] @not_null,
CAST([UP].[ATTRIBUTE] AS VARCHAR(20)) AS [ATTRIBUTE] @accepted_values("'EMP_NAME', 'EMP_CITY'"),
[UP].[ATTR_VALUE] AS [ATTR_VALUE]
FROM [UNPIVOTED] [UP]
  • Cast every value to the same type, since they land in one column.
  • The IS NOT NULL filters match UNPIVOT, which drops NULL values; remove them to keep NULLs.

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(1),
[SIGNUP_DATE] AS [SIGNUP_DATE] @min_max("'2000-01-01'", "CAST(SYSDATETIME() AS DATE)"),
[CREATED_AT] AS [CREATED_AT] @max_value("SYSDATETIME()"),
[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') }} [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. Comparisons are case-sensitive.
  • BIT — 1/0. Date/time — a literal or expression, e.g. "'2000-01-01'", "SYSDATETIME()", "'08:00:00'".
  • @relative_time compares two date/time columns (CREATED_AT and UPDATED_AT are both DATETIME2); the other column's name must match its case exactly.
  • 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 through {{ ref() }}; After tests read the loaded target through {TARGET} ({TARGET_SCHEMA} is its database and schema).

@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 {TARGET} WHERE [BALANCE] < 0 AND [STATUS] = 'ACTIVE'") -- runs After the load (default), continueOnFailure=true (default)
@tests("SELECT 1 FROM {TARGET} 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') }} [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.
  • T-SQL accepts HAVING without GROUP BY — the whole table is one group.
  • 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. The table is clustered on the business key.

SELECT
[CUSTOMER_ID] AS [CUSTOMER_ID] @id("a10001") @isBusinessKey @clusterKey(1),
[NAME] AS [NAME] @id("a10002"),
[CITY] AS [CITY] @id("a10003"),
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_CREATE_DATE] @id("a10008") @isSystemCreateDate,
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_UPDATE_DATE] @id("a10009") @isSystemUpdateDate
FROM {{ ref('SRC', 'CUSTOMER') }} [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. The load generates the surrogate key — a new key for every version. The table is clustered on the business key.

@mergeStrategy("changeTracking")
SELECT
CAST(0 AS BIGINT) AS [DIM_CUSTOMER_KEY] @id("b10000") @isSurrogateKey,
[CUSTOMER_ID] AS [CUSTOMER_ID] @id("b10001") @isBusinessKey @clusterKey(1),
[NAME] AS [NAME] @id("b10002"),
[CITY] AS [CITY] @id("b10003") @isChangeTracking,
1 AS [SYSTEM_VERSION] @id("b10005") @isSystemVersion,
CAST('Y' AS VARCHAR(1)) AS [SYSTEM_CURRENT_FLAG] @id("b10006") @isSystemCurrentFlag,
CAST('2999-12-31 00:00:00' AS DATETIME2(6)) AS [SYSTEM_END_DATE] @id("b10007") @isSystemEndDate,
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_CREATE_DATE] @id("b10008") @isSystemCreateDate,
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_UPDATE_DATE] @id("b10009") @isSystemUpdateDate
FROM {{ ref('SRC', 'CUSTOMER') }} [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(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_CREATE_DATE] @id("c10008") @isSystemCreateDate,
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_UPDATE_DATE] @id("c10009") @isSystemUpdateDate
FROM {{ ref('SRC', 'CUSTOMER') }} [CUSTOMER]
Last Modified SCD2​

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

@mergeStrategy("lastModified")
SELECT
CAST(0 AS BIGINT) AS [DIM_CUSTOMER_KEY] @id("d10000") @isSurrogateKey,
[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,
CAST('Y' AS VARCHAR(1)) AS [SYSTEM_CURRENT_FLAG] @id("d10006") @isSystemCurrentFlag,
CAST('2999-12-31 00:00:00' AS DATETIME2(6)) AS [SYSTEM_END_DATE] @id("d10007") @isSystemEndDate,
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_CREATE_DATE] @id("d10008") @isSystemCreateDate,
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_UPDATE_DATE] @id("d10009") @isSystemUpdateDate
FROM {{ ref('SRC', 'CUSTOMER') }} [CUSTOMER]
  • UPDATED_AT must not be NULL in the source — the Check NULL values stage stops the run otherwise.
  • A change in NAME alone, without a newer UPDATED_AT, is not applied.
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') }} [CUSTOMER]
Zero Key Record​

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

@zeroKey("-1")
SELECT
CAST(0 AS BIGINT) AS [DIM_CUSTOMER_KEY] @id("f10000") @isSurrogateKey,
[CUSTOMER_ID] AS [CUSTOMER_ID] @id("f10001") @isBusinessKey,
[NAME] AS [NAME] @id("f10002") @zeroKey("'NA'"),
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_CREATE_DATE] @id("f10008") @isSystemCreateDate,
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_UPDATE_DATE] @id("f10009") @isSystemUpdateDate
FROM {{ ref('SRC', 'CUSTOMER') }} [CUSTOMER]
  • The surrogate key is a BIGINT, so the zero value is a number — quoted because it is negative ("-1"). Real rows are numbered from the current highest key + 1, so they start at 1.
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') }} [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
CAST(0 AS BIGINT) 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(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_CREATE_DATE] @id("e5f6a7") @isSystemCreateDate,
CAST(SYSDATETIME() AS DATETIME2(6)) 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.
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 [SRC].[CITY] IS DISTINCT FROM [EX].[CITY] AND [EX].[CUSTOMER_ID] IS NOT NULL
THEN [EX].[CITY]
ELSE [EX].[CITY_PREV] END AS [CITY_PREV] @id("0b0004"),
CASE WHEN [SRC].[CITY] IS DISTINCT FROM [EX].[CITY] AND [EX].[CUSTOMER_ID] IS NOT NULL
THEN CAST(SYSDATETIME() AS DATETIME2(6))
ELSE [EX].[CITY_CHANGED_DATE] END AS [CITY_CHANGED_DATE] @id("0b0005"),
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_CREATE_DATE] @id("0b0008") @isSystemCreateDate,
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_UPDATE_DATE] @id("0b0009") @isSystemUpdateDate
FROM {{ ref('SRC', 'CUSTOMER') }} [SRC]
LEFT JOIN [EXISTING] [EX] ON [SRC].[CUSTOMER_ID] = [EX].[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.
Date Dimension Generated in SQL​

A standalone calendar dimension with no upstream node: one row per day from 1992-01-01 to 2030-12-31 (14,245 rows), generated from a numbers CTE (recursive CTEs aren't supported). It is a changeTracking SCD Type 1 dimension keyed on DATE_KEY (YYYYMMDD), clustered on that key, with day and month names that don't depend on the session language or SET DATEFIRST.

@id("95eead91-6c8a-478e-be15-2f7b63e82937")
@nodeType("SQLDimension")
@materializationType("table")
@mergeStrategy("changeTracking")
@writeMode("append")
@disableTests(false)
@tests("SELECT COUNT(*) AS [DATE_ROWS] FROM {TARGET} HAVING COUNT(*) <> 14245", true, "After")
@tests("SELECT [DATE_KEY] FROM {TARGET} GROUP BY [DATE_KEY] HAVING COUNT(*) > 1", true, "After")
@tests("SELECT MIN([CALENDAR_DATE]) AS [MIN_DATE], MAX([CALENDAR_DATE]) AS [MAX_DATE] FROM {TARGET} HAVING MIN([CALENDAR_DATE]) <> CAST('1992-01-01' AS DATE) OR MAX([CALENDAR_DATE]) <> CAST('2030-12-31' AS DATE)", true, "After")
WITH [DIGITS] AS (
SELECT 0 AS [N] UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
[DAY_OFFSETS] AS (
SELECT [D1].[N] + [D2].[N] * 10 + [D3].[N] * 100 + [D4].[N] * 1000 + [D5].[N] * 10000 AS [DAY_OFFSET]
FROM [DIGITS] [D1]
CROSS JOIN [DIGITS] [D2]
CROSS JOIN [DIGITS] [D3]
CROSS JOIN [DIGITS] [D4]
CROSS JOIN [DIGITS] [D5]
),
[CALENDAR] AS (
SELECT
CAST(DATEADD(DAY, [DAY_OFFSET], CAST('1992-01-01' AS DATE)) AS DATE) AS [CAL_DATE]
FROM [DAY_OFFSETS]
WHERE [DAY_OFFSET] <= DATEDIFF(DAY, CAST('1992-01-01' AS DATE), CAST('2030-12-31' AS DATE))
),
[CALENDAR_PARTS] AS (
SELECT
[CAL_DATE],
YEAR([CAL_DATE]) AS [Y],
MONTH([CAL_DATE]) AS [M],
DATEPART(QUARTER, [CAL_DATE]) AS [Q],
/* 1900-01-01 was a Monday, so this gives ISO day of week (1 = Monday ... 7 = Sunday) regardless of SET DATEFIRST */
(DATEDIFF(DAY, CAST('1900-01-01' AS DATE), [CAL_DATE]) % 7) + 1 AS [ISO_DOW]
FROM [CALENDAR]
)
SELECT
CAST([Y] * 10000 + [M] * 100 + DAY([CAL_DATE]) AS INT) AS [DATE_KEY] @id("19a9af") @isBusinessKey @clusterKey @not_null @uniqueness @min_max("19920101", "20301231"),
[CAL_DATE] AS [CALENDAR_DATE] @id("b033ab") @not_null @uniqueness @min_max("'1992-01-01'", "'2030-12-31'"),
CAST([ISO_DOW] AS INT) AS [DAY_OF_WEEK_NUMBER] @id("da77d0") @not_null @min_max("1", "7"),
CAST(CHOOSE([ISO_DOW], 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') AS VARCHAR(9)) AS [DAY_NAME] @id("ac03c7") @not_null @accepted_values("'Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday'"),
CAST(CHOOSE([ISO_DOW], 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun') AS CHAR(3)) AS [DAY_SHORT_NAME] @id("d9a38b") @not_null @accepted_values("'Mon','Tue','Wed','Thu','Fri','Sat','Sun'"),
CAST(DAY([CAL_DATE]) AS INT) AS [DAY_OF_MONTH] @id("804546") @not_null @min_max("1", "31"),
CAST(DATEPART(DAYOFYEAR, [CAL_DATE]) AS SMALLINT) AS [DAY_OF_YEAR] @id("66c521") @not_null @min_max("1", "366"),
CAST(DATEPART(ISO_WEEK, [CAL_DATE]) AS INT) AS [ISO_WEEK_OF_YEAR] @id("e9828f") @not_null @min_max("1", "53"),
CAST(DATEADD(DAY, 1 - [ISO_DOW], [CAL_DATE]) AS DATE) AS [WEEK_START_DATE] @id("a1569f") @not_null @relative_time("<=", "CALENDAR_DATE"),
CAST(DATEADD(DAY, 7 - [ISO_DOW], [CAL_DATE]) AS DATE) AS [WEEK_END_DATE] @id("2f4627") @not_null @relative_time(">=", "CALENDAR_DATE"),
CAST([M] AS INT) AS [MONTH_NUMBER] @id("97fbaf") @not_null @min_max("1", "12"),
CAST(CHOOSE([M], 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December') AS VARCHAR(9)) AS [MONTH_NAME] @id("c03578") @not_null @empty,
CAST(CHOOSE([M], 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') AS CHAR(3)) AS [MONTH_SHORT_NAME] @id("218089") @not_null @accepted_values("'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'"),
CAST([Y] * 100 + [M] AS INT) AS [YEAR_MONTH] @id("d20e4f") @not_null @min_max("199201", "203012"),
CAST(DATEFROMPARTS([Y], [M], 1) AS DATE) AS [MONTH_START_DATE] @id("25e8a5") @not_null @relative_time("<=", "CALENDAR_DATE"),
CAST(EOMONTH([CAL_DATE]) AS DATE) AS [MONTH_END_DATE] @id("e805a8") @not_null @relative_time(">=", "CALENDAR_DATE"),
CAST([Q] AS INT) AS [QUARTER_NUMBER] @id("81e15e") @not_null @min_max("1", "4"),
CAST('Q' + CAST([Q] AS VARCHAR(1)) AS CHAR(2)) AS [QUARTER_NAME] @id("90240d") @not_null @accepted_values("'Q1','Q2','Q3','Q4'"),
CAST(CAST([Y] AS VARCHAR(4)) + '-Q' + CAST([Q] AS VARCHAR(1)) AS CHAR(7)) AS [YEAR_QUARTER] @id("b5b927") @not_null @empty,
CAST(DATEFROMPARTS([Y], ([Q] - 1) * 3 + 1, 1) AS DATE) AS [QUARTER_START_DATE] @id("541292") @not_null @relative_time("<=", "CALENDAR_DATE"),
CAST(EOMONTH(DATEFROMPARTS([Y], [Q] * 3, 1)) AS DATE) AS [QUARTER_END_DATE] @id("0a95ac") @not_null @relative_time(">=", "CALENDAR_DATE"),
CAST([Y] AS SMALLINT) AS [YEAR_NUMBER] @id("4ac98c") @not_null @min_max("1992", "2030"),
CAST(DATEFROMPARTS([Y], 1, 1) AS DATE) AS [YEAR_START_DATE] @id("82ca0e") @not_null @relative_time("<=", "CALENDAR_DATE"),
CAST(DATEFROMPARTS([Y], 12, 31) AS DATE) AS [YEAR_END_DATE] @id("d5f08c") @not_null @relative_time(">=", "CALENDAR_DATE"),
CAST(CASE WHEN [ISO_DOW] IN (6, 7) THEN 1 ELSE 0 END AS BIT) AS [IS_WEEKEND] @id("2ac6b7") @not_null @accepted_values("0,1"),
CAST(CASE WHEN [CAL_DATE] = EOMONTH([CAL_DATE]) THEN 1 ELSE 0 END AS BIT) AS [IS_MONTH_END] @id("8017e2") @not_null @accepted_values("0,1"),
CAST(CASE WHEN ([Y] % 4 = 0 AND [Y] % 100 <> 0) OR [Y] % 400 = 0 THEN 1 ELSE 0 END AS BIT) AS [IS_LEAP_YEAR] @id("795d06") @not_null @accepted_values("0,1"),
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_CREATE_DATE] @id("bebc32") @isSystemCreateDate @not_null,
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_UPDATE_DATE] @id("ebf903") @isSystemUpdateDate @not_null @relative_time(">=", "SYSTEM_CREATE_DATE")
FROM [CALENDAR_PARTS]
  • [DIGITS] cross-joined five times gives the offsets 0–99,999; [CALENDAR] keeps only the days up to the end date.
  • (DATEDIFF(DAY, '1900-01-01', date) % 7) + 1 is the ISO day of week (1 = Monday), because 1900-01-01 was a Monday — unlike DATEPART(WEEKDAY, …), it doesn't change with SET DATEFIRST. CHOOSE turns the numbers into names without depending on the session language.
  • Every column is cast, so Coalesce resolves each datatype; the node-level After tests check the row count, unique keys and the date range.
  • Re-running it changes nothing (every key already exists with the same values); extend the range by changing both end dates and the row-count test.

Fact Examples​

Fact Plain Insert​

No @mergeStrategy and no @isBusinessKey column, so every source row is inserted with no matching. The table is clustered on order date and order id.

SELECT
[ORDER_ID] AS [ORDER_ID] @id("1a0001") @clusterKey(2),
[CUSTOMER_ID] AS [CUSTOMER_ID] @id("1a0002"),
[AMOUNT] AS [AMOUNT] @id("1a0003"),
[ORDER_DATE] AS [ORDER_DATE] @id("1a0004") @clusterKey(1),
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_CREATE_DATE] @id("1a0008") @isSystemCreateDate
FROM {{ ref('SRC', 'ORDERS') }} [ORDERS]
Fact Change Tracking SCD1​

The default strategy with a business key. 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"),
CAST([AMOUNT] AS DECIMAL(15,2)) AS [AMOUNT] @id("1b0003"),
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_CREATE_DATE] @id("1b0008") @isSystemCreateDate,
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_UPDATE_DATE] @id("1b0009") @isSystemUpdateDate
FROM {{ ref('SRC', 'ORDERS') }} [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(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_CREATE_DATE] @id("1c0008") @isSystemCreateDate,
CAST(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_UPDATE_DATE] @id("1c0009") @isSystemUpdateDate
FROM {{ ref('SRC', 'ORDERS') }} [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') }} [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(SYSDATETIME() AS DATETIME2(6)) AS [SYSTEM_CREATE_DATE] @id("1e0008") @isSystemCreateDate
FROM {{ ref('SRC', 'ORDER_LINES') }} [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.

Fabric Table Options​

Cluster Key and NOT NULL​

The Fabric table options in one node — they work the same on Work, Dimension and Fact tables (on a view they are ignored).

@materializationType("table")
SELECT
[n_NationKey] AS [N_NATIONKEY] @notNull @clusterKey(1),
[n_Name] AS [N_NAME] @notNull @clusterKey(2),
CAST(COALESCE([n_RegionKey], -1) AS INT) AS [N_REGIONKEY] @notNull @clusterKey(3),
COALESCE([n_Comment], 'NA') AS [N_COMMENT],
CAST([n_NationKey] / 7.0 AS DECIMAL(10,4)) AS [N_NATIONKEY_RATIO],
CAST([last_Modified] AS DATE) AS [LOAD_DATE] @notNull
FROM {{ ref('SRC', 'Nation_Test_CamelCase') }} [Nation_Test_CamelCase]

On the first deployment this creates ([<DATABASE>].[<SCHEMA>] is the node's own location):

DROP TABLE IF EXISTS [<DATABASE>].[<SCHEMA>].[WRK_NATION]

CREATE TABLE [<DATABASE>].[<SCHEMA>].[WRK_NATION]
(
[N_NATIONKEY] INT NOT NULL,
[N_NAME] VARCHAR(25) NOT NULL,
[N_REGIONKEY] INT NOT NULL,
[N_COMMENT] VARCHAR(152),
[N_NATIONKEY_RATIO] DECIMAL(10,4),
[LOAD_DATE] DATE NOT NULL
) WITH (CLUSTER BY ([N_NATIONKEY], [N_NAME], [N_REGIONKEY]))
  • Source names keep their exact case ([n_NationKey]) — Fabric names are case-sensitive.
  • A NOT NULL column whose source can hold NULLs fails the load ("Cannot insert the value NULL into column …") — COALESCE it, as N_REGIONKEY does here.
  • There is no column default — N_COMMENT gets its fallback value from COALESCE in the SELECT.
  • On a later deployment, a cluster key change rebuilds the table with its data (Rebuild Table With Cluster Key); a NOT NULL change is not applied to the existing table (Warning: Column Change Not Applied) — see Altering the Work Tables.

Code​

Work​

Dimension​

Fact​

Macro​

Versions​

Available versions of the package.

Version #Release DateNotes
1.0.0November 17, 2026Fabric Base Node Types - SQL - initial release