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

# Why Sumatra

> The case for transforming data inside the database — and the two engines that make it practical: the batch execution model and the merge write path.

Every data platform eventually needs a transformation step that
set-based SQL cannot express: a per-account state machine, a running
balance, a change feed where each row's fate depends on a value the
data just produced. Today that step usually leaves the database.
Sumatra's premise is that it shouldn't — and its two core mechanisms,
the **batch engine** and the **merge engine**, are what make staying
in-database practical rather than merely possible.

## Transformation without leaving the database

The conventional answer to procedural transformation is a round trip:
export the data to an external compute layer (a Spark cluster, a fleet
of Python jobs), transform it there, and load the results back. Every
part of that round trip is a cost you keep paying:

* **Data movement, twice.** The working set crosses the wire out, and
  the results cross back in — often the slowest and most expensive
  part of the pipeline.
* **A second platform to operate.** The external layer has its own
  sizing, patching, monitoring, credentials, and failure modes. For
  many teams the cluster exists *only* to host the procedural steps
  the database couldn't run.
* **Two failure domains.** A half-finished external job leaves state
  split across systems, and recovery logic has to reason about both.
* **A wider security surface.** Data now rests, at least transiently,
  outside the database — a second system to bring inside audit and
  compliance boundaries.
* **Schema drift.** The external job carries its own copy of schema
  knowledge, which silently rots as tables evolve.

A Sumatra program removes the second platform rather than managing it:

* The program **compiles against the live catalog** of the connected
  database — the database itself is the schema, so there is no copy to
  drift. If the schema changes after compile, the program refuses to
  run on stale assumptions and recompiles.
* Reads and writes are the **engine's own SQL**. Set-shaped work never
  leaves the engine at all; procedural work streams through the
  program in bounded batches and lands back as set-based writes. Rows
  are never parked in a second system of record.
* The runtime is **one native binary on a host you already have** — a
  process, not a platform. There is no cluster, no job framework, no
  scheduler service; cron and an exit code are enough.
* Failure semantics live in one place: committed batches are durable
  in the database, everything else rolled back — one system to reason
  about when something goes wrong.

The result: the pipeline's set steps and its procedural mid-work run
in the same place, against the same catalog, under the same
transactional rules.

## The batch engine

Sumatra's execution grain is the **batch** — and one declaration sets
it:

```sql theme={null}
FOR batch IN (SELECT entry_id, customer_id, delta, running_balance FROM ledger)
    ORDER KEY (customer_id, entry_id)
    BATCH SIZE 50000
LOOP
  ...
  COMMIT;
END LOOP;
```

That `BATCH SIZE` is one knob controlling four things at once — the
read chunk, the write flush, the commit checkpoint, and the memory
bound. Behind it, the compiler generates the machinery you would
otherwise hand-build:

**Keyset pagination, generated.** Chunked reads page by key, not by
offset — the shape of the generated fetch is:

```sql theme={null}
-- the shape of the generated fetch (illustrative)
SELECT ... FROM ledger
WHERE (customer_id, entry_id) > (last_seen_customer, last_seen_entry)
ORDER BY customer_id, entry_id
LIMIT 50000
```

This is exactly how pagination is hand-written by people who have been
burned by `OFFSET`: no quadratic re-scans, no scratch tables, no
manufactured row numbers. You declare the key; the cursor logic,
the boundary tracking, and the end-of-set detection are generated.

**An order you can trust.** `ORDER KEY` declares a strict total order,
and the toolchain treats it as a contract: a duplicate key or a NULL
in a key column at runtime is a loud error — never a silently skipped
or double-processed row. Carried state flows across batch seams in
declared order, which is what makes a 50,000-row-batched running
balance produce the identical answer to a single-pass run. That
invariance — **the same result at any batch size** — is a language
guarantee on the compute path, not a hope.

**Checkpoints for free.** A `COMMIT` inside the batch loop makes every
batch a durable checkpoint. A ten-hour backfill that faults at hour
nine keeps nine hours of committed work as a valid prefix, and the
standard done-flag idiom makes the re-run resume exactly where it
stopped — restartability falls out of the commit grain instead of
being engineered separately.

**Bounded memory.** `BATCH SIZE N` caps the client working set at N
rows regardless of table size; `BATCH SIZE ALL` is the explicit
whole-set choice for aggregating reads, where one distributed
evaluation of the query is the efficient shape.

**And the row-at-a-time trap is fenced off.** On a distributed
columnar engine, per-row server calls — the natural idiom on OLTP
databases — are catastrophically slow. Sumatra does not merely
discourage them; the grammar has no way to write a per-row server
query or a per-row commit. The language's promise is **per-row logic
at set-based cost**, and the promise is structural.

## The merge engine

The write side is where hand-rolled pipelines hide their sharpest
edges. Applying a mixed stream of inserts, updates, and deletes
correctly means writing a staged `MERGE`: create a scratch table, bulk
it full, get the match conditions and the operation arms exactly
right, clean it all up — and get it right again in every job that
writes.

In Sumatra you write only natural per-row statements:

```sql theme={null}
IF rec.new_balance < 0 THEN
  DELETE FROM accounts WHERE customer_id = rec.customer_id;
ELSIF rec.exists_flag THEN
  UPDATE accounts SET balance = rec.new_balance WHERE customer_id = rec.customer_id;
ELSE
  INSERT INTO accounts (customer_id, balance) VALUES (rec.customer_id, rec.new_balance);
END IF;
```

and the compiler turns each batch's captured changes into **one
set-based, op-tagged MERGE**:

```sql theme={null}
-- the shape of the generated write-back (illustrative)
MERGE INTO accounts t USING sumatra_stage_... m ON t.customer_id = m.customer_id
  WHEN MATCHED     AND m.op = 'd' THEN DELETE
  WHEN MATCHED     AND m.op = 'u' THEN UPDATE SET balance = m.balance
  WHEN NOT MATCHED AND m.op = 'i' THEN INSERT (customer_id, balance) VALUES (m.customer_id, m.balance);
```

What that buys you:

* **One statement per batch, whatever the mix.** Ten thousand rows'
  worth of per-row branching — some deleted, some updated, some
  inserted — lands as a single set-based apply, at set-based cost.
* **The staging lifecycle is invisible.** The scratch table is
  created, filled in bulk over the same SQL connection (no CSV
  export, no object-store hop, no COPY pipeline to build), applied,
  and dropped automatically. A crashed run's orphan staging table is
  reclaimed by the next run of the same program.
* **Only what changed is written.** A single-column `UPDATE` stages
  just the key and that column — a sparse update that leaves every
  other column untouched and lets the read project only what it
  needs. `SET ROW` writes the whole record when that is what you
  mean.
* **The lightest form wins.** Captured-and-staged MERGE is the
  *heaviest* route, used only when per-row compute demands it. A write
  that is already set-shaped runs as plain DML exactly as you wrote
  it, and a write that correlates to the batch as a set runs as one
  correlated statement — the compiler always routes each write to the
  cheapest form that is correct, with no keyword and no hints.

And it holds you to the invariants that make merges trustworthy:

* **One change per key per flush.** Two captured changes to the same
  merge key in one batch is a program error surfaced loudly — never a
  silent last-write-wins that quietly picks a survivor.
* **Keys are immutable in a write.** A statement that would change its
  own merge key is rejected at compile time, because a staged merge
  cannot express it correctly.
* **NULL keys never match**, exactly as SQL joins treat them — no
  accidental cross-matching through NULLs.

The net effect: the trickiest statement in analytical SQL is one you
never write, generated from per-row logic that says what you actually
mean — and the classic merge bugs are unrepresentable.

## Built to run unattended

The same machinery composes into an operational shape schedulers can
rely on. Every run ends in exactly one of three states — `0` completed,
`2` stopped cleanly with the committed prefix retained, `1` needs a
human — so cron can branch without parsing logs. Stored procedures
compile once and run from cache on any host, recompiling only when
source, toolchain, or schema actually changed — and each recompile is
announced. Concurrent duplicate runs of the same procedure with the
same arguments are refused engine-side, so an overlapping cron slot
cannot double-apply a batch.

## Engine-neutral by construction

Sumatra deliberately defines program meaning as **the connected
engine's meaning**: values, rounding, overflow, and NULL behavior are
the bound backend's semantics, verified bit-exact, and everything
engine-specific in the toolchain — type names, SQL dialect, semantic
constants — lives behind per-backend profiles rather than in the
language. Your read SQL is your engine's own dialect, relayed
verbatim.

The backend is declared by the DSN's URL scheme, per database.
**Firebolt is the first live backend** and the engine used throughout
these docs; the language surface you learn — batches, routing, merge
discipline, exceptions — is the portable part, designed to carry to
further engines as they are bound.

## See it in practice

* [Quickstart](/sumatra/quickstart) — run the round trip yourself in
  ten minutes.
* [Learning by example](/sumatra/examples) — the running balance,
  change-feed apply, and fail-safe scoring programs.
* [Programs and batches](/sumatra/programs) — the precise rules behind
  the batch and merge machinery described here.