> ## 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. # Learning by example > Six small, complete Sumatra programs — from the smallest possible program to carried state, runtime-routed writes, parameters, and exception handling. Every program on this page is complete and runnable as written, and each comes from the toolchain's live-verified example corpus. Each example lists the tables it needs — create them (with a few sample rows) in your Firebolt database, save the program to a file, and run it with `sumatra --dsn my.dsn @file.sumatra`. They build on each other in order, so if this is your first contact with Sumatra, read top to bottom. ## 1. The smallest program: set-DML When the work is already set-shaped, a program is just SQL statements and a `COMMIT` — no variables, no loops. Sumatra runs these server-side exactly as written. ```sql Tables theme={null} CREATE TABLE sumatra_sessions ( session_id BIGINT NOT NULL, state TEXT, last_seen TIMESTAMP ) PRIMARY INDEX session_id; CREATE TABLE sumatra_session_tokens ( token_id BIGINT NOT NULL, revoked BOOLEAN ) PRIMARY INDEX token_id; ``` ```sql expire.sumatra theme={null} -- Expire idle sessions and purge revoked tokens, in one transaction. BEGIN UPDATE sumatra_sessions SET state = 'expired' WHERE state = 'active' AND last_seen < CURRENT_TIMESTAMP - INTERVAL '24' HOUR; DELETE FROM sumatra_session_tokens WHERE revoked = TRUE; COMMIT; -- one transaction for the whole program END; ``` What to notice: * A body can hold plain `UPDATE`/`DELETE`/`INSERT ... SELECT` statements directly. They run on the engine as-is — rows never move to the client. * The single top-level `COMMIT;` makes both statements one atomic transaction. * The datetime expression is the engine's own dialect — inside SQL statements you write the engine's SQL (Firebolt here), verbatim. ## 2. Branching per row: CASE and write-back The canonical Sumatra shape: read in batches, compute per row, write each row back. ```sql Tables theme={null} CREATE TABLE sumatra_grade_demo ( id BIGINT NOT NULL, band INT, grade TEXT, gpa NUMERIC(5,2) ) PRIMARY INDEX id; INSERT INTO sumatra_grade_demo VALUES (1, 3, NULL, NULL), (2, 1, NULL, NULL), (3, 2, NULL, NULL), (4, 0, NULL, NULL); ``` ```sql grades.sumatra theme={null} -- Assign a letter grade and GPA from a numeric band. BEGIN FOR batch IN (SELECT id, band, grade, gpa FROM sumatra_grade_demo) ORDER KEY (id) BATCH SIZE 4000 LOOP FOR rec IN batch LOOP CASE rec.band WHEN 3 THEN rec.grade := 'A'; rec.gpa := 4.00; WHEN 2 THEN rec.grade := 'B'; rec.gpa := 3.00; WHEN 1 THEN rec.grade := 'C'; rec.gpa := 2.00; ELSE rec.grade := 'D'; rec.gpa := 1.00; END CASE; UPDATE sumatra_grade_demo SET ROW = rec WHERE id = rec.id; END LOOP; COMMIT; END LOOP; END; ``` What to notice: * The `CASE ... END CASE;` **statement** puts statements in each branch. Its cousin, the CASE *expression*, selects a value instead — see [the language reference](/sumatra/language#operators). * The read selects every column of the table because `SET ROW = rec` writes the whole record back (all columns except the key it matches on). * The compiler turns the loop's captured writes into one set-based statement per batch — you get row-by-row logic at set-based cost. ## 3. Carried state: a running balance The pattern SQL struggles with most: each row's result depends on the previous row's result. Variables declared in `DECLARE` carry across rows **and across batches**, and `ORDER KEY` guarantees the order they carry in. ```sql Tables theme={null} CREATE TABLE sumatra_ledger ( entry_id BIGINT NOT NULL, customer_id INT, delta NUMERIC(12,2), running_balance NUMERIC(14,2) ) PRIMARY INDEX customer_id, entry_id; INSERT INTO sumatra_ledger VALUES (1, 100, 50.00, NULL), (2, 100, -20.00, NULL), (3, 100, -80.00, NULL), (4, 200, 10.00, NULL), (5, 200, 15.00, NULL); ``` ```sql balance.sumatra theme={null} -- A floored per-customer running balance. DECLARE running NUMERIC(14,2) := 0; -- carried across rows and batches last_customer BIGINT := NULL; BEGIN FOR batch IN (SELECT entry_id, customer_id, delta, running_balance FROM sumatra_ledger) ORDER KEY (customer_id, entry_id) BATCH SIZE 50000 LOOP FOR rec IN batch LOOP IF rec.customer_id <> last_customer OR last_customer IS NULL THEN running := 0; -- new customer: reset last_customer := rec.customer_id; END IF; running := GREATEST(running + rec.delta, 0); -- clamp at zero rec.running_balance := running; UPDATE sumatra_ledger SET ROW = rec WHERE entry_id = rec.entry_id; END LOOP; COMMIT; -- per-batch checkpoint END LOOP; END; ``` Customer 100's balances land as `50.00`, `30.00`, `0.00` (the clamp caught the overdraft); customer 200's as `10.00`, `25.00`. What to notice: * `ORDER KEY (customer_id, entry_id)` makes each customer's rows contiguous and ordered — which is exactly what lets `running` and `last_customer` carry correctly, even across batch boundaries. * The per-customer reset is runtime logic (`IF` on a comparison with carried state), not a window-function trick. * `BATCH SIZE 50000` bounds memory; the answer is identical at any batch size — that invariance is a language guarantee. ## 4. Runtime-routed writes: insert, update, or delete per row A per-row branch chooses `DELETE`, `UPDATE`, or `INSERT` based on a value the data produced. Compile-time templating cannot express this; in Sumatra it is just an `IF`. ```sql Tables theme={null} CREATE TABLE sumatra_account_changes ( customer_id INT NOT NULL, new_balance NUMERIC(14,2), exists_flag BOOLEAN ) PRIMARY INDEX customer_id; CREATE TABLE sumatra_account ( customer_id INT NOT NULL, balance NUMERIC(14,2) ) PRIMARY INDEX customer_id; ``` ```sql apply_changes.sumatra theme={null} -- Apply a change feed: negative balance deletes the account, -- an existing account updates, a new one inserts. BEGIN FOR batch IN (SELECT customer_id, new_balance, exists_flag FROM sumatra_account_changes) ORDER KEY (customer_id) BATCH SIZE 20000 LOOP FOR rec IN batch LOOP IF rec.new_balance < 0 THEN DELETE FROM sumatra_account WHERE customer_id = rec.customer_id; ELSIF rec.exists_flag THEN UPDATE sumatra_account SET balance = rec.new_balance WHERE customer_id = rec.customer_id; ELSE INSERT INTO sumatra_account (customer_id, balance) VALUES (rec.customer_id, rec.new_balance); END IF; END LOOP; COMMIT; END LOOP; END; ``` What to notice: * All three writes target one table, keyed on `customer_id`; the compiler captures whichever branch fired for each row and applies the whole batch's mixed changes as **one** set-based statement. * Inside a row loop, `INSERT` uses `VALUES` — per-row inserts are captured and batched like the other writes. * Writing the same key twice in one batch is a program error surfaced loudly at run time — never a silent last-write-wins. ## 5. Parameters: one program, many runs `PARAM` declares program inputs; values arrive on the command line, in declaration order, and bind into the read's SQL as real query parameters. ```sql Tables theme={null} CREATE TABLE sumatra_param_src ( region TEXT, sale_date DATE, grp INT, amt NUMERIC(12,2) ); CREATE TABLE sumatra_param_tgt ( grp INT NOT NULL, total NUMERIC(14,2), n BIGINT ) PRIMARY INDEX grp; ``` ```sql region_rollup.sumatra theme={null} -- Roll one region-day of sales into per-group totals. PARAM want_region TEXT; PARAM want_date DATE; BEGIN FOR batch IN (SELECT grp, SUM(amt) AS amt, COUNT(*) AS n FROM sumatra_param_src WHERE region = PARAM('want_region') AND sale_date = CAST(PARAM('want_date') AS DATE) GROUP BY grp) BATCH SIZE ALL LOOP UPDATE sumatra_param_tgt SET total = total + batch.amt, n = n + batch.n WHERE grp = batch.grp; INSERT INTO sumatra_param_tgt (grp, total, n) SELECT grp, amt, n FROM batch WHERE grp NOT IN (SELECT grp FROM sumatra_param_tgt); END LOOP; COMMIT; END; ``` ```bash theme={null} sumatra --dsn my.dsn @region_rollup.sumatra "EMEA" 2026-07-01 ``` What to notice: * A parameter is referenced only inside SQL, as `PARAM('name')`, and a non-text parameter needs a cast at the point of use. * `BATCH SIZE ALL` runs the aggregating read as one whole-set batch — the right grain for a `GROUP BY` source — and, because the body is order-independent, no `ORDER KEY` is needed. * There is no row loop here: the statements reference `batch` as a set, so the whole body runs server-side. The update-then-insert pair is the idiomatic upsert — accumulate existing keys, insert the complement. ## 6. Exceptions: fail safe, keep the checkpoint Arithmetic on real data can fault: a zero divisor, an overflow. Rather than guard every row, this program handles faults at the program level and leans on per-batch commits for recovery. ```sql Tables theme={null} CREATE TABLE sumatra_risk_inputs ( account_id INT NOT NULL, gross NUMERIC(14,2), factor NUMERIC(8,4), weight NUMERIC(8,4), risk_score NUMERIC(14,4), scored BOOLEAN ) PRIMARY INDEX account_id; ``` ```sql score.sumatra theme={null} -- Score each unscored account; stop cleanly on bad data. DECLARE score NUMERIC(14,4); BEGIN FOR batch IN (SELECT account_id, gross, factor, weight FROM sumatra_risk_inputs WHERE scored = FALSE) ORDER KEY (account_id) BATCH SIZE 50000 LOOP FOR rec IN batch LOOP score := rec.gross * rec.factor / rec.weight; -- weight = 0 raises ZERO_DIVIDE UPDATE sumatra_risk_inputs SET risk_score = score, scored = TRUE WHERE account_id = rec.account_id; END LOOP; COMMIT; -- per-batch durable checkpoint END LOOP; EXCEPTION WHEN ZERO_DIVIDE THEN STOP; -- bad denominator: stop, keep committed batches WHEN OVERFLOW THEN STOP; WHEN OTHERS THEN RAISE; -- anything unexpected: surface it loudly END; ``` What to notice: * The `UPDATE` here sets **two named columns** rather than `SET ROW` — a sparse update. Only those columns are written; everything else is untouched, and the read projects just the columns it needs. * On a fault, `STOP` discards only the in-flight batch and exits with code `2`. Already-committed batches remain a valid prefix — and because processed rows are flagged `scored = TRUE`, re-running the same program picks up exactly where it stopped. * `WHEN OTHERS THEN RAISE` is the recommended tail: anticipated faults stop gracefully, everything else stays loud. ## Where to go deeper * [Programs and batches](/sumatra/programs) — the rules behind read blocks, write routing, and commit grains. * [Types, expressions, and control flow](/sumatra/language) — the full language reference, including records and keyed collections. * [Exceptions and recovery](/sumatra/errors) — the fault model in detail. * [Stored procedures](/sumatra/procedures) — turn any of these programs into a named, schedulable procedure.