Why Sumatra

Every data platform eventually needs a transformation step that set-based SQL cannot express: a per-account state machine, a running balance, a change feed where each row’s fate depends on a value the data just produced. Today that step usually leaves the database. Sumatra’s premise is that it shouldn’t — and its two core mechanisms, the batch engine and the merge engine, are what make staying in-database practical rather than merely possible.

Transformation without leaving the database

The conventional answer to procedural transformation is a round trip: export the data to an external compute layer (a Spark cluster, a fleet of Python jobs), transform it there, and load the results back. Every part of that round trip is a cost you keep paying:

Data movement, twice. The working set crosses the wire out, and the results cross back in — often the slowest and most expensive part of the pipeline.
A second platform to operate. The external layer has its own sizing, patching, monitoring, credentials, and failure modes. For many teams the cluster exists only to host the procedural steps the database couldn’t run.
Two failure domains. A half-finished external job leaves state split across systems, and recovery logic has to reason about both.
A wider security surface. Data now rests, at least transiently, outside the database — a second system to bring inside audit and compliance boundaries.
Schema drift. The external job carries its own copy of schema knowledge, which silently rots as tables evolve.

A Sumatra program removes the second platform rather than managing it:

The program compiles against the live catalog of the connected database — the database itself is the schema, so there is no copy to drift. If the schema changes after compile, the program refuses to run on stale assumptions and recompiles.
Reads and writes are the engine’s own SQL. Set-shaped work never leaves the engine at all; procedural work streams through the program in bounded batches and lands back as set-based writes. Rows are never parked in a second system of record.
The runtime is one native binary on a host you already have — a process, not a platform. There is no cluster, no job framework, no scheduler service; cron and an exit code are enough.
Failure semantics live in one place: committed batches are durable in the database, everything else rolled back — one system to reason about when something goes wrong.

The result: the pipeline’s set steps and its procedural mid-work run in the same place, against the same catalog, under the same transactional rules.

The batch engine

Sumatra’s execution grain is the batch — and one declaration sets it:

FOR batch IN (SELECT entry_id, customer_id, delta, running_balance FROM ledger)
    ORDER KEY (customer_id, entry_id)
    BATCH SIZE 50000
LOOP
  ...
  COMMIT;
END LOOP;

That BATCH SIZE is one knob controlling four things at once — the read chunk, the write flush, the commit checkpoint, and the memory bound. Behind it, the compiler generates the machinery you would otherwise hand-build: Keyset pagination, generated. Chunked reads page by key, not by offset — the shape of the generated fetch is:

-- the shape of the generated fetch (illustrative)
SELECT ... FROM ledger
WHERE (customer_id, entry_id) > (last_seen_customer, last_seen_entry)
ORDER BY customer_id, entry_id
LIMIT 50000

This is exactly how pagination is hand-written by people who have been burned by OFFSET: no quadratic re-scans, no scratch tables, no manufactured row numbers. You declare the key; the cursor logic, the boundary tracking, and the end-of-set detection are generated. An order you can trust. ORDER KEY declares a strict total order, and the toolchain treats it as a contract: a duplicate key or a NULL in a key column at runtime is a loud error — never a silently skipped or double-processed row. Carried state flows across batch seams in declared order, which is what makes a 50,000-row-batched running balance produce the identical answer to a single-pass run. That invariance — the same result at any batch size — is a language guarantee on the compute path, not a hope. Checkpoints for free. A COMMIT inside the batch loop makes every batch a durable checkpoint. A ten-hour backfill that faults at hour nine keeps nine hours of committed work as a valid prefix, and the standard done-flag idiom makes the re-run resume exactly where it stopped — restartability falls out of the commit grain instead of being engineered separately. Bounded memory. BATCH SIZE N caps the client working set at N rows regardless of table size; BATCH SIZE ALL is the explicit whole-set choice for aggregating reads, where one distributed evaluation of the query is the efficient shape. And the row-at-a-time trap is fenced off. On a distributed columnar engine, per-row server calls — the natural idiom on OLTP databases — are catastrophically slow. Sumatra does not merely discourage them; the grammar has no way to write a per-row server query or a per-row commit. The language’s promise is per-row logic at set-based cost, and the promise is structural.

The merge engine

The write side is where hand-rolled pipelines hide their sharpest edges. Applying a mixed stream of inserts, updates, and deletes correctly means writing a staged MERGE: create a scratch table, bulk it full, get the match conditions and the operation arms exactly right, clean it all up — and get it right again in every job that writes. In Sumatra you write only natural per-row statements:

IF rec.new_balance < 0 THEN
  DELETE FROM accounts WHERE customer_id = rec.customer_id;
ELSIF rec.exists_flag THEN
  UPDATE accounts SET balance = rec.new_balance WHERE customer_id = rec.customer_id;
ELSE
  INSERT INTO accounts (customer_id, balance) VALUES (rec.customer_id, rec.new_balance);
END IF;

and the compiler turns each batch’s captured changes into one set-based, op-tagged MERGE:

-- the shape of the generated write-back (illustrative)
MERGE INTO accounts t USING sumatra_stage_... m ON t.customer_id = m.customer_id
  WHEN MATCHED     AND m.op = 'd' THEN DELETE
  WHEN MATCHED     AND m.op = 'u' THEN UPDATE SET balance = m.balance
  WHEN NOT MATCHED AND m.op = 'i' THEN INSERT (customer_id, balance) VALUES (m.customer_id, m.balance);

What that buys you:

One statement per batch, whatever the mix. Ten thousand rows’ worth of per-row branching — some deleted, some updated, some inserted — lands as a single set-based apply, at set-based cost.
The staging lifecycle is invisible. The scratch table is created, filled in bulk over the same SQL connection (no CSV export, no object-store hop, no COPY pipeline to build), applied, and dropped automatically. A crashed run’s orphan staging table is reclaimed by the next run of the same program.
Only what changed is written. A single-column UPDATE stages just the key and that column — a sparse update that leaves every other column untouched and lets the read project only what it needs. SET ROW writes the whole record when that is what you mean.
The lightest form wins. Captured-and-staged MERGE is the heaviest route, used only when per-row compute demands it. A write that is already set-shaped runs as plain DML exactly as you wrote it, and a write that correlates to the batch as a set runs as one correlated statement — the compiler always routes each write to the cheapest form that is correct, with no keyword and no hints.

And it holds you to the invariants that make merges trustworthy:

One change per key per flush. Two captured changes to the same merge key in one batch is a program error surfaced loudly — never a silent last-write-wins that quietly picks a survivor.
Keys are immutable in a write. A statement that would change its own merge key is rejected at compile time, because a staged merge cannot express it correctly.
NULL keys never match, exactly as SQL joins treat them — no accidental cross-matching through NULLs.

The net effect: the trickiest statement in analytical SQL is one you never write, generated from per-row logic that says what you actually mean — and the classic merge bugs are unrepresentable.

Built to run unattended

The same machinery composes into an operational shape schedulers can rely on. Every run ends in exactly one of three states — 0 completed, 2 stopped cleanly with the committed prefix retained, 1 needs a human — so cron can branch without parsing logs. Stored procedures compile once and run from cache on any host, recompiling only when source, toolchain, or schema actually changed — and each recompile is announced. Concurrent duplicate runs of the same procedure with the same arguments are refused engine-side, so an overlapping cron slot cannot double-apply a batch.

Engine-neutral by construction

Sumatra deliberately defines program meaning as the connected engine’s meaning: values, rounding, overflow, and NULL behavior are the bound backend’s semantics, verified bit-exact, and everything engine-specific in the toolchain — type names, SQL dialect, semantic constants — lives behind per-backend profiles rather than in the language. Your read SQL is your engine’s own dialect, relayed verbatim. The backend is declared by the DSN’s URL scheme, per database. Firebolt is the first live backend and the engine used throughout these docs; the language surface you learn — batches, routing, merge discipline, exceptions — is the portable part, designed to carry to further engines as they are bound.

See it in practice

Quickstart — run the round trip yourself in ten minutes.
Learning by example — the running balance, change-feed apply, and fail-safe scoring programs.
Programs and batches — the precise rules behind the batch and merge machinery described here.

Introduction

Getting Started

Writing Programs

Reference

Transformation without leaving the database

The batch engine

The merge engine

Built to run unattended

Engine-neutral by construction

See it in practice

​Transformation without leaving the database

​The batch engine

​The merge engine

​Built to run unattended

​Engine-neutral by construction

​See it in practice

Transformation without leaving the database

The batch engine

The merge engine

Built to run unattended

Engine-neutral by construction

See it in practice