SyncSpur | Syncing MongoDB to Snowflake with Fivetran: Change Streams, Packed Mode and Nested JSON

Every Fivetran rollout that touches a document database hits the same wall. The connector authenticates, the first sync finishes, and an analyst opens the destination expecting orders and instead finds a table where half the interesting data is sitting inside a single column called _fivetran_values — or a wide table of columns that appeared out of nowhere and will change again next week. Nothing is broken. MongoDB simply does not have a schema, and the warehouse insists on one.

This tutorial covers what actually happens when you sync MongoDB to Snowflake (or BigQuery, Databricks, Redshift — the destination matters less than you think) with Fivetran: choosing between the standard and change-stream-based connector modes, how schema inference and the packed-mode flattening decisions play out, what nested arrays cost you in MAR, and the dbt pattern that turns the landed documents into something an analyst can join. The same reasoning applies to DynamoDB, Cosmos DB and Firestore, which land semi-structured data in the same shapes.

Before you configure anything: three facts about your data

Answer these first, because they determine the connector mode and the modelling work downstream. A mongosh session against a replica is enough.

// 1. How stable is the document shape?
db.orders.aggregate([
  { $sample: { size: 5000 } },
  { $project: { fields: { $objectToArray: "$$ROOT" } } },
  { $unwind: "$fields" },
  { $group: { _id: "$fields.k", n: { $sum: 1 } } },
  { $sort: { n: -1 } }
])

// 2. How deep and how large are the documents?
db.orders.aggregate([
  { $sample: { size: 1000 } },
  { $group: { _id: null, avg: { $avg: { $bsonSize: "$$ROOT" } },
                          max: { $max: { $bsonSize: "$$ROOT" } } } }
])

// 3. Are there unbounded arrays?
db.orders.aggregate([
  { $sample: { size: 1000 } },
  { $project: { n: { $size: { $ifNull: ["$line_items", []] } } } },
  { $group: { _id: null, max: { $max: "$n" } } }
])

A field present in 4,998 of 5,000 sampled documents is effectively a column. A field present in 40 of them is not a column, it is a tenant-specific extension, and treating it as a column is how you end up with a 900-column table. An embedded array with a p99 length of 200 is the single biggest cost driver in the whole pipeline, for reasons covered below.

Standard connector or change streams?

Fivetran offers more than one way to read MongoDB, and the choice is not cosmetic.

	Collection scan / polling	Change streams (oplog-based)
How it reads	Queries the collection, tracks a cursor	Tails the oplog via `$changeStream`
Deletes captured	Not reliably	Yes, as soft-delete markers
Updates	Re-reads the document	Delivered as change events
Source load	Heavy on large collections	Light and steady
Requirements	Read access	Replica set, adequate oplog window, `changeStream` privileges
Right for	Small, append-mostly collections	Anything production-sized

Pick change streams unless something prevents it. Then go and check the one thing that will break it: the oplog window.

db.getSiblingDB("local").oplog.rs.stats().maxSize   // bytes
rs.printReplicationInfo()                           // "oplog first event" -> window

If your oplog holds four hours of writes and your connector is paused for a weekend deploy, the resume token expires, and Fivetran has no choice but to re-scan the collection from scratch. That re-scan is a full re-read of every document — which is a MAR event for every row and a very memorable invoice. Size the oplog for your worst realistic outage, not your average one: 24 hours minimum, 72 if you have change windows. This is the same class of failure as an expired Postgres replication slot, and it is worth reading alongside the schema drift and re-sync playbook.

Two more source-side prerequisites that quietly cause failed setups:

Read from a secondary, but a healthy one. Point the connector at a dedicated analytics secondary with readPreference=secondary. If that member lags, your data lags, and the freshness alert will blame Fivetran.
A least-privilege user is enough. read on the target databases plus clusterMonitor, and changeStream privileges on the collections you sync. It does not need dbAdmin, whatever the first draft of the ticket says.

Packed mode vs unpacked: the decision you cannot un-make cheaply

This is the core of a MongoDB sync. Fivetran has to turn a document into rows, and it offers you two philosophies.

Unpacked (schema inference). Fivetran samples documents, infers a schema, and writes top-level fields — and, depending on configuration, nested paths — as real typed columns. Analysts can query it immediately with no modelling work.

Packed. Each document lands as one row with _id, Fivetran metadata, and the document body in a single semi-structured column (_fivetran_values, typed VARIANT in Snowflake, JSON/RECORD in BigQuery). The warehouse schema never changes, no matter what the application team ships.

How to choose, in practice:

Highly variable or multi-tenant documents, or fields added by product teams weekly → packed. You absorb drift in dbt instead of in DDL. A new field is already in the VARIANT the day it appears; you surface it when someone asks.
Stable, well-governed collections consumed directly by BI → unpacked. Real columns, real types, no :: casts in every query.
Mixed estate → packed for the messy collections, unpacked for the tidy ones. This is per-table configuration, not per-connector, and the hybrid is usually the correct answer.

Two traps worth naming. First, inferred types are inferred from a sample: a field that has been an integer for a year and receives its first string value can produce a type conflict or a coerced column, and the fix arrives after the bad data does. Second, switching a table between modes later means re-landing it — a full re-sync, full MAR. Decide deliberately at setup; do not plan to "clean it up in Q3".

If your organisation is nervous about types at all, packed mode plus explicit casts in dbt is the defensible position, because the cast lives in version-controlled SQL where a reviewer can see it.

Nested arrays and the MAR bill

Here is the mechanic that surprises people. An embedded array that Fivetran unpacks does not stay one row. A single order document with 120 line_items becomes a parent row plus 120 child rows in an orders_line_items table, each with its own primary key. Those child rows are active monthly rows.

Worse, a common MongoDB update pattern rewrites the whole array — $set: { line_items: [...] } — so touching one line item re-emits all children as changed. A modest collection can generate an order of magnitude more MAR than its document count suggests, and this is the number one cause of "our MongoDB connector costs more than Salesforce".

What to do about it:

Exclude arrays you do not use. Column-level selection is the cheapest optimisation available and nobody ever regrets switching off an audit-trail array.
Keep genuinely unbounded arrays packed even in an otherwise unpacked table, and flatten them in the warehouse where compute is cheap and deterministic.
Sync high-churn collections less often. A collection whose documents are rewritten continuously does not benefit from 15-minute syncs; hourly may deliver the same analytics with a fraction of the MAR.
Watch the child tables specifically in the Platform Connector's MAR-by-table view rather than the account total. See pipeline observability for the queries, and the connection-level MAR pricing guide for how these rows are counted.

Modelling packed documents in dbt

Packed mode moves the work downstream, so here is the pattern. In Snowflake, a staging model that pins down every field you actually depend on:

-- models/staging/stg_mongo__orders.sql
with src as (
    select
        _id,
        _fivetran_values as doc,
        _fivetran_deleted,
        _fivetran_synced
    from {{ source('mongo', 'orders') }}
)

select
    _id                                                as order_id,
    doc:customer_id::varchar                           as customer_id,
    doc:status::varchar                                as order_status,
    try_to_decimal(doc:total_amount::varchar, 18, 2)    as total_amount,
    try_to_timestamp_ntz(doc:created_at::varchar)       as created_at_utc,
    coalesce(doc:currency::varchar, 'USD')             as currency,
    doc:line_items                                     as line_items_raw,
    _fivetran_deleted                                  as is_deleted,
    _fivetran_synced                                   as synced_at
from src
where not coalesce(_fivetran_deleted, false)

Three deliberate choices in there. try_to_* rather than a hard cast, because one malformed document should not fail the whole model at 06:00. coalesce for fields the application added later, so historical documents without them do not return nulls that look like data loss. And the _fivetran_deleted filter, because change streams deliver deletes as markers rather than removing rows — the same semantics described in handling deletes in Fivetran.

Then flatten arrays once, in the warehouse:

-- models/staging/stg_mongo__order_line_items.sql
select
    o.order_id,
    li.index                                        as line_number,
    li.value:sku::varchar                           as sku,
    li.value:quantity::number                       as quantity,
    try_to_decimal(li.value:unit_price::varchar, 18, 2) as unit_price
from {{ ref('stg_mongo__orders') }} o,
     lateral flatten(input => o.line_items_raw) li

BigQuery is the same idea with JSON_VALUE and UNNEST(JSON_QUERY_ARRAY(...)); Databricks uses from_json with an explicit struct plus explode. If you would rather not own the dbt project, Quickstart data models do not cover custom MongoDB shapes — this modelling layer is yours either way, which is exactly the trade-off discussed in that post.

Add two dbt tests you will be glad of: not_null on order_id, and a singular test asserting that fewer than N rows per day fail the timestamp cast. Silent cast failures are how a packed pipeline rots.

The `_id` problem

MongoDB's _id is an ObjectId, or a UUID, or a string, or occasionally a nested document, depending on which team wrote the collection. Fivetran lands it as a string in the destination. Two consequences:

Joins across collections need matching representations. An _id stored as an ObjectId in one collection and its string form in a referencing field elsewhere will not join. Normalise in staging, once.
ObjectIds encode a creation timestamp in their first four bytes. Useful as a fallback when created_at is missing from older documents, but do not treat it as authoritative — application code can supply its own _id.

A sane rollout order

Profile the collections (the three queries at the top). Decide packed vs unpacked per collection, in writing.
Fix the oplog window and stand up a read-only analytics secondary before the first sync.
Sync one medium collection with change streams. Watch a full day of MAR before adding more.
Select columns aggressively. Excluded arrays and blobs cost nothing and are trivial to add later.
Build the staging layer for that one collection, with tests, and get an analyst to use it.
Only then add the remaining collections, in cost order — largest MAR last, when you already understand the shape of the bill.

Teams that skip steps 3 and 4 tend to discover their real MongoDB cost on a monthly invoice rather than in a sandbox. Teams that skip step 5 ship a VARIANT column to analysts and call the migration finished, which is how a technically successful pipeline becomes a shelf-ware warehouse.

Where SyncSpur fits

Document-store pipelines are where Fivetran's automation is most valuable and most easily misconfigured: the connector is genuinely one afternoon of work, and the schema strategy behind it decides your cost and usability for years. We profile the collections, set the packed/unpacked boundary, size the oplog with your platform team, and hand over the dbt staging layer with tests so analysts get columns rather than JSON.

If you are staring at a _fivetran_values column and wondering what to do next, get in touch. You can also read how we approach data pipeline development, Fivetran connectors configuration and management and Fivetran cost optimization and MAR management.

Syncing MongoDB to Snowflake with Fivetran: Change Streams, Packed Mode and Nested JSON