Learning by example

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.

Tables

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;

expire.sumatra

-- 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.

Tables

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);

grades.sumatra

-- 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.
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.

Tables

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);

balance.sumatra

-- 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.

Tables

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;

apply_changes.sumatra

-- 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.

Tables

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;

region_rollup.sumatra

-- 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;

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.

Tables

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;

score.sumatra

-- 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 — the rules behind read blocks, write routing, and commit grains.
Types, expressions, and control flow — the full language reference, including records and keyed collections.
Exceptions and recovery — the fault model in detail.
Stored procedures — turn any of these programs into a named, schedulable procedure.

Introduction

Getting Started

Writing Programs

Reference

1. The smallest program: set-DML

2. Branching per row: CASE and write-back

3. Carried state: a running balance

4. Runtime-routed writes: insert, update, or delete per row

5. Parameters: one program, many runs

6. Exceptions: fail safe, keep the checkpoint

Where to go deeper

​1. The smallest program: set-DML

​2. Branching per row: CASE and write-back

​3. Carried state: a running balance

​4. Runtime-routed writes: insert, update, or delete per row

​5. Parameters: one program, many runs

​6. Exceptions: fail safe, keep the checkpoint

​Where to go deeper

1. The smallest program: set-DML

2. Branching per row: CASE and write-back

3. Carried state: a running balance

4. Runtime-routed writes: insert, update, or delete per row

5. Parameters: one program, many runs

6. Exceptions: fail safe, keep the checkpoint

Where to go deeper