Change data capture

​

The three objects

1. A location stores the connection details and credentials for the source database. See CREATE LOCATION (Postgres) and CREATE LOCATION (MongoDB).
2. A stream binds to one source table or collection and tracks a consumed position in its change feed: a replication slot for Postgres, a change-stream position for MongoDB. You can read a stream directly with READ_STREAM to inspect raw change events. See CREATE STREAM.
3. A CDC table consumes a Postgres or MongoDB stream and maintains the current state of the source table: one live row per key, with inserts, updates, and deletes applied in source order. See CREATE CDC TABLE.

┌──────────────────────┐
│ SOURCE DATABASE      │
├──────────────────────┤
│ Postgres / MongoDB   │
│ (WAL / change stream)│
└──────────┬───────────┘
           ▼
┌──────────────────────┐      ┌────────────────────────┐
│ FIREBOLT ENGINE      │      │ FIREBOLT ENGINE        │
├──────────────────────┤      ├────────────────────────┤
│ runs the ingest      │      │ any other engine       │
│ worker: decodes and  │      │ SELECT ... FROM orders │
│ appends changes,     │      │                        │
│ commits data + stream│      │                        │
│ position atomically  │      │                        │
└──────────┬───────────┘      └───────────▲────────────┘
           ▼                              │
┌─────────────────────────────────────────┴────────────┐
│ OBJECT STORAGE                                       │
├──────────────────────────────────────────────────────┤
│ the CDC table's tablets                              │
└──────────────────────────────────────────────────────┘

CREATE LOCATION pg_prod WITH (
    SOURCE = 'POSTGRES',
    HOST = 'pg.example.internal',
    DATABASE = 'app',
    USER = 'firebolt_cdc',
    PASSWORD = '********',
    PUBLICATION = 'firebolt_cdc'
);

CREATE STREAM orders_changes (order_id BIGINT, customer_id BIGINT, amount NUMERIC(12, 2))
    TABLE = 'public.orders'
    LOCATION = 'pg_prod';

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;

SELECT table_name, state, phase, apply_count, live_lag_ms
FROM information_schema.cdc_ingests;

​

What you can rely on

• Exactly once. The stream’s position and the rows it produced commit in the same transaction, so no change is lost or applied twice. There is no deduplication step to build or monitor.
• Always consistent. A query sees each change fully applied or not at all, at its own snapshot, on every engine.
• Fresh within seconds. A change is visible as soon as its batch commits. Background consolidation never delays it.
• Fast queries at high change rates, on your terms. You choose, per table, where the merge work runs. The default merges on read: ingestion stays append-only, superseded rows are excluded through deletion masks, and queries keep running as a single sorted, indexed scan while changes pour in. merge_mode = 'write' merges on the write side instead: reads carry no merge work at all, which buys the last bit of analytical query performance, and the table stays open to direct DML. CDC tables covers the trade-off.
• Isolated ingestion. The ingest worker runs on the engine where you execute ALTER CDC TABLE ... RESUME. Every other engine reads the same continuously updated table from object storage without sharing compute with it.
• Scale-out for hot tables. A partitioned Postgres source is ingested with one replication slot per partition, in parallel. See Postgres CDC.

​

Guides

• Postgres CDC: prerequisites, setup, partitioned sources, TOAST handling, operations.
• MongoDB CDC: prerequisites, schema inference, querying undeclared fields, operations.
• CDC tables: semantics, merge modes and their performance characteristics, lifecycle, monitoring.