Execute any SQL

When to use this Flow type

In Etlworks, it is possible to execute SQL directly against a connection — without a source-to-destination transformation. On a database Connection, that means any SQL statement the database supports (including DDL and anonymous blocks) or a list of ;-separated statements. Starting with 10.0.9, the same flow can also:

• maintain a managed High Watermark window for incremental ELT on relational connections, and
• run the existing MongoDB SQL dialect against a regular MongoDB connection (SELECT, UPDATE, DELETE only — not arbitrary SQL or DDL).

Create Flow

Step 1. Start creating an SQL Flow by creating a database Connection.

Step 2. Continue by opening the Flows window, clicking Add flow, and typing execute SQL in the Select Flow Type box:

Step 3. Select the destination Connection created in step 1.

Step 4. Select the Parameters tab and enter the SQL to execute and other parameters:

Parameters:

• SQL: SQL to execute. You can enter multiple ; delimited SQL statements. If the SQL is an Anonymous Block Statement, the ; is used as a part of the block and not as a statement separator.
• SQL is Anonymous Block Statement : if this option is enabled, the SQL is treated as an executable statement that can contain control statements, variables, and other SQL statements. It can be used to implement procedural logic in a scripting language. By default, it is assumed that the SQL is not an anonymous block statement, so the parameter must be explicitly set.

An example of the anonymous block statement (TSQL):

• DECLARE 
     @param1 varchar(100);
  BEGIN
     set @param1 = '_xyz';
  
     insert into loop_test (name) 
     values (CONCAT('{name}', @param1));
  END;   
  
  

• Auto Commit: if this option is enabled (the default), the transaction will be automatically committed when this task completes. It does not enable JDBC auto-commit on the connection itself — for atomic multi-statement SQL, the underlying connection must also use an appropriate transaction configuration. Must stay selected for MongoDB SQL and for managed High Watermark.
• Open new connection: enable this option if you want the flow to open a new database connection on each run. It is specifically useful if you want to execute the SQL flow in a loop that updates database connection parameters on each iteration.
• Log executed SQL statement: enable this flag if you want to log executed SQL statements.
• Script: the JavaScript or Python program that will be executed before executing SQL. You can use it to set the Flow variables that can be referenced as {parameters} in SQL. You can also use it to conditionally disable the SQL Flow.
• Flow variables: the static flow variables. Variables can be references in SQL as {variable_name}.

Exception handling

• On Exception: by default, any error causes the halt of execution. When ignore is selected, the errors will be ignored, and execution will continue. Read how to configure the Flow to ignore all or specific exceptions.
• Exception Mask: you can specify what errors should be ignored and still halt the execution for all other errors. Enter part or all of the exception string. This field works only when the Ignore on Exception option is selected.
• Execute on Exception: a JavaScript program that will be executed in case of any error regardless of whether this error is ignored by the flow or not. Read more. 
• Rollback on Exception: some databases (for example, PostgreSQL) require transaction rollback in case of any exception. This is a relational setting — it does not provide transaction semantics for MongoDB SQL.

Flow control

• Execute if Error: if this option is selected, Etlworks will execute this SQL if an error occurs. It could be useful if, for example, you want to clean up a database after a failed data integration Flow.

Managed High Watermark for SQL flows (10.0.9+)

ELT patterns run SQL inside one database or warehouse — INSERT ... SELECT, MERGE, UPDATE — and need to process only the window of changes since the last successful run. Previously you had to track the last watermark yourself with variables, scripts, or a control table. With Enable managed High Watermark (in the flow's Change Replication category, off by default), the flow manages a durable checkpoint for you; you still write the SQL that filters a bounded window. No control table or schema change is required.

Calculating versus remembering. The saved lower bound records which window completed successfully last time. The High Watermark Query calculates the next upper bound before the main SQL runs, and the same fixed upper bound applies across the whole script. After the SQL succeeds and commits, that upper bound is saved as the next run's lower bound. Recalculating MAX(...) on every run by itself cannot tell you what was already processed — that is exactly what the checkpoint remembers.

Settings (Change Replication category)

• Enable managed High Watermark (off by default): explicit opt-in; existing flows keep their behavior. Relational JDBC connections only — rejected for MongoDB.
• High Watermark Query: required when enabled. Runs before the main SQL, on the same connection, and must return at most one row with exactly one column — the upper bound. It may reference unquoted {HIGH_WATERMARK} and ordinary flow variables.
• High Watermark Data Type: Number, Timestamp (default), Timestamp with time zone, Date, or String. Number retains decimal precision; Timestamp retains fractional seconds; Timestamp with time zone uses ISO-8601 with an offset and compares instants; String uses lexical ordering. Changing the type of an existing checkpoint fails — use a new state key for a deliberately independent sequence.
• Initial High Watermark: required on the first run of each new state key. An unquoted literal matching the type (for example 0 for Number, 2020-01-01 00:00:00 for Timestamp). Ignored once a checkpoint exists. Flow variables can supply it.
• Watermark State Key (default: default): separates independent checkpoint sequences within this SQL flow. With dynamic connections or loops, use flow variables in the key to distinguish each independent source, tenant, or partition — the default key is not automatically partitioned by whatever database or table the run happens to touch.

Auto Commit must be selected and the flow must execute synchronously.

Tokens and a complete example

The main SQL must use both reserved tokens, unquoted, in executable SQL:

• {HIGH_WATERMARK} — the saved lower bound (or Initial High Watermark on the first run).
• {HIGH_WATERMARK_UPPER} — the upper bound captured for this run.

They are typed JDBC bound values, not text fragments: do not put them inside string literals, identifiers, comments, dollar-quoted block bodies, or dynamically assembled SQL strings. The HWM query may use {HIGH_WATERMARK} but not {HIGH_WATERMARK_UPPER}. Ordinary flow variables keep working as plain substitution.

PostgreSQL example (assumes warehouse.orders.order_id has a unique or primary-key constraint). Settings: type Timestamp, Initial High Watermark 2020-01-01 00:00:00. High Watermark Query:

SELECT MAX(updated_at) FROM staging.orders;

Main SQL:

INSERT INTO warehouse.orders (order_id, amount, updated_at)
SELECT order_id, amount, updated_at
FROM staging.orders
WHERE updated_at > {HIGH_WATERMARK}
  AND updated_at <= {HIGH_WATERMARK_UPPER}
ON CONFLICT (order_id) DO UPDATE
SET amount = EXCLUDED.amount,
    updated_at = EXCLUDED.updated_at;

The window is (lower, upper]: strictly greater than the saved watermark, up to and including the captured upper bound. Fixing the upper bound before execution keeps this run's window distinct from the next one's, even while new rows keep arriving. ON CONFLICT is PostgreSQL syntax — use your warehouse's own idempotent MERGE/upsert equivalent. The product does not add predicates for you and does not discover the watermark column: the window filter is your SQL.

Operational notes

• An empty, NULL, or unchanged upper bound skips the main script and preserves the state. Multiple rows or columns from the HWM query, or an upper bound lower than the saved watermark, fail the task.
• Failed SQL never advances the checkpoint — even when the outer exception policy lets the surrounding workflow continue, this SQL window stops. The checkpoint reflects this SQL task's own successful completion, not the eventual success of a parent workflow.
• With JDBC auto-commit off on the connection, a pre-commit SQL failure triggers rollback. With it on — or with SQL that commits implicitly or explicitly — earlier changes can already be durable. Leave transaction-control statements out of managed SQL.
• At-least-once, not exactly-once: the SQL commit and the checkpoint save are not one distributed transaction. A crash or cancellation after commit but before the checkpoint persists can replay the window on the next run — write idempotent SQL (MERGE/upsert), as in the example. There are no automatic retries.
• Scalar watermarks need a complete, monotonically advancing change domain. Late commits with older or equal timestamps, and source deletes, are not captured automatically — no arbitrary timestamp column is guaranteed to miss nothing.
• The checkpoint is persisted alongside metrics on the executing server or Agent, separately from normal metric values — include it in application-data backups. Independent data folders and Agents have independent state; moving execution storage or changing the flow identity requires deliberately migrating or re-initializing the state (there is no reset button — change the state key or initial value deliberately).
• Concurrent runs using the same storage and state key are rejected. This is per execution storage, not cluster-wide locking across independent servers.
• Requires engine/Agent 10.0.9+. Older or unknown Agent versions are rejected when the feature is enabled rather than silently running the SQL unmanaged.
• Anonymous-block support remains driver-specific, and enabling managed HWM does not make token binding possible inside quoted procedural block text.

Run MongoDB SQL from this flow (10.0.9+)

The Execute SQL flow also accepts the regular MongoDB and MongoDB document connection types (not Mongo CDC connections) and runs the same SQL-to-MongoDB translator used by source queries and Explorer. It is not a MongoDB SQL server and not unrestricted relational SQL.

Setup: create or select a regular MongoDB connection, create an Execute SQL flow, choose that connection, enter supported SQL, keep Auto Commit selected, save, and run or schedule it. Requires engine/Agent 10.0.9+.

The connection selects the database; collection names come from the SQL, so sequential statements can target different collections in that database (a collection configured on the connection does not override the SQL). Example:

UPDATE orders SET status = 'archived' WHERE customer_id = 123;
DELETE FROM order_audit WHERE customer_id = 123;
SELECT COUNT(*) FROM orders WHERE status = 'archived';

Statements separated by semicolons run sequentially (quoted semicolons are not boundaries). Flow and schedule variables, the Script before SQL option, and Log executed SQL statement work as usual — variables are ordinary text substitution here, not the JDBC-typed HWM binding.

Results: SELECT results are consumed incrementally and cursors are closed — the flow does not create an output dataset or export documents. Logs report result-row or scalar counts, and affected-document counts for writes. To map or export documents, use a MongoDB source transformation; to inspect them, use Explorer (whose display limit does not truncate Execute SQL execution).

Boundaries:

• Supported commands: SELECT, UPDATE, DELETE within the existing dialect — see Using SQL with MongoDB for supported projections, predicates, and aggregations. No INSERT, no DDL, no anonymous blocks, and no native Mongo shell or Java-driver code in this input.
• Writes target a single collection, with SET assignments for UPDATE and an optional WHERE. Unsupported write modifiers (LIMIT, ORDER BY, JOIN, USING, RETURNING) are rejected. Without a WHERE, an UPDATE or DELETE affects all documents in the collection.
• The whole expanded script is parsed and validated before connecting — which does not prevent later permission, network, or server-side failures.
• Statements execute independently: there is no shared multi-statement transaction, Auto Commit is required, earlier writes are not rolled back when a later statement fails, and Rollback on Exception does not apply. The first runtime statement error stops the remainder of the script; outer Ignore handling lets the surrounding workflow continue but does not resume the remaining Mongo statements. No automatic write retry.
• UPDATE and DELETE require an acknowledged write concern; unacknowledged writes are rejected.
• Managed High Watermark is JDBC-only and is rejected for MongoDB.
• A fresh execution-owned client is opened and closed on every run, regardless of the Open new connection checkbox. Cleanup covers success, failure, and cooperative cancellation; a blocked driver call still follows the driver's own timeout behavior.
• Mongo execution is selected from a concrete MongoDB connection type; do not rely on a generic Dynamic connection automatically switching between JDBC and Mongo dialects.