Skip to content
Sabbir HossainData Engineer
System Note 09 · Enterprise Systems

MongoDB → BigQuery Migration

Bell Canada

GCPBigQueryMongoDBAirflowTerraformCloud RunGitLab CI/CDPython
Private build
What it is

The pipelines and cloud setup that land MongoDB Atlas collections in BigQuery and check that the copy matches. Built as code: Terraform, Cloud Composer DAGs, and Cloud Run ingestion jobs deployed through GitLab CI/CD, with watermark-based change data capture and MERGE upserts.

5 collections · 42M+ documents backfilled · 6.09M rows reconciled · Terraform + Cloud Run + GitLab CI

Why I built it

The team had never moved a NoSQL source into the warehouse before, and the data was too big and too important to eyeball.

What went wrong
  1. 01
    The silent NULLs
    Broke
    Some columns loaded as all-NULL at multi-million-row scale, with no error to warn anyone.
    Why
    The loader figured out which source fields to read from the destination column names. Any naming mismatch just read nothing, and quietly wrote NULL instead of failing.
    Fix
    Made the projection explicit and made a mismatch an error, not a shrug. The landing schema is hybrid (partitioned, clustered key columns plus one JSON payload column) so partition pruning keeps queries cheap.
  2. 02
    The delta that wasn’t
    Broke
    The largest collections kept timing out on what was supposed to be a small incremental load.
    Why
    The “delta” job had an empty filter, so every run was secretly a full scan of the entire collection.
    Fix
    Fixed the filter so it only pulls changes. Then built a reconciliation toolset (value diff, ID-set diff, and checkpoint-resumable row hashing over the BigQuery Storage Read API) and compared 6.09M rows across 5 tables on QA-sourced development data: 99.98% identical.
  3. 03
    The half-load
    Broke
    A large backfill (42M+ documents from 5 production collections into a BigQuery dev dataset) silently half-loaded.
    Why
    The load reported success without checking itself against source counts. The same run also hit Airflow zombie tasks and out-of-memory batches.
    Fix
    Caught the gap by reconciling against source counts, and fixed the zombie tasks and out-of-memory batches. A follow-up hardening package for 5 DAGs (the watermark advances only after a successful MERGE) is in code review, with 16 behavioral tests and 42 static checks.
Outcome
42M+documents backfilled
99.98%rows identical
← Back to Build Notes