Data Flows - Ocient Documentation

Data flows are procedural control-flow blocks that execute multiple SQL statements sequentially as a single unit in the Ocient® System. You use data flows to build multi-step workflows entirely within the database, eliminating the need for external orchestration tools. Data flows support two modes.

Void data flow — Executes statements for side effects such as creating tables, inserting data, or performing cleanup. A void data flow does not return a result set.
Query data flow — Executes statements and returns a result set using a RETURN SELECT statement.

Use Cases

Data flows are useful for these scenarios:

Stage and Transform Data with Data Flows — Create staging tables, apply transformations across multiple steps, load into final tables, and clean up with the FINALLY clause.
Build Iterative Queries with Data Flows — Use WHILE loops with the MAX ITERATIONS keyword for convergence algorithms, graph traversals, or accumulation patterns.
Validate Data Before Loading with Data Flows — Use IF, ELSEIF, and RAISE keywords as guard clauses to verify data quality before committing a load.

Syntax

This code block shows the complete syntax for data flows.

SQL

-- Void data flow (no result set)
BEGIN DATAFLOW
    <initial_statement_list>
    [FINALLY <statement_list>]
END DATAFLOW;

-- Query data flow (returns a result set)
BEGIN QUERY DATAFLOW
    [<initial_statement_list>]
    RETURN <select_statement>;
    [FINALLY <statement_list>]
END DATAFLOW;

A void data flow requires at least one statement in its body. A query data flow can consist of only a RETURN SELECT statement with no preceding statements. The <initial_statement_list> and <statement_list> placeholders represent the following elements.

Initial Statement List (`<initial_statement_list>`)

Appears at the top level of the data flow body and accepts declarations and flow statements. You can use DECLARE statements only in this list, not inside loops, IF blocks, or a FINALLY clause.

Element	Description
`DECLARE`	Declares a variable with a type and optional initial value.
`SET`	Assigns a value to a previously declared variable.
`CREATE TABLE #name`	Creates a temporary table scoped to the data flow block.
`WHILE`	Repeats statements while a condition is true.
`IF`, `ELSEIF`, and `ELSE`	Executes statements conditionally.
`FOR`	Iterates using C-style or iterator-style loops.
`BREAK`	Exits the innermost enclosing loop.
`CONTINUE`	Skips to the next iteration of the innermost loop.
`RAISE`	Throws a user-defined error and aborts execution.
`TRY` and `EXCEPTION WHEN`	Catches errors raised by statements in the `TRY` block.
`CALL`	Executes a stored procedure.
Any DDL, DML, or DCL statement	For example, `INSERT`, `DELETE`, `CREATE TABLE`, `DROP TABLE`, or `GRANT`.

Statement List (`<statement_list>`)

Appears inside loops, IF branches, and FINALLY clauses. Supports the same elements as the <initial_statement_list> except for the DECLARE element.

Element	Description
`SET`	Assigns a value to a previously declared variable.
`CREATE TABLE #name`	Creates a temporary table scoped to the data flow block.
`WHILE`	Repeats statements while a condition is true.
`IF`, `ELSEIF`, and `ELSE`	Executes statements conditionally.
`FOR`	Iterates using C-style or iterator-style loops.
`BREAK`	Exits the innermost enclosing loop.
`CONTINUE`	Skips to the next iteration of the innermost loop.
`RAISE`	Throws a user-defined error and aborts execution.
`TRY` and `EXCEPTION WHEN`	Catches errors raised by statements in the `TRY` block.
`CALL`	Executes a stored procedure.
Any DDL, DML, or DCL statement	For example, `INSERT`, `DELETE`, `CREATE TABLE`, `DROP TABLE`, or `GRANT`.

The following table describes other structural elements.

Element	Description
`FINALLY`	A clause that always executes after the main body completes, even after an error, that contains its own `<statement_list>`.
`RETURN SELECT`	Returns a result set from a query data flow. Appears only once, after the `<statement_list>` and before `FINALLY`.

Temporary Tables

Temporary tables use a # prefix in their names, and the database scopes them to the data flow block. The database automatically drops temporary tables when the data flow completes, so you do not need an explicit DROP TABLE SQL statement in a FINALLY clause. Syntax

SQL

CREATE TABLE #table_name (column_definitions);

Example This example creates a temporary table with the # prefix and uses it for intermediate processing. The database drops the temporary table automatically when the data flow completes.

SQL

BEGIN DATAFLOW
    CREATE TABLE #temp (id INT, value DOUBLE);
    INSERT INTO #temp SELECT id, amount FROM raw_data WHERE amount > 0;
    INSERT INTO final_table SELECT * FROM #temp;
END DATAFLOW;

BEGIN DATAFLOW

A void data flow executes one or more statements for their side effects. This data flow does not return a result set. Syntax

SQL

BEGIN DATAFLOW
    <initial_statement_list>
    [FINALLY <statement_list>]
END DATAFLOW;

Example This example creates a temporary table (indicated by the # prefix), filters and stages data, and loads it into a final table. The database drops the temporary table automatically when the data flow completes.

SQL

BEGIN DATAFLOW
    CREATE TABLE #staging (id INT, amount DOUBLE);
    INSERT INTO #staging SELECT id, amount FROM raw_orders WHERE amount > 0;
    INSERT INTO orders SELECT * FROM #staging;
END DATAFLOW;

BEGIN QUERY DATAFLOW

A query data flow executes statements and returns a result set. The RETURN SELECT statement must appear after all other statements and before the optional FINALLY clause. Syntax

SQL

BEGIN QUERY DATAFLOW
    [<initial_statement_list>]
    RETURN <select_statement>;
    [FINALLY <statement_list>]
END DATAFLOW;

Example This example declares the total variable, assigns it the row count from the orders table, and returns the result.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @total BIGINT;
    SET @total = (SELECT COUNT(*) FROM orders);
    RETURN SELECT @total;
END DATAFLOW;

DECLARE

Declares a script variable with a specified data type and an optional initial value. You can use DECLARE statements only at the top level of the data flow body, not inside loops, IF clauses, or FINALLY clauses. Syntax

SQL

DECLARE @variable_name data_type [NOT NULL] [= expression];

Parameters

Parameter	Description
`@variable_name`	The variable name, prefixed with `@`.
`data_type`	The data type for the variable. See Supported variable data types.
`NOT NULL`	Optional keyword. Requires that the variable always holds a non-NULL value. If you specify `NOT NULL`, you must also provide an initial value using the `= expression` element.
`= expression`	Optional expression that sets the initial value. If you omit the expression, the variable uses the default value for its data type.

Example This example declares variables with different data types and initialization options and returns their values. The counter variable has the BIGINT type and 0 as the default value. The name variable has the VARCHAR type and default as the default string. Also, the threshold variable has the DOUBLE type and a non-NULL value with 0.95 as the default value.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @counter BIGINT = 0;
    DECLARE @name VARCHAR = 'default';
    DECLARE @threshold DOUBLE NOT NULL = 0.95;
    RETURN SELECT @counter, @name, @threshold;
END DATAFLOW;

Supported Variable Data Types

This table lists all data types supported for data flow variables. For details on Ocient data types, see Data Types.

Type	Default Value	Initialization Example
`BIGINT`	`0`	`DECLARE @v BIGINT = 42`
`INT`	`0`	`DECLARE @v INT = 42`
`SMALLINT`	`0`	`DECLARE @v SMALLINT = 100`
`BYTE`	`0`	`DECLARE @v BYTE = 10`
`DOUBLE`	`0.0`	`DECLARE @v DOUBLE = 3.14`
`FLOAT`	`0.0`	`DECLARE @v FLOAT = 1.5`
`DECIMAL`	Empty	`DECLARE @v DECIMAL(10,2) = 123.45`
`BOOLEAN`	`FALSE`	`DECLARE @v BOOLEAN = TRUE`
`STRING`	`''` (empty)	`DECLARE @v STRING = 'hello'`
`BINARY`	Empty	`DECLARE @v BINARY = BINARY('0xDEADBEEF')`
`HASH`	Empty	`DECLARE @v HASH = HASH('0x01020304', 4)`
`DATE`	Empty	`DECLARE @v DATE = DATE '2024-01-15'`
`TIMESTAMP`	Empty	`DECLARE @v TIMESTAMP = TIMESTAMP '2024-06-15 10:30:00'`
`TIME`	Empty	`DECLARE @v TIME = TIME '14:30:00'`
`UUID`	Empty	`DECLARE @v UUID = UUID '550e8400-e29b-41d4-a716-446655440000'`
`IP`	Empty	`DECLARE @v IP = IP '::1'`
`IPV4`	Empty	`DECLARE @v IPV4 = IPV4 '192.168.1.1'`
`ST_POINT`	Empty	Using `ST_POINT(x, y)` in queries
`ST_LINESTRING`	Empty	Using `ST_LINESTRING('...')` WKT in queries
`ST_POLYGON`	Empty	Using `ST_POLYGON('...')` WKT in queries
`ARRAY(<type>)`	Uninitialized	`DECLARE @v ARRAY(INT) = ARRAY[1,2,3]`
`TUPLE(<types>)`	Uninitialized	`DECLARE @v TUPLE(INT, VARCHAR(100)) = TUPLE(1,'x')`

Type synonyms: CHAR and VARCHAR map to STRING. INTEGER maps to INT. TINYINT maps to BYTE.

Data flow variables do not support the VARBINARY, MATRIX, and FUNCTION data types.

You must initialize container variables (ARRAY and TUPLE) before use. Referencing an uninitialized container variable in an expression produces a runtime error.

SET

Assigns a new value to a previously declared variable. Syntax

SQL

SET @variable_name = expression;

The expression can be a literal, an arithmetic expression, or a scalar subquery. Example This example assigns a variable using an arithmetic expression and then reassigns it using a scalar subquery.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @x BIGINT = 5;
    SET @x = @x + 1;
    SET @x = (SELECT @x * 3);
    RETURN SELECT @x;
END DATAFLOW;

Output: 18

WHILE

Repeats a block of statements while a condition evaluates to true. You can use the optional MAX ITERATIONS clause to set an upper bound on the number of iterations. Syntax

SQL

WHILE (condition) [MAX ITERATIONS integer] DO
    <statement_list>
END WHILE;

Parameters

Parameter	Description
`condition`	A Boolean expression or a `SELECT` statement that evaluates to a Boolean value.
`integer`	Optional. The maximum number of loop iterations. If the loop exceeds this limit, the system throws the `maximum iterations` error.

Example This example uses a WHILE loop with the MAX ITERATIONS clause to sum integers from one to 10.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @i BIGINT = 0;
    DECLARE @sum BIGINT = 0;
    WHILE (@i < 10) MAX ITERATIONS 100 DO
        SET @i = @i + 1;
        SET @sum = @sum + @i;
    END WHILE;
    RETURN SELECT @sum;
END DATAFLOW;

IF, ELSEIF, and ELSE

Executes statements conditionally. The data flow evaluates conditions in order and executes the first matching branch. You can chain zero or more ELSEIF clauses. The optional ELSE clause executes if no condition matches. Each branch contains a statement list <statement_list>. Syntax

SQL

IF (condition) THEN
    <statement_list>
[ELSEIF (condition) THEN
    <statement_list>]
[ELSE
    <statement_list>]
END IF;

Parameters

Parameter	Description
`condition`	A Boolean expression or a `SELECT` statement that evaluates to a Boolean value.

Example This example uses chained ELSEIF clauses to map a numeric value to a string label.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @x BIGINT = 2;
    DECLARE @result VARCHAR;
    IF (@x = 1) THEN
        SET @result = 'one';
    ELSEIF (@x = 2) THEN
        SET @result = 'two';
    ELSEIF (@x = 3) THEN
        SET @result = 'three';
    ELSE
        SET @result = 'other';
    END IF;
    RETURN SELECT @result;
END DATAFLOW;

Output: two

FOR

Iterates using either C-style or iterator-style syntax. Both styles support the optional MAX ITERATIONS clause. The loop body contains a statement list <statement_list>.

C-Style FOR

Syntax

SQL

FOR (SET @var = init; condition; SET @var = increment) [MAX ITERATIONS integer] DO
    <statement_list>
END FOR;

Parameters

Parameter	Description
`@var`	A previously declared variable that the system uses as the loop counter.
`init`	An expression that sets the initial value of `@var`.
`condition`	A Boolean expression that the system evaluates before each iteration. The loop continues while the condition is `true`.
`increment`	An expression that updates `@var` after each iteration.
`integer`	Optional. The maximum number of loop iterations. If the loop exceeds this limit, the system throws the `maximum iterations` error.

The increment statement executes after each iteration, including iterations that execute the CONTINUE element. Example This example uses a C-style FOR loop to sum integers from one to five.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @i BIGINT;
    DECLARE @sum BIGINT = 0;
    FOR (SET @i = 1; @i <= 5; SET @i = @i + 1) MAX ITERATIONS 100 DO
        SET @sum = @sum + @i;
    END FOR;
    RETURN SELECT @sum;
END DATAFLOW;

Output: 15

Iterator-Style FOR

Iterates over the rows returned by a query. The source query must return exactly one column. Syntax

SQL

FOR @var IN (<select_statement>) [MAX ITERATIONS integer] DO
    <statement_list>
END FOR;

Parameters

Parameter	Description
`@var`	A previously declared variable that receives the value of each row.
`<select_statement>`	A query that returns exactly one column. The loop iterates once per row.
`integer`	Optional. The maximum number of loop iterations. If the loop exceeds this limit, the system throws the `maximum iterations` error.

Example This example iterates over the rows returned by a query and accumulates a sum.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @val BIGINT;
    DECLARE @sum BIGINT = 0;
    FOR @val IN (SELECT c1 FROM sys.dummy10) DO
        SET @sum = @sum + @val;
    END FOR;
    RETURN SELECT @sum;
END DATAFLOW;

Output: 55

BREAK

Immediately exits the innermost enclosing loop (WHILE or FOR). In nested loops, the BREAK element exits only the innermost loop. Using the BREAK element outside a loop produces a runtime error. Syntax

SQL

BREAK;

Example This example exits a WHILE loop early when the counter reaches 3.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @i BIGINT;
    WHILE (true) MAX ITERATIONS 100 DO
        SET @i = @i + 1;
        IF (@i = 3) THEN
            BREAK;
        END IF;
    END WHILE;
    RETURN SELECT @i;
END DATAFLOW;

Output: 3

CONTINUE

Skips the remainder of the current loop iteration and proceeds to the next iteration. In C-style FOR loops, the increment statement still executes before the next condition check. Using the CONTINUE element outside a loop produces a runtime error. Syntax

SQL

CONTINUE;

Example This example skips the iteration where @i equals 3, so the sum excludes that value.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @i BIGINT = 0;
    DECLARE @sum BIGINT = 0;
    WHILE (@i < 5) MAX ITERATIONS 100 DO
        SET @i = @i + 1;
        IF (@i = 3) THEN
            CONTINUE;
        END IF;
        SET @sum = @sum + @i;
    END WHILE;
    RETURN SELECT @sum;
END DATAFLOW;

Output: 12 (1 + 2 + 4 + 5; skips the iteration where @i = 3)

RAISE

Throws a user-defined error message and aborts the data flow. If the FINALLY clause is present, the database executes it before the error propagates. Syntax

SQL

RAISE [expression];

Parameters

Parameter	Description
`expression`	Optional. A `VARCHAR` expression containing the error message. If you omit the expression, the system raises a generic error. A NULL or non-string value produces an `INVALID_ARGUMENT` error.

Example This example checks the row count in a staging table and raises an error if the table contains no records.

SQL

BEGIN DATAFLOW
    DECLARE @count BIGINT = (SELECT COUNT(*) FROM staging_table);
    IF (@count = 0) THEN
        RAISE 'No records found in staging table';
    END IF;
    INSERT INTO target_table SELECT * FROM staging_table;
FINALLY
    DROP TABLE IF EXISTS staging_table;
END DATAFLOW;

TRY and EXCEPTION WHEN

Catches errors raised by statements in a TRY element. You define one or more EXCEPTION WHEN handlers that match the specific SQLSTATE code or all errors using the OTHERS string. Inside a handler, the @SQLERRM and @SQLSTATE variables expose the caught error message and SQLSTATE code. Both the TRY body and each handler contain a statement list <statement_list>. Syntax

SQL

TRY
    <statement_list>
EXCEPTION WHEN match_spec THEN
    <statement_list>
[EXCEPTION WHEN match_spec THEN
    <statement_list>]
[...]
END TRY;

Parameters

Parameter	Description
`match_spec`	Either the `OTHERS` string to catch all errors, or a string literal containing the specific SQLSTATE code (for example, `'22012'`).

Example This example catches any error from an INSERT SQL statement and re-raises it.

RAISE @SQLERRM forwards the original error message but not the original SQLSTATE code. The re-raised error uses a generic exception code.

SQL

BEGIN DATAFLOW
    TRY
        INSERT INTO target_table SELECT * FROM staging_table;
    EXCEPTION WHEN OTHERS THEN
        RAISE @SQLERRM;
    END TRY;
END DATAFLOW;

FINALLY

Defines a cleanup statement that always executes when the data flow completes, regardless of whether an error occurred. Use the FINALLY clause to drop staging tables, release resources, or perform other cleanup operations that must execute even after an error. Syntax

SQL

BEGIN DATAFLOW
    <statement_list>
FINALLY
    <statement_list>
END DATAFLOW;

The FINALLY clause executes even after a RAISE statement or other runtime error. Example This example uses the FINALLY clause to ensure that the data flow drops the staging table even if an error occurs during processing. Unlike temporary tables (which the database drops automatically), regular staging tables require explicit cleanup.

SQL

BEGIN DATAFLOW
    INSERT INTO final_scores
        SELECT id, score FROM staging_scores WHERE score > 0.5;
FINALLY
    DROP TABLE IF EXISTS staging_scores;
END DATAFLOW;

RETURN

Returns a result set from a query data flow. The RETURN clause must appear after all other statements and before the optional FINALLY clause. Only query data flows (using BEGIN QUERY DATAFLOW) support the RETURN clause. Syntax

SQL

RETURN <select_statement>;

Example This example filters predictions into the temporary table #filtered and returns the result set sorted by the confidence column.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @threshold DOUBLE = 0.9;
    CREATE TABLE #filtered (id INT, confidence DOUBLE);
    INSERT INTO #filtered SELECT id, confidence FROM predictions WHERE confidence >= @threshold;
    RETURN SELECT * FROM #filtered ORDER BY confidence DESC;
END DATAFLOW;

Stage and Transform Data with Data Flows Build Iterative Queries with Data Flows Validate Data Before Loading with Data Flows Data Query Language (DQL) Statement Reference Data Definition Language (DDL) Statement Reference Transactions

​Use Cases

​Syntax

​Initial Statement List (<initial_statement_list>)

​Statement List (<statement_list>)

​Temporary Tables

​BEGIN DATAFLOW

​BEGIN QUERY DATAFLOW

​DECLARE

​Supported Variable Data Types

​SET

​WHILE

​IF, ELSEIF, and ELSE

​FOR

​C-Style FOR

​Iterator-Style FOR

​BREAK

​CONTINUE

​RAISE

​TRY and EXCEPTION WHEN

​FINALLY

​RETURN

​Related Links

Use Cases

Syntax

Initial Statement List (`<initial_statement_list>`)

Statement List (`<statement_list>`)

Temporary Tables

BEGIN DATAFLOW

BEGIN QUERY DATAFLOW

DECLARE

Supported Variable Data Types

SET

WHILE

IF, ELSEIF, and ELSE

FOR

C-Style FOR

Iterator-Style FOR

BREAK

CONTINUE

RAISE

TRY and EXCEPTION WHEN

FINALLY

RETURN

Related Links