Inside OcientAIQ™: Dataflows in a unified data platform

By Jason Arnold, Co-Founder and Distinguished Engineer

Welcome back. Over this series we have looked at casting and timestamps, arrays, tuples, graph analytics and more. Most of those posts were about the shape of the data or a feature built for a particular use case that a data engineer might try exploring in a new way. Today I’d like to share something that we’ve been working on that is more like a small programming language that lives inside the database: dataflows.

TLDR Summary

• Dataflows in OcientAIQ bring imperative programming to SQL, letting you run loops, variables, conditionals, and exception handling as a single ad hoc SQL statement, without creating stored procedures.
• They keep complex processing inside the database, eliminating application round-trips while automatically managing temporary data, memory, query planning, and cleanup.
• The result is a simpler way to build iterative, multi-step analytics that scales efficiently and runs through the same SQL interfaces your applications already use.

A dataflow is the thing you reach for when a single declarative SELECT cannot express what you need, and you do not want to drag the data back to the application to loop over it. If you have ever written a stored procedure, the mental model will feel familiar, with one big difference that I will get to immediately, because it is the whole point.

An anonymous, ad-hoc stored procedure

Here is the one-sentence version: a dataflow is an anonymous, imperative SQL script that an application submits exactly like a query.

That sentence is doing a lot of work, so let me unpack it against what you already know about stored procedures. In most databases a stored procedure is a persistent catalog object. Someone with the right privileges has to CREATE PROCEDURE it ahead of time, it lives in the catalog forever until someone drops it, and the client usually has to invoke it through a specialized CALL protocol that is different from how it sends a normal query.

A dataflow is none of those things. It is:

• Anonymous. It has no name and is not stored anywhere. There is no CREATE step and no catalog object to manage or clean up.

• Ad-hoc. The client sends it as a plain SQL string, on the same connection, through the same driver call that it already uses for SELECT. Nothing special is required of the JDBC or ODBC layer.

• Ephemeral. It is parsed, compiled, and executed as a single request, and then it vanishes. Any scratch tables it created disappear with it.

There are two flavors, and the distinction matters:

A BEGIN QUERY DATAFLOW ends in a RETURN and hands rows back to the caller, so the application submits it the way it submits a SELECT. A BEGIN DATAFLOW (no QUERY) returns nothing, so it is the right shape for the work that would otherwise be an INSERT, DELETE, or a multi-step maintenance routine. In both cases the client is doing an ordinary submit. It never learns that the server ran a loop.

Pro Tip: This is why dataflows compose so well with tools you do not control. A BI tool, an ORM, or a reporting layer that only knows how to send a query string and read a result set can drive a BEGIN QUERY DATAFLOW without any awareness that it is running procedural logic on the other side. You are not limited to what the client library exposes.

Something you cannot write as one query

Let me set up a tiny graph with the examples so you have something to chew on: cities, and the (two-way) roads between them.

A good example has to earn its keep. Plain reachability (“what can I get to from here”) is the textbook use of a recursive common table expression, and if that is all you need you should reach for WITH RECURSIVE, not a hand-written dataflow. So let me pick something a recursive CTE genuinely cannot express: an iterative numeric computation that runs until it converges. PageRank is the classic one. It starts every vertex with an equal score and repeatedly pushes each vertex’s score out along its edges, stopping when the scores stop moving, that is, when the total change from one pass to the next drops below a small epsilon.

The stop condition is the whole point. Standard SQL recursion terminates when the recursive step produces no new rows, or at a fixed depth. It has no way to say “keep going until this floating-point number gets small enough.” A dataflow does, because the convergence test is just scalar arithmetic on a variable in the loop guard:

The client submitted one string and got back a ranked list:

Indianapolis comes out on top because it is the interior hub every route seems to pass through. No procedure was created, no CALL protocol was used, and the #rank, #outdeg, and #newrank scratch tables were gone the moment the rows came back.

That @dangling line is worth a second look, because it is the kind of thing the procedural surface makes easy. A vertex with no outgoing edge has nowhere to send its score, so a naive push loses a little rank mass every pass and the scores drift below where they should be. Capturing that mass into a scalar and folding it back into the next iteration keeps the total stable. It is exactly the sort of per-iteration bookkeeping a declarative recursive query has no place to put.

A few things in that script are worth naming, because they are the building blocks.

Variables, and every data type is fair game

Variables are declared with a @ prefix, are strongly typed, and can have an initializer. As of the latest release they can also carry a NOT NULL constraint, which is handy when a null would only ever indicate a bug in your own logic. An initializer can even be a scalar subquery, which is how @n above got the vertex count:

You can declare a variable anywhere at the outermost level of the dataflow, interleaved with the executable statements; you do not have to cluster all the declarations at the very top. The one restriction is that a DECLARE has to live at that outermost level and not inside a nested block, so declaring a variable in the body of an IF or a WHILE is not allowed. You update a variable with SET, and you can pull a scalar out of the data plane into one with a subquery, which is exactly what the convergence check does:

The important part is that variables are not limited to integers and doubles. A dataflow variable can hold any OcientAIQ data type, and when it is substituted back into SQL the platform formats it correctly for that type. A DATE variable expands to a DATE literal, a TIMESTAMP to a TIMESTAMP literal, and the same is true for TIME, UUID, IP, IPV4, DECIMAL, BINARY, the geospatial ST_POINT / ST_LINESTRING / ST_POLYGON types, and even the container types ARRAY and TUPLE we covered earlier in this series. You do not have to hand-format any of it. If OcientAIQ has a type, a dataflow variable can carry it and splice it back into a statement as a valid literal.

Three ways to expand a variable

This is my favorite detail, and it is the part that turns dataflows from “a loop” into “a way to generate SQL.” A variable reference can be expanded in three different modes, chosen by the sigil you put in front of the name.

• @var is value mode. The variable’s value is substituted as a properly formatted, properly quoted SQL literal for its type. This is what you want almost all the time: WHERE id >= @min_id becomes WHERE id >= 3, and if @day is a DATE then WHERE d = @day becomes WHERE d = DATE '2026-07-01'.

• @@var is identifier mode. The variable’s string value is substituted as a double-quoted SQL identifier, with internal quotes escaped for you. This is how you parameterize the name of a table, column, or schema chosen at runtime.

• @!var is raw mode. The value is substituted as raw text with no quoting at all. This is the escape hatch for when the variable holds a fragment of SQL you assembled yourself, such as a column list.

Here is one small void dataflow that uses all three to snapshot a runtime-chosen set of columns from a runtime-chosen table:

That compiles to SELECT id, name FROM jason."cities" WHERE id >= 3 and writes the matching rows into jason.snapshot. The value mode put a safe literal in the predicate, the identifier mode turned a string into the actual table name, and the raw mode dropped a column list in verbatim, which value mode could not do because it would have quoted id, name into a single string literal.

Pro Tip: The three modes map cleanly to three jobs. Use @ for a value in a predicate or a computation, @@ when the name of a table or column is data your logic decided on, and @! when you are deliberately assembling SQL, like the column list above. Reaching for @! where @ or @@ would do is the dataflow equivalent of building a query with string concatenation, so keep it deliberate.

There is a fourth kind of expansion that is not a variable at all. Tables whose names start with #, like #rank, are scoped to the dataflow. When you reference one, the platform rewrites the name to the private physical table backing it for this execution. Two dataflows running at the same time can both use #rank and never collide, and when the dataflow ends those tables are cleaned up automatically.

Control flow that actually looks like code

Inside a dataflow you get the control-flow constructs you would expect from a procedural language, and the set has grown recently.

Conditionals now include ELSEIF, so you can write a real ladder instead of nesting IF blocks:

Loops come in three shapes. There is the WHILE loop you saw in PageRank. There is a C-style FOR loop with an initializer, a condition, and a step. And there is an iterator-style FOR ... IN loop that walks the rows of a query and binds each one to a declared variable:

The iterator loop today walks a single-column query and binds the scalar; the loop variable is declared with the column’s type. (Iterating a multi-column result and reaching each column through one row variable is a natural next step, and something we would like to add.) Every loop also accepts an optional MAX ITERATIONS clause, and BREAK and CONTINUE do what they do everywhere else:

MAX ITERATIONS is worth calling out. An imperative loop introduces the one risk a declarative query never has, which is running forever. The clause lets you cap a loop’s iterations right in the syntax, so a logic bug becomes a clean error instead of a runaway job. The administrator also has a system-wide ceiling as a backstop, and a user’s own MAX ITERATIONS can only make the limit stricter, never looser.

Exceptions: Throw, catch, and always clean up

The newest and, I think, most useful addition is real exception handling. A dataflow can raise an error, catch it, inspect it, and guarantee cleanup.

RAISE throws an error, optionally with a message you provide. TRY ... EXCEPTION WHEN ... THEN catches one. You can match a specific SQLSTATE by its string, or catch everything with OTHERS, and the handlers are tried in order until one matches:

Inside a handler, two special variables are available: @SQLERRM holds the caught error’s message and @SQLSTATE holds its SQLSTATE code. That is enough to log a precise diagnostic, branch on the error class, or re-raise.

Then there is the FINALLY block, which runs at the end whether the dataflow succeeded, failed, or was cancelled. The thing to understand about FINALLY is what it is not for: your # scratch tables are dropped automatically, so you never clean those up yourself. Where FINALLY earns its keep is the effect a dataflow has on normal tables, the ones that outlive it.

The classic case is an ELT chain. A dataflow stages some data, transforms it, and lands the result in a normal target table. If it fails partway, that target is left half-built, and the cleanest thing to do is roll it back so the next run starts from a known state. FINALLY is where you do that. Here the dataflow only marks itself successful at the very end, and the FINALLY block truncates the target unless that marker was set:

On a clean run the target keeps its freshly loaded rows. On a failure the FINALLY block wipes the half-written target, so re-running the dataflow does not have to reason about a partial load. You can just as easily DROP the target instead of truncating, or clean up several staging tables at once; the point is that FINALLY is your hook for the normal-table state a dataflow leaves behind, because the # tables take care of themselves.

Pro Tip: While we are talking about normal tables, one of my favorite debugging tricks uses one deliberately. Create a normal run_log(ts TIMESTAMP, msg VARCHAR(...)) table at the top of a dataflow and INSERT INTO run_log SELECT now(), 'reached step N' as it makes progress. Because it is a normal table, not a # table, it survives after the dataflow ends, so you can SELECT it afterward and see exactly how far the run got and when. That one is the opposite of the ELT case: you want it to persist, so its cleanup does not belong in FINALLY at all.

Watching it run, and stopping it

A loop that can run for a while raises an obvious operational question: while it is going, can I see it, and can I stop it? For a dataflow the answer to both is yes, and it behaves the way you would want.

A running dataflow shows up in sys.queries with its own query UUID, the same as any ordinary query. That means the observability you already use works on it: you can see that it is running, how long it has been going, and who submitted it.

Because it has a real query UUID, you can stop it with the same commands you use for any query. CANCEL QUERY and KILL QUERY both take the UUID as a quoted string:

Cancellation is cooperative and checked at every statement boundary, which for a loop means once per iteration. So a cancel does not wait for a multi-hour traversal to finish; it takes effect at the next turn of the loop. And this is where the FINALLY block earns its keep again: when a cancel lands, the platform stops the loop and then still runs your FINALLY block, so the cleanup you set up still happens even though the dataflow was stopped in the middle.

Pro Tip: Pair MAX ITERATIONS with FINALLY. The first bounds how long a logic bug can run before it errors out on its own, and the second guarantees your cleanup happens on that error, on a manual cancel, or on a normal finish. Between the two, a dataflow is about as safe to turn loose on a shared cluster as an imperative loop can be.

The Cool Part: What the platform does while your loop runs

Everything so far is syntax you write. The reason dataflows are interesting from a performance standpoint is what the platform does underneath a loop that you never see. Because each statement in the loop body is executed on its own, the runtime gets to look at the actual data between iterations and make decisions a single static plan cannot.

It keeps small scratch tables in memory. Registering a real distributed table involves a round trip through the metadata consensus layer, which is worth it for a large table and pure overhead for a table of forty rows. So the runtime intercepts your CREATE TABLE #rank and, while the table is small, keeps it entirely in the coordinator’s memory instead of writing it to storage. When a query references it, the data is injected inline. An iteration that touches only a handful of rows pays no storage cost at all.

It promotes to real storage when the data grows, and demotes when it shrinks. The moment a scratch table crosses a size threshold, the runtime flushes it to NVMe and switches to a normal distributed table for the rest of the run. The reverse also happens: for algorithms that shrink their working set over time, a table that starts large and drops back below the threshold is pulled back into memory. The tail of a reduction algorithm runs at in-memory latency even if it started with far more data than fits in memory.

It plans each iteration with exact numbers. Because the loop body is compiled and run one iteration at a time, and the row count of a scratch table is known exactly the moment the previous statement commits, the optimizer plans each pass from facts rather than estimates. When a working set is small it can broadcast; when it balloons it can shuffle; and it decides that per iteration from the real cardinality, not from a guess made once before the loop started.

It shortcuts the loop guard. The kind of statement that runs every single iteration, a SELECT count(*) over a scratch table or a scalar SET, is answered directly by the runtime when the data is in memory, skipping the full parse, validate, plan, execute path. Recent releases pushed this further, stripping out redundant per-iteration work so that a deep loop issues far fewer sub-queries than it used to. A recursion that once spent almost all of its time re-parsing and re-planning the same loop body now spends it on the actual join.

It plays fair with everyone else. A naive recursive operator holds a workload-management slot for the entire, possibly hours-long, duration of the job, which can starve short queries stuck behind it. A dataflow instead acquires a slot for each heavy statement and releases it the instant that statement finishes, before the loop check. A giant traversal yields the cluster between iterations, so it does not block the quick queries running alongside it.

Its spill path is its normal path. The scratch tables are just regular tables on OcientAIQ’s NVMe-backed storage. There is no separate, slower “we ran out of memory” code path to fall into, because writing intermediate state to NVMe is the standard path. A working set of billions of rows is not a special case; it is Tuesday.

You do not opt into any of this. You write a loop with a few scratch tables, and the runtime does the memory management, the per-iteration planning, the fairness, and the spilling on your behalf.

How this compares

Plenty of databases have procedural SQL. It is worth being precise about what is genuinely different here rather than pretending nobody else has a WHILE loop.

• The ad-hoc, anonymous submission is the standout. In most systems, procedural logic means a persistent, privileged, catalog-resident stored procedure invoked over a special protocol. Being able to send an imperative script as an ordinary query string, on the same connection your application already has open, with no CREATE and nothing to clean up, is the part that changes how you use it. It turns procedural logic into something an application, or even a BI client, can generate on the fly.

• The intermediate state is JIT-managed. A traditional engine’s procedural loop materializes its working tables the same way every time. The dataflow runtime moves each scratch table between coordinator memory and NVMe based on its actual size that iteration, and because those tables commit between statements, the next iteration plans against their exact cardinality. That between-iterations inspection, and the memory-to-storage transitions that come with it, is the piece that is hard to find elsewhere.

• It is built for a distributed warehouse. The control logic runs on the coordinator while the heavy joins push down to the compute nodes next to the data. The fair, slot-per-statement scheduling and the spill-to-NVMe-as-the-normal-path behavior exist because the loop body is a sequence of independently schedulable statements rather than one opaque operator.

• The type system carries through. Because a variable can hold any OcientAIQ type and expand back into valid SQL, the arrays, tuples, timestamps, and geospatial values we spent earlier posts on are all first-class inside a dataflow, not flattened to strings the moment you loop over them.

None of this requires you to learn a separate language or a separate engine. A dataflow is SQL, plus variables, plus control flow, submitted the way you already submit SQL.

Summary

A dataflow is an anonymous, ephemeral, imperative SQL script that an application submits exactly like a query:

• Two flavors.BEGIN QUERY DATAFLOW ... RETURN ... hands back a result set like a SELECT; BEGIN DATAFLOW ... returns nothing, for the INSERT/DELETE style of work. Both go over the ordinary client protocol, with no CREATE and nothing to clean up.

• Variables can be declared anywhere at the outermost level, are strongly typed, can be NOT NULL, can initialize from a scalar subquery, and can hold any OcientAIQ data type. The platform formats each one correctly when it is spliced back into SQL.

• Three expansion modes.@var for a typed literal value, @@var for a quoted identifier (parameterize a table or column name), and @!var for raw verbatim text like a runtime-built column list. Plus #tables that are private to the run and cleaned up automatically.

• Control flow.IF / ELSEIF / ELSE, WHILE, a C-style FOR, and a single-column iterator FOR ... IN, all with optional MAX ITERATIONS, plus BREAK and CONTINUE.

• Exceptions.RAISE to throw, TRY ... EXCEPTION WHEN <sqlstate> | OTHERS THEN to catch, @SQLERRM and @SQLSTATE to inspect the error, and a FINALLY block for cleaning up the normal-table state a dataflow leaves behind (your # scratch tables are dropped automatically).

• Observable and cancellable. A running dataflow appears in sys.queries with its own UUID, and CANCEL QUERY or KILL QUERY stops it at the next iteration boundary, with FINALLY still running.

• Optimizations you get for free. Small scratch tables kept in memory and promoted or demoted as they grow and shrink, per-iteration planning from exact cardinalities, a shortcut for the count-based loop guard, fair workload-management scheduling that yields between iterations, and an NVMe spill path that is the normal path rather than an exception.

If the shape of a problem is “do a thing, look at the result, decide what to do next, repeat,” it has probably been living in your application as a loop of round-trips. A dataflow lets you push that loop down next to the data, submit it as a single string, and let the platform handle the memory, the planning, and the cleanup. Start with something small: take a multi-step routine you currently orchestrate from the client, wrap it in BEGIN DATAFLOW ... END DATAFLOW, and watch it run as one request.

Thanks for reading. You go deeper on OcientAIQ in our docs.