Cursors and state

An incremental table has a cursor_column, such as updated_at or id. Each run reads from the destination’s maximum of that column. SQLLoad doesn’t store the position anywhere else: it reads it from the table, so a table you change, truncate or drop by hand is followed on the next run.

$ sqlload load app__orders
…
    └── cursor  updated_at from 2026-10-12 16:00:00.000000+00 (destination maximum)

Cursor settings

Setting	Meaning
cursor_column	The column runs continue from: an integer, date, timestamp or text column.
cursor_start	Where the first run starts, when the table is empty. Without it, the first run reads everything.
lookback	Read this far before the maximum, such as "1h" or "2d", to pick up late rows. Date and timestamp cursors only; a date cursor moves back by whole days. Not with append.
lookback_from	From 0.26.0. destination (default) counts lookback back from the destination’s maximum; now counts it back from the time of the run. See Reload a window.
lookahead	From 0.26.0. Read only cursor values before now plus this; a date cursor reads up to and including today plus that many days. Date and timestamp cursors only.
append_cursor_inclusive	false (default) reads strictly after the maximum; true reads from the maximum itself, inserting its rows again on every run. See append.

Where the read starts, in order:

1. --start-cursor, when given.
2. With lookback_from = "now", now minus lookback, on every run, the first one too. Otherwise the destination’s maximum of cursor_column, minus lookback. append reads strictly after it. From 0.27.0, plan prints that read as after the maximum; before, it printed from.
3. cursor_start, when the table is empty.
4. Otherwise, everything.

plan says which one applies, on the table’s cursor line.

From 0.27.0, text cursors compare by Unicode code point on both sides, whatever collation the source column or the destination uses, so 'B1' comes before 'a9'. Postgres sources and targets compare with COLLATE "C", and Snowflake with utf8. Before, each side used its own collation. A linguistic one, such as en_US or a case-insensitive ICU collation, could then read rows again on every run.

• Postgres stops using a plain index on a text cursor for the read, the destination’s maximum and delete_insert’s deletes, unless the column is already COLLATE "C". Create the index as CREATE INDEX ... (event_key COLLATE "C"). On Snowflake, a cursor column declared with another collation may prune fewer micro-partitions.
• If an existing table’s keys only increase in a linguistic order (apple1, Banana2, cherry3, Date4), code point order puts some new keys below the maximum, so they are not read. Use keys that increase in either order, such as zero-padded numbers, ULIDs or ISO timestamps.
• DuckDB, BigQuery and Databricks compare by code point unless a dataset, schema, table or column sets a collation, which SQLLoad doesn’t override.

Backfilling a range

--start-cursor and --end-cursor read a range instead, from the start up to but not including the end. Values are typed from each selected table’s cursor: an integer, an RFC 3339 timestamp or a date.

$ sqlload plan app__orders --start-cursor 2026-10-01 --end-cursor 2026-10-08
…
    └── cursor  updated_at from 2026-10-01 00:00:00.000000+00 (--start-cursor), below 2026-10-08 00:00:00.000000+00 (--end-cursor)

Every selected table must have the same kind of cursor. Select tables of one cursor type per run.

Batches

A large load commits along the way instead of all at the end, so a failure keeps what committed and the next run carries on from there. The exception is delete_insert into ClickHouse read by cursor, which publishes once per run (last row below). plan shows how each table commits on its batch line.

Table	Commits
batch_size = "1d" (date or timestamp cursor) or batch_size = 1000 (integer cursor)	One cursor window at a time, up to 100,000 windows.
A Postgres table whose cursor_column leads an index, without batch_size	About every 1 GB, reading in cursor order (NULLs first), only between distinct cursor values. batch_size = "none" opts out.
Other cursor tables, text cursors, and append without a cursor or logged state	Once per read, since committing part of an unordered read could skip or duplicate rows.
Files sources	At 1,000 files or about 1 GB; under delete_insert a partition is never split.
replace and --reload	Once, at the end: the new table is swapped in.
delete_insert into ClickHouse, in windows or cursor order	Once, at the end: everything read is staged and the whole range published together. A stopped run publishes nothing, so split a long backfill with --end-cursor.

With batch_size, the first run needs a start, from cursor_start or --start-cursor. A Postgres integer cursor without --end-cursor ends at its maximum when plan ran.

[sources.app.tables."public.orders"]
description = "Orders, one row per order."
cursor_column = "updated_at"
cursor_start = "2026-09-01"
batch_size = "7d"
lookback = "1h"

$ sqlload load
…
    ├── batch   10 windows of 7days, one commit each
    ├── note    windows are read in one REPEATABLE READ snapshot, held open on the source for the whole table load (mind idle_in_transaction_session_timeout and vacuum)
    └── cursor  updated_at first run, from cursor_start 2026-09-01 00:00:00.000000+00
…
loaded   raw.app__orders  1,001 rows  0.1s

A Python table with batch_size is called once per window, with ctx.cursor_value and ctx.cursor_end set to it.

Logged state

Some positions aren’t in the data, such as an API’s sync token. A Python table with state="logged" keeps its position in ctx.state, and SQLLoad records it in an append-only run log with each commit. Most projects never need it: a table with a cursor_column finds its position in the loaded data.

@shop.table(write_strategy="append", state="logged")
def events(ctx: sl.Context):
    """Shop events, read page by page with a sync token."""
    token = ctx.state
    while True:
        page = fetch_events(since=token)
        yield from page.items
        token = page.next_token or token
        yield sl.checkpoint(token)
        if not page.has_more:
            break

• ctx.state is the position the last successful run left: None on the first run, after --reload, or after sqlload logged-state reset --clear.
• yield sl.checkpoint(token) after a page’s rows marks exactly what those rows cover. A run that fails part-way resumes from the last committed checkpoint without loading any row twice. sl.checkpoint needs state="logged" and a JSON value.
• Rows yielded after the last checkpoint load again on the next run, unless the function leaves the final position in ctx.state. Once a function yields a checkpoint, only checkpoints move the position during the run; a value it leaves in ctx.state when it finishes still becomes the final one.
• sl.checkpoint(None) clears the position, so the next run starts from the beginning. For an API whose last page has no next token, keep the last real one, as or token does above.
• Without checkpoints, assign ctx.state once per page. Each commit records the value ctx.state had one assignment before its current one, so a run that fails part-way never loses rows, but under append the next run may load up to about two pages again. Assigning more than once per page can lose rows after a crash.

The run log is _sqlload_runs in the table’s schema, or objects under <table folder>/_sqlload/runs/ on a files target. It’s written in the same transaction as the data where the warehouse allows it, so a failed load never moves the position.

A logged table with a cursor_column also logs the data’s cursor. When the log and the data disagree, the data wins, and the load appends a visible reconciliation. Deleting the log is safe: the next run reads as a stateless one would.

Inspecting and correcting the log

$ sqlload logged-state show
raw.shop__events  state 4 (run 54ab366a-bca1-4088-9723-4c6d1723ad02 at 2026-11-09T18:02:47.844103Z)
  2026-11-09T18:02:47.552256Z  e1bf5051-3333-4fdd-aa31-18bf2d74a51c  run  - -> -, state 2  6 rows
  2026-11-09T18:02:47.844103Z  54ab366a-bca1-4088-9723-4c6d1723ad02  run  - -> -, state 4  6 rows

logged-state verify compares the logged cursor with the data. logged-state reconcile --yes adopts the data’s position where they disagree. logged-state reset --to VALUE --yes sets the next position, and --clear forgets it. Nothing is ever deleted from the log; each correction is appended. load --reload also starts a logged table over.

A record SQLLoad can’t read, because it’s corrupt or written by a newer version, blocks only its own table. logged-state show lists it, and a reset ... --yes starts a new position after it.

See sqlload logged-state for every subcommand and flag.