What is Sumatra?

Sumatra is a procedural language for the database your data lives in. You write row-by-row logic — loops, variables, conditionals, exception handlers — around plain SQL reads and writes, and the toolchain compiles the program into a native binary that runs it against your database, batching reads and routing every write to the lightest correct SQL form automatically. The language is built for the new class of analytical warehouse — object storage beneath elastic, shared compute — and runs against Firebolt and Snowflake. The engine underneath is a profile, not a premise: the backend is declared per-database by the connection string, and the program’s SQL surface is whatever the connected engine speaks. Firebolt is the concrete engine behind every example in this documentation — the proof runs deepest there. Here is a complete Sumatra program. It computes a per-customer running balance — each row’s result depends on the previous row’s result, the kind of logic SQL alone expresses only awkwardly:

balance.sumatra

DECLARE
  running       NUMERIC(14,2) := 0;
  last_customer BIGINT        := NULL;
BEGIN
  FOR batch IN (SELECT entry_id, customer_id, delta, running_balance FROM 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;
        last_customer := rec.customer_id;
      END IF;

      running := GREATEST(running + rec.delta, 0);
      rec.running_balance := running;

      UPDATE ledger SET ROW = rec WHERE entry_id = rec.entry_id;
    END LOOP;
    COMMIT;
  END LOOP;
END;

No connection handling, no pagination code, no staging tables, no MERGE statements — the compiler produces all of that. The program reads in 50,000-row batches, carries running across every row and every batch in order, and commits each batch as a durable checkpoint.

The problem it solves

Modern analytical engines — Firebolt among them — deliberately ship no stored procedures and no procedural SQL dialect. Set-based SQL covers enormous ground, but the moment a pipeline needs a step that carries state across rows, branches on a value the data just produced, or loops until a computed condition is met, that step has to leave the database: a Python script here, a Spark job there, dbt orchestrating around the gap, and data movement stitching it together. Sumatra collapses that split. The procedural mid-work is written in one small language and executes as batch-shaped SQL on the engine itself, next to the set-based steps it belongs with.

Approach	What it gives you	Where it stops
The engine’s SQL alone	Fast, distributed set transforms	No carried state, no runtime branching, no loops
dbt	Orchestrated set steps	Control flow is compile-time templating, not runtime data
Python / Spark	Full procedural power	Data leaves the platform; connections, batching, staging, and retries are all yours to build
Sumatra	Procedural logic over batched reads and writes	Runs inside the database, in one language — the claim is the combination

The full case — what staying in-database saves you operationally, and what the batch and merge machinery buys you technically — is on Why Sumatra.

The mental model

Every Sumatra program reduces to one shape: Read → Compute → Write

Read — pull rows from the engine in batches into memory. Never a per-row server query.
Compute — arbitrary in-memory work: loops, branches, carried variables, record and collection types. No server round-trips.
Write — push results back in batches. Never a per-row server write.

The loop in the middle never touches the server — the engine receives SELECTs and MERGEs, nothing else. If you have written stored procedures in a classic procedural SQL dialect, the only real shift is this: the grain is a batch of rows, not a single row. Cursor-style row-at-a-time server access — natural on OLTP databases — is pathological on a distributed analytical engine, so Sumatra makes it inexpressible: the language’s grammar has no way to write a per-row server call. In exchange, the batch machinery (pagination, ordering, staging, write-back, commit grain) is entirely the compiler’s job, driven by two declarations on the read: ORDER KEY and BATCH SIZE. Beyond that frame, the language is deliberately the familiar stored-procedure idiom: DECLARE/BEGIN/END, := assignment, IF/ELSIF, CASE, WHILE, FOR loops, records, %ROWTYPE, keyed collections, EXCEPTION handlers, explicit COMMIT. A handful of new words cover the batch boundary: BATCH SIZE, ORDER KEY, PARAM, STOP.

How programs run

The sumatra shell compiles a program against the live catalog of the connected database — the database itself is the schema — then generates native code, builds it with g++ on your host, and runs it. The compiled program makes its own connection from the same DSN file and speaks batch-shaped SQL to the engine. Writes are routed, not written by hand:

A write that is already set-shaped (plain UPDATE/DELETE/INSERT ... SELECT) runs server-side as-is — rows never leave the engine.
Per-row writes captured inside a loop are staged and applied as one set-based MERGE per batch. You never write MERGE or manage staging tables yourself.

Programs run one-shot from a file (@prog.sumatra), or are stored in the database as named procedures and run with EXEC — compiled once, cached per host, and automatically recompiled when the source, the toolchain, or the table schemas change.

Semantics you can trust

Sumatra’s answer for what a program means is deliberately boring: it means what the engine underneath means. Values, types, rounding, overflow, NULL handling — the in-memory evaluator is bit-exact to the bound engine’s own semantics (Firebolt’s, in these docs’ examples), so moving a computation between the read SQL and the procedural middle never changes the answer, and a program never invents behavior the engine does not have. Three properties are worth calling out:

Determinism. A compute program’s result is declared-order deterministic: ORDER KEY fixes a strict total order, state carries across batch boundaries, and the answer is invariant under BATCH SIZE. An order tie or a NULL key at runtime is a loud error, never a silent skip or duplicate.
Loud failure. Overflow, division by zero, and lossy surprises are errors, never silently wrong values. Committed batches always survive a fault as a durable prefix; recovery is explicit and yours.
Honest rejection. A program the model cannot run well — a nested read, a per-row server op, an unbounded server-dependent loop — is rejected at compile time with a named reason. The failure mode is a compile error, not a production performance cliff.

Status at a glance


Current version	0.2.0
Platform	Linux x86-64
Backends	The new class of analytical warehouse — Firebolt and Snowflake. The DSN scheme declares the backend per database; the examples in these docs run on Firebolt
Toolchain	`sumatra` (shell) and `sumatra-compile` (offline inspector), plus `g++` on your host
Distribution	A single tarball; no dependencies beyond the C++ toolchain and libcurl

Sumatra is built by the Wirekite team and documented here, but it is a standalone product: it is not part of the Wirekite replication pipeline, is not invoked by Wirekite, and nothing in this section depends on Wirekite in any way.

Reading order

The section is laid out like a book — read it front to back:

Why Sumatra — the case for in-database transformation, and the batch and merge engines in depth.
Quickstart — install, connect, and run your first two programs.
Types, expressions, and control flow — the language itself: variables, operators, conditionals, loops, records, and keyed collections.
Programs and batches — Sumatra’s own layer on top: the program skeleton, batched reads, write routing, commit grains and transactions, then pipelines and dynamic table names.
Exceptions and recovery · Stored procedures — faults, restarts, and shipping programs as named, scheduled procedures.
Learning by example — nine complete programs, graded from the smallest possible to pipelines and dynamic targets.
Reference: The sumatra shell · Built-in functions · Limitations.

Introduction

Getting Started

The Language

Learning by Example

Reference

The problem it solves

The mental model

How programs run

Semantics you can trust

Status at a glance

Reading order

​The problem it solves

​The mental model

​How programs run

​Semantics you can trust

​Status at a glance

​Reading order

The problem it solves

The mental model

How programs run

Semantics you can trust

Status at a glance

Reading order