Programs and batches

A Sumatra program is a single file. Two kinds exist: the anonymous block (run one-shot with @file.sumatra) and the stored procedure (the same body wrapped in CREATE OR REPLACE PROCEDURE, covered in Stored procedures). This page walks the anonymous block top to bottom.

-- comments run to the end of the line; /* block comments */ also work

PARAM run_date DATE;               -- program inputs (optional section)

DECLARE                            -- variables and types (optional section)
  total NUMERIC(14,2) := 0;
BEGIN
  ...                              -- the body: reads, writes, compute
EXCEPTION                          -- fault handlers (optional section)
  WHEN ZERO_DIVIDE THEN STOP;
  WHEN OTHERS      THEN RAISE;
END;

Every statement ends with ;. The corpus convention is UPPERCASE keywords and lowercase identifiers; identifier case is not significant.

PARAM: program inputs

PARAM want_region TEXT;
PARAM want_date   DATE;

Parameters are declared before the body, one per line, and their values arrive on the command line in declaration order:

sumatra --dsn my.dsn @rollup.sumatra "North America" 2026-07-01

A parameter is referenced only inside SQL — reads and write statements — as PARAM('name'), and it is bound out-of-band as a real query parameter, never substituted into the SQL text:

FOR batch IN (SELECT grp, SUM(amt) AS amt
              FROM sales
              WHERE region = PARAM('want_region')
                AND sale_date = CAST(PARAM('want_date') AS DATE)
              GROUP BY grp)
    BATCH SIZE ALL

Two rules follow from that design:

A non-TEXT parameter needs an explicit cast at the point of use: CAST(PARAM('want_date') AS DATE).
A bare want_date in compute code is an undeclared-variable error — parameters are read-only inputs to your SQL, not variables.

DECLARE: variables and carried state

DECLARE
  running       NUMERIC(14,2) := 0;
  last_customer BIGINT        := NULL;

Variables are program-scoped: they carry across rows, across batches, and across sequential read blocks — nothing ever resets them implicitly. That is what makes multi-pass and running-state programs natural to write. Initializers are optional, but reading a variable before it has ever been assigned is a compile error (definite-assignment analysis, the same rule as Java or Rust) — never a silent NULL. Give accumulators an explicit := 0. DECLARE is also where record and collection types live — see records and collections.

The read block

All row input comes through one construct:

FOR batch IN (SELECT ...)          -- any SELECT the engine accepts, relayed verbatim
    ORDER KEY (col1, col2)         -- strict total order (when required — see below)
    BATCH SIZE 50000               -- chunk size, or ALL
LOOP
  ...                              -- the batch body
END LOOP;

The parenthesized SELECT is opaque: Sumatra relays it to the engine byte-verbatim and learns its column names and types at compile time from the live database. Filter, join, aggregate, use window functions — anything the engine accepts is fine, and any of the engine’s functions may appear inside it.

BATCH SIZE

BATCH SIZE is mandatory on every read and sets the working grain:

BATCH SIZE N — fetch the result in chunks of up to N rows, using keyset pagination over the ORDER KEY (the same WHERE key > last ... LIMIT N pattern you would hand-write). Each batch is processed and written as a unit, and memory stays bounded at N rows.
BATCH SIZE ALL — one whole-set batch, evaluated once. This is the right choice for aggregating reads (a GROUP BY source re-evaluates per chunk under BATCH SIZE N) and for order-independent bodies. On the client-compute path the whole set is held in memory.

An empty read is a clean no-op: the loop body runs zero times and the program continues.

ORDER KEY

ORDER KEY (...) declares a strict total order over the read. It is required when:

BATCH SIZE N chunks the read (the key is the pagination cursor), or
the body is order-dependent — carried state that must see rows in a specific sequence — at any batch size, or
the loop is a writing client-compute loop.

It may be omitted only for order-independent, whole-set (ALL) work. Key columns must be INT, BIGINT, or TIMESTAMP (composite keys are fine, e.g. ORDER KEY (customer_id, entry_id)). The order must actually be strict: a duplicate key value or a NULL in a key column at runtime is a loud error — never a silently skipped or double-processed row.

Row loops and batch statements

Inside the batch body you either iterate rows in memory:

FOR rec IN batch LOOP
  rec.total := rec.qty * rec.price;      -- rec's fields = the SELECT's columns
  UPDATE orders SET ROW = rec WHERE order_id = rec.order_id;
END LOOP;

or reference the batch as a set in SQL statements (no row loop), which keeps the work server-side:

INSERT INTO order_totals (id, total)
  SELECT id, qty * price FROM batch;

Reads do not nest: one read block inside another is a compile error (join or use a keyed collection instead), and a read cannot appear inside a compute loop. Sequential read blocks are fine and share carried variables — the two-pass pattern.

How writes are routed

You write only natural UPDATE, INSERT, and DELETE. The compiler routes each write to the lightest correct SQL form — there is no routing keyword, and you never write MERGE, COPY, or staging tables:

You write	Where it runs
A top-level set statement (`UPDATE ... WHERE`, `DELETE ... WHERE`, `DELETE ... USING`, `INSERT ... SELECT`)	Server-side, as-is — rows never leave the engine
A statement referencing `batch` as a set (`... FROM batch`, `WHERE k = batch.k`)	Server-side, correlated to the batch
Per-row writes on `rec` inside a row loop	Captured in memory, staged, and applied as one set-based MERGE per batch

The per-row forms and their rules:

-- Full-row write-back: writes every column except the matched key
UPDATE orders SET ROW = rec WHERE order_id = rec.order_id;

-- Sparse update: writes only the SET columns; untouched columns are preserved,
-- and the read may project just a subset of the target's columns
UPDATE accounts SET balance = rec.balance + 10 WHERE id = rec.id;

-- Append: INSERT inside a row loop takes VALUES (a record, or a column list)
INSERT INTO audit VALUES ev;
INSERT INTO installments (invoice_id, seq, amount) VALUES (rec.invoice_id, i, per);

-- Delete
DELETE FROM accounts WHERE customer_id = rec.customer_id;

A program may mix them — an IF that deletes one row, updates another, and inserts a third produces a single op-tagged change set applied by one MERGE. The merge key is whatever the write’s WHERE equates with the record’s own fields — a single column or a composite AND-chain (WHERE wh = rec.wh AND sku = rec.sku). Any extra non-key condition on rec fields acts as a capture filter: the change is captured only when it is TRUE (WHERE id = rec.id AND rec.qty > 0 skips non-positive rows). Three rules the compiler holds you to:

A write may not change its own key: SET of a merge-key column, or a SET ROW matched on anything but the record’s own key field, is a compile error.
One change per key per flush: writing the same key twice in one batch is a program error. Statically visible cases are compile errors; data-dependent duplicates surface as the engine’s own MERGE cardinality error at runtime (catchable with WHEN OTHERS). Repeated DELETEs of the same key are idempotent and allowed.
An upsert is written as its two natural statements — an accumulate UPDATE plus a complement INSERT ... WHERE key NOT IN (...) — and both run server-side; together they behave as the upsert.

COMMIT: placement is meaning

COMMIT is always explicit — Sumatra never commits behind your back — and where you put it defines the transaction grain:

-- Per-batch grain: each batch commits as soon as it is written.
FOR batch IN (...) ORDER KEY (id) BATCH SIZE 10000 LOOP
  FOR rec IN batch LOOP ... END LOOP;
  COMMIT;                       -- durable checkpoint per batch
END LOOP;

-- At-end grain: nothing is durable unless the whole program succeeds.
FOR batch IN (...) ORDER KEY (id) BATCH SIZE 10000 LOOP
  FOR rec IN batch LOOP ... END LOOP;
END LOOP;
COMMIT;                         -- one all-or-nothing transaction

Per-batch commits give you bounded memory and a durable prefix: if the program faults on batch 40, batches 1–39 stay committed, and a re-run picks up the remainder (see Exceptions and recovery). The at-end grain trades that for all-or-nothing atomicity. The compiler enforces coherence:

A writing loop with no COMMIT anywhere is a compile error — there is no implicit commit to fall back on.
COMMIT cannot appear inside a compute loop (WHILE, count loops, row loops) — per-row commits are inexpressible by grammar.
A server-side read at BATCH SIZE N must commit in the batch body.

Writes persist iff control reaches a COMMIT: a fault or crash rolls back the open transaction while every already-committed batch survives. On Firebolt, transactions are ACID and snapshot-isolated, so readers never see a half-written batch.

What the compiler manages in your database

Two pieces of engine-side plumbing exist so you know what they are:

Staging tables — client-computed changes land in a per-run scratch table named sumatra_stage_... before the MERGE applies them. They are created, truncated, and dropped automatically; a crashed run’s orphan is reclaimed by the next run of the same program. A companion sumatra_stage_lock_... marker refuses two concurrent runs of the same program with the same parameters.
sumatra_source — the stored-procedure catalog table (only created once you store a procedure).

Treat the sumatra_ name prefix as reserved for the toolchain in databases you point Sumatra at.

Program lifecycle

A run finishes in exactly one of three states, surfaced as the process exit code: 0 completed, 2 stopped by a handler with the committed prefix retained, 1 error. There is no automatic retry or resume — re-running a program is always your action, and the standard idiom is to make the read re-runnable (WHERE processed = FALSE, flipping the flag as part of each row’s write) so a re-run continues where the committed prefix ends.

Introduction

Getting Started

Writing Programs

Reference

PARAM: program inputs

DECLARE: variables and carried state

The read block

BATCH SIZE

ORDER KEY

Row loops and batch statements

How writes are routed

COMMIT: placement is meaning

What the compiler manages in your database

Program lifecycle

​PARAM: program inputs

​DECLARE: variables and carried state

​The read block

​BATCH SIZE

​ORDER KEY

​Row loops and batch statements

​How writes are routed

​COMMIT: placement is meaning

​What the compiler manages in your database

​Program lifecycle

PARAM: program inputs

DECLARE: variables and carried state

The read block

BATCH SIZE

ORDER KEY

Row loops and batch statements

How writes are routed

COMMIT: placement is meaning

What the compiler manages in your database

Program lifecycle