Incremental Export Mode

When to use

Use mode: incremental when you only want to export rows that are new or updated since the last run. Best for:

• Append-only tables (events, logs, audit trails)
• Tables with a reliable updated_at timestamp
• Tables with a monotonically increasing ID
• Daily/hourly syncs where re-exporting everything is wasteful

SQL sources only (PostgreSQL, MySQL, SQL Server). incremental does not apply to MongoDB, a document store — use full or cdc there (../reference/mongodb.md).

Required fields

• cursor_column – the column used to track progress (must be monotonically increasing)

Minimal config

source:
  type: postgres
  url: "postgresql://user:pass@host:5432/dbname"

exports:
  - name: orders_incremental
    query: "SELECT id, user_id, product, price, status, updated_at FROM orders"
    mode: incremental
    cursor_column: updated_at       # tracks last exported value
    format: parquet
    destination:
      type: local
      path: ./output

Run it

# First run — exports all rows (no cursor yet)
rivet run --config orders.yaml --validate --reconcile

# Second run — only exports rows with updated_at > last cursor
rivet run --config orders.yaml --validate

# Check current cursor position
rivet state show --config orders.yaml

# Reset cursor to re-export everything
rivet state reset --config orders.yaml --export orders_incremental

What happens

1. First run: no cursor exists, so all rows matching the query are exported
2. Rivet records the maximum value of cursor_column as the cursor
3. Subsequent runs: Rivet wraps your query in a subquery, adds WHERE updated_at > <last cursor>, and orders by the cursor column (the cursor is an inlined literal on Postgres/SQL Server — Postgres uses the escaped E'…' form — and a bind parameter on MySQL)
4. Only new/updated rows are exported; cursor advances after successful write

Run 1 (no cursor):  SELECT * FROM (SELECT ... FROM orders) AS _rivet ORDER BY "updated_at"
                     → 5000 rows, cursor saved: 2026-04-05 23:59:59
                     (the sort applies even to the full first run — an index
                      on the cursor column matters)

Run 2 (with cursor): SELECT * FROM (SELECT ... FROM orders) AS _rivet
                       WHERE "updated_at" > E'2026-04-05 23:59:59' ORDER BY "updated_at"
                      → 47 rows (only changes since last run)

Cursor column tips

The cursor column must be:

• Present in the SELECT clause
• Monotonically increasing (new rows always have a larger value)
• Not NULL for rows you want exported

Batch size and tuning

Even in incremental mode, Rivet fetches rows in batches (not all at once). The batch_size from tuning: controls how many rows are fetched per FETCH call:

source:
  type: postgres
  url_env: DATABASE_URL
  tuning:
    batch_size: 5000            # rows per fetch (default: 10,000 for balanced)

exports:
  - name: orders_incremental
    query: "SELECT id, user_id, product, price, updated_at FROM orders"
    mode: incremental
    cursor_column: updated_at
    format: parquet
    destination:
      type: local
      path: ./output
    tuning:
      batch_size: 2000          # per-export override (takes precedence)

On the first incremental run (no cursor yet), all rows are exported. If the table has millions of rows, this first run behaves like a full export — so batch_size directly impacts memory and source load. Use a smaller batch_size (1,000-5,000) for wide tables or production databases.

See reference/tuning.md for all tuning parameters.

Common options

exports:
  - name: orders_incremental
    query: "SELECT id, user_id, product, price, updated_at FROM orders"
    mode: incremental
    cursor_column: updated_at
    format: parquet
    skip_empty: true            # a run with no new rows reports `skipped`
    meta_columns:
      exported_at: true         # add _rivet_exported_at for dedup downstream
    destination:
      type: local
      path: ./output

Switching to incremental

The usual path is a full load first, then mode: incremental on the same export. What the first incremental run does depends on the export’s previous mode (ADR-0033, matrix):

rivet load follows what each run holds. A run that re-read the whole table — a full load, or an incremental export’s first run — lands as a plain <table>. The first delta renames that table to <table>__changes, adds __op / __pos / __seq (NULL on the rows it already held) and makes <table> a view over it; the log keeps the table’s partitioning and clustering, and nothing is copied or dropped. A whole-table load onto a table rivet did not load, one whose partitioning or clustering differs from the config, or a view left by an earlier incremental load fails naming the difference and changes nothing — drop or rename the table, or align the config. The rename refuses the same way when the table’s columns differ from the export’s or a <table>__changes already exists beside it. An append also refuses a view at <table>, or a <table>__changes, that the state DB has no record of rivet loading: it exits 5 with RIVET_LOAD_TARGET_NOT_RIVETS and writes nothing. The record lives in the state DB rivet load opens, so that state DB must outlive the run (a volume, or a PostgreSQL state); with a fresh one every cycle, the second load is refused.

Settle window

Some rows keep changing for a while after they are inserted, and no column records when: a page view’s time-on-page arrives with the next hit, a session’s totals grow until it closes. A cursor exports such a row as soon as it appears, before its final values exist, and never sees the later write.

settle holds each row back until it is older than after by the source clock:

exports:
  - name: page_views
    table: page_views
    mode: incremental
    cursor_column: id              # a cheap primary-key range per run
    settle:
      column: server_time          # the row's insert time
      after: 1h                    # longer than the time the row keeps changing

• after takes s, m, h or d. The warehouse copy lags the source by that much.
• Without column, the cursor itself is aged (cursor_column, or the COALESCE in coalesce mode) — the right choice for an updated_at cursor.
• With a separate column, the cursor also stops below the first row that is still settling, so a row committed out of id order is never skipped.
• A row whose settle value is NULL never ages, so it is exported without waiting.
• The column must be a date or timestamp; zone-less values are compared as UTC.
• Deletes stay invisible to a cursor. Use mode: cdc when they must reach the warehouse.

Transactions that commit during a run

Each incremental read must see a transaction either whole or not at all. PostgreSQL and MySQL (InnoDB) read every statement from one snapshot, so they do. SQL Server does only when the database has a row-versioning option on:

Under locking READ COMMITTED a scan can pass a row, wait on a row a writer holds, and read it once the writer commits, so the scan returns that transaction half-applied. The cursor then moves past the rows it had already passed, and no later run reads them. Enable ALLOW_SNAPSHOT_ISOLATION (ALTER DATABASE [<db>] SET ALLOW_SNAPSHOT_ISOLATION ON), or set a settle window longer than your longest write transaction.

A snapshot does not cover everything. A transaction that stamps its rows, commits late, and ends up with a cursor value below a row that committed earlier is skipped on every engine, since each read already moved past it. settle guards that race: set after longer than your longest write transaction.

Troubleshooting

the stored cursor ... was written for ... – The export’s cursor changed (a new cursor_column, or a keyset chunk_by_key export switched to incremental on another column). The old value means nothing for the new column; rivet state reset --config ... --export <name> starts the new cursor with a full pass.

A run with no new rows reports success – Add skip_empty: true to record it as skipped (no file is written for 0 rows either way).

Data appears duplicated across runs – Ensure cursor_column updates when rows are modified. If rows are updated without changing updated_at, they will be missed.

Need to re-export all data – rivet state reset --config ... --export <name> clears the cursor.

rivet apply fails with invalid configuration: password missing – A prior rivet plan silently stripped the plaintext password: from the artifact (ADR-0005 PA9). Migrate to password_env: DB_PASSWORD in the config and re-generate the plan. See the WARN line in the plan output.

Composite cursor (nullable primary)

If your primary column can be NULL for some rows (e.g. updated_at only set on updates), see incremental-coalesce.md — progression switches to COALESCE(primary, fallback) via incremental_cursor_mode: coalesce.