Skip to content

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.

RequirementApproach
Populate an existing MV’s empty destination from historical source rowsmv_replay, selected automatically from your schema’s materialized views
Replay rows into the same tableTable 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 tableA 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.

Terminal window
bun add -d @chkit/plugin-backfill
# npm alternative:
npm install --save-dev @chkit/plugin-backfill

Register 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.

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-bytes controls 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 --from and --to filter partitions using system.parts.min_time / max_time, or min_date / max_date when the partition key uses a Date column. 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 Date32 column) 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.

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.

Terminal window
# 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.json

Read 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.

Terminal window
chkit plugin backfill run --plan-id <planId> --concurrency 1 --json
chkit plugin backfill status --plan-id <planId> --json
chkit status --json
chkit check --strict --json

Check the data separately. For a destination with day, event_type, and total, compare its grouped totals with the source query used by the MV:

Terminal window
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" --json
chkit query "SELECT day, event_type, sum(total) AS total FROM analytics.daily_event_counts GROUP BY day, event_type ORDER BY day, event_type" --json

The result rows must match exactly; also verify that the source rows are unchanged. Store review evidence without committing sensitive production data.

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:

Terminal window
chkit plugin backfill plan --cutover analytics.events --json
chkit plugin backfill run --plan-id <planId>
# or, on ObsessionDB:
chkit plugin backfill submit --cutover analytics.events
chkit migrate --apply

plan 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.

Terminal window
chkit plugin backfill resume --plan-id <samePlanId> --json
# To retry failed chunks deliberately:
chkit plugin backfill resume --plan-id <samePlanId> --replay-failed --json
chkit plugin backfill doctor --plan-id <samePlanId> --json

Resume 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.

All commands support the usual --json and --config flags. Replace chkit with bunx chkit or npx chkit for a local installation.

FlagRequiredMeaning
--target <database.table>Unless --cutoverExisting destination table
--cutover <database.table>Unless --targetCopy the rows of the pending cutover of this table
--from <timestamp>NoLower partition-metadata selection bound
--to <timestamp>NoUpper partition-metadata selection bound
--max-chunk-bytes <size>NoChunk-size override, e.g. 1G, 500M
FlagRequiredMeaning
--plan-id <hex16>YesSaved plan ID
--concurrency <n>NoConcurrent async queries, default 3
--poll-interval <ms>NoPoll interval, default 5000
--force-environmentNoExplicitly bypass the saved environment check
--replay-failedResume onlyReset 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.

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:

Terminal window
chkit obsessiondb login
chkit obsessiondb service select
chkit plugin backfill submit --target analytics.daily_event_counts --max-chunk-bytes 1G

The 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.

Useful current configuration fields:

OptionDefaultPurpose
stateDir<metaDir>/backfillLocal plans/checkpoints
maxChunkBytes10737418240 (10 GiB)Numeric approximate input-byte target
maxParallelChunks1Stored planning concurrency option
requireIdempotencyTokentrueInclude stable per-chunk insert tokens
failCheckOnRequiredPendingBackfilltrueFail 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 completion

The 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.

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.