MongoDB CDC

​

Prepare the source deployment

1. A replica set or sharded cluster. Change streams read the oplog; a standalone mongod has none. A sharded collection must be sharded on _id, the only key MongoDB keeps unique across shards.
2. MongoDB 6.0 or later, with document pre- and post-images enabled on the collection. Firebolt reads exact point-in-time images rather than re-fetching documents, so updates are never observed out of order or half-applied:
   db.runCommand({
     collMod: "orders",
     changeStreamPreAndPostImages: { enabled: true }
   })
   
3. A role with find and changeStream privileges on the collection, authenticated with SCRAM.
4. Oplog and pre-image retention sized so the feed’s history outlives any planned ingestion pause. If the stored position ages out of the oplog, ingestion cannot resume from it and the table must be re-bootstrapped.
5. Network reachability from the Firebolt engine to the cluster (VPC peering, PrivateLink, or an Atlas access list).

​

Create the location

CREATE LOCATION mongo_prod WITH (
    SOURCE   = 'MONGODB',
    URL      = 'mongodb+srv://cluster0.example.mongodb.net/',
    USER     = 'firebolt_cdc',
    PASSWORD = '********'
);

​

Create the stream

CREATE STREAM orders_changes
LOCATION = 'mongo_prod'
COLLECTION = 'app.orders';

​

Read raw change events

SELECT "$op", "$wall_time", _id, doc
FROM READ_STREAM(STREAM orders_changes, consume => false);

INSERT INTO orders_load SELECT * FROM READ_SNAPSHOT(STREAM orders_changes);

​

Create the CDC table and start ingestion

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

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

​

Query document fields

SELECT _id, doc.status, doc.customer.city
FROM orders
WHERE JSON_VALUE(doc.amount)::DOUBLE PRECISION > 100;

​

Operations

• Delivery and recovery. The resume token commits atomically with the data it produced. After a crash or Engine restart, run RESUME again; the new worker continues from the last committed token.
• Feed expiry. If ingestion is paused long enough for the stored token to age out of the oplog (or the needed post-images to expire), the worker’s reads fail with a history-lost error, shown in last_error of information_schema.cdc_ingests, until the retry budget runs out and the attachment stops in state = 'error'. Nothing on the source is harmed; recreate the stream and the CDC table, which loads the collection again. A stream no CDC table is attached to can instead be reloaded with READ_SNAPSHOT, which moves its position to just before the scan.
• Collection lifecycle events. A collection drop, rename, or stream invalidation ends the feed. Like any persistent failure, the attachment stops in state = 'error' once the retry budget runs out. Recreate the stream and table against the new collection.
• DROP STREAM removes only Firebolt metadata. MongoDB change streams hold no server-side resource comparable to a replication slot, so there is nothing to clean up on the source. An Engine still reading the dropped stream ahead closes its change streams when the kept reader expires, two to two and a half minutes after the last read.

​

Limitations

• One collection per stream.
• A stream’s columns are fixed: _id and doc.
• A sharded collection is supported only when it is sharded on _id. CREATE STREAM checks this; resharding the collection later is not detected.
• A document the engine cannot store stops the table’s ingestion at that change: information_schema.cdc_ingests reports it in last_error; once the retry budget runs out, the attachment stops in state = 'error'. To recover, detach the stream and attach a new one; the next RESUME reloads the collection.
• ALTER STREAM is not supported; schema changes are drop-and-recreate.
• The table’s columns are the stream’s: _id and doc. Fields are read through doc.