> ## Documentation Index > Fetch the complete documentation index at: https://docs.wirekite.io/llms.txt > Use this file to discover all available pages before exploring further. # Programs and batches > The anatomy of a Sumatra program: parameters, declarations, the batched read block, write routing, and commit grains. 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](/sumatra/procedures)). This page walks the anonymous block top to bottom. ```sql theme={null} -- 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 ```sql theme={null} 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: ```bash theme={null} 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: ```sql theme={null} 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 ```sql theme={null} 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](/sumatra/language#records). ## The read block All row input comes through one construct: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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](/sumatra/language#collections) 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: ```sql theme={null} -- 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: ```sql theme={null} -- 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](/sumatra/errors)). 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.