Overview

Etlworks lets you use SQL to query data extracted from non-relational sources such as files (CSV, JSON, XML, Excel, Parquet, Avro and others) and API responses. The SQL runs on the extracted, in-memory dataset, so you can filter, join, aggregate and reshape data with familiar SQL syntax regardless of where it came from.

Starting with Etlworks 10.x, this local DataSet SQL is executed by the Modern DataSet SQL engine by default. Modern is the recommended engine: it adds joins between extracted relations, derived tables, subqueries, nonrecursive CTEs, a window-function subset, native scalar functions, and the ability to reference the retained results of earlier transformations in the same flow.

The previous engine remains available as the Legacy compatibility option. You can select Legacy globally, per tenant, or per individual SQL statement — see Choosing the engine.

Important: Modern supports a documented SQL subset, described in this article. It is not complete ANSI SQL and it does not emulate a particular database dialect. Where behavior differs from textbook SQL, the difference is called out next to the affected feature.

Contents:

• How DataSet SQL works
• Where to write and test SQL
• Choosing the engine: Modern and Legacy
• Execution targets: application, Agents, scripting
• Example datasets used in this article
• SQL syntax, identifiers and projections
• Nested objects, arrays and ancestor access
• Expressions, predicates and scalar functions
• Grouping and aggregation
• Joins
• Derived tables and CTEs
• Subqueries
• Window functions
• Set operations
• DISTINCT, ORDER BY, TOP and LIMIT
• Querying earlier transformations: sources.<name>
• Script expressions and advanced APIs
• Performance and memory
• Troubleshooting
• Migration checklist

How DataSet SQL works

DataSet SQL operates on data that Etlworks has already extracted:

1. A connector reads and extracts the source data (a file, an API response, a spreadsheet, a message, and so on).
2. Etlworks represents the extracted data as an in-memory DataSet. Flat sources map to columns and rows; nested sources (JSON, XML) become hierarchies of nested DataSets.
3. DataSet SQL queries and transforms that extracted data.
4. The result can be inspected, mapped to the destination, or processed by the rest of the flow.

This applies to data extracted from CSV, JSON, XML, Excel, Parquet, Avro, API responses, and other supported connectors.

What DataSet SQL is not:

• It queries extracted DataSets, not raw JSON text and not arbitrary files by filename.
• The tables, nodes and fields available to a query depend on the connector and format settings that produced the DataSet. For example, JSON/XML streaming options that serialize child objects as strings make those children plain strings — a serialized child is not a navigable nested relation.
• Native database SQL continues to execute on the database, and native MongoDB queries keep their native execution path. DataSet SQL does not redirect or replace them, and it does not replace a database server.
• Local SQL is materialized and in memory. Selecting Modern does not add streaming SQL, disk spill, or distributed execution.

What the Modern engine gives you:

• Richer relational operations over extracted data.
• Nested-node navigation with access to parent (ancestor) context.
• Joins, derived tables, subqueries, nonrecursive CTEs, and a documented subset of window functions.
• Reuse of retained results from earlier transformations in the same flow (sources.<name>).
• Native evaluation of the supported expressions and predicates.
• Invocation-local indexes and reusable work for eligible queries (for example, equality-join indexes and shared window layouts).
• Independent queries can run concurrently; there is no global Modern query lock.

Note: these are capability improvements. No universal speedup or memory-reduction percentage applies to every workload — test representative data volumes in your environment.

Where to write and test SQL

Source query in a transformation

In a flow transformation, open MAPPING and enter the SQL in the Source query field. When the source connection is a file, API, or another non-relational connector, the query executes locally against the extracted DataSet. (When the source is a database, the same field holds native database SQL that runs on the database — that path is unchanged and not covered by this article.)

Test Transformation

Click Test Transformation in the mapping editor to run the transformation, including its Source query, against the configured source and preview the result.

Explorer

In Explorer:

Step 1. Select the file or object in the connection tree.

Step 2. Expand the nested data object if needed.

Step 3. Click Develop SQL.

Step 4. Enter the SQL in the editor.

Step 5. Click Execute SQL.

SQL invoked from scripts

SQL executed through the supported scripting APIs (for example, lookups and SQL helpers called from JavaScript or Python) runs under the same engine policy as the surrounding invocation. See Execution targets.

Important: testing a single transformation, or querying in Explorer, does not automatically execute earlier transformations in the flow. A query that references sources.<name> needs the complete sequential flow run to have produced those earlier results, so it can fail in an isolated preview while succeeding in the full flow.

A first query

With a flat extracted dataset such as the customers example, the simplest useful query filters and projects fields. A query without a FROM clause operates on the supplied DataSet:

SELECT id, name
WHERE active = true

The same query can name the dataset node explicitly:

SELECT id, name
FROM customers
WHERE active = true

Note: a query without FROM still queries the extracted DataSet that was supplied to it. It is not a database-style one-row constants source (SELECT 1 does not produce a standalone row independent of the input). If you need a one-row result, use a one-row input.

Choosing the engine: Modern and Legacy

Two engines can execute app-managed local DataSet SQL:

• Modern — the recommended default on 10.x, with the language described in this article.
• Legacy — the previous engine, kept as a compatibility option. Legacy retains its own language and result behavior; Modern-only features (joins between extracted relations, CTEs, window functions, the sources namespace, and so on) are not available under Legacy.

For app-managed local SQL, the engine is resolved in this order of precedence:

The policy applies to new and existing flows, Test Transformation, and Explorer. Saved SQL is not rewritten, and an explicitly saved Legacy setting remains Legacy — the new default does not overwrite that choice. There is no transformation-level engine selector: a single transformation chooses its engine with a statement directive, not a dropdown.

Global selection

The global setting is Default DataSet SQL Engine:

1. Open Settings.
2. Open the Flows settings group.
3. Locate Default DataSet SQL Engine.
4. Select Legacy.
5. Save.

This changes the inherited policy for all executions that do not have a tenant override or a statement directive. To restore the recommended behavior, select Modern and save.

Tenant selection

A tenant can override the global setting. The tenant editor's Flows category exposes Default DataSet SQL Engine with three choices:

• Use default — inherit the current global setting. This does not always mean Modern; it means whatever the global value currently is.
• Legacy — override the global setting with Legacy for this tenant.
• Modern — explicitly select Modern for this tenant.

Super administrators reach it through Users → Tenants → selected tenant → Flows. A tenant's own Account → Flows view also exposes the override when the user has the required permissions.

Tip: prefer a narrow compatibility exception — a statement directive or a single tenant override — over changing the global policy for everyone.

Per-query directives

An individual SQL statement selects its engine with a leading block comment:

/* etlworks:sql-engine=Legacy */
SELECT id, name

/* etlworks:sql-engine=Modern */
SELECT id, customer.name AS customer_name
FROM data.items
LIMIT 100

Directive rules:

• The directive is a leading block comment on the statement.
• Its spelling and values are case-insensitive.
• Whitespace and ordinary comments may precede it.
• Supported values are Modern and Legacy. "Optimized" is an old configuration alias, not a supported directive value.
• Repeated identical directives are accepted. Conflicting, malformed, or misplaced reserved directives fail.
• The directive applies to the entire statement, including its CTEs, subqueries, and set-operation branches.
• It does not change any saved setting, and it does not alter the default of a separate SQL lookup invoked by a script.
• A trailing semicolon is allowed. Do not place multiple SQL statements behind one directive.
• Modern does not automatically retry an unsupported or failed query with Legacy — an unsupported query fails with an error.
• Selecting Legacy does not make Modern-only features available there.

Tip: before broadening a Legacy override to a tenant or globally, test the smallest affected query with an explicit /* etlworks:sql-engine=Legacy */ directive to confirm the engine is actually the issue.

Execution targets: application, Agents, scripting

Key points:

• Upgrading the host application does not add Modern SQL support to an old Agent. The flow executes with the engine available on the Agent that runs it.
• Agents do not independently retrieve tenant settings; the application resolves the policy and sends it with the execution request.
• Existing supported SQL helpers and lookups called from scripts inherit the active invocation policy.
• SQL-engine selection is separate from JavaScript-engine selection. Selecting Modern SQL does not select GraalJS and does not change the scripting language.

Advanced (Java embedding): code that calls SQL helpers such as CommonEtlUtils.executeSql(...) or Extractor.lookup() / lookupNoCache() inherits the invocation's engine policy. Standalone Java callers outside an application or Agent invocation default to Legacy unless an explicit mode or directive selects Modern. These are API-level controls; the ordinary configuration methods are the UI settings and statement directives described above.

Example datasets used in this article

Most examples below use this small, consistent example pack, so every output can be checked by hand. Each example states which dataset it queries and any extraction assumptions.

customers (flat, with inactive rows, a duplicate key, and special field names)

{
  "customers": [
    {"id": 10, "name": "Alice", "active": true,  "customer tier": "gold",   "tax.code": "US-1"},
    {"id": 11, "name": "Bob",   "active": false, "customer tier": "silver", "tax.code": "US-2"},
    {"id": 12, "name": "Carol", "active": true,  "customer tier": "gold",   "tax.code": "EU-1"},
    {"id": 12, "name": "Carl",  "active": true,  "customer tier": "bronze", "tax.code": "EU-2"}
  ]
}

Notes: two rows share id 12 (a duplicate join key); customer tier contains a space; tax.code contains a literal dot. Both require double-quoted identifiers (see Identifiers).

orders (flat, with nulls, a zero quantity, duplicates, and an unmatched customer)

{
  "orders": [
    {"id": 1, "customerId": 10, "amount": 100, "quantity": 2, "note": "rush"},
    {"id": 2, "customerId": 10, "amount": 150, "quantity": 0, "note": null},
    {"id": 3, "customerId": 11, "amount": 50,  "quantity": 1, "note": "gift"},
    {"id": 4, "customerId": 99, "amount": 250, "quantity": 5, "note": null},
    {"id": 5, "customerId": 12, "amount": 150, "quantity": 3, "note": "rush"}
  ]
}

Notes: order 4 references customer 99, which does not exist in customers (unmatched reference); two orders share amount 150 (duplicate values); note is null in two rows; order 2 has quantity 0 (division guard example).

nested orders (hierarchical, with singleton objects, arrays, and missing/null children)

{
  "data": {
    "orders": [
      {"id": 101, "region": "US",
       "customer": {"id": 10, "name": "Alice", "address": {"city": "Austin"}},
       "lines": [
         {"sku": "A-1", "qty": 2, "price": 25},
         {"sku": "A-2", "qty": 1, "price": 50}
       ]},
      {"id": 102, "region": "US",
       "customer": {"id": 11, "name": "Bob", "address": {"city": "Boston"}},
       "lines": []},
      {"id": 103, "region": "EU",
       "customer": null,
       "lines": [
         {"sku": "B-1", "qty": 3, "price": 10}
       ]},
      {"id": 104, "region": "US"}
    ]
  }
}

Notes: customer and its address are singleton objects; lines is an array; order 102 has an empty lines array; order 103 has a null customer; order 104 is missing both children entirely.

Extraction assumption for all nested examples: the JSON is extracted with nested objects and arrays preserved as nested DataSets (the default), not with options that serialize children into strings. A child serialized into a string is a string, not a navigable relation.

SQL syntax, identifiers and projections

Supported query outline

Modern supports the following query shape. It is a guide to the supported subset — not a promise that every conceivable combination of clauses is implemented, and not everything a SQL parser will accept is actually supported.

WITH relation_name AS (
    SELECT ...
)
SELECT DISTINCT expression AS alias, ...
FROM node_path relation_alias
LEFT JOIN other_relation other_alias ON ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...

Clauses are optional where supported. Keywords are case-insensitive.

Literals and identifiers

• String literals use single quotes: 'US'. Escape an apostrophe by doubling it: 'O''Brien'.
• Double-quote identifiers that contain spaces, punctuation, or literal dots: "customer tier", "tax.code".
• A quoted dot belongs to the identifier; an unquoted dot separates path components. "customer.name" is one field literally named customer.name; customer.name is the name field inside the customer node.
• Identifier lookup against the DataSet is generally case-insensitive. Names that differ only by case are ambiguous and invalid.

Example using the special names in the customers dataset:

SELECT id, name, "customer tier" AS tier, "tax.code" AS tax_code
FROM customers
WHERE active = true

Expected output:

Bob is excluded because active is false. Both id 12 rows appear — filtering does not deduplicate.

Projections and aliases

• AS is optional for projection aliases: SELECT amount total and SELECT amount AS total are equivalent. Prefer AS for readability.
• SELECT ALL keeps duplicates (the default); SELECT DISTINCT removes duplicate projected rows (see DISTINCT).
• An unqualified * is supported for single-source queries, alone or mixed with explicit columns and calculated expressions — see Mixing * with explicit columns.
• Joined * and alias.* are rejected. In a join, enumerate the output columns explicitly.
• Use explicit, unique aliases for calculated expressions, dotted projections, join outputs, derived tables and CTE outputs. Ambiguous duplicate output names in derived tables, CTEs and joins are invalid.

Mixing * with explicit columns

For single-source queries, an unqualified * can be combined with explicit columns and calculated expressions in the same SELECT list:

SELECT first, last, age, *

SELECT first AS given_name, *

SELECT *, age + 1 AS next_age

Expansion rules:

• * includes the selected relation’s original columns.
• Source columns appear once, in their original order. Explicitly listing columns before * does not move them to the front.
• New aliases and calculated columns follow the source columns.
• Multiple wildcards do not duplicate columns.
• For the same output name, the last projection wins:

-- Keeps the original age because * comes last:
SELECT age + 1 AS age, *

-- Replaces age with the calculated value:
SELECT *, age + 1 AS age

Scope:

• This applies to a single supplied DataSet, nested FROM nodes, derived tables, and CTEs.
• Internal calculation fields created for window functions and subqueries are excluded from wildcard expansion.
• The join restrictions are unchanged: joined wildcards and qualified wildcards such as alias.* are not supported. In a join, enumerate the output columns explicitly.

Column order in the result

The order of columns in the result can depend on how the projection is evaluated. Source fields selected without aliases may come back in source order rather than SELECT order in some query shapes, while aliased and calculated columns follow the SELECT statement.

Recommendation: when the result shape matters — for example, when the result feeds a mapping or a destination with positional expectations — give every projected column an explicit, unique alias and verify the output of your specific query. Do not rely on a universal column-ordering rule. For queries that mix * with explicit columns, the wildcard expansion order is defined — see Mixing * with explicit columns.

Querying Excel data

For Excel sources, SQL sees the DataSet produced by the workbook and worksheet selection in the Excel format and the object you selected:

• The format's Worksheet Name or 1-based index field supplies the default sheet.
• With Read Worksheet Names enabled, Explorer shows the workbook with its worksheets as children, and a specific worksheet can be selected as the object (the object identity has the form filename|worksheet).

How to query depends on how many worksheets the format reads:

A single worksheet is selected (by name or 1-based index, or through the Explorer object selection). The worksheet's rows become the root dataset — query it without a FROM clause:

SELECT OrderNum, OrderDate, Amount
WHERE Amount > 150

In this case FROM [Sheet1] fails with an unknown-node error: there is no worksheet node, because the single worksheet is the dataset itself.

Multiple worksheets are read (for example, Worksheet Name or 1-based index set to *). Each matched worksheet becomes a nested node named after the sheet, and the bracket syntax selects one of them in FROM:

SELECT *
FROM [Sheet1]

Note: SQL selects from the worksheets that were already extracted by the format — it does not independently open another worksheet. A worksheet that was not matched by the format's selection is not available to the query.

Nested objects, arrays and ancestor access

FROM paths select nested nodes

A FROM path selects and expands the records of a nested node. Using the nested orders dataset:

SELECT id, region, customer.id AS customer_id, customer.name AS customer_name
FROM data.orders

Expected output:

Explanation: customer is a singleton child object, so a dotted scalar projection such as customer.name traverses it. For order 103 the customer is null and for order 104 it is missing entirely — supported scalar navigation yields null in both cases rather than failing.

Rules:

• A FROM path is not JSONPath. There is no $, no JSONPath filter or wildcard language, and no array-index syntax.
• Dotted scalar projections traverse singleton child DataSets. Missing, null, or empty children yield null.
• A multi-row child used as a single scalar value fails. To process an array (such as lines), select it in FROM or express the relationship with a supported join or subquery.
• Traversing a scalar as though it were a node fails.
• Repeated nested instances align fields by name; optional fields that are absent in some instances become null.
• An unknown FROM node normally fails with an error.
• An entirely empty input may lack the child metadata needed for joins; it cannot manufacture join-key information.

Selecting a deeper array with parent context

SELECT
    orders.id AS order_id,
    l.sku AS sku,
    l.qty AS quantity,
    orders.customer.address.city AS city
FROM data.orders.lines l
WHERE orders.region = 'US'

Expected output:

Explanation: FROM data.orders.lines expands the line items. Each line row retains its ancestor context, so orders.id, orders.region and orders.customer.address.city are available. Order 102 contributes no rows (empty lines array), order 103 is excluded by the region filter, and order 104 contributes no rows (no lines node).

Name resolution with ancestors:

• An unqualified field name resolves to the current (child) relation first.
• If the child does not contain it, lookup can use the nearest ancestor containing that field.
• Qualify parent references explicitly (orders.id above) to remove ambiguity.
• this addresses the immediate parent where one exists.

Sibling arrays are not automatically correlated

Consider an order with two sibling arrays:

{
  "orders": [
    {"id": 201,
     "lines":     [{"sku": "A-1", "qty": 2}, {"sku": "A-2", "qty": 1}],
     "shipments": [{"box": "BX-7", "sku": "A-1"}]}
  ]
}

Selecting FROM orders.lines does not automatically pair, multiply, or correlate the sibling shipments array by their shared parent. If you need lines with their shipments, express the relationship explicitly as a join with the keys that relate them — including parent keys where necessary:

SELECT
    l.sku AS sku,
    l.qty AS qty,
    s.box AS box
FROM orders.lines l
LEFT JOIN orders.shipments s ON l.sku = s.sku

Expected output:

Note: with multiple parent orders, join on the parent order id as well (a composite key such as ON l.orders.id = s.orders.id AND l.sku = s.sku), or the join will relate lines and shipments across different orders.

Expressions, predicates and scalar functions

Supported expression elements

• String, numeric, boolean, and null literals.
• Field references and parentheses.
• Arithmetic: +, -, *, /. Division by zero is an error — guard it (see the CASE example below).
• Comparisons: =, <>, !=, <, <=, >, >=.
• AND, OR, NOT. AND and OR short-circuit.
• BETWEEN / NOT BETWEEN.
• IN / NOT IN.
• LIKE / NOT LIKE with % and _, and an optional one-character ESCAPE. LIKE is case-sensitive.
• IS NULL / IS NOT NULL.

Null handling:

• Native predicates use three-valued logic: comparing anything with null is unknown, and WHERE and HAVING retain only rows where the predicate is true.
• Use IS NULL, not = NULL.
• NOT IN with a null in the candidate set can evaluate to unknown for every row. It is not interchangeable with NOT EXISTS — see Subqueries.

Also do not assume database behaviors that are not part of the contract: there is no configurable collation and no automatic string-to-date conversion in comparisons.

Scalar functions

A guarded expression over the orders dataset:

SELECT
    id,
    CASE
        WHEN quantity = 0 THEN NULL
        ELSE amount / quantity
    END AS unit_amount,
    COALESCE(note, 'No note') AS display_note
FROM orders

Expected output:

Explanation: order 2 has quantity 0, so the CASE selects the NULL branch instead of dividing by zero. CASE and COALESCE evaluate their branches lazily — the division is never evaluated for order 2. Laziness extends to supported scalar subqueries in unselected branches, but it does not make invalid or unsupported SQL valid: the whole statement must still be supported SQL.

CAST

Supported CAST targets:

• INT / INTEGER, BIGINT
• REAL / FLOAT / DOUBLE / DOUBLE PRECISION
• DECIMAL / NUMERIC, including DECIMAL(p,s) / NUMERIC(p,s)
• VARCHAR / CHAR, optionally with a length
• TEXT / STRING
• BOOLEAN
• DATE, TIMESTAMP

Examples:

CAST(amount_text AS DECIMAL(12,2))
CAST(date_text AS DATE)
CAST(timestamp_text AS TIMESTAMP)

Conversion rules:

• Null remains null.
• Integer conversions must be exact and in range; a fractional or out-of-range value is an error.
• Decimal scale uses HALF_UP rounding; precision overflow fails.
• Length-limited strings truncate by Java string length.
• BOOLEAN accepts true/false text, not arbitrary truthiness.
• DATE converts ISO local-date text; TIMESTAMP accepts timestamp text, including T as the date/time separator.
• There is no TRY_CAST, no interval or timezone casts, and no arbitrary vendor type names. A failed conversion is an error, not a null.

Grouping and aggregation

Supported: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAX.

Using the orders dataset:

SELECT
    customerId AS customer_id,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customerId
HAVING total_amount > 100
ORDER BY total_amount DESC, customer_id

Expected output:

Explanation: the groups are customer 10 (100 + 150 = 250), customer 11 (50), customer 99 (250), and customer 12 (150). HAVING total_amount > 100 removes customer 11. The result is ordered by total descending, with customer_id as the tie-breaker between the two totals of 250.

HAVING can reference:

• Selected aliases (total_amount above).
• Grouping values that are not projected.
• Aggregates that are not in the SELECT list.

Select grouping keys and aggregates. Do not rely on selecting a non-grouped, non-aggregated column as if it were standard SQL — that is not part of the supported contract.

DISTINCT aggregates

Single-expression DISTINCT aggregates are supported:

COUNT(DISTINCT customerId)
SUM(DISTINCT amount)
AVG(DISTINCT amount)

MIN and MAX also accept the same single-expression DISTINCT form. Not supported: COUNT(DISTINCT *) and multi-column forms such as COUNT(DISTINCT column_a, column_b).

Example: SELECT SUM(DISTINCT amount) AS distinct_total FROM orders sums each distinct amount once — 100 + 150 + 50 + 250 = 550, with the duplicate 150 counted once.

Aggregates compose with CASE and COALESCE:

SELECT
    customerId AS customer_id,
    SUM(CASE WHEN note = 'rush' THEN amount ELSE 0 END) AS rush_amount
FROM orders
GROUP BY customerId
ORDER BY customer_id

Expected output: customer 10 → 100 (order 1 is rush; order 2's null note makes the WHEN unknown, selecting ELSE 0), customer 11 → 0, customer 99 → 0, customer 12 → 150.

Current null-handling qualification for aggregates

Read this before relying on COUNT(expression) or AVG over data with nulls.

Depending on the query shape, ordinary aggregates can execute through a shared compatibility path, while composite/DISTINCT aggregates, correlated aggregates and aggregate windows use native evaluation. These paths currently differ in null handling:

• The shared path's COUNT(expression) counts group rows even when the expression is null.
• The shared path's AVG does not universally use the standard non-null denominator.
• Native aggregates skip null arguments; COUNT(*) counts rows either way.
• For an empty input, native COUNT is 0 and native SUM, AVG, MIN and MAX are null.
• The query shape can determine which path is used.

Practical guidance:

• Do not assume uniform ANSI null behavior for COUNT(expression) and AVG across all query shapes.
• When null-skipping semantics matter, make them explicit — for example, filter nulls in WHERE, or use COUNT(*) together with an explicit null filter — and verify the result on your data.
• Do not add arbitrary expressions to a query merely to force one implementation path; that is not a supported control.

Null test case over orders (note is null in orders 2 and 4):

SELECT COUNT(note) AS note_count, COUNT(*) AS row_count
FROM orders

row_count is 5. note_count is 3 under null-skipping semantics, but can be 5 when the query executes through the shared compatibility path described above. Verify this query's result in your release before depending on it.

Also note: native numeric accumulation is double-based. Casting the input to DECIMAL does not make SUM/AVG exact arbitrary-precision financial arithmetic.

Joins

Modern supports INNER JOIN and LEFT [OUTER] JOIN, including multiple chained joins.

Joining orders to customers (both extracted in the same input, as sibling top-level nodes):

SELECT
    o.id AS order_id,
    o.amount AS amount,
    c.name AS customer_name
FROM orders o
LEFT JOIN customers c
    ON o.customerId = c.id
    AND c.active = true
WHERE o.amount > 0
ORDER BY o.id

Expected output:

This one example shows most of the join contract at once:

• Additional ON predicates: order 3 matches customer 11 (Bob) by key, but Bob fails the residual condition c.active = true. Because this is a LEFT JOIN, the left row is retained with null right values. In an INNER JOIN, order 3 would be dropped.
• Unmatched rows: order 4 references customer 99, which does not exist — retained with nulls by LEFT JOIN, dropped by INNER JOIN.
• Duplicate key matches: customers has two rows with id 12, so order 5 produces two output rows, one per match.
• ON versus WHERE: the filter c.active = true lives in ON, so it only disqualifies match candidates. The same condition in WHERE would run after the join and remove every row whose customer_name side is null — including the retained unmatched rows. Filtering the right relation in ON and in WHERE are different queries.

Composite keys and chained joins (the same customers node joined twice under distinct aliases):

SELECT
    o.id AS order_id,
    c.name AS customer_name,
    c2."customer tier" AS tier
FROM orders o
INNER JOIN customers c ON o.customerId = c.id
LEFT JOIN customers c2 ON c.id = c2.id AND c.name = c2.name

Expected output:

Explanation: the INNER JOIN drops unmatched order 4. The second join uses a composite equality key (two AND-ed equalities: id and name), so each duplicate-id customer row matches only itself — Carol matches Carol, Carl matches Carl — instead of cross-matching on id alone. Each join's ON links the newly joined alias to a previously introduced alias.

Join contract:

• Each joined relation requires an explicit, distinct alias.
• Every ON requires at least one cross-table equality key linking the new alias to a preceding alias.
• Composite equality keys joined with AND are supported.
• Additional residual predicates may use either side, including OR inside the residual condition.
• A pure non-equality join, or an OR-only join condition, is unsupported.
• Null join keys do not match anything (including other nulls).
• Duplicate matches produce multiple output rows.
• Keys must be scalar. Numeric keys are normalized across numeric representations.
• Date, time and timestamp keys keep type-specific semantics — timestamp nanoseconds are retained — and strings are not automatically converted to date keys.
• Enumerate the output fields and uniquely alias duplicate names. Joined * and alias.* are rejected.

Not supported: RIGHT, FULL, CROSS, comma-separated, NATURAL and LATERAL joins, and the USING clause.

Derived tables and CTEs

Derived tables

SELECT
    t.customer_id,
    t.total_amount
FROM (
    SELECT
        customerId AS customer_id,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customerId
) t
WHERE t.total_amount > 100

Expected output: the three groups with totals over 100 — customer_id 10 (250), 99 (250) and 12 (150).

Rules:

• A derived table is independently evaluated and materialized.
• It requires an alias, and its output names must be unambiguous — alias calculated columns explicitly.
• Derived tables can filter, group, sort, limit, combine sets, be nested, and participate on either side of a supported join.
• They cannot reference an outer row — there is no LATERAL behavior.

Common table expressions (WITH)

WITH totals AS (
    SELECT
        customerId AS customer_id,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customerId
)
SELECT
    customer_id,
    total_amount,
    ROW_NUMBER() OVER (ORDER BY total_amount DESC, customer_id) AS position
FROM totals
ORDER BY position

Expected output:

Explanation: the CTE computes grouped totals once; the outer query ranks them. This CTE-then-window pattern is also the supported way to rank grouped results (see Window functions).

More CTE forms:

WITH us_orders AS (
    SELECT id, customerId, amount FROM orders WHERE amount > 0
),
totals (customer_id, total_amount) AS (
    SELECT customerId, SUM(amount) FROM us_orders GROUP BY customerId
)
SELECT t.customer_id, t.total_amount
FROM totals t
INNER JOIN totals t2 ON t.customer_id = t2.customer_id

This shows multiple CTEs, a later CTE referencing an earlier one, an explicit CTE column list, and reuse of one CTE under two aliases.

CTE rules:

• CTEs are nonrecursive. Recursive CTEs are unsupported.
• Each CTE is materialized once per query invocation and can be reused by joins, subqueries and set branches.
• CTE names are query-local and can shadow input names.
• An explicit column list must match the result width, and its names must be unique.
• Duplicate CTE names, self-references and forward references fail.
• Nested WITH inside a derived table is outside the supported parser subset.

Subqueries

Supported forms:

• Scalar subquery in SELECT.
• Scalar comparison in WHERE.
• IN / NOT IN in WHERE.
• EXISTS / NOT EXISTS in WHERE.
• Correlation through outer aliases, and through child paths of the outer row.

Using the nested orders dataset (aliasing data.orders as o):

SELECT
    o.id AS order_id,
    (
        SELECT SUM(l.qty * l.price)
        FROM o.lines l
    ) AS line_total
FROM data.orders o
WHERE EXISTS (
    SELECT 1
    FROM o.lines l
    WHERE l.qty > 0
)

Expected output:

Explanation: o.lines expands within the current outer row only. Order 101's total is 2×25 + 1×50 = 100; order 103's is 3×10 = 30. Orders 102 (empty lines) and 104 (missing lines) have no line with qty > 0, so EXISTS excludes them — a missing child forms an empty relation rather than an error.

Independent (uncorrelated) membership subquery over the flat datasets:

SELECT o.id AS order_id
FROM orders o
WHERE o.customerId IN (
    SELECT c.id
    FROM customers c
    WHERE c.active = true
)

Expected output: orders 1, 2 (customer 10) and 5 (customer 12). Order 3's customer is inactive and order 4's customer does not exist.

Scalar cardinality

• A scalar subquery must produce exactly one output column.
• Zero rows produce null.
• More than one row is an error.
• If you deliberately want one row of several, make it deterministic with an ordered TOP or LIMIT inside the subquery.

Correlation rules

• A child path such as o.lines expands within the current outer row.
• Missing children yield an empty relation.
• Explicit outer aliases can cross nested subquery scopes; an inner alias shadows an outer alias of the same name.
• Outer values are bound as values — they are never substituted into SQL text.
• Joined outer rows can supply their aliases to supported single-node correlated subqueries.

Subquery performance

• Independent subqueries execute once per containing invocation, and their membership sets are reusable.
• Simple EXISTS stops at the first match. Grouped EXISTS still applies grouping and HAVING first.
• SELECT subqueries run after WHERE, so filtered-out rows never evaluate them.
• Eligible equality correlations can use invocation-local indexes.
• OR and non-equality correlations can require repeated scans of the subquery input — correct, but potentially expensive.

Subquery restrictions

• A correlated subquery supports a single node with the documented native predicates, aggregates, GROUP BY/HAVING, DISTINCT, sorting and limits.
• Joins, derived tables, and set operations inside a correlated subquery are unsupported (they are fine inside independent subqueries).
• Correlated window subqueries are unsupported.
• Subqueries in ON, GROUP BY, HAVING, or ORDER BY expression positions are unsupported. (HAVING inside a supported subquery is fine; a subquery inside HAVING is not.)
• Arbitrary script expressions are not supported in correlated evaluation.
• Use explicit output aliases; unaliased subquery projections receive generated column_N names.

Window functions

The supported window subset:

Example over orders:

SELECT
    id,
    ROW_NUMBER() OVER (
        PARTITION BY customerId ORDER BY id
    ) AS row_number,
    LAG(amount, 1, 0) OVER (
        PARTITION BY customerId ORDER BY id
    ) AS previous_amount,
    SUM(amount) OVER (
        PARTITION BY customerId
    ) AS customer_total
FROM orders
ORDER BY customerId, id

Expected output:

Explanation: each customer is a partition. Within customer 10, order 2's previous_amount is order 1's amount (100); every partition's first row gets the supplied default 0 instead of null. customer_total repeats the whole-partition sum on every row — window calculations preserve the row count. The final display order comes from the outer ORDER BY customerId, id (customers 10, 11, 12, 99), not from the window's own ordering.

Window rules:

• Window calculations preserve the row count; they never collapse rows.
• Window ordering does not order the final displayed result — use the outer ORDER BY.
• LAG/LEAD offsets are literal nonnegative integers; the default offset is 1. An out-of-range access yields null unless a default argument is supplied.
• Aggregate windows use native null handling (null arguments are skipped; COUNT(*) counts rows).
• Native window ordering puts nulls first ascending and last descending.
• Window functions with identical partition/order definitions reuse the prepared partition and sort work within the invocation.

Explicitly not supported:

• Running (ordered) aggregate windows.
• Explicit ROWS/RANGE frames and named windows.
• DISTINCT window aggregates.
• DENSE_RANK and NTILE.
• Window predicates directly in WHERE.
• Direct grouping + window combinations as a general pattern. To rank grouped totals, put the aggregation in a CTE or derived table first — see the CTE example.

Set operations

Supported: UNION ALL, UNION, INTERSECT, EXCEPT, MINUS (MINUS behaves as EXCEPT).

UNION and UNION ALL

SELECT name FROM groupA
UNION
SELECT name FROM groupB

With groupA = Alice, Bob and groupB = Bob, Charlie, UNION returns Alice, Bob, Charlie; UNION ALL returns Alice, Bob, Bob, Charlie.

Rules:

• UNION ALL concatenates the branch results.
• UNION concatenates, then applies Modern's row DISTINCT.
• Nonempty branches require equal column counts and combine positionally. Output labels normally come from the left branch.
• When one input is empty, shortcut handling can make the resulting schema come from the other branch. Align aliases and types explicitly in both branches for a predictable output shape.
• A final ORDER BY and LIMIT apply to the complete combined result — including when the set query sits inside a CTE, derived table or subquery.
• A TOP attached to one operand remains local to that operand.

INTERSECT and EXCEPT / MINUS — compatibility note

Important: INTERSECT and EXCEPT/MINUS execute through shared DataSet membership operations, not a full standard set-algebra implementation. The differences matter:

• When both inputs are nonempty, they require matching field names, order, and visibility — not just positional compatibility.
• Duplicates from the left input are retained according to membership. The result is not reduced to a set, and duplicate handling is not standard SQL multiset cardinality.
• ALL variants (INTERSECT ALL, EXCEPT ALL) route to the same membership operations and do not implement standard multiset semantics.
• Row matching uses the shared DataSet key construction, which is not identical to Modern DISTINCT's typed key comparison. Null, empty and key-conversion edge cases are therefore not a universal SQL equality guarantee.
• Known discrepancy: INTERSECT with an empty right input currently returns the left DataSet unchanged, rather than the empty result standard SQL would produce.

For membership tests where these qualifications are a problem, prefer explicit EXISTS / NOT EXISTS patterns, which use the documented subquery semantics:

SELECT a.name
FROM groupA a
WHERE EXISTS (
    SELECT 1 FROM groupB b WHERE b.name = a.name
)

DISTINCT, ORDER BY, TOP and LIMIT

DISTINCT

• SELECT DISTINCT removes duplicate projected rows using scalar keys.
• Numeric values are normalized across representations (10, 10.0 compare equal), while string case and whitespace differences are preserved as distinct values.
• There is no general deep-equality guarantee for nested, non-scalar values — project scalars.
• DISTINCT requires additional memory to track seen rows.

ORDER BY

• ORDER BY accepts output labels, bound source fields, and one-based column positions, each with ASC or DESC.
• You can order by retained source or group values that are not displayed.
• Prefer projecting a calculated sort expression under an alias and ordering by the alias.
• Sorts are stable; add an explicit tie-breaker when you publish an exact row order (ORDER BY amount DESC, id).
• Main-query ordering uses the DataSet comparison behavior (including its numeric-string, date, and locale handling). It is not a configurable database collation, and it need not match every native operator's comparison rules.
• NULLS FIRST / NULLS LAST syntax is not supported.

TOP and LIMIT

SELECT TOP 10 id, amount
FROM orders
ORDER BY amount DESC, id

SELECT id, amount
FROM orders
ORDER BY amount DESC, id
LIMIT 10

• TOP n and TOP (n) are supported, as is a final LIMIT n.
• Values are literal nonnegative integers up to 2147483647. Zero returns no rows.
• If TOP and LIMIT both apply, the smaller value wins.
• Without ORDER BY, "first n" is not a business-defined ordering — always order before limiting when the choice of rows matters.
• Eligible ordered limits use bounded top-N selection internally. This reduces sort work, but it does not prevent the source from being materialized.
• DISTINCT may still require full sorting before limiting.

Not supported: OFFSET / FETCH, TOP PERCENT, TOP WITH TIES, DISTINCT ON.

Querying earlier transformations: sources.<name>

In a multi-source flow, a Modern query can reference the retained result of an earlier transformation in the same flow through the sources namespace:

SELECT
    o.id AS order_id,
    o.amount,
    c.name AS customer_name
FROM Orders o
LEFT JOIN sources.Customers c
    ON o.customer_id = c.id

sources.Customers means: the retained result of an earlier successful transformation named Customers in the same sequential extraction invocation.

It is not:

• A connection name.
• A destination table.
• A separate flow.
• A request to extract another source on demand.
• A distributed query or external-source federation feature.

Walkthrough: a two-transformation flow

Setup. A flow has two source-to-destination transformations, executed sequentially:

1. Transformation 1, with its Transformation name set to Customers, extracts the customer file.
2. Transformation 2, named Orders, extracts the orders file. Its Source query is the SQL above.

Execution. When the flow runs, transformation 1 completes first and its result is retained in memory for the rest of the invocation. Transformation 2's query then resolves Orders (its own current source) and sources.Customers (the retained earlier result) and joins them.

Why an isolated preview can fail. Test Transformation on transformation 2 alone, or running the query in Explorer, does not execute transformation 1 first — there is no retained Customers result in that isolated run, so the sources.Customers reference fails explicitly. The same query succeeds in the complete sequential flow. To iterate on such a query, run the full flow, or temporarily test the query body against its own source without the sources reference.

Rules

• Names are case-insensitive. Quote names with spaces or punctuation, and continue into nested paths: sources."Customer List".data.items
• The referenced transformation must be earlier in the flow, enabled, successful, completed, and still retained.
• Missing, ambiguous, current/forward, skipped, failed, or discarded results fail explicitly with an error.
• Parallel source extraction does not support cross-source dependencies — the reference fails when sources are extracted in parallel.
• A real input or root node actually named sources takes precedence over the special namespace (existing nested queries keep their meaning). Rename the current root if that conflicts.
• Legacy does not support this namespace; a cross-source reference under Legacy reports that Modern is required.

Script expressions and advanced APIs

Existing script and custom-function expressions in SQL continue to work through the shared expression facilities where supported. These expressions can use calculation-context bindings such as:

• dataSet — the dataset in the current calculation context
• drivingDataSet — the driving dataset
• currentRecord — the record being processed
• groupCount — group size where the context provides it

Important: these are calculation-context bindings, not SQL keywords. They are available where that calculation context exists, not universally in every native operator. In particular:

• Arbitrary scripts do not run inside native correlated predicates.
• A script predicate in WHERE cannot be combined with a subquery in the same WHERE (a script-free WHERE can accompany SELECT subqueries).
• JavaScript functions are not native SQL functions, and the shared expression primitives are not a fallback to the Legacy query engine.

Prefer native dotted navigation, ancestor qualification, joins and subqueries where they express the same intent — they are clearer and they participate in native evaluation. Script expressions remain useful for logic the native subset does not cover.

Example of a legacy-era dynamic navigation expression (deep singleton paths):

SELECT
    dataSet.getFieldValueWithNested(currentRecord, 'company.departments.HR.head.name').getValue() AS hr_head_name
FROM company

The native equivalent for supported singleton paths is plain dotted projection:

SELECT departments.HR.head.name AS hr_head_name
FROM company

Keep script-expression use separate from the native SQL patterns in this article: test any script expression you rely on under the Modern engine before depending on it in production.

Migration option: treat missing JavaScript variables as null (10.x)

Some existing scripts relied on a missing variable name resolving to null. After upgrading, those scripts can fail with javax.script.ScriptException: ReferenceError. (Java 8 Nashorn did not universally return null for undeclared identifiers; older bindings or compatibility wrappers could produce that behavior.)

A compatibility option restores it: open Settings, open the Flows group, enable Treat Missing JavaScript Variables as Null, and save. The default is off. No restart, JVM flags, or configuration-file edits are required; the change applies to subsequent flow runs, transformation tests, and listener preprocessors. Updated Integration Agents receive the value automatically with each request (already-running or queued Agent requests keep the value they were started with). The executing application and Agents must be on a release that includes this option.

With compatibility enabled, an undeclared variable resolves to actual JavaScript null:

com.toolsverse.util.log.Logger.log(com.toolsverse.util.log.Logger.INFO, None, 'test')

Here an undeclared None resolves to null instead of failing. Existing variables, Java imports, and ordinary script errors keep their behavior; a missing function call, or property access on null, still fails.

Use it as a migration aid, not a permanent setting. The fallback also turns misspelled variable names into null, which can conceal defects or unexpected source-data changes. Review the affected scripts, make optional-field handling explicit, test with representative data, then disable the checkbox and validate again.

Performance and memory

Local DataSet SQL is in-memory SQL over materialized data. Plan for that:

• Source extraction may materialize the entire DataSet before the query runs.
• Joins, sorts, groups, DISTINCT, CTEs, window functions and subquery indexes consume additional heap on top of the extracted input.
• Many-to-many joins can greatly expand the result — duplicate keys multiply rows.
• TOP/LIMIT is not a source-size or total-memory cap. It limits the result, not the input.
• A SQL WHERE clause does not necessarily become a remote API or database filter. Prefer connector-side filtering (query parameters, source-side queries, format options) when available and suitable.
• Reduce unnecessary fields and nested materialization where the connector and format support it.
• Use equality joins and equality correlations where appropriate — they are index-eligible; OR/non-equality shapes can require repeated scans.
• Test with representative data volumes, and include nulls, duplicates, missing nodes and empty inputs in the test data.
• Force Streaming concerns the later load path for eligible transformations. It does not turn local SQL into streaming extraction.
• Cancellation is cooperative: it does not guarantee immediate interruption of custom code or external I/O.

Troubleshooting

When a Modern query fails, correct the query or explicitly route that statement to Legacy with a directive where appropriate. No automatic fallback occurs — do not design flows around an expected silent retry on the other engine.

Migration checklist

Use this checklist when moving existing flows to the Modern engine, or validating them after an upgrade:

1. Inventory local DataSet SQL. Find the transformations, lookups and scripted SQL that run against non-relational sources (native database and MongoDB queries are unaffected).
2. Test representative queries under Modern. Include your most complex queries and your most common ones.
3. Check the results — output names and order, values, types, nulls, duplicates, and empty-input behavior. Pay particular attention to the aggregate and set-operation qualifications.
4. Validate extraction settings and nested shape. Streaming and nested-as-string options change what the query sees.
5. Verify the actual execution target, including Agents: a pre-10.0 Agent stays on Legacy regardless of the application's setting.
6. Check memory and elapsed time on representative workloads.
7. Use the narrowest Legacy override necessary — a statement directive first, then a tenant override, then the global setting.
8. Restore Modern after compatibility issues are resolved.