CDC tables

How CDC tables keep one live row per key, what the two merge modes cost, and how to operate them.

Nightly Feature

This feature is currently in private preview. Contact [e-mail] to request early access.

A CDC table is a managed table that Firebolt maintains from a change stream: one live row per merge key, with source inserts, updates, and deletes applied continuously and in order. You query it like any other table; ingestion, staging storage, and background consolidation belong to the engine.

For source-specific setup, see Postgres CDC and MongoDB CDC; for exact DDL syntax, see CREATE CDC TABLE.

Creating a CDC table

CREATE CDC TABLE orders (
    order_id    BIGINT,
    customer_id BIGINT,
    amount      NUMERIC(12, 2),
    PRIMARY KEY (order_id) NOT ENFORCED
);

ALTER CDC TABLE orders ATTACH STREAM orders_changes;
ALTER CDC TABLE orders RESUME;

PRIMARY KEY ... NOT ENFORCED names the merge key: the column set that identifies a row across its lifetime, normally the source table’s primary key. Composite keys are supported. The key is trusted, not checked; one live row per key follows from applying the source’s changes in order, not from a uniqueness scan.

An optional PRIMARY INDEX clause sets the table’s sort order independently of the merge key, for example on an event-time column your dashboards filter by. By default the table is sorted and indexed on the merge key.

CREATE CDC TABLE only records metadata. ALTER CDC TABLE ... ATTACH STREAM names the stream that feeds the table; one stream feeds one table. Ingestion starts with ALTER CDC TABLE ... RESUME: the worker backfills from a snapshot of the source, then switches to the change feed. Both phases are visible in information_schema.cdc_ingests.

Semantics

The latest change wins, per key. The table applies changes in the order the stream delivers them, which is the order the source committed them: the WAL order for Postgres, the change stream’s order for MongoDB. The latest change for a key is its live row. A delete is a change like any other: when it is the latest event for a key, the row is gone until a later insert re-creates it.

Ordered per key at the source. Each key’s changes flow through a single feed in commit order. The table relies on that order; there is no reordering buffer for late events.

Reads are always current and consistent. A SELECT returns the latest-per-key state as of its transaction snapshot, including changes not yet consolidated into base storage. Freshness equals the ingest worker’s append cadence, typically a few seconds behind the source commit. A query pins one snapshot across the table’s base and staging storage, so no reader, on any engine, ever observes a half-applied batch or a mid-consolidation state.

Exactly-once effect. The stream position advances only with the transaction that applied the batch, so a crash or restart neither loses a change nor applies one twice.

Merge modes

Keeping one live row per key means superseded row versions must be excluded somewhere. The merge_mode option chooses where: on the read side ('read', the default) or eagerly on the write path ('write'). The mode is set at creation and cannot be changed later.

merge_mode = ‘read’ (default)

Changes are appended to hidden staging storage, an O(batch) operation that never touches the base table. Superseded rows are excluded positionally: when a query plans against the table, the engine resolves which physical row positions are superseded, by merging the staged changes per key and probing an index on the merge key for each older version’s position. The result is a per-tablet deletion mask, the same Roaring-bitmap primitive that serves deletes on managed tables. The scan then reads base and staging as one plain table with masks applied.

• Read cost: mask resolution, then a plain scan. Before the scan, the engine resolves which row positions are superseded; the result depends only on the current set of tablets, so it is computed once, cached, and shared by concurrent queries. The scan then applies the masks exactly as row-level deletes are applied on any managed table, and is otherwise ordinary: the WHERE clause prunes tablets and granules through the same min/max metadata, and predicate pushdown and index-served reads keep their full effect. Masks never pile up, either: tablets where masked rows accumulate are vacuumed automatically in the background, so the overhead stays bounded no matter how long the table has been ingesting.
• Write cost: appends plus a background cascade. On a cadence (cascade_interval, default 60 seconds), the cascade folds the current masks into committed deletion masks and adopts the staging tablets into the base as a metadata-only re-parent; no row data is rewritten to move a change from staging to base. The vacuum of heavily masked tablets runs within a per-cascade budget so it never delays adoption, and the staging window is consolidated into well-sized tablets before adoption.

Merge-key columns must have an index-supported type: BOOLEAN, INTEGER, BIGINT, DATE, TIMESTAMP, TIMESTAMPTZ, NUMERIC, or TEXT.

merge_mode = ‘write’

Each batch of changes is merged directly into the table: matched keys updated or deleted, new keys inserted, guarded by the source sequence. The table is always fully consolidated, and it stays an ordinary table operationally: direct DML and schema changes remain available.

• Read cost: none. Queries scan a plain, fully consolidated table.
• Write cost: a per-batch merge that probes the table by key. On a large table with uniformly distributed keys, each batch touches most of the table, so merge cost is driven by table size rather than batch size. At sustained high change rates this becomes the bottleneck: at 10,000 random-key changes per second against a multi-billion-row table, ingestion settles into a steady-state lag of several minutes that more compute does not remove.

Choosing

Stay with the default read mode unless you have a reason not to: it sustains high change rates and gives queries a plain scan. Choose write when the change volume is modest and you want the table to remain open to direct DML and schema changes, or when the merge key’s type is not supported by read mode.

Lifecycle and operations

• ALTER CDC TABLE <t> RESUME starts ingestion and binds the worker to the Engine you run it on. Running it on another Engine moves ingestion there: the worker on the previous Engine stops after the statement it is running finishes (for an initial load, after the whole load) and after any cascade already in progress. The enabled intent is persisted, but worker registration is not: after an Engine restart, issue RESUME again. The worker continues from the committed stream position.
• ALTER CDC TABLE <t> SUSPEND stops the worker. The table stays queryable at its last state. For Postgres sources, the idle replication slot retains WAL on the source while suspended. On a table in error, SUSPEND does not interrupt the worker’s final drain of already staged changes.
• A failure identified as permanent, such as a source schema change, stops the current attachment in error, and so does a failure of the initial load, ingestion, or staging maintenance that persists for at least 10 attempts over at least 4 hours (the defaults of execution.cdc.retry_budget_min_failures and execution.cdc.retry_budget_min_window). The worker stops reading the source and then applies the changes it had already staged (the final drain), unless applying them was the failure. The final drain is best-effort: it gives up once its failures exhaust the same retry budget and does not resume after an Engine restart, so staged changes can stay unapplied until the binding is replaced. RESUME rejects that attachment and includes the recorded reason. To start a new binding, detach the stream and repair the cause. After a Postgres source schema change, that means a new stream and a new CDC table with the source’s current columns, because a CDC table cannot change its columns (see Postgres CDC).
• ALTER CDC TABLE <t> DETACH STREAM stops ingestion; the table keeps its rows. Attaching the same stream again continues from where it stopped. Attaching a stream that has not loaded a table before reseeds the table instead: the first load after RESUME replaces all of its rows with that stream’s snapshot.
• DROP TABLE <t> drops the table together with its hidden staging storage and stops the worker.
• Pacing. Each append reads for a window, execution.cdc.append_max_read_ms in the engine configuration (default two seconds), or until it holds execution.cdc.append_max_rows change events (default two million), then stops at the next source transaction boundary and writes what it has. A source that pauses inside the window does not end the append early, and a backlog does not stretch it: the window bounds how long one append reads, whatever the source’s rate. The next append starts as soon as the previous one commits, whether or not it wrote anything, so the window is also how often an idle source is polled. By default an append decodes the stream and writes staging tablets with as many threads as a query gets; set execution.cdc.append_max_threads to give appends their own thread count instead, lower to leave more cores to queries or higher to let ingestion use more than a query does.
• Workload isolation. Ingestion consumes compute only on the engine where it was resumed. Every other engine queries the same table from object storage at full freshness, so a dedicated ingest engine isolates CDC work from serving entirely.

Monitoring

information_schema.cdc_ingests has one row per CDC table with an attached stream. Query it on the Engine where ingestion runs for local worker details:

SELECT table_name, stream_name, state, phase, has_worker,
       apply_count, last_commit_time, live_lag_ms, last_error, error_count
FROM information_schema.cdc_ingests;

state reports persisted attachment status (enabled, paused, or error). phase reports the matching local worker’s backfilling or streaming phase, and has_worker makes worker absence explicit. apply_count counts progress ticks (the initial load is not counted), live_lag_ms measures time since the last applied batch (it also grows while the source is idle), and last_error carries the durable terminal reason or the latest outstanding local retry diagnostic.

Prometheus metrics

The engine running ingestion also exports per-table metrics on its Prometheus endpoint (/metrics; for a Helm deployment, see Monitoring). Every series carries the labels database, table (schema.table), table_id, and stream.

• Lifetime. A table’s series exist while its ingest worker runs on that engine. SUSPEND, DETACH, DROP TABLE, a permanent stop (state = 'error'), and an engine restart remove them, so counters start from 0 after RESUME; rate() and increase() handle the reset.
• Renames. Renaming the table moves its series to the new table label, and counters start from 0 there. Aggregate by table_id to follow a table across a rename.
• Committed work only. The worker records a statement after it commits. A statement that fails counts once, as failed, and contributes nothing else.
• Unknown values. A gauge without a value yet reads NaN. The tablet gauges are counted by each cascade, so they read NaN until the worker’s first cascade.

Metric	Type	Meaning
firebolt_cdc_initial_load_state{state}	gauge	1 for the initial load’s current state (running, completed, failed), 0 for the others. A worker that finds the load already complete reports completed.
firebolt_cdc_initial_load_started_timestamp_seconds	gauge	Unix time this worker’s latest load attempt started. While it runs, time() minus this is its elapsed time.
firebolt_cdc_initial_load_duration_seconds	gauge	Wall time of the load that completed on this worker.
firebolt_cdc_initial_load_rows, firebolt_cdc_initial_load_tablets	gauge	Rows and tablets that load wrote; 0 when the load completed on another worker.
firebolt_cdc_staging_appended_change_events_total{op}	counter	Source change events appended to staging, by insert, update, and delete. An update is one event.
firebolt_cdc_staging_append_operations_total{outcome}	counter	Appends by outcome: appended (the stream position advanced), empty, or failed.
firebolt_cdc_staging_append_operation_duration_seconds_total{outcome}	counter	Time spent in appends, by outcome.
firebolt_cdc_staging_as_of_timestamp_seconds	gauge	Source commit time of the newest change staged.
firebolt_cdc_staging_tablets	gauge	Tablets in staging.
firebolt_cdc_cascades_total{outcome}	counter	Cascades by outcome: drained, empty (nothing was staged), or failed.
firebolt_cdc_cascade_duration_seconds_total{outcome}	counter	Time spent in cascades, by outcome.
firebolt_cdc_cascade_tablets_total{fate}	counter	Staging tablets cascades moved out of staging: adopted into the base, or retired because every row in them was superseded.
firebolt_cdc_cascade_masked_staging_rows_total	counter	Staged rows cascades masked: deletes, and changes a later change to the same key superseded.
firebolt_cdc_cascade_masked_base_rows_total	counter	Base rows cascades masked because a staged change superseded or deleted them.
firebolt_cdc_base_as_of_timestamp_seconds	gauge	Source commit time the base is current through.
firebolt_cdc_base_tablets	gauge	Tablets in the base.

time() - firebolt_cdc_staging_as_of_timestamp_seconds is the staging lag, and time() - firebolt_cdc_base_as_of_timestamp_seconds is how far consolidation trails the source. Both are measured against source commit times, so, like live_lag_ms, they also grow while the source is idle. A MongoDB stream records no source commit time, so for a MongoDB source both read NaN. A load replaces the table, so after one both read NaN until the next append, and the base’s until the cascade after it. A steady rate of empty appends with no failed ones tells an idle source from a stalled worker:

time() - firebolt_cdc_staging_as_of_timestamp_seconds
sum by (table_id) (rate(firebolt_cdc_staging_append_operations_total{outcome="empty"}[5m]))
sum by (table_id) (rate(firebolt_cdc_staging_appended_change_events_total[5m]))

Restrictions

In read mode, the table’s state belongs to the ingest worker:

• No direct INSERT, UPDATE, DELETE, MERGE, or TRUNCATE.
• No column-level ALTER TABLE and no ALTER TABLE ... SET PRIMARY INDEX; renaming the table is allowed.
• No aggregating indexes.

write tables accept direct DML and schema changes like ordinary tables.

General limits:

• CDC tables require a single-node Engine. CREATE CDC TABLE and ALTER CDC TABLE ... RESUME return an error on an Engine with more than one node. SUSPEND, ATTACH STREAM, and DETACH STREAM still work, so a table on an Engine that was resized can be stopped and detached.
• ALTER CDC TABLE ... RESUME cannot run inside an explicit transaction. Run it as a single statement.
• merge_mode is fixed at creation.
• One stream feeds one CDC table. A partitioned Postgres table cannot be streamed; see partitioned source tables.
• The change feed must be in order per key, which the supported sources guarantee; there is no reordering buffer for late events.
• Column names beginning with __, plus last_seq, are reserved for CDC bookkeeping.