> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Flows

> Learn how to use data flows in Ocient to execute procedural SQL blocks with variables, control flow, temporary tables, and error handling.

export const Ocient = "Ocient®";

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](/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](/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](/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 SQL theme={null}
-- 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>`](#initial_statement_list) and [`<statement_list>`](#statement_list) placeholders represent the following elements.

<h3 id="initial_statement_list">
  Initial Statement List (`<initial_statement_list>`)
</h3>

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`. |

<h3 id="statement_list">
  Statement List (`<statement_list>`)
</h3>

Appears inside loops, `IF` branches, and `FINALLY` clauses. Supports the same elements as the [`<initial_statement_list>`](#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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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](#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 SQL theme={null}
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](/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`.

<Note>
  Data flow variables do not support the `VARBINARY`, `MATRIX`, and `FUNCTION` data types.
</Note>

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

## SET

Assigns a new value to a previously declared variable.

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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>`](#statement_list).

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
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>`](#statement_list).

### C-Style FOR

**Syntax**

```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
BREAK;
```

**Example**

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

```sql SQL theme={null}
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 SQL theme={null}
CONTINUE;
```

**Example**

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

```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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](https://en.wikipedia.org/wiki/SQLSTATE) 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>`](#statement_list).

**Syntax**

```sql SQL theme={null}
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.

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

```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
RETURN <select_statement>;
```

**Example**

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

```sql SQL theme={null}
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;
```

## Related Links

[Stage and Transform Data with Data Flows](/stage-and-transform-data-with-data-flows)

[Build Iterative Queries with Data Flows](/iterative-queries-with-data-flows)

[Validate Data Before Loading with Data Flows](/validate-data-before-loading-with-data-flows)

[Data Query Language (DQL) Statement Reference](/data-query-language-dql-statement-reference)

[Data Definition Language (DDL) Statement Reference](/data-definition-language-ddl-statement-reference)

[Transactions](/transactions)