Skip to main content
Private Preview FeatureThis feature is currently in private preview. Contact support@firebolt.io 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

-- Explicit columns and merge key
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> [, ...]]
FROM STREAM <stream_name>
[WITH (<option> = <value> [, ...])];

-- Columns and merge key inferred from the stream
CREATE CDC TABLE [IF NOT EXISTS] <table_name>
FROM STREAM <stream_name>
[WITH (<option> = <value> [, ...])];

​
Parameters

ParameterDescription
<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. Omit the whole list to infer columns and merge key from the stream; the inferred form also adds a JSON overflow column for undeclared source fields.
PRIMARY KEY ... NOT ENFORCEDThe merge key: the column set identifying a row across its lifetime, normally the source primary key. Composite keys are supported. Required in the explicit form; the NOT ENFORCED keyword is mandatory. The key is trusted, not validated.
PRIMARY INDEXOptional sort order for the table, independent of the merge key. Defaults to the merge key columns.
FROM STREAM <stream_name>The stream that feeds the table. One stream feeds one CDC table.

​
WITH options

OptionValuesDefaultDescription
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_intervalINTERVAL '<n>' SECOND|MINUTE|HOUR, 1 second to 24 hours60 secondsread 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; ingestion starts with ALTER CDC TABLE ... RESUME.
  • 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.
  • For a stream over a partitioned Postgres source, every source partition-key column must be part of the merge key.
  • 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
) FROM STREAM orders_changes;

ALTER CDC TABLE orders RESUME;

​
Mirror a MongoDB collection with an inferred schema

CREATE CDC TABLE orders FROM 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
FROM STREAM events_changes;

​
ALTER CDC TABLE

Controls the ingest worker of a CDC table: 
ALTER CDC TABLE <table_name> RESUME;    -- start or restart ingestion
ALTER CDC TABLE <table_name> SUSPEND;   -- stop ingestion; the table stays queryable
ALTER CDC TABLE <table_name> CASCADE;   -- consolidate staged changes into the base now
  • RESUME binds the ingest worker to the engine that runs the statement and persists the enabled state, so ingestion resumes automatically after an engine restart. The first RESUME backfills the table from a source snapshot before streaming.
  • SUSPEND stops the worker without a final consolidation. For Postgres sources, the idle replication slot retains WAL on the source while suspended.
  • CASCADE applies to read mode and triggers the consolidation that otherwise runs every cascade_interval.

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