I'm building [mongopg-migrate], a tool that migrates MongoDB collections onto an existing Postgres schema you already designed. It's alpha pip install mongopg-migrate, currently v0.2.0. This is a bug from it, the worst one I've hit so far, because every safeguard the tool has fired correctly, and the migration still came out wrong. The setup The tool supports nested arrays (explode:): a Mongo array becomes a child table, and an array inside that array becomes a grandchild table. A field at any level can also be a lookup:, resolved against another entity's already-migrated rows and rewritten as a foreign key: explode: facilities: explode: categoryParts: fields: categoryId: lookup: zcategories # one level deeper than the top Two things need to know about that lookup: entity_dependencies() (so zcategories loads before whatever references it) and validate_structure() (so a typo'd entity name gets caught early). Both only ever walked the first level of explode. A lookup one level deeper was invisible to both: lookup at TOP explode level -> order ['zcategories', 'hospitals'] correct same lookup ONE LEVEL DEEPER -> order ['hospitals', 'zcategories'] backwards validate on typo'd nested lookup -> [] zero issues found On its own, that's a real bug. But it used to be a loud one, a lookup with an empty id_map raised an unconditional LoadError and crashed the run. The twist Separately, the tool had just grown an on_missing policy: what to do when a reference genuinely doesn't resolve (source doc deleted, a normal case). on_missing: null writes NULL instead of crashing the whole migration. Reasonable on its own. Combine both: Wrong order schedules the referencing entity before zcategories. Every lookup misses, because zcategories's id_map is empty, not because anything is actually dangling. on_missing: null, built for real dangling references, fires on every miss and writes NULL. Row counts are unaffected - NULLs don't change counts. The post-migration validator re-checks dangling references after the full run, by which point zcategories has loaded, so it finds nothing wrong and reports clean. End state: a whole foreign-key column silently NULL, and count diff, dangling-reference check, and structural validation all report success. Each check answered the exact question it was built to answer, correctly. The chain connecting them was wrong. the fix: two parts, not one A regression test for this exact repro would fix the one shape found and miss the actual assumption: a policy for "this one reference is dangling" isn't a safe answer to "the entity it points at has never loaded a single row." Those are different failure modes. Root cause recurse through every level of nested explode, not just the first: def _explode_lookup_targets(explode: dict[str, ExplodeSpec]) -> set[str]: targets: set[str] = set() for exp in explode.values(): for fspec in exp.fields.values(): if fspec.lookup: targets.add(fspec.lookup) targets |= _explode_lookup_targets(exp.explode) # recurse return targets Defense in depth even with correct ordering, refuse to apply on_missing blind. Check whether the referenced entity has loaded anything at all first: if on_missing != OnMissing.ERROR: if not idmap.has_any(lookup_conn, lookup_entity, schema=lookup_schema): raise LoadError( f"{lookup_entity!r} has NO id_map rows at all — this looks like a load-order " "bug or a forgotten prerequisite run, not a genuinely dangling reference. " f"Refusing to apply on_missing={on_missing.value!r} here." ) That second check matters independently; it also catches referencing an entity from a separate migration run that a human simply never ran. No amount of correct ordering fixes that; checking "has this entity loaded anything, ever" does. It's cached per entity, so it's one extra indexed query the first time a lookup to that entity misses, not one per row. the same shape, twice more I first wrote this up as a one-off. Two bugs since have changed my mind. Two checks aimed at one mistake are one check. --pg-schema exists so you can migrate into a schema other than public. It reached introspect_postgres() and nothing else. Every write named its table with no schema qualifier, so rows landed wherever the connecting role's search_path pointed, normally public. Nothing errored; the load was perfectly valid against the tables it found. Then validate counted those same wrong tables and printed a clean pass: [OK] hospitals (hospitals): mongo=4002 postgres=4002 The migration reported success. The validation agreed. Both were pointed at the wrong tables. It had been there since the flag was added, and it could only ever have hurt someone whose tables don't live in public, which is to say, someone who wasn't me. All three commands now set search_path explicitly. A precondition that only held at fixture scale. To make lookup: fast over a slow link, the tool prefetches a referenced entity's whole id_map into memory before the first document. That was unbounded, at ~200 bytes a row, ~1.2 GB at 5M rows and ~12 GB at 50M, allocated up front. It never came close in development, because the largest entity there was ~65k rows. There's now a ceiling (--idmap-prefetch-max, default 2,000,000); above it, lookups go per row behind a bounded cache, slower, but it can't exhaust memory, and the run says which mode it picked. Same pattern all three times: logic that's correct given an assumption nobody wrote down, composed with other correct-in-isolation logic, producing something wrong that looks clean. The lesson "Every check passed" is a claim about which checks exist, not a claim about correctness. And two checks that share an assumption are, for catching a violation of that assumption, one check. Worth asking of any null/skip/retry fallback in a system: what does this do if the precondition I'm assuming turns out to be silently false? Repo: https://github.com/aggtushar123/mongopg-migrate - alpha, building in the open. pip install mongopg-migrate.