> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

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

# CDC tables

<Warning>
  **Private Preview Feature**

  This feature is currently in private preview. Contact [support@firebolt.io](mailto:support@firebolt.io) to request early access.
</Warning>

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](/guides/change-data-capture/postgres) and [MongoDB CDC](/guides/change-data-capture/mongodb); for exact DDL syntax, see [CREATE CDC TABLE](/reference-sql/commands/data-definition/create-cdc-table).

## Creating a CDC table

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
CREATE CDC TABLE orders (
    order_id    BIGINT,
    customer_id BIGINT,
    amount      NUMERIC(12, 2),
    PRIMARY KEY (order_id) NOT ENFORCED
) FROM 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.

You can also omit the column list, and the columns and key are inferred from the stream: `CREATE CDC TABLE orders FROM STREAM orders_changes`. Tables over MongoDB streams are usually created this way.

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. 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`](/reference-sql/information-schema/cdc-ingests).

## Semantics

**The latest change wins, per key.** Every change carries a source sequence: the WAL position for Postgres, the cluster timestamp for MongoDB. The row with the highest sequence is the live row for its key, and that is decided by the sequence, not by arrival order: a redelivered or late event with an older sequence never overwrites newer state. 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 (for partitioned Postgres sources, the partition-key-in-merge-key rule guarantees this). Sequence-based resolution already makes inserts and updates safe against redelivery; deletes are what rely on this ordering, because a deleted row leaves nothing behind for an even older event to compare against.

**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, and every apply is guarded by the source sequence. Crashes and restarts can only redeliver changes that then apply as no-ops.

## 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](/performance-and-observability/storage-and-indexing#writes%2C-updates%2C-and-deletes). 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. For a partitioned Postgres source, each source partition consolidates independently and in parallel.

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. The enabled state is persisted: after an engine restart, ingestion resumes automatically from the committed stream position, including a restart mid-backfill.
* `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.
* `ALTER CDC TABLE <t> CASCADE` runs a consolidation immediately instead of waiting for the next interval.
* `DROP TABLE <t>` drops the table together with its hidden staging storage and stops the worker.
* **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`](/reference-sql/information-schema/cdc-ingests) has one row per CDC table with an active ingest worker; query it on the engine where ingestion runs:

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
SELECT table_name, stream_name, state, phase,
       apply_count, last_commit_time, live_lag_ms, last_error, error_count
FROM information_schema.cdc_ingests;
```

`state` and `phase` distinguish the initial backfill from steady-state streaming, `apply_count` counts applied batches, `live_lag_ms` measures time since the worker last advanced (a growing value while `streaming` signals a stalled feed), and `last_error` carries the most recent failure.

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

* `merge_mode` is fixed at creation.
* One stream feeds one CDC table. This is not a scaling limit for partitioned Postgres sources: the stream detects the partitioning at creation and ingests the leaf partitions in parallel, still as one stream into one table. See [partitioned source tables](/guides/change-data-capture/postgres#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.