---
title: Backfill Plugin (beta)
description: Plan and resume partition/sort-key SQL replays, including historical materialized-view aggregates.
sidebar:
  order: 4
  badge:
    text: Beta
    variant: caution
---

`@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.

:::caution[Choose the data operation first]
A backfill writes rows. A direct-table replay selects from and inserts into the **same table**; on ordinary MergeTree this can duplicate logical rows. For a transformation into a replacement table, use the [data-preserving migration recipe](/guides/data-preserving-migrations/), rather than treating table replay as UPDATE.
:::

## 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](#cutover-copy)) |
| Change sorting/partition keys or transform source rows into a shadow table | A [zero-downtime cutover](/guides/data-preserving-migrations/), 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.

## Setup

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

Register it alongside your existing schema/connection configuration:

```ts
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](/python/overview/) rather than assuming identical planning flags.

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

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

```sh
# 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.

```sh
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:

```sh
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.

## Cutover copy

A [zero-downtime cutover](/guides/data-preserving-migrations/) 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:

```sh
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.

## Resume and duplicate protection

```sh
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.

## Commands and flags

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

### `plan`

| Flag | Required | Meaning |
| --- | --- | --- |
| `--target <database.table>` | Unless `--cutover` | Existing destination table |
| `--cutover <database.table>` | Unless `--target` | Copy the rows of the pending [cutover](#cutover-copy) 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`

| 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`

`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](/obsessiondb/overview/) alongside `backfill()`; it supplies the account/service commands. Submission requires an authenticated ObsessionDB session and selected service:

```sh
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](/obsessiondb/backfills/#re-submit-a-failed-or-cancelled-job). 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

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.

```text
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.

## 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](/obsessiondb/backfills/#submitted-jobs-in-chkit-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.
