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
- Copy the Package ID:
@coalesce/fabric/fabric-base-node-types-sql - In Coalesce, open the Workspace where you wish to install the package.
- Go to the Build Setting of the Workspace, tab Packages, and click the Install button on the top right of the page.
- 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
| Annotation | Work | Dimension | Fact |
|---|---|---|---|
@materializationType | table / view | table / view | table / 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
| Annotation | Work | Dimension | Fact |
|---|---|---|---|
@id (recommended) | ✅ | ✅ | ✅ |
@isBusinessKey | ➖ | ✅ (required) | ✅ (conditional) ³ |
@isChangeTracking | ➖ | ✅ | ➖ |
@lastModifiedTracking | ➖ | ✅(scdType?) | ✅ |
@zeroKey (column-level) | ➖ | ✅ | ➖ |
@isSurrogateKey | ➖ | ✅ | ➖ |
@isSystemVersion | ➖ | ✅ | ➖ |
@isSystemCurrentFlag | ➖ | ✅ | ➖ |
@isSystemCreateDate | ➖ | ✅ | ✅ |
@isSystemUpdateDate | ➖ | ✅ | ✅ |
@isSystemEndDate | ➖ | ✅ | ➖ |
@clusterKey | ✅ | ✅ | ✅ |
@inHash | ✅ | ✅ | ✅ |
| all column tests | ✅ | ✅ | ✅ |
- ³ Optional when
@mergeStrategyisn't set — without it the Fact is loaded as a plain insert. Required for an explicitchangeTracking,lastModifiedorupsert; ignored underallColumnMatch. @idis 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
| Property | Description |
|---|---|
| Storage Location | Storage Location (Fabric Warehouse and schema) where the Work table or view will be created |
| Node Type | Read-only: the node type's name and ID — Work (SQLWork). Set from @nodeType when the node is created. |
Work Node Annotations
| Annotation | Summary |
|---|---|
@id(id) (reserved) | Unique identifier for the node. Static and auto-generated when the node is created — not meant to be edited. |
@nodeType(type) (reserved) | Identifies the node's type. Set automatically based on the node type chosen when the node is created. |
@description(text) (reserved) | 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
| Annotation | Summary |
|---|---|
@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:
| Stage | Description |
|---|---|
| Unsupported Materialization Type: <node> | @materializationType is not table or view (Fabric has no transient table) — stops the deployment and the run |
| Warning: Not Supported in Fabric | Non-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 Column | Table 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 Applied | Non-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:
| Stage | Description |
|---|---|
| Drop table | Drops any existing table — Fabric has no CREATE OR REPLACE TABLE |
| Create table | Executes 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:
| Stage | Description |
|---|---|
| Drop Stage View | Drops any existing view |
| Create Stage View | Executes a CREATE VIEW statement ([schema].[name] — Fabric's CREATE VIEW takes no database name) |
Work Load
| Stage | Description |
|---|---|
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 table | Executed unless @writeMode("append") is set — clears the target before the load. |
| Load table using Insert | Inserts 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 View | For 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
@clusterKeycolumns
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:
| Stage | Description |
|---|---|
| Clone Table | Creates an internal clone of the table (CREATE TABLE … AS CLONE OF) |
| Delete Column | Alter Column | Rename Column | Add Column | Applied 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 Table | Upon 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 Key | Replaces 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 Table | Drops the internal clone (and the old table, when renamed or moved) |
| Warning: Column Change Not Applied | Non-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@idkeeps its data. A Work column without an@idis 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:
| Stage | Description |
|---|---|
| Drop Stage View | Removes the existing view |
| Create Stage View | Creates 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:
| Stage | Description |
|---|---|
| Delete Table | Delete View | Drops 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
| Property | Description |
|---|---|
| Storage Location | Storage Location (Fabric Warehouse and schema) where the Dimension table or view will be created |
| Node Type | Read-only: the node type's name and ID — Dimension (SQLDimension). Set from @nodeType when the node is created. |
Dimension Node Annotations
| Annotation | Summary |
|---|---|
@id(id) (reserved) | Unique identifier for the node. Static and auto-generated when the node is created — not meant to be edited. |
@nodeType(type) (reserved) | Identifies the node's type. Set automatically based on the node type chosen when the node is created. |
@description(text) (reserved) | 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
| Annotation | Summary |
|---|---|
@id("<value>") | Stable column ID for lineage tracking. |
@notNull (reserved) | Marks column as NOT NULL. Note: Ignored on Views. Example: @notNull |
@description(<text>) (reserved) | 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
| Annotation | Summary |
|---|---|
@isBusinessKey (required) | Marks a column as part of the business key used to match existing rows during the merge. Note: Ignored on Views. Example: @isBusinessKey |
@isChangeTracking | Marks 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. |
@isSurrogateKey | Marks this column as the node's surrogate key. |
@isSystemVersion | Marks this column as the SCD version number, incremented each time a business key gets a new version. |
@isSystemCurrentFlag | Marks this column as the SCD "is current" flag — 'Y' on the active version of a row, overwritten to 'N' when it's expired. |
@isSystemCreateDate | Marks this column as the timestamp a row (or, under SCD Type 2, a specific row version) was first created. |
@isSystemUpdateDate | Marks this column as the timestamp a row was last updated. |
@isSystemEndDate | Marks this column as the SCD Type 2 expiration timestamp — a far-future sentinel while the row is current, set to the actual expiry time once superseded. |
Shared annotations link to the Annotation Reference; the Dimension-specific ones are described in Dimension Annotation Details.
🚦 The full set of column-level data quality tests (@not_null, @uniqueness, @empty, @accepted_values, @rejected_values, @min_max, @min_value, @max_value, @freshness, @relative_time) applies to Dimension columns exactly as described in Column-Level Data Quality Tests.
Dimension Annotation Details
@mergeStrategy (Dimension)
@mergeStrategy("upsert | changeTracking | lastModified")
Chooses how this dimension decides a row has changed, and therefore which column annotations it requires.
| Value | Description |
|---|---|
upsert | No change detection at all; a matched row is always updated, an unmatched row is inserted. Doesn't assert any SCD type of its own — if the upstream SELECT/CTE already implements SCD1/SCD2 logic, this strategy just merges that result through as-is. The SELECT/CTE should return one row per business key (de-duplicate upstream). System columns are optional; any that are present are written exactly as the SELECT/CTE computes them. @isChangeTracking / @lastModifiedTracking columns aren't used — if present, a warning is raised and they're ignored. |
changeTracking | Compares columns marked @isChangeTracking (or, if none are marked, every plain attribute column) to detect a change. SCD Type 2 if any column is marked @isChangeTracking, otherwise SCD Type 1. A @lastModifiedTracking column isn't used — a warning is raised and it's ignored. |
lastModified | Compares 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
Beforetest 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.
| Parameter | Description |
|---|---|
| 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
@isSurrogateKeycolumn (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.VARCHARwith no length holds 1 character. - Positive numbers and booleans can be unquoted (
@zeroKey(0),@zeroKey(false); a boolean is written toBITas1/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
@isChangeTrackingupdates the current row in place — no new version is created. - Note: Ignored on Views.
Example:
@isChangeTracking
@zeroKey (Dimension column)
@zeroKey(value)
Adds a custom zero key value (ghost record) to this column, overriding the node-level @zeroKey defaults for it.
| Parameter | Description |
|---|---|
| value | Zero key value for this column, pasted into the SQL verbatim. |
- The value is pasted into the SQL verbatim, so quote it to match the column's data type.
- A 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
@zeroKeyis 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
@zeroKeyis set, since the zero/ghost record is matched against the target by surrogate key value. - The table has no
IDENTITYcolumn (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, getsROW_NUMBER()+ the current highest key. Existing rows keep their key.- The column must be an integer type (
BIGINTrecommended), 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.
- The column must be an integer type (
- 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:
@mergeStrategy | Resolves to | Driven by |
|---|---|---|
upsert | Whatever SCD shape the upstream SELECT/CTE already implements — Coalesce doesn't detect or enforce SCD1/SCD2 here, it just merges the result through as-is. Out of the box (plain SELECT, one row per business key) this behaves as an SCD1 overwrite; SCD2 needs the CTE to do the versioning — see note ² | User's own SQL logic, not any column annotation |
changeTracking (default) | SCD2 if any column is marked, otherwise SCD1 | @isChangeTracking |
lastModified | SCD1 or SCD2, from the column's scdType | @lastModifiedTracking(scdType) |
Requirement level of each column annotation, by resolved strategy/SCD type:
| Annotation | Category | SCD1 (changeTracking / lastModified(1)) | SCD2 (changeTracking / lastModified(2)) | upsert |
|---|---|---|---|---|
@isBusinessKey | Key | 🔴 | 🔴 | 🔴 |
@lastModifiedTracking | Tracking | 🔴 lastModified only | 🔴 lastModified only | — |
@isSurrogateKey | Key | ⚪ | 🟡 | ⚪ |
@isSystemCreateDate | System column | 🔴 | 🔴 | ⚪ |
@isSystemUpdateDate | System column | 🔴 | 🔴 | ⚪ |
@isSystemVersion | System column | ⚪ | 🔴 | ⚪ |
@isSystemCurrentFlag | System column | ⚪ | 🔴 | ⚪ |
@isSystemEndDate | System column | ⚪ | 🔴 | ⚪ |
Legend: 🔴 Required · 🟡 Recommended · ⚪ Optional¹
- ¹
upsertnever 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@isBusinessKeycolumn(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/@lastModifiedTrackingcolumns on anupsertnode are never read — a warning is raised and they're ignored.- Under
lastModifiedonly 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 (BIGINTrecommended); 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:
| Column | Value written |
|---|---|
@isSystemVersion | 1 on a new key; previous version + 1 on a new SCD2 version |
@isSystemCurrentFlag | 'Y' on the current row; 'N' on an expired SCD2 row |
@isSystemCreateDate | SYSDATETIME() on first insert and on each new SCD2 version, then kept |
@isSystemUpdateDate | SYSDATETIME() 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:
| Stage | Description |
|---|---|
| Unsupported Materialization Type: <node> | @materializationType is not table or view — stops the deployment and the run |
| Warning: Not Supported in Fabric | A 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 Long | Dimension validation checks (table only), rendered only when the annotations have a problem — see Dimension Load. A required problem stops the deployment; a recommendation 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 Applied | Redeploy 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:
| Stage | Description |
|---|---|
| Drop table / Create table | Table: 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 View | View: 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,@idand@mergeStrategyaren'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,@clusterKeyand 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
@defaultValuearen't supported in Fabric and are ignored, the same as for tables. - Column-level data quality tests and node-level
@testsstill 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:
| Stage | Description |
|---|---|
Pre Load Test <n> | Node-level @tests(..., "Before") tests, in the order they appear. |
| Validation checks | The checks listed under Dimension Deployment, rendered only when the node's annotations have a problem. |
Check NULL values for <column> column | Pre-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 table | Executed only when @writeMode("truncateInsert") is set — clears the target before the merge. |
| Load table Using Merge - Zero Key Record | Executed only when node-level @zeroKey is set and a surrogate key column exists — merges the placeholder "zero key" row into the target. |
| Load table Using Merge - Upsert | Executed for @mergeStrategy("upsert") — every matched row is updated from the SELECT/CTE (system columns included, @isSystemCreateDate excepted) and every unmatched row is inserted. |
| Load table Using Merge - Last Modified Comparison - SCD1 | SCD2 | Executed for @mergeStrategy("lastModified") — 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 | SCD2 | Executed 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
@idis applied by rebuilding the table with the new name, so its data is kept. A column without an@idis 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
| Property | Description |
|---|---|
| Storage Location | Storage Location (Fabric Warehouse and schema) where the Fact table or view will be created |
| Node Type | Read-only: the node type's name and ID — Fact (SQLFact). Set from @nodeType when the node is created. |
Fact Node Annotations
| Annotation | Summary |
|---|---|
@id(id) (reserved) | Unique identifier for the node. Static and auto-generated when the node is created — not meant to be edited. |
@nodeType(type) (reserved) | Identifies the node's type. Set automatically based on the node type chosen when the node is created. |
@description(text) (reserved) | Node-level description. 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
| Annotation | Summary |
|---|---|
@id("<value>") | Stable column ID for lineage tracking. |
@notNull (reserved) | Marks column as NOT NULL. Note: Ignored on Views. Example: @notNull |
@description(<text>) (reserved) | Adds column description. 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. |
@isBusinessKey | Marks a column as part of the business key used to match existing rows during the merge. |
@lastModifiedTracking | Marks the column used to detect newer source rows under @mergeStrategy("lastModified") (SCD Type 1). |
@isSystemCreateDate | Marks this column as the timestamp a row was first created. |
@isSystemUpdateDate | Marks this column as the timestamp a row was last updated. |
Shared annotations link to the Annotation Reference; the Fact-specific ones are described in Fact Annotation Details.
Note: 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.
| Value | Description |
|---|---|
changeTracking | SCD Type 1. Compares every plain attribute column against the target; a changed row is overwritten in place, a new key is inserted. Doesn't use @lastModifiedTracking — if present, a warning is raised and it's ignored. |
lastModified | SCD Type 1. Compares a single @lastModifiedTracking column against the target's stored value; a newer value overwrites the row in place. Requires exactly one column marked @lastModifiedTracking — none, or more than one, stops the load. |
upsert | No change detection at all; a matched row is always updated, an unmatched row is inserted. The SELECT/CTE should return one row per business key (de-duplicate upstream). System columns are optional; any that are present are written exactly as the SELECT/CTE computes them. @lastModifiedTracking isn't used — if present, a warning is raised and it's ignored. |
allColumnMatch | Treats every non-system column as the business key: a source row is inserted only if no target row matches it on all of those columns, and an existing matching row is never updated. @isBusinessKey isn't needed — if present, a warning is raised, it's ignored and all columns are used for the comparison. @lastModifiedTracking isn't used either — a warning is raised and it's ignored. System columns are optional, are left out of the comparison, and are written exactly as the SELECT/CTE computes them. |
- Not specified in the SQL editor → defaults to changeTracking - SCD Type 1 when a column is marked
@isBusinessKey, otherwise the node is loaded as a plain insert. - The value is not case-sensitive; any other value fails the validation check Invalid configuration: mergeStrategy and stops the deployment and the run.
- When set,
@mergeStrategyalways takes priority:changeTracking,lastModifiedandupsertmerge on the business key, so without an@isBusinessKeycolumn a validation check fails and the load stops.allColumnMatchneeds 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
@mergeStrategyisn't set — if no column is marked, there is no merge and every source row is simply inserted. - Required for an explicit
changeTracking,lastModifiedorupsert— without it the load stops with a validation error. - Ignored under
@mergeStrategy("allColumnMatch"), which uses every non-system column as the key (a warning is raised). - Note: Ignored on Views.
Example:
@isBusinessKey
Fact Load Behaviour
How a Fact table is loaded depends on @mergeStrategy and on whether any column is marked @isBusinessKey:
@isBusinessKey | @mergeStrategy | Load |
|---|---|---|
| none | not set | Plain insert (default without a business key) |
| none | changeTracking / lastModified / upsert | ❌ Validation error — the load stops |
| marked | not set, or changeTracking | SCD1 merge on the business key (default with a business key) |
| marked | lastModified | SCD1 merge on the business key, driven by the @lastModifiedTracking column |
| marked | upsert | Merge on the business key |
| any (ignored, warning if marked) | allColumnMatch | Merge on every non-system column — inserts only new rows |
Requirement level of each column annotation, by load:
| Annotation | changeTracking / lastModified | upsert | allColumnMatch | Plain insert |
|---|---|---|---|---|
@isBusinessKey | 🔴 | 🔴 | ⚪¹ | — |
@lastModifiedTracking | 🔴 for lastModified only | — | — | — |
@isSystemCreateDate | 🔴 | ⚪ | 🟡 | 🟡 |
@isSystemUpdateDate | 🔴 | ⚪ | ⚪ | ⚪ |
Legend: 🔴 Required · 🟡 Recommended · ⚪ Optional · — Not used
-
¹ Ignored if present — every non-system column is used as the business key and a warning is raised.
-
allColumnMatchis 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/lastModifiedthe load writes fixed values into the system columns:@isSystemCreateDategetsSYSDATETIME()on first insert and is then kept;@isSystemUpdateDategetsSYSDATETIME()whenever the row is inserted or changed. Both are written asDATETIME2(6). The expression the SELECT gives them is only used to derive the column's datatype. -
Under
upsert,allColumnMatchand plain insert, system columns are written exactly as the SELECT/CTE computes them.@isSystemCreateDatekeeps its first-inserted value on anupsertupdate. -
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:
| Stage | Description |
|---|---|
| Unsupported Materialization Type: <node> | @materializationType is not table or view — stops the deployment and the run |
| Warning: Not Supported in Fabric | Non-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 Columns | Fact 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 Column | Table 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 Applied | Non-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
| Stage | Description |
|---|---|
| Drop table / Create table | Table: 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 View | View: 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,@idand@mergeStrategyaren't required or checked. - There is no load; the run shows Load Skipped for View.
@mergeStrategy,@writeMode,@preSQL,@postSQL,@clusterKeyand@lastModifiedTrackingare ignored. @isSystemCreateDate/@isSystemUpdateDatecolumns are selected as a typedNULL, keeping the column's datatype.- Descriptions and
@defaultValuearen't supported in Fabric and are ignored, the same as for tables. - Column-level data quality tests and node-level
@testsstill run.
Fact Load
| Stage | Description |
|---|---|
Pre Load Test <n> | Node-level @tests(..., "Before") tests, in the order they appear. |
| Validation checks | The checks listed under Fact Deployment, rendered only when the node's annotations have a problem. |
Check NULL values for <column> column | Pre-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 table | Executed only when @writeMode("truncateInsert") is set — clears the target before the load. |
| Load table Using Insert | Executed when no @mergeStrategy is set and no column is marked @isBusinessKey — inserts every source row with INSERT INTO … SELECT. |
| Load table Using Merge - Change Tracking - SCD1 | Executed for @mergeStrategy("changeTracking"), and by default when no @mergeStrategy is set and a column is marked @isBusinessKey. |
| Load table Using Merge - Last Modified Comparison - SCD1 | Executed for @mergeStrategy("lastModified") — a row is overwritten only when its @lastModifiedTracking value is newer than the stored one. |
| Load table Using Merge - Upsert | Executed for @mergeStrategy("upsert"). |
| Load table Using Merge - All Column Match | Executed for @mergeStrategy("allColumnMatch") — matches on every non-system column and inserts only rows that don't already exist; nothing is updated. |
Post-SQL <n> | Each @postSQL statement, in the order they appear. |
<column>: <test> | Post Load Test <n> | Column-level data quality tests and node-level @tests(..., "After") tests, run after the load. |
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
@idis applied by rebuilding the table with the new name, so its data is kept. A column without an@idis 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)
| Value | Description |
|---|---|
table | Table (default). |
view | View. |
- Fabric has no transient table — any value other than
tableorviewstops 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:
| Value | Description |
|---|---|
truncateInsert | Clears the table (Truncate table stage) and then inserts the new rows (INSERT INTO … SELECT), replacing its contents entirely. |
append | Inserts the new rows via INSERT INTO, alongside whatever is already there. |
- Not specified in the SQL editor → defaults to truncateInsert.
Example:
@writeMode("append")
Dimension / Fact:
| Value | Description |
|---|---|
truncateInsert | Clears the table before loading, replacing its contents entirely. |
append | Dimension: Inserts the new rows via merge, alongside whatever is already there. Fact: Loads the new rows alongside whatever is already there. |
- Not specified in the SQL editor → defaults to append.
Example:
@writeMode("truncateInsert")
@disableTests
@disableTests²
Controls whether configured tests are skipped.
- Specified in the SQL editor → all node- and column-level tests are skipped.
- Not specified in the SQL editor → tests run normally.
- To turn tests back on, remove the annotation. Useful while developing a node — iterate on the SQL first, then re-enable once the logic is settled.
Example:
@disableTests
@tests
@tests(querySQL, continueOnFailure?, runOrder?)²
(repeatable) Node-level data quality test.
² Node level tests are performed only when disableTests is OFF.
@tests("<querySQL>", <continueOnFailure>, "<runOrder>")
| Parameter | Description |
|---|---|
| querySQL | SQL statement to execute as a validation test. The test fails if the query returns any records. 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.
| Parameter | Description |
|---|---|
| value | Six 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
DEFAULTis 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.
BITand(MAX)columns can't be clustered — the deploy stops with Invalid configuration: clusterKey Data Type. Only the first 32 characters of aCHAR/VARCHARfeed 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.
| Parameter | Description |
|---|---|
| hashName | Columns sharing the same value are grouped together into the same hash. |
| hashOrder | This column's position within that group. |
- Call
get_hash("<hashName>")elsewhere in the SELECT to produce the hash column from the marked columns.
Example:
<col_name> AS <col_name> @inHash("GH_COL", 1),
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>") }}
| Parameter | Description |
|---|---|
hash_name | Hash 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/REALwith full precision), a NULL becomes'null', they are joined inhashOrderwith the delimiter, hashed withHASHBYTES, and returned as a lowercase hex string (64 characters forSHA2_256, 128 forSHA2_512, 40 forSHA1, 32 forMD5). - 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. algois not case-sensitive ('md5'='MD5').delimiter=''joins the values with no delimiter.- When no column is marked with the hash name,
get_hash()returnsNULL. The hash name is matched exactly, soget_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:
| Parameter | Description |
|---|---|
| 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
allColumnMatchand 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.
| Property | Description |
|---|---|
@not_null | Fails on rows where the column is NULL. Example: @not_null |
@uniqueness | Fails when a value appears on more than one row. Example: @uniqueness |
@empty | Fails on rows where the column trims to the empty string. NULL values pass this test — they're caught by @not_null instead.Example: @empty |
@accepted_values("<value>", ...) | Fails on rows whose value is outside the allow list. NULL values pass. Repeatable — every occurrence on the column is merged into one list. Each call takes 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 quotesdate/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
UNKNOWNdatatype may cause stage generation or runtime failures. The create dry-run still reportspassedwithUNKNOWNin 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 ofNVARCHAR,DATETIME2(6)instead ofDATETIME,DECIMAL(p,s)instead ofMONEY). -
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
DEFAULTthat, while valid SQL keywords elsewhere, don't fit in aSELECTclause.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
WITHclauses 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 insideINSERT … SELECTorMERGE … USING, so the node's CTEs are placed before the generatedINSERT/MERGEstatement. -
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/UNPIVOTas keywords are not supported by the Coalesce SQL parser for Fabric. Until they are, write a pivot withGROUP BYand conditional aggregation (MAX(CASE WHEN …)/SUM(CASE WHEN …)), and an unpivot as aUNION ALLof 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 asCREATE,MERGE,DELETE,UPDATE, orTRUNCATEare not supported and will cause execution failures. -
Support for
UNION, andUNION ALL:
UNIONandUNION ALLare fully supported when used within Common Table Expressions (CTEs). While these keywords can also be used in standardSELECTstatements without generating an error, they may not be parsed correctly by the platform. As a result, subsequent clauses (such asJOINs) 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 allowGROUP 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:
PIVOTandUNPIVOTas 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, useGROUP BYwith conditional aggregation for a pivot andUNION ALLfor 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,@postSQLand@relative_time.'Mark' = 'mark'is false, so business key matching, change detection,@accepted_values/@rejected_valuesand 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@clusterKeychange — 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 noALTER COLUMNorRENAME 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 withCREATE TABLE AS SELECT, and only an added column usesALTER 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@notNullcolumn, 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.@descriptionand@defaultValueare 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 noIDENTITYcolumn. New@isSurrogateKeyvalues areROW_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
@nodeTypeon 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. -
truncateInsertwith 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 defaultappendfor 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/@isSystemUpdateDateto 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 exceptallColumnMatchmerges 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 noQUALIFY, 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] = 1To check it before each load, add a
Beforenode-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
- Sample Node with Annotations
- Sample Node with DISTINCT
- Basic Transformation and Cleaning
- Aggregate Functions inside a CTE
- Using CTEs
- Multi-CTE Transformation with Window Functions
- Date Series without Recursion
- CTE for Multi-Source Combine
- Pivot without PIVOT
- Unpivot without UNPIVOT
Data Quality Test Examples
Dimension Examples
- Change Tracking SCD1
- Change Tracking SCD2
- Last Modified SCD1
- Last Modified SCD2
- Upsert
- Zero Key Record
- Dimension as a View
- Duplicate or NULL Business Key Check
- Upsert with Custom SCD Logic
- Date Dimension Generated in SQL
Fact Examples
- Fact Plain Insert
- Fact Change Tracking SCD1
- Fact Last Modified SCD1
- Fact Upsert
- Fact All Column Match
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 JOINfor longer ranges. - For a fixed range, replace
[DATE_BOUNDS]with literals, e.g.DATEADD(DAY, [N], CAST('2025-01-01' AS DATE))withWHERE [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
UNIONremoves duplicates; useUNION ALLto 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
CASEcolumn per pivoted value; the values are compared case-sensitively ('I'≠'i'). MAXkeeps one value per key and column (likePIVOT (MAX(...))); useSUM/COUNTto 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 NULLfilters matchUNPIVOT, 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_valuescan 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_timecompares two date/time columns (CREATED_ATandUPDATED_ATare bothDATETIME2); 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 (
Afterby default). Line 4 — the target row count matches the source. - T-SQL accepts
HAVINGwithoutGROUP BY— the whole table is one group. continueOnFailureis the second argument.@disableTestson 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_ATmust not be NULL in the source — the Check NULL values stage stops the run otherwise.- A change in
NAMEalone, without a newerUPDATED_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
@isBusinessKeycolumn in theGROUP BYand theIS NULLchecks.
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, soCITY_PREVandCITY_CHANGED_DATEare NULL. - City changed — the old city moves to
CITY_PREVandCITY_CHANGED_DATEis stamped. - City unchanged — the stored
CITY_PREVandCITY_CHANGED_DATEare kept. @isSystemCreateDatekeeps 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) + 1is the ISO day of week (1 = Monday), because 1900-01-01 was a Monday — unlikeDATEPART(WEEKDAY, …), it doesn't change withSET DATEFIRST.CHOOSEturns the numbers into names without depending on the session language.- Every column is cast, so Coalesce resolves each datatype; the node-level
Aftertests 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_DATEis 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
DISTINCTto 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 …") —
COALESCEit, asN_REGIONKEYdoes here. - There is no column default —
N_COMMENTgets its fallback value fromCOALESCEin 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 Date | Notes |
|---|---|---|
| 1.0.0 | November 17, 2026 | Fabric Base Node Types - SQL - initial release |