Backfill Plugin (beta)
@chkit/plugin-backfill builds a saved plan from ClickHouse’s physical partitions and sorting keys, then executes its chunks with local checkpoints or a managed job. This page describes the current TypeScript planner.
Choose a strategy
Section titled “Choose a strategy”| Requirement | Approach |
|---|---|
| Populate an existing MV’s empty destination from historical source rows | mv_replay, selected automatically from your schema’s materialized views |
| Replay rows into the same table | Table copy, only after reviewing the engine and retry/duplicate semantics |
Copy the rows of a chkit generate --cutover into its shadow table | --cutover <database.table> on plan or submit (cutover copy) |
| Change sorting/partition keys or transform source rows into a shadow table | A zero-downtime cutover, or a reviewed tracked SQL migration with copy, validation and swap |
MV replay runs each materialized view’s SELECT over the source chunks and inserts the result into its destination. A destination fed by several views replays their queries together with UNION ALL. Source-based chunking requires every view to resolve to the same simple FROM table; verify that source in the saved plan. Unresolved or different sources fall back to destination metadata and are unsuitable for this empty-target bootstrap. With a resolved source, chunk sizing uses the source table, so the destination may start empty. Define the source, destination and MVs in the configured schema before planning.
Use an empty historical aggregate target and coordinate writers so live materialization does not overlap the historical replay. Compare the final grouped totals with the source; a completed checkpoint alone does not establish aggregate correctness.
bun add -d @chkit/plugin-backfill# npm alternative:npm install --save-dev @chkit/plugin-backfillRegister it alongside your existing schema/connection configuration:
import { defineConfig } from '@chkit/core'import { backfill } from '@chkit/plugin-backfill'
export default defineConfig({ schema: './src/db/schema/**/*.ts', plugins: [backfill({ stateDir: './chkit/backfill', maxChunkBytes: 1024 ** 3, requireIdempotencyToken: true, failCheckOnRequiredPendingBackfill: true, })], // Keep your explicitly configured clickhouse connection here.})Planning and local execution require the real clickhouse connection. Obtain URL, username, password and database; do not substitute localhost/default credentials when these are unknown. Use matching chkit/core/plugin versions. Python is in beta; consult its separate documentation rather than assuming identical planning flags.
Partition selection and chunk boundaries
Section titled “Partition selection and chunk boundaries”The planner reads active physical partitions from system.parts, sizes them using uncompressed input-byte metadata (falling back to on-disk bytes when necessary), and divides large partitions along sorting-key ranges. Plans contain partition IDs and the range predicates used to select rows.
--max-chunk-bytescontrols approximate uncompressed input bytes per chunk. It is not a hard upper bound: unsplittable keys or tables without usable sorting keys can exceed it. It is not a time interval.- Optional
--fromand--tofilter partitions usingsystem.parts.min_time/max_time, ormin_date/max_datewhen the partition key uses aDatecolumn. Partitions whose metadata overlaps the interval are eligible. Empty parts (such as those a DELETE mutation leaves) report a zero minimum and do not move a partition’s start to 1970. - These flags do not add an exact timestamp-column WHERE filter to every row. An overlapping partition can include rows outside the supplied interval.
- Unpartitioned tables and tables with a non-temporal partition key (or one on a
Date32column) report epoch timestamps in this metadata. A recent date filter may therefore select no partitions even though a timestamp column contains recent rows. For whole-table replay, omit both filters and review all selected partitions. For an exact row-level interval, use explicit reviewed SQL with the required predicate or design a suitable source; do not reinterpret the partition filter as that predicate.
--time-column, --chunk-hours, --force-large-window and --force are not current planning flags. Older time-windowed examples do not describe this planner. The accepted legacy config fields timeColumn/chunkHours do not switch it back to timestamp-column chunking.
Local plan, run and verify
Section titled “Local plan, run and verify”First migrate the target table and materialized view through the normal schema workflow. Verify that the historical target is empty and that the source contains the expected rows. Pause writers for a simple bootstrap; concurrent ingestion requires a separately designed boundary.
# Example: replay the complete source history into an empty MV destination.chkit plugin backfill plan --target analytics.daily_event_counts --max-chunk-bytes 1G --json > backfill-plan.jsonRead the returned planId and planPath, then inspect that saved JSON. For this aggregate bootstrap, require the returned strategy to be mv_replay and the saved chunkPlan.table to identify the intended source. Schema-loading failures can fall back to table-copy mode; do not execute that plan against an aggregate destination. Confirm the target, source queries, selected partitions, sort-key ranges and environment before running. plan reads live metadata and saves local state but does not execute replay INSERTs.
chkit plugin backfill run --plan-id <planId> --concurrency 1 --jsonchkit plugin backfill status --plan-id <planId> --jsonchkit status --jsonchkit check --strict --jsonCheck the data separately. For a destination with day, event_type, and total, compare its grouped totals with the source query used by the MV:
chkit query "SELECT toDate(occurred_at) AS day, event_type, count() AS total FROM analytics.events GROUP BY day, event_type ORDER BY day, event_type" --jsonchkit query "SELECT day, event_type, sum(total) AS total FROM analytics.daily_event_counts GROUP BY day, event_type ORDER BY day, event_type" --jsonThe result rows must match exactly; also verify that the source rows are unchanged. Store review evidence without committing sensitive production data.
Cutover copy
Section titled “Cutover copy”A zero-downtime cutover generated with --cutover-copy backfill leaves the copy of the existing rows to this plugin. Once chkit migrate --apply has stopped at the cutover’s copy check, plan the copy by naming the table instead of a target:
chkit plugin backfill plan --cutover analytics.events --jsonchkit plugin backfill run --plan-id <planId># or, on ObsessionDB:chkit plugin backfill submit --cutover analytics.eventschkit migrate --applyplan finds the cutover’s snapshot table (events__cutover_<stamp>_snapshot) and reads the shadow table, the unique key and the copied columns from its comment. The plan’s strategy is cutover_copy: chunks are sized over the snapshot and insert into the shadow, and each chunk skips the rows whose key the shadow already holds, so the mirrored rows are never copied and a chunk that runs again never duplicates rows. The skip reads the shadow’s keys within the chunk’s sort-key range when that range is on a copied column, otherwise every key of the shadow. Cutover chunks carry no deduplication token (a retry writes fewer rows than the first attempt, so a token would drop them) and read with select_sequential_consistency = 1. --target and --cutover cannot be combined. The plan is refused when no cutover of the table is pending, or when several are.
Resume and duplicate protection
Section titled “Resume and duplicate protection”chkit plugin backfill resume --plan-id <samePlanId> --json# To retry failed chunks deliberately:chkit plugin backfill resume --plan-id <samePlanId> --replay-failed --jsonchkit plugin backfill doctor --plan-id <samePlanId> --jsonResume uses saved progress and skips completed chunks. A completed run returns a successful no-op; it does not create a new plan. run refuses an existing run and directs you to resume. --replay-failed resets failed chunks for retry; the default resume does not request failed-chunk replay.
Before submitting anything, resume reads system.processes and system.query_log to find chunks that are still running or already finished, so it polls them instead of submitting them again. A chunk runs on the replica that received it, so on a multi-replica target these reads cover every replica of clickhouse.cluster, or of default (a multi-replica ObsessionDB service) when no cluster is set. If a replica cannot be read, resume stops with an error and submits nothing; re-run it.
Each chunk has a stable token within its saved plan. ClickHouse’s actual deduplication depends on the table engine, settings and deduplication window; a token is not a universal exactly-once guarantee. A crash after an INSERT but before checkpoint persistence needs engine-specific recovery testing. Verify totals before and after resuming.
Calling plan again generates a new random plan ID, even for the same target/options. It is a new replay, not an idempotent lookup of the previous job, and can duplicate aggregates. Preserve and reuse the original plan/checkpoint.
Commands and flags
Section titled “Commands and flags”All commands support the usual --json and --config flags. Replace chkit with bunx chkit or npx chkit for a local installation.
| Flag | Required | Meaning |
|---|---|---|
--target <database.table> | Unless --cutover | Existing destination table |
--cutover <database.table> | Unless --target | Copy the rows of the pending cutover of this table |
--from <timestamp> | No | Lower partition-metadata selection bound |
--to <timestamp> | No | Upper partition-metadata selection bound |
--max-chunk-bytes <size> | No | Chunk-size override, e.g. 1G, 500M |
run and resume
Section titled “run and resume”| Flag | Required | Meaning |
|---|---|---|
--plan-id <hex16> | Yes | Saved plan ID |
--concurrency <n> | No | Concurrent async queries, default 3 |
--poll-interval <ms> | No | Poll interval, default 5000 |
--force-environment | No | Explicitly bypass the saved environment check |
--replay-failed | Resume only | Reset failed chunks for retry |
status, cancel, and doctor accept --plan-id. cancel marks the saved run as cancelled; it does not kill ClickHouse queries or reliably interrupt a runner that is already executing. Stop that runner separately and inspect resulting data before planning further work.
Managed submit
Section titled “Managed submit”submit builds the same partition/sort-key plan and hands it to a managed backend. It accepts --target (or --cutover), optional --from/--to, --max-chunk-bytes, --title, and --concurrency (1–48). First register the ObsessionDB plugin alongside backfill(); it supplies the account/service commands. Submission requires an authenticated ObsessionDB session and selected service:
chkit obsessiondb loginchkit obsessiondb service selectchkit plugin backfill submit --target analytics.daily_event_counts --max-chunk-bytes 1GThe result supplies a job ID and console link. chkit plugin backfill list lists the selected service’s jobs (--service-slug <service> picks another service); status and cancel take --job-id for one job. Without the ObsessionDB plugin, list fails with setup guidance: local runs have no job list, so inspect them with status --plan-id. submit saves the plan like plan does and records the job ID next to it; chkit check then passes for that plan only once the ObsessionDB plugin confirms the job completed. When that job failed or was cancelled, submit --plan-id <planId> sends the saved plan again as a new job (with optional --title and --concurrency) and records the new job ID in place of the old one. See managed backfill jobs. If ObsessionDB is the active target, local plan/run/resume require an explicit --local choice; they do not silently bypass the selected managed service.
Configuration and persisted state
Section titled “Configuration and persisted state”Useful current configuration fields:
| Option | Default | Purpose |
|---|---|---|
stateDir | <metaDir>/backfill | Local plans/checkpoints |
maxChunkBytes | 10737418240 (10 GiB) | Numeric approximate input-byte target |
maxParallelChunks | 1 | Stored planning concurrency option |
requireIdempotencyToken | true | Include stable per-chunk insert tokens |
failCheckOnRequiredPendingBackfill | true | Fail the plugin check for unfinished plans |
The execution command’s --concurrency controls local async query submission. Inspect --help and the saved plan when changing options; legacy time-window/retry-policy fields in the schema are not evidence of timestamp-column chunking or universal automatic retry guarantees.
chkit/backfill/ plans/<planId>.json # saved target, environment, chunk boundaries and queries runs/<planId>.json # mutable progress checkpoint submissions/<planId>.json # job ID of a plan sent with submit, and its confirmed completionThe plan stores an environment fingerprint derived from URL origin and database. A changed URL origin or configured database is refused unless --force-environment is explicitly supplied. This fingerprint does not verify URL paths, credentials, schema or data identity. Plan creation requires a connection; there is no offline metadata plan to treat as verified.
Target safety and CI
Section titled “Target safety and CI”Automatic replay omits MATERIALIZED and ALIAS target columns and cannot supply EPHEMERAL inputs. Like a materialized view inserting into its TO table, mv_replay writes each view output column to the target column of the same name, whatever the column order; target columns the views do not output get their DEFAULT. plan reads the live target and each view’s output columns (also for a target not declared in your schema) and saves that column list in the plan as execution.targetColumns. Planning fails when a view outputs a column the target cannot take (missing, MATERIALIZED or ALIAS), or when the views feeding one target output different columns. run and resume refuse a plan whose target no longer has one of its planned columns; create a new plan. Every chunk INSERT sets asterisk_include_materialized_columns=0 and asterisk_include_alias_columns=0, so a settings profile that enables them does not change which columns SELECT * reads. Planning/execution refuses unsafe column combinations, unreadable metadata and missing targets. For a blocked target, use an explicitly mapped, reviewed SQL migration instead.
chkit check --strict --json includes plugins.backfill. For each saved plan, a missing, unfinished or failed local run can fail the gate; a relaxed policy is surfaced. If the plans directory is missing or empty, the check passes with requiredCount: 0: it cannot discover omitted historical work. A plan sent with submit and never run locally is left to its job backend: the backfill check counts it in submittedCount, not requiredCount, and the ObsessionDB plugin’s check fails until the job is confirmed completed. Without the ObsessionDB plugin loaded, the backfill check reports an unconfirmed submitted plan as backfill_submitted_unverified, with the same severity as backfill_required_pending. Once a local run of a submitted plan exists, the backfill check judges the plan by that run. Keep the state directory available to the machine running that check—local checkpoints are not migration-journal rows. A passing plugin check does not replace source preservation and aggregate validation.