Skip to main content

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

Troubleshooting and FAQ for SQL Nodes

Use this page when a SQL Node does not parse, columns look wrong, lineage is missing, DAG edges do not appear, or you need the current limits and answers to common questions.

Troubleshooting​

Columns Not Appearing After Saving​

You save your SQL but the Mapping tab shows no columns, or fewer columns than you expect. Work through these causes:

  • Missing aliases on expressions - Coalesce infers column names from your SELECT clause. Complex expressions without an alias, for example L_EXTENDEDPRICE * (1 - L_DISCOUNT), can produce an unexpected name or fail to parse. Add an explicit alias: L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS discounted_price.
  • Parse error earlier in the file - A syntax error above your final projection can stop the parser from reading downstream columns. Look for an error indicator in the editor, fix the issue, and save again.
  • SELECT * from a CTE or upstream Node - SELECT * records Node-level lineage and does not populate Mapping with named columns the way explicit projections do. List column names when you need column-level lineage in Mapping.

Column Data Type Shows as UNKNOWN​

The Mapping tab shows UNKNOWN as the data type for one or more columns when Coalesce cannot infer a type from a complex expression. Add an explicit cast in your SQL:

value::TIMESTAMP_NTZ
CAST(value AS DECIMAL(12,2))

Ref Helper Not Appearing as a DAG Dependency​

An upstream Node you reference with {{ ref() }} does not show as a connected dependency in the DAG view, even though the warehouse runs the SQL.

{{ ref() }} calls inside deeply nested subqueries do not always register as DAG dependencies. The warehouse still runs the SQL, but the Coalesce parser does not detect the reference.

Restructure the query so the ref sits in a top-level CTE:

-- Harder for the parser to detect:
SELECT *
FROM (
    SELECT order_id FROM {{ ref('STAGING', 'STG_ORDERS') }}
) sub

-- Easier for the parser to detect:
WITH orders AS (
    SELECT order_id FROM {{ ref('STAGING', 'STG_ORDERS') }}
)
SELECT * FROM orders

Parse Error on Save​

Coalesce shows an error with a line and column number when you save. Your SQL stays in the editor, and a parse error does not roll back previously compiled output or the deployed table. Fix the issue and save again.

Common causes include mismatched parentheses, an unclosed CTE block, or Jinja outside the supported ref pattern. For what is supported today, read Jinja Templates Beyond Ref in Known Limitations.

Annotations Not Taking Effect​

You added an annotation but the Node does not behave the way you expect. Examples include @writeMode("append") still overwriting the table, @preSQL with no Pre-SQL stage, or a column test that never runs.

Parsing writes annotation values into the Node's metadata, but the Node Type's templates decide what SQL to emit. An annotation nothing consumes changes nothing, with no error or warning, so a run can succeed while omitting the stage you expected. Work through these layers in order:

  1. Annotations panel - Open the Node's Annotations panel and confirm the Node Type actually supports the annotation, with the exact spelling and casing shown. If it is not listed and not reserved, the Node Type does not consume it.
  2. Create and Run templates - Confirm the template reads the hydrated value, for example config.writeMode.parameters[0], the config.preSQL array, or column.not_null.
  3. Node SQL - Confirm the annotation is spelled and placed correctly: node-level above the leading WITH or SELECT, column-level after the column in the outermost SELECT.

Watch for these common mistakes:

  • A typo or casing difference in the annotation name. Coalesce accepts any name and hydrates its value, so @writeMod("append") or @PreSQL(...) parses fine and does nothing.
  • A quoted boolean. @disableTests("false") is the string "false", which templates treat as true; write @disableTests(false) or omit the annotation.
  • A bare annotation where the template expects a parameter. @writeMode alone hydrates to true, so config.writeMode.parameters[0] is undefined.
  • An annotation placed inside a CTE or subquery, where it does not parse as an annotation.

After you change the definition or templates, re-validate Create and Run and confirm the compiled plan lists the stages you expect, for example Pre-SQL 1 before Load Table.

See SQL Annotations Reference for template examples and Defining a SQL Node Type for a wired node type.

Invalid Reserved Annotation​

The SQL editor underlines a reserved annotation, the Problems panel lists it under the Node, or a deploy is blocked with Invalid Annotations.

Coalesce validates the reserved annotations (@id, @nodeType, @description, @materializationType, and the column-level @description, @notNull, @defaultValue). Common causes:

  • @materializationType with a value other than lowercase "table" or "view", for example "TABLE" or "transient".
  • A reserved annotation that appears more than once. Only the first is used; later duplicates are flagged.
  • The wrong number of arguments, such as @nodeType with none or @notNull with one.
  • @nodeType pointing at an unknown ID or at a YAML Node Type.
  • @id that does not match the Node. Coalesce manages @id; restore the original value.

Fix the annotation in the SQL, save, and re-validate. coa validate reports the same issues for local Workspaces. Node-type-defined annotations are not validated; see Annotations Not Taking Effect for those.

Lineage Missing Through Subqueries​

Column-level lineage is incomplete or missing for columns that flow through a subquery.

Use CTEs instead of deeply nested subqueries. The parser traces lineage through CTE steps and does not always follow lineage through nested subqueries. A CTE chain usually restores column-level lineage.

Known Limitations​

Sections note whether a limit is by design, a known limitation, or planned for a future release.

Jinja Templates Beyond Ref​

This capability is planned. The SQL editor supports {{ ref() }} for upstream references. General Jinja, including custom macros, package macros, environment variables, and conditional blocks, is not supported. If you rely on patterns such as {{ env.database }}, use a YAML Node Type for those Nodes.

Column Propagation From Upstream Nodes​

This behavior is by design. When columns are added to an upstream Node, they do not automatically flow into downstream SQL Nodes. Your SQL is the source of truth. Add the new columns to your SELECT, or use SELECT * for broader propagation with the trade-off of limited column-level lineage in the grid.

Lineage and Refs Inside Subqueries​

This is a known limitation. {{ ref() }} inside subqueries can fail to register as DAG dependencies, so upstream Nodes might not show as connected in lineage views even though execution succeeds. Prefer CTEs so refs resolve and lineage stays consistent.

Copilot Integration​

This capability is planned. Copilot does not support creating or editing SQL Nodes.

Platform Support​

SQL Node Types are supported on Snowflake and BigQuery. Other data platforms are not yet generally available for SQL Nodes.

Bulk Editing​

This behavior is by design. The Node and Column Bulk Editors exclude SQL Nodes. Edit SQL Nodes through their SQL.

No Mapping To SQL Conversion​

This behavior is by design. There is no automated conversion from a mapping-grid YAML Node to a SQL Node. Add a new Node that uses a SQL Node type and rewrite the transformation in SQL. See Choosing Between YAML and SQL Node Types for when a rebuild makes sense.

FAQ​

Can You Mix YAML and SQL Nodes in the Same Pipeline?​

Yes. SQL Node Types are additive. They do not remove YAML Node Types. Both run in the same Workspace and pipeline. You pick YAML or SQL per Node Type. YAML and SQL Nodes connect through {{ ref() }} like any other Nodes.

Do You Lose Lineage When Using SQL Nodes?​

No. Coalesce parses {{ ref() }} to maintain DAG lineage, including column-level lineage for explicitly named columns. SELECT * records Node-level lineage only.

Can You Convert an Existing YAML Node to a SQL Node?​

Not automatically. Create a new Node on a SQL Node type and rewrite the transformation in SQL. See Choosing Between YAML and SQL Node Types for when a rebuild makes sense.

What Happens on a Parse Error?​

Coalesce shows an error with line and column information when it is available. Your SQL stays in the editor. Parse errors do not change previously compiled output or the deployed table on their own. Fix the error and save again.

Do You Need to Use the Ref Helper?​

Yes. {{ ref('STORAGE_LOCATION', 'NODE_NAME') }} is how Coalesce tracks dependencies and builds the DAG. Hard-coded table names do not create edges in the graph, and Job ordering does not treat them as declared dependencies.

How Do Annotations Work?​

On a SQL Node, you set per-Node options with SQL annotations. A small reserved set works on every SQL Node Type; everything else is declared by the Node Type and consumed by its Create and Run templates, which read config.* and column fields. The Annotations panel on each Node lists what its type supports. Reserved @id and @nodeType are managed by Coalesce. See SQL Annotations Reference and Defining a SQL Node Type.

Is There a Query Size Limit?​

There is no fixed character cap. Very large queries with hundreds of columns can take longer to parse.

How Is a SQL Node Different From Override Create SQL?​

On a SQL Node, the SQL editor replaces the mapping grid. You write a SELECT and Coalesce derives columns from it. Override Create SQL replaces the generated CREATE DDL and applies to views in that workflow. SQL Nodes are for authoring transformations. Override Create SQL is for customizing DDL output.

Can You Use SQL Nodes With Custom Node Types?​

Yes. Create a SQL Node Type from the Create Node Type menu under Build Settings > Node Types, or duplicate an existing SQL Node Type. See Defining a SQL Node Type for the full workflow. If you use custom templates, add {{ source.cteString }} to the Run template when you need CTE text passed through.

What SQL Constructs Are Supported?​

CTEs, JOINs, window functions, aggregations, CASE expressions, UNION, subqueries, casting, and most standard Snowflake SQL you would run in a SELECT pipeline are supported.

What About the inputMode SQL Setting?​

The inputMode: 'sql' setting on YAML Node type definitions is deprecated and will be removed in a future release. Node types that still use it show a deprecation warning in the Coalesce App. To move off it, create a replacement SQL Node Type, copy each Node's SQL into a new SQL Node, express its config as annotations, then retire the deprecated type. There is no automated conversion; the two formats store Nodes differently (.yml versus .sql).

Can You Use Stored Procedures?​

The main body of the file must be a SELECT. You can run procedure calls from a @preSQL annotation, for example @preSQL("CALL my_schema.my_proc()"). If the Node must deploy procedure DDL, handle that in custom templates.

What's Next?​