Base Node Types - SQL Package

The SQL Node is a powerful transformation tool within Coalesce that allows developers to 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. While it provides maximum flexibility, it shifts the responsibility of column definition and logic maintenance to the SQL author.

The Base Node Types - SQL Package includes:

• Work
• Persistent Stage
• Dimension
• Fact
• Factless Fact
• View
• Code

The key differences between these nodes are outlined below.

• Node properties

• ¹ Enabled only if Last Modified Based Incremental Load is ON

• ² For timestamp-based incremental loads, a validation test checks the selected Last Modified column for NULL values before the merge. If NULL values are detected, the merge is stopped; otherwise, processing continues.

• ³ Changing the surrogate key value after deployment is not recommended.

• ⁴ Tests are performed only when Enable tests is ON

  @tests("<SQL Query>", "<Run Order>", <Continue On Failure>)
  

  Parameter	Description
  SQL Query	SQL statement to execute as a validation test. The test fails if the query returns any records.
  Run Order	Before or After. Determines whether the test is executed before or after the load operation.
  Continue On Failure	true or false. Determines whether execution continues when the test fails.

  Examples

  @tests("SELECT 1 FROM {{ this }} GROUP BY N_COMMENT HAVING COUNT(*) > 1", "Before", true)
  
  @tests("SELECT 1 FROM {{ this }} GROUP BY N_COMMENT HAVING COUNT(*) > 1", "After", true)
  

• ⁵ Applicable to Dimension nodes only.

  Option	Description
  Default Surrogate Key Value³	Default surrogate key value used for the zero key record.
  Default String Value	Default value used for string columns in the zero key record.
  Default Date/Time/Timestamp Value	Default value used for date, time, and timestamp columns in the zero key record.
  Default Boolean Value	Default value used for boolean columns in the zero key record.

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

• If a Dim and PStage node is renamed, ensure that the surrogate key column is also renamed to follow the {{NODE_NAME}}_KEY naming convention. Failure to do so may result in the surrogate key attribute being lost, which can impact the expected behavior of the flow.

• @nullable defaults to true. Use @nullable("false") to enforce NOT NULL.

• Column-level @zeroKey takes precedence over node-level configuration. If @zeroKey is not defined at the column level, the node-level @zeroKey configuration is applied based on the column data type else NULL is applied by default.

• Once the surrogate-zero key value is defined, it is not advisable to change it in future deployments or redeployments. Modifying the surrogate-zero key can lead to unintended behavior, such as new records being inserted instead of updating existing ones, causing data inconsistencies.

• The hash transformation can be defined either using the reusable macro or by writing the full hash expression explicitly. Both approaches are supported and will produce the same result. Choose the macro approach for better reusability and cleaner code, or use the explicit expression when custom logic is required.

  Examples:

  Using hash macro(default-SHA1)

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

  Using hash macro(MD5)

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

  Using hash macro(SHA256)

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

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

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

  Using multiple keys hash macro

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

  Using multiple hash macros

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

  Using explicit expression:

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

Note: Load strategy is determined by the node type and selected configuration options. It is not a user-configurable setting.

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

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

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

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

When deployed for the first time into an environment the SQL Node of materialization type table/View will execute the below stage:

After the SQL Node with materialization type table has been deployed for the first time into a target environment, subsequent deployments may result in either altering the SQL Table or recreating the SQL table.

A few types of column or table changes will result in an ALTER statement to modify the SQL 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

The following stages are executed:

Any of the following changes to views will result in deleting and recreating the view.

• View definition
• Adding table description
• Renaming the view

This is executed in two stages:

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

The following patterns represent common ways to use the SQL Node.

Sample node with Annotations

SELECT
    0 AS "MRG_ALL_ANNOT_KEY" @zeroKey(0),
     NATION."N_NATIONKEY" AS "N_NATIONKEY" @nullable("false") @inHash("1|GH_COL"),
     NATION."N_NAME" AS "N_NAME" @defaultValue("NA"),
     NATION."N_REGIONKEY" AS "N_REGIONKEY" @description("region key"),
     NATION."N_COMMENT" AS "N_COMMENT" @inHash("2|GH_COL"),
     NATION."N_LOAD_TIMESTAMP" AS "N_LOAD_TIMESTAMP" @tests("null", "unique"),
     {{ get_hash('GH_COL') }}::STRING AS "GH_COL",
    "SYSTEM_CURRENT_FLAG"::VARCHAR AS "SYSTEM_CURRENT_FLAG",
    "SYSTEM_VERSION"::NUMBER AS "SYSTEM_VERSION",
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS "SYSTEM_CREATE_DATE",
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS "SYSTEM_UPDATE_DATE",
    CAST('2999-12-31 00:00:00' AS TIMESTAMP) AS "SYSTEM_END_DATE"
FROM {{ ref('SRC', 'NATION') }} "NATION"

Basic Transformation & Cleaning Standard pattern for renaming columns and handling nulls.

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

• Using CTEs (Common Table Expressions)
  For more complex, multi-step logic.

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

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

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

Using Recursive CTE - Date Series

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

Using Recursive CTE - Classic Employee

WITH RECURSIVE RCTE_FINAL AS (

    -- Anchor clause: top-level employees (no manager)
    SELECT
        "EMPLOYEES_RECUR"."EMPLOYEE_ID"  AS "EMPLOYEE_ID",
        1                                AS "LEVEL",
        "EMPLOYEES_RECUR"."TITLE"        AS "TITLE",
        "EMPLOYEES_RECUR"."MANAGER_ID"   AS "MANAGER_ID"
    FROM {{ ref('SRC', 'EMPLOYEES_RECUR') }} AS "EMPLOYEES_RECUR"
    WHERE "EMPLOYEES_RECUR"."MANAGER_ID" IS NULL

    UNION ALL

    -- Recursive clause: employees reporting to someone in the CTE
    SELECT
        "EMPLOYEES_RECUR"."EMPLOYEE_ID"  AS "EMPLOYEE_ID",
        "RCTE_FINAL"."LEVEL" + 1         AS "LEVEL",
        "EMPLOYEES_RECUR"."TITLE"        AS "TITLE",
        "EMPLOYEES_RECUR"."MANAGER_ID"   AS "MANAGER_ID"
    FROM {{ ref('SRC', 'EMPLOYEES_RECUR') }} AS "EMPLOYEES_RECUR"
    JOIN RCTE_FINAL
        ON "EMPLOYEES_RECUR"."MANAGER_ID" = "RCTE_FINAL"."EMPLOYEE_ID"
)

SELECT
    "LEVEL"          AS "LEVEL",
    "TITLE"::VARCHAR AS "TITLE"
FROM RCTE_FINAL

Using CTE for multisource combine

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

SELECT * FROM ALL_NATIONS

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

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

• Flexible Projection (SELECT * with Expressions): Enhanced projection capabilities that allow for selecting all columns from a source (SELECT *) while simultaneously appending new calculated expressions, timestamps, or metadata in the same statement.

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

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

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

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

Macros