CREATE CDC TABLE

Reference and syntax for the CREATE CDC TABLE and ALTER CDC TABLE commands.

Nightly Feature

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

Creates a managed table that Firebolt maintains continuously from a change stream: one live row per merge key, with source inserts, updates, and deletes applied in order. See CDC tables for semantics, merge modes, and performance characteristics.

Syntax

CREATE CDC TABLE [IF NOT EXISTS] <table_name> (
    <column_name> <data_type>
    [, <column_name> <data_type> ...],
    PRIMARY KEY (<column_name> [, ...]) NOT ENFORCED
)
[PRIMARY INDEX <column_name> [, ...]]
[WITH (<option> = <value> [, ...])];

ALTER CDC TABLE <table_name> ATTACH STREAM <stream_name>;

Parameters

Parameter	Description
<table_name>	An identifier for the table. Must be unique within the schema.
<column_name> <data_type>	Columns to maintain, matching the stream’s columns.
PRIMARY KEY ... NOT ENFORCED	The merge key: the column set identifying a row across its lifetime, normally the source primary key. Composite keys are supported. Required; the NOT ENFORCED keyword is mandatory. The key is trusted, not validated.
PRIMARY INDEX	Optional sort order for the table, independent of the merge key. Defaults to the merge key columns.

WITH options

Option	Values	Default	Description
merge_mode	'read', 'write'	'read'	Where superseded row versions are excluded: on the read side through deletion masks, or eagerly on the write path. See merge modes.
cascade_interval	INTERVAL '<n>' SECOND|MINUTE|HOUR, 1 second to 24 hours	60 seconds	read mode: how often the background cascade consolidates staged changes into the base table. Does not affect result freshness; reads are always current.

Notes

• CREATE CDC TABLE records metadata only. ALTER CDC TABLE ... ATTACH STREAM names the stream that feeds the table, and ingestion starts with ALTER CDC TABLE ... RESUME. One stream feeds one CDC table.
• CDC tables are supported on single-node Engines only. 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 are still accepted, so a CDC table on an Engine that was resized can be stopped and detached.
• Merge-key columns are implicitly NOT NULL.
• In read mode, merge-key columns must be BOOLEAN, INTEGER, BIGINT, DATE, TIMESTAMP, TIMESTAMPTZ, NUMERIC, or TEXT (NUMERIC only as a single-column key). Use merge_mode = 'write' for other key types.
• Column names beginning with __, and last_seq, are reserved for CDC bookkeeping.
• merge_mode cannot be changed after creation.
• In read mode, direct DML, TRUNCATE, column-level ALTER TABLE, SET PRIMARY INDEX, and aggregating indexes are rejected; renaming the table is allowed. write tables behave like ordinary tables.

Examples

Mirror a Postgres 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;

Mirror a MongoDB collection

A MongoDB stream’s columns are always _id and doc, so the table’s are too:

CREATE CDC TABLE orders (
    _id TEXT,
    doc JSON,
    PRIMARY KEY (_id) NOT ENFORCED
);

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

Sort the table for analytics instead of by key

CREATE CDC TABLE events (
    event_id   BIGINT,
    event_time TIMESTAMPTZ,
    payload    TEXT,
    PRIMARY KEY (event_id) NOT ENFORCED
)
PRIMARY INDEX event_time;

ALTER CDC TABLE events ATTACH STREAM events_changes;

ALTER CDC TABLE

Attaches a stream to a CDC table and controls its ingest worker:

ALTER CDC TABLE <table_name> ATTACH STREAM <stream_name>;  -- name the stream that feeds the table
ALTER CDC TABLE <table_name> DETACH STREAM;                -- stop ingestion; the table keeps its rows
ALTER CDC TABLE <table_name> RESUME;                       -- start or restart ingestion
ALTER CDC TABLE <table_name> SUSPEND;                      -- stop ingestion; the table stays queryable

• ATTACH STREAM takes a stream whose columns match the table’s. Attaching the stream the table was detached from continues where it stopped; attaching a stream that has not loaded a table before reloads the table from that stream’s snapshot on the next RESUME. A stream that has already loaded a table does not load another one, so mirroring into a new table needs a new stream.

• DETACH STREAM stops the worker and unlinks the stream. The table stays queryable at its last state.

• RESUME binds the ingest worker to the Engine that runs the statement and persists the enabled state. A worker that an earlier RESUME started on another Engine stops after the statement it is running finishes (for an initial load, after the whole load) and after any cascade already in progress. After an Engine restart, run RESUME again on the Engine where ingestion should continue. The first RESUME backfills the table from a source snapshot before streaming. RESUME is rejected while the attachment is in error (see CDC tables); the error includes the recorded reason.

• RESUME requires a single-node Engine. On an Engine with more than one node it returns an error and starts nothing.

• SUSPEND stops the worker without a final consolidation. For Postgres sources, the idle replication slot retains WAL on the source while suspended. On an attachment in error, SUSPEND has no effect; a worker still applying what it had staged continues until it is done.

DROP TABLE

DROP TABLE <table_name> drops a CDC table together with its hidden staging storage and stops its ingest worker. Dropping the table does not drop the stream; see DROP STREAM for source-side cleanup.

Related

• CDC tables: semantics, merge modes, monitoring.
• CREATE STREAM: define the change feed.
• information_schema.cdc_ingests: ingest state, progress, and errors.