Build Iterative Queries with Data Flows

The examples demonstrate how to use data flows to execute iterative computations within the database. You use WHILE and FOR loops to implement patterns such as accumulation, graph traversal, and convergence algorithms. Data flows provide loop constructs that are especially useful when a computation requires repeated passes over data, such as propagating values through a graph or refining an estimate until it stabilizes. The RETURN SELECT statement in a query data flow returns the final result set to the caller. For details about data flow syntax and supported statements, see Data Flows.

Prerequisites

Before you begin, ensure that you have:

A running Ocient deployment with access to execute SQL statements.
The CREATE TABLE and INSERT privileges on the target schema.

Accumulation with a `WHILE` Loop Example

This example computes a running factorial using a WHILE loop. The example demonstrates the basic loop pattern with variable manipulation and a safety limit.

Execute the Query Data Flow

Execute this query data flow to compute the factorial of 10. The MAX ITERATIONS clause acts as a safety limit to prevent infinite loops if the loop never satisfies the termination condition.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @n BIGINT = 10;
    DECLARE @i BIGINT = 1;
    DECLARE @result BIGINT = 1;

    WHILE (@i <= @n) MAX ITERATIONS 100 DO
        SET @result = @result * @i;
        SET @i = @i + 1;
    END WHILE;

    RETURN SELECT @result AS factorial;
END DATAFLOW;

This data flow returns a single row containing the factorial of 10 (3628800).

Graph Traversal with Iterative Expansion Example

This example finds all nodes reachable from a starting node in a directed graph by iteratively expanding the frontier of discovered nodes. Each iteration discovers new neighbors and adds them to the set of reached nodes.

Create the Graph Table

Create a table representing a directed graph with edges between nodes.

SQL

CREATE TABLE edges (
    src INT,
    dst INT
);

Insert Sample Data

Insert edges representing a simple directed graph.

SQL

INSERT INTO edges VALUES
    (1, 2), (1, 3), (2, 4), (3, 4),
    (4, 5), (5, 6), (6, 7), (7, 8),
    (10, 11), (11, 12);

Execute the Traversal Data Flow

Execute this query data flow to find all nodes reachable from node 1.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @start_node INT = 1;
    DECLARE @prev_count BIGINT = 0;
    DECLARE @curr_count BIGINT = 0;

    -- Initialize the reached set with the starting node
    CREATE TABLE #reached (node_id INT);
    INSERT INTO #reached VALUES (@start_node);

    -- Iteratively expand the frontier
    WHILE (true) MAX ITERATIONS 100 DO
        -- Add newly discovered neighbors
        INSERT INTO #reached
            SELECT DISTINCT e.dst
            FROM edges e
            INNER JOIN #reached r ON e.src = r.node_id
            WHERE e.dst NOT IN (SELECT node_id FROM #reached);

        -- Check if the set grew
        SET @curr_count = (SELECT COUNT(*) FROM #reached);
        IF (@curr_count = @prev_count) THEN
            BREAK;
        END IF;
        SET @prev_count = @curr_count;
    END WHILE;

    RETURN SELECT node_id FROM #reached ORDER BY node_id;
END DATAFLOW;

This data flow returns all nodes reachable from node 1: 1, 2, 3, 4, 5, 6, 7, and 8. Nodes 10, 11, and 12 are not reachable from node 1 and do not appear in the results.

Convergence Algorithm Example

This example implements an iterative averaging algorithm that converges to a stable value. Each iteration computes the average of the current value and a target, stopping when the change between iterations falls below a threshold.

Execute the Convergence Data Flow

Execute this query data flow to iterate until the value converges.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @value DOUBLE = 100.0;
    DECLARE @target DOUBLE = 42.0;
    DECLARE @threshold DOUBLE = 0.001;
    DECLARE @prev_value DOUBLE = 0.0;
    DECLARE @iterations BIGINT = 0;

    WHILE (true) MAX ITERATIONS 1000 DO
        SET @prev_value = @value;
        SET @value = (@value + @target) / 2.0;
        SET @iterations = @iterations + 1;

        IF ABS(@value - @prev_value) < @threshold THEN
            BREAK;
        END IF;
    END WHILE;

    RETURN SELECT @value AS converged_value, @iterations AS iterations;
END DATAFLOW;

Output: 42.0009, 16 (converged value and iteration count)

Iterator-Style FOR Loop Example

This example uses an iterator-style FOR loop to iterate over query results and accumulate a sum.

Execute the Iterator Data Flow

Execute this query data flow to sum values from a query result.

SQL

BEGIN QUERY DATAFLOW
    DECLARE @val BIGINT;
    DECLARE @total BIGINT = 0;

    FOR @val IN (SELECT c1 FROM sys.dummy10) DO
        SET @total = @total + @val;
    END FOR;

    RETURN SELECT @total AS row_sum;
END DATAFLOW;

Output: 55The iterator-style FOR loop executes once per row returned by the SELECT statement. The source query must return exactly one column.

Data Flows Stage and Transform Data with Data Flows Validate Data Before Loading with Data Flows Data Query Language (DQL) Statement Reference

​Prerequisites

​Accumulation with a WHILE Loop Example

​Graph Traversal with Iterative Expansion Example

​Convergence Algorithm Example

​Iterator-Style FOR Loop Example

​Related Links

Prerequisites

Accumulation with a `WHILE` Loop Example

Graph Traversal with Iterative Expansion Example

Convergence Algorithm Example

Iterator-Style FOR Loop Example

Related Links