Use incremental loads and backfills

An incremental query keeps the rows already published and asks SQL Server only for rows beyond the table's stored high-water mark. Backfill windows recover late-arriving rows without discarding the rest of the table.

Declare an incremental key

The key must be an output column with an integer or timestamp type:

[[queries]]
source = "reporting"
file = "queries/incidents.sql"
table = "incidents"
incremental_key = "opened_at"

SELECT
    id,
    opened_at,
    priority
FROM dbo.incidents;

The key name may contain only letters, digits, and underscores. The SQL file must not have a top-level ORDER BY, because Skiff wraps the query in a filtered subquery.

How a normal refresh advances

On the first refresh, or when no published table exists, Skiff loads the complete query result.

On later normal refreshes, it reads the maximum key from the published DuckDB and runs the source query with this effective predicate:

incremental_key > previous_high_water_mark

Skiff copies the previous table into the private generation and appends the newly loaded rows. Checks and atomic publication still apply to the complete result.

The key should be stable and monotonically increasing. A row inserted or corrected at or below the current watermark is not included by the next normal refresh.

Backfill a half-open window

Use matching integer bounds:

skiff refresh --since 100000 --until 200000

Or timestamps:

skiff refresh \
  --since '2026-08-01 00:00:00' \
  --until '2026-08-08 00:00:00'

Bounds use [since, until): the start is included and the end is excluded. Skiff deletes that slice from each incremental table in the private generation and inserts the source rows returned for the same slice.

Both bounds are required, must be the same type, and must increase. Accepted timestamps are YYYY-MM-DD, YYYY-MM-DD HH:MM:SS, or YYYY-MM-DDTHH:MM:SS.

Non-incremental tables are rebuilt normally during a window refresh.

Force a complete rebuild

skiff refresh --full

Full mode ignores stored watermarks and rebuilds every table from its full query result. Use it after intentionally changing an incremental key, repairing history outside a bounded window, or resolving an append failure caused by an incompatible schema change.

--full cannot be combined with --since or --until.

Plan for limits and schema changes

The row limit applies to rows loaded by each source query during the current run, not the retained total in the final table. A large initial or full build may therefore need a higher reviewed max_rows than routine incremental refreshes. Set max_rows = -1 only when that query is intentionally unbounded; the timeout still applies.

Normal and window modes append staged rows to the prior table shape. If the query's schema changed incompatibly, Skiff tells you to run skiff refresh --full. Review the reported drift and downstream effects before rebuilding.

MCP always performs normal automatic mode. Run backfills and full rebuilds through the CLI after human review.