Skip to content

SQL Transform

Description

SQL Transform is a compose node that runs SELECT or CTE-based SQL against available working data and publishes the query result as a downstream artifact. Use it to filter, join, reshape, calculate, validate, or prepare data after source data has entered the pipeline.

The SQL Editor is embedded inside the SQL Transform configuration experience. It is not a separate node. Use it from SQL Transform when you need a larger editing surface, query preview, result inspection, formatting, or table browsing before saving the SQL back to the node.

Field reference

Field Description
SQL Required. The query SQL Transform runs. Runtime execution supports SELECT statements and CTEs that begin with WITH. Multiple statements are not allowed. A single trailing semicolon is accepted and stripped before execution.
Edit / Preview Opens the embedded SQL Editor for the SQL field. Use it to write or edit SQL, preview results, browse available tables, beautify SQL, and save the SQL back to the node. The button also shows the F8 shortcut.
Format Output format for the query result. Supported values are NDJSON, XML, CSV, and Text (delimited). Delimited settings apply only to CSV and text output.
Filename template Optional output filename template. If blank, SchemAlign uses output with the selected format extension. The runtime supports filename placeholders such as {run_id}, {step_id}, {step_order}, {date}, {ts}, {ext}, and {format}.
Delimiter Delimiter used for CSV or text output. Common presets include comma, tab, pipe, semicolon, and custom. Ignored for NDJSON and XML output.
Custom delimiter Custom delimiter value used when Delimiter is set to custom. Ignored for NDJSON and XML output.
Quote char Quote character used for CSV or text output. If the value is invalid or too long, runtime handling reduces it to a single usable character.
Escape char Escape character used for CSV or text output. Defaults to the quote character when not provided.
Include header row Controls whether CSV or text output includes a first row containing column names. Ignored for NDJSON and XML output.

Embedded SQL Editor

The SQL Editor belongs to SQL Transform. It opens from Edit / Preview and provides a larger workspace for the SQL field.

The editor supports:

  • query preview for SELECT or CTE-based SQL
  • F8 or Ctrl/⌘+Enter to run preview
  • result inspection with row and column counts
  • a table browser for global and current-schema tables
  • table and column filtering in the browser
  • SQL beautify/formatting
  • saving the edited SQL back to the SQL Transform node

Preview runs are limited for interactive review and are separate from the full pipeline run. Saving from the SQL Editor updates the SQL field on the SQL Transform node.

When to use SQL Transform

Use SQL Transform when data is already available in SQL-accessible working data and needs to be shaped before delivery. Common patterns include:

  • SQL Loader -> SQL Transform -> File Put
  • Ethos Get -> SQL Transform -> File Put
  • SQL Transform -> HTTP Get when a single-row transform provides parameter values for a downstream source field
  • SQL Transform -> HTTP Response for inbound request workflows that need a computed response

Query rules

Runtime validation requires the SQL to begin with SELECT or WITH. SQL Transform is designed for read/query transformation work, not table creation, updates, deletes, or multi-statement scripts.

The node streams query results in batches and writes the selected output format to the run workspace before publishing the result to Documents.

Output behavior

SQL Transform always publishes the latest output to Documents. Downstream nodes, such as File Put, can use the published artifact.

Output formats behave as follows:

Output format Behavior
NDJSON Writes one JSON object per result row. This is the default output format.
CSV Writes delimited rows using CSV settings and optional header row.
Text (delimited) Writes delimited rows using the same delimiter, quote, escape, and header settings.
XML Writes XML output. For a single-column result that already contains XML-like text, the value can be written directly; otherwise rows and columns are wrapped into XML elements.

Validation and common errors

Message or condition What it means
sql_transform requires config.sql The SQL field is empty.
sql_transform only supports SELECT/CTE queries The SQL does not begin with SELECT or WITH.
sql_transform allows only a single SELECT statement The SQL contains multiple statements. Remove additional statements and keep one SELECT or CTE query.
sql_transform requires tenant-bound ctx.db from the executor The runtime did not provide the tenant-bound database session needed to execute the query.
SQL Editor says preview supports SELECT or WITH The preview editor enforces the same read-query shape expected by SQL Transform.

Security notes

SQL Transform runs within the tenant-bound execution context for the pipeline. Keep SQL readable and intentional, avoid embedding secrets in query text, and use named runtime values when a field needs a dynamic value instead of hard-coding environment-specific text.