Skip to main content

Upgrading from V1 to V2 Node Types

This guide walks you through moving Nodes from deprecated inputMode: 'sql' V1 Node types to Node Type V2.

Overview​

Other Node types in Coalesce could set inputMode: 'sql' in YAML by duplicating a built-in Node type and adding the setting manually. The Coalesce App shows a deprecation warning for any Node type that still uses this setting. The inputMode: 'sql' setting will be removed in a future release.

Node Type V2 is the supported replacement. It is not backwards compatible with inputMode: 'sql' Nodes. The two formats do not convert directly and do not share the same storage. V1 Nodes save as .yml files. V2 Nodes save as .sql files. The upgrade is a manual copy-and-paste process.

Deprecated inputMode SQL

inputMode: 'sql' is deprecated and will be removed in a future release. Node types that use it show a deprecation warning in the Coalesce App. Finish this migration before the setting is removed so Jobs and deploys stay predictable.

What You Are Moving​

When you upgrade a Node from V1 inputMode: 'sql' to V2, you copy three things into the new V2 Node:

  1. The SQL query - Your SELECT statement, including any CTEs, joins, and expressions.
  2. Node configuration - Options that used to live in the Config panel, such as materialization type, insert strategy, preSQL and postSQL, become Node-level annotations above the SELECT in the .sql file.
  3. Column configuration - Per-column options from the mapping grid, such as business keys and change tracking, become column-level annotations after each expression in the SELECT list.

There is no automated migration. You recreate each Node manually on a V2 Node type.

Step 1: Inventory Your V1 Nodes​

Identify every Node type in your Workspace that still uses inputMode: 'sql'. They show a deprecation warning under Build Settings > Node Types. List the Nodes on each deprecated type so you know how much you need to migrate.

Step 2: Create a Replacement V2 Node Type​

  1. Go to Build Settings > Node Types and open Create Node Type.
  2. Choose V2 so the new type has version: 2.
  3. Give the type a clear name so it is easy to tell from the deprecated one during migration. If the old type was SQL Stage, you might use SQL Stage V2.

Copy the Create, Run, Join, and Macro templates from the deprecated Node type into the new one. Keep templates aligned with what runs in production so deployment behavior does not drift.

If your Run template loops over sources and emits a SELECT, add {{ source.cteString }} immediately before that SELECT inside the loop so CTEs from the SQL editor compile. For example:

{% for source in sources %}
    {{ source.cteString }}
    SELECT
    {% for col in source.columns %}
        ...
    {% endfor %}
    {{ source.join }}
{% endfor %}

Step 3: Recreate Each Node​

For each Node on the deprecated type, add a new Node on your V2 Node type and move the content by hand.

Copy the SQL Query​

Open the V1 Node and copy the full SELECT, including any CTEs. Paste it into the V2 Node's SQL editor.

Rewrite Node Configuration as Annotations​

Options from the Config panel become Node-level annotations above the SELECT. Use this table to map common panel settings to annotations:

V1 Config panel settingV2 annotation
Materialization type: table@materializationType("table")
Insert strategy: MERGE@insertStrategy("MERGE")
Pre-SQL@preSQL("your SQL here")
Post-SQL@postSQL("your SQL here")
Tests enabled@testsEnabled

Rewrite Column Configuration as Annotations​

Per-column options from the mapping grid become annotations after each column expression. Use this table for common mappings:

V1 column settingV2 annotation
Business key@isBusinessKey
Change tracking@isChangeTracking
Not null@notNull
Unique@isUnique
Surrogate key@isSurrogateKey

A fully annotated SELECT can look like this:

@materializationType("table")
@insertStrategy("MERGE")

SELECT
    C_CUSTKEY AS customer_key @isBusinessKey @notNull,
    C_NAME AS customer_name @isChangeTracking @notNull,
    C_ADDRESS AS customer_address @isChangeTracking,
    C_NATIONKEY AS nation_key,
    C_ACCTBAL AS account_balance,
    C_MKTSEGMENT AS market_segment
FROM {{ ref('STAGING', 'STG_CUSTOMER') }}

See the SQL Annotations Reference for the full annotation list and template wiring.

Step 4: Validate the New Node​

Before you migrate every Node, prove one path end to end:

  1. Save the new V2 Node and confirm the Columns tab lists the expected columns and types.
  2. Check the DAG and confirm upstream links from your {{ ref() }} calls look correct.
  3. Run Validate Select to catch parse errors.
  4. Run Create and Run and confirm warehouse results match what the V1 Node produced.

Fix template or annotation gaps before you continue in bulk.

Step 5: Migrate Remaining Nodes​

Repeat Step 3: Recreate Each Node and Step 4: Validate the New Node for every Node still on the deprecated type. You can migrate one subgraph at a time to reduce risk. V1 and V2 Nodes can live in the same pipeline and connect through {{ ref() }}.

Step 6: Retire the Deprecated Node Type​

After every Node is migrated and branches are merged, remove the deprecated Node type under Build Settings > Node Types. That clears the deprecation warning and drops the old inputMode: 'sql' definition from your Workspace.

Preserving Lineage and Job Order​

Use these practices so lineage and Job order stay correct after migration:

  • Refs for Coalesce Nodes - Use {{ ref('LOCATION', 'NODE_NAME') }} for every upstream Coalesce Node you query. Hard-coded table names do not create graph edges and Job ordering does not treat them as declared dependencies.
  • Top-level CTEs - Prefer CTEs over deeply nested subqueries. {{ ref() }} inside deeply nested subqueries does not always register as a DAG dependency even when the warehouse runs the SQL. Restructure so each ref() sits where the parser can see it, usually in a top-level CTE.

What's Next?​