Learning by example

Every program on this page is complete and runnable as written, and each comes from — or directly mirrors — 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                -- plain set-DML: runs on the engine
    SET state = 'expired'                -- exactly as written, rows never
    WHERE state = 'active'               -- leave the server
      AND last_seen < CURRENT_TIMESTAMP - INTERVAL '24' HOUR;

  DELETE FROM sumatra_session_tokens     -- second statement, same open
    WHERE revoked = TRUE;                -- transaction

  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)                     -- a strict order: required for a chunked read
      BATCH SIZE 4000                    -- rows arrive 4,000 at a time
  LOOP
    FOR rec IN batch LOOP                -- iterate the batch in memory
      CASE rec.band                      -- CASE *statement*: statements per branch
        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;
                                         -- captured per row; the whole batch
                                         -- lands as ONE statement
    END LOOP;
    COMMIT;                              -- this batch is now durable
  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)  -- each customer's rows arrive contiguous, in order
      BATCH SIZE 50000                   -- memory bounded; the answer is batch-size-invariant
  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;                     -- state flows row to row —
                                                          -- and across batch seams
      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        -- the data picks the operation, per row
        DELETE FROM sumatra_account      -- captured as op 'd'
          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;   -- captured as op 'u'
      ELSE
        INSERT INTO sumatra_account (customer_id, balance)
          VALUES (rec.customer_id, rec.new_balance);   -- captured as op 'i'
      END IF;
    END LOOP;                            -- the whole mixed change-set lands as
    COMMIT;                              -- ONE op-tagged MERGE, then commits
  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;                  -- values arrive on the command line,
PARAM want_date   DATE;                  -- in declaration order

BEGIN
  FOR batch IN (SELECT grp, SUM(amt) AS amt, COUNT(*) AS n
                FROM sumatra_param_src   -- aggregate in the engine; PARAM(...)
                WHERE region = PARAM('want_region') AND sale_date = CAST(PARAM('want_date') AS DATE)
                GROUP BY grp)            -- binds out-of-band, never spliced in
      BATCH SIZE ALL                     -- whole-set: the right grain for GROUP BY
  LOOP
    UPDATE sumatra_param_tgt             -- accumulate into existing groups —
      SET total = total + batch.amt, n = n + batch.n
      WHERE grp = batch.grp;             -- server-side, correlated to the batch

    INSERT INTO sumatra_param_tgt (grp, total, n)
      SELECT grp, amt, n FROM batch      -- insert the complement: together with
      WHERE grp NOT IN (SELECT grp FROM sumatra_param_tgt);   -- the UPDATE, an upsert
  END LOOP;
  COMMIT;                                -- both statements, one transaction
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. The incremental watermark: a program that parameterizes itself

Every declared variable is visible to the program’s own SQL as PARAM('name') — so a program can compute a cursor from its target and filter its read by it. The result is an incremental job with no command-line arguments at all: every run rolls forward exactly the days that arrived since the last run.

Tables

CREATE TABLE sumatra_wm_sales (
  day DATE,
  amt NUMERIC(12,2)
);

CREATE TABLE sumatra_wm_rollup (
  day   DATE NOT NULL,
  total NUMERIC(14,2),
  n     BIGINT
) PRIMARY INDEX day;

watermark_rollup.sumatra

-- Roll up any sales days newer than what the rollup already holds.

DECLARE
  wm DATE;
BEGIN
  SELECT MAX(day) INTO wm FROM sumatra_wm_rollup;   -- where did we stop last time?
  IF wm IS NULL THEN
    wm := DATE '1970-01-01';                        -- first run: everything is new
  END IF;

  FOR batch IN (SELECT day, SUM(amt) AS total, COUNT(*) AS n
                FROM sumatra_wm_sales
                WHERE day > CAST(PARAM('wm') AS DATE)   -- the variable, bound into the read
                GROUP BY day)
      BATCH SIZE ALL
  LOOP
    INSERT INTO sumatra_wm_rollup (day, total, n)
      SELECT day, total, n FROM batch;   -- append the new days, server-side
  END LOOP;
  COMMIT;                                -- the rollup itself is the new watermark
END;

What to notice:

PARAM('wm') inside the read refers to the declared variable — every declared scalar publishes into every server statement’s bind list under its own name, per execution. A non-text value reads back through a cast, exactly like an argument.
The watermark is derived from the target itself (MAX(day)), so the job needs no state file, no scheduler-managed cursor, and no arguments: re-running it is always safe and always incremental.
A variable that is unset or NULL at the moment a statement runs is absent from that statement’s binds — referencing it then is the engine’s own loud error. The IF wm IS NULL guard makes the first run explicit instead.

7. 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)    -- only unprocessed rows: a re-run
      ORDER KEY (account_id)             -- resumes where the last one stopped
      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           -- sparse update: writes just
        WHERE account_id = rec.account_id;              -- these two columns
    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.

8. A pipeline: the whole pass, once per slice

A counted loop at the top level repeats an entire read → compute → write pass — here, settling each month in its own transaction. The loop variable is visible to the read’s SQL as PARAM('m').

Tables

CREATE TABLE sumatra_settlements (
  id       BIGINT NOT NULL,
  month_no INT,
  amt      NUMERIC(12,2),
  settled  BOOLEAN,
  pass     INT
) PRIMARY INDEX id;

settle_year.sumatra

-- Settle every month of the year, one transaction grain per month.

BEGIN
  FOR m IN 1 .. 12 LOOP                  -- twelve passes: one month per iteration
    FOR batch IN (SELECT id, month_no, amt, settled, pass
                  FROM sumatra_settlements
                  WHERE month_no = CAST(PARAM('m') AS INT)   -- the loop variable,
                    AND settled = FALSE)                     -- bound per iteration
        ORDER KEY (id)
        BATCH SIZE 20000
    LOOP
      FOR rec IN batch LOOP
        rec.settled := TRUE;
        rec.pass    := m;                -- in compute, m is an ordinary INT
        UPDATE sumatra_settlements SET ROW = rec WHERE id = rec.id;
      END LOOP;
      COMMIT;                            -- checkpoint inside the iteration
    END LOOP;
  END LOOP;                              -- a month with nothing pending is a
END;                                     -- clean zero-row pass

What to notice:

The pipeline body holds a whole read block — legal precisely because the loop is at the top level; the same loop inside a row loop would be in-memory compute, where a read is a compile error.
Each iteration’s read binds the current m (published like any variable), so twelve passes each see only their own month’s rows — and a month with nothing pending is a clean zero-row no-op.
The bound is the program’s own (1 .. 12), never a per-row server call: twelve read-compute-write passes, each checkpointing per batch.
rec.pass := m uses the loop variable in compute — it is an ordinary read-only INT there. A WHILE pipeline works the same way, its predicate over carried variables a lookup can refresh — “repeat until the data says done”.

9. Dynamic table names: one program, many targets

A %TABLE parameter makes the table name a program input — the same logic run against interchangeable, shape-identical tables.

Tables

CREATE TABLE sumatra_region_us (
  id      BIGINT NOT NULL,
  amt     NUMERIC(12,2),
  flagged BOOLEAN
) PRIMARY INDEX id;

CREATE TABLE sumatra_region_eu (
  id      BIGINT NOT NULL,
  amt     NUMERIC(12,2),
  flagged BOOLEAN
) PRIMARY INDEX id;

flag_region.sumatra

-- Flag large amounts in whichever region table the run names.

PARAM tgt sumatra_region_us%TABLE;       -- compile against this table's shape;
                                         -- the run supplies the actual name
BEGIN
  FOR batch IN (SELECT id, amt, flagged
                FROM PARAM('tgt')        -- every use substitutes the runtime name
                WHERE flagged = FALSE)
      ORDER KEY (id)
      BATCH SIZE 10000
  LOOP
    FOR rec IN batch LOOP
      IF rec.amt >= 1000 THEN
        rec.flagged := TRUE;
        UPDATE PARAM('tgt') SET ROW = rec WHERE id = rec.id;
      END IF;                            -- the captured write targets it too
    END LOOP;
    COMMIT;
  END LOOP;
END;

sumatra --dsn my.dsn @flag_region.sumatra sumatra_region_us
sumatra --dsn my.dsn @flag_region.sumatra sumatra_region_eu

What to notice:

The declaration names a reference table (sumatra_region_us) — the program compiles against that shape; the run supplies the actual name, and a table whose shape drifted from the reference is refused at run start.
The runtime name must be a plain identifier (at most 128 bytes) — quotes, spaces, and punctuation are unspellable, so a dynamic name can only ever be what a static name could have been.
The name parameter works in the read’s FROM and as the captured write’s target — the two places a target table is named.

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

The Language

Reference

Learning by example

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. The incremental watermark: a program that parameterizes itself

7. Exceptions: fail safe, keep the checkpoint

8. A pipeline: the whole pass, once per slice

9. Dynamic table names: one program, many targets

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. The incremental watermark: a program that parameterizes itself

​7. Exceptions: fail safe, keep the checkpoint

​8. A pipeline: the whole pass, once per slice

​9. Dynamic table names: one program, many targets

​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. The incremental watermark: a program that parameterizes itself

7. Exceptions: fail safe, keep the checkpoint

8. A pipeline: the whole pass, once per slice

9. Dynamic table names: one program, many targets

Where to go deeper