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 plan app__orders
…
    ├── reads   updated_at >= 2026-10-12 16:00:00.000+00 (warehouse maximum)
…

Cursor settings

Setting	Meaning
cursor_column	The column runs continue from: an integer, date, timestamp or text column. From 0.51.0, a list of columns of one type family, such as ["updated_at", "created_at"], reads the first non-NULL of them in each row. See NULL cursor values.
null_cursor	From 0.51.0. fail (default), include or ignore: what a read does with source rows whose cursor is NULL. See NULL cursor values.
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, which plan shows as > rather than >=.
3. cursor_start, when the table is empty.
4. Otherwise, everything.

plan says which one applies, on the table’s reads 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.

NULL cursor values

A read from the destination’s maximum compares each row’s cursor with it, and a NULL compares with nothing. A row whose cursor is NULL loads on the first run, which reads everything, and is never read again while its cursor stays NULL: an edit to it, or a new row with a NULL cursor, never loads. Systems that set a modified date only after the first edit leave it NULL on every row never edited.

From 0.51.0, a load that would skip such rows fails the table instead. Before the read, SQLLoad counts the source rows with a NULL cursor (SELECT count(*) ... WHERE <cursor> IS NULL on a database or warehouse source; a Python table counts the rows it yields). On a database or warehouse source, the table fails before its read when there are any, and nothing is written. A Python table counts while it reads, so it fails at the end of the window holding them: the windows before it are already committed, and that window and the ones after it aren’t written:

$ sqlload load
…
failed  raw.app__orders  2 source rows have a NULL updated_at, which an incremental read never selects, so they would never load; set null_cursor = "include" to read them on every run, list a column that is always set to coalesce with it (such as cursor_column = ["updated_at", "created_at"]), or set null_cursor = "ignore" to skip them
…

Choose one fix per table:

Fix	Effect
cursor_column = ["updated_at", "created_at"]	The cursor is the first non-NULL of the columns in each row (COALESCE), for the read, the destination’s maximum, lookback and batch_size. A row never edited is read by its created_at, and again once its updated_at is set. The columns must be of one type family: integers, text, dates, timestamps, or timestamps with a time zone. A row where every one is NULL follows null_cursor.
null_cursor = "include"	Every run reads the rows whose cursor is NULL as well as its range: cursor >= ? OR cursor IS NULL. merge only, which merges them again on each run; plan refuses it on append and delete_insert, which would insert them again on every run.
null_cursor = "ignore"	Skip them, for tables where a NULL cursor marks rows that should not load.

[sources.app.tables."public.orders"]
description = "Orders, one row per order."
write_strategy = "merge"
unique_key = "{primary_key}"
cursor_column = ["updated_at", "created_at"]

plan names the rule on the table’s reads line. fail the load shows when the source reports the cursor column nullable, and nothing when it reports it NOT NULL, which skips the count:

$ sqlload plan app__orders
…
    ├── reads   updated_at >= 2026-10-12 16:00:00.000+00 (warehouse maximum) (NULL cursors: included)
…

null_cursor	reads line ends with
fail, cursor nullable at the source	(NULL cursors: fail the load)
include	(NULL cursors: included)
ignore	(NULL cursors: skipped)

The count is one more query on the source before each read that has a lower or upper bound. It filters on the cursor alone, so its cost depends on the source:

• On Postgres, a cursor column without an index makes it a sequential scan of the table.
• On BigQuery, a table read through a read session gets a query job for it, which needs bigquery.jobs.create (as in bigquery.jobUser) and bills a scan of the cursor column. See BigQuery permissions.

null_cursor = "ignore" and null_cursor = "include" skip the count, as does a cursor column the source declares NOT NULL.

Sources differ in what they check:

• Postgres, ODBC, Snowflake and BigQuery sources count with one query per load, and a table read through sql counts that query’s rows. ODBC counts the same way under either cursor_bounds.
• A Python table counts the rows it yields with a NULL cursor. A function that filters by ctx.cursor_value itself drops them before SQLLoad sees them, so yield them too, or under null_cursor = "include". A Python table takes one cursor_column: yield the first set value as one field and name that field.
• A files source’s cursor is a partition or path column, set from each file’s path, so it is never NULL. Its cursor_column is one column.
• A files target, and delete_insert, take one cursor_column: they partition and replace by one column’s values.

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
…
    ├── reads   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 of one table shows how it commits, on its commits line, or batch for a table with neither a cursor nor logged state.

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. Other sources can’t report their maximum, so an integer batch_size there needs --end-cursor. From 0.51.0 that includes --reload, which reads the windows from cursor_start (or --start-cursor) up to --end-cursor into a new table and swaps it in once at the end. The rebuilt table holds only that range; see Reloading.

$ sqlload plan --reload --end-cursor 200000 shop__orders
…
└── raw.shop__orders                                recreate from the whole source (--reload)
…
    ├── reads   order_id reload, from cursor_start 0, below 200000 (--end-cursor)
…
    └── commits 4 windows of 50000, read one after another into a new table, swapped in once at the end

Without --end-cursor, plan stops and asks for it: “an integer batch needs –end-cursor to know where the last window ends, with –reload too”.

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

$ sqlload plan
…
    ├── reads   updated_at >= 2026-09-01 00:00:00.000+00 (first run, from cursor_start)
    ├── commits 11 windows of 7days, one commit each
    ├── route   read as Arrow -> DuckDB takes Arrow
    └── 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)
…
$ sqlload load
…
loaded  raw.app__orders  1,000 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.