$merge and $out for Materialized Views, ETL, Rebuilds, and Operational Safety

Learning objectives

1. Write-back changes the failure model

Previous aggregation lessons were read-only: a bad pipeline wasted resources or returned the wrong answer, but did not rewrite collections. $merge and $out are write-back stages and must be last in their pipeline. $merge matches output documents to target documents and can insert, merge, replace, keep, fail, or run a restricted update pipeline depending on its options. $out builds a new result collection and, if the target already exists, atomically replaces that target collection upon successful completion.

2. Seed authoritative orders and a derived target

docker rm -f atlasmart-mongo-ch09-l5 2>/dev/null || truedocker volume rm atlasmart-mongo-ch09-l5-data 2>/dev/null || truedocker run -d --name atlasmart-mongo-ch09-l5 \  -p 127.0.0.1:27061:27017 \  -v atlasmart-mongo-ch09-l5-data:/data/db \  mongodb/mongodb-community-server:8.3.8-ubuntu2204-slimmongosh "mongodb://127.0.0.1:27061/atlasmart?directConnection=true" --quiet --eval \'printjson({server:db.version(),hello:db.hello().isWritablePrimary}); printjson(db.getSiblingDB("admin").runCommand({getParameter:1,featureCompatibilityVersion:1,allowDiskUseByDefault:1}))' 

const src=db.orders_ch09_l5;const mv=db.daily_sales_mv_ch09_l5;src.drop(); mv.drop(); db.orders_ch09_l5_victim.drop();src.insertMany([ {_id:"o1",tenantId:"tenant-a",status:"paid",createdAt:ISODate("2026-08-01T10:00:00Z"),totalCents:1000}, {_id:"o2",tenantId:"tenant-a",status:"paid",createdAt:ISODate("2026-08-01T12:00:00Z"),totalCents:1500}, {_id:"o3",tenantId:"tenant-a",status:"paid",createdAt:ISODate("2026-08-02T10:00:00Z"),totalCents:2000}, {_id:"o4",tenantId:"tenant-a",status:"cancelled",createdAt:ISODate("2026-08-02T11:00:00Z"),totalCents:9000}, {_id:"o5",tenantId:"tenant-b",status:"paid",createdAt:ISODate("2026-08-01T09:00:00Z"),totalCents:700}, {_id:"o6",tenantId:"tenant-b",status:"paid",createdAt:ISODate("2026-08-03T09:00:00Z"),totalCents:800}]);mv.createIndex({tenantId:1,day:1},{unique:true});printjson({source:src.countDocuments({}),targetIndexes:mv.getIndexes()});

3. $merge: deterministic upsert by tenant and day

The pipeline groups paid orders and uses [tenantId, day] as the materialized-view identity. The target has a unique index on those exact fields, which is required for this non-_id on key. whenMatched:"replace" makes the emitted aggregate document authoritative for that key; whenNotMatched:"insert" creates new days.

function refreshWithMerge(){ return src.aggregate([  {$match:{status:"paid"}},  {$group:{_id:{tenantId:"$tenantId",day:{$dateToString:{format:"%Y-%m-%d",date:"$createdAt",timezone:"UTC"}}},orderCount:{$sum:1},revenueCents:{$sum:"$totalCents"}}},  {$project:{_id:0,tenantId:"$_id.tenantId",day:"$_id.day",orderCount:1,revenueCents:1}},  {$merge:{into:"daily_sales_mv_ch09_l5",on:["tenantId","day"],whenMatched:"replace",whenNotMatched:"insert"}} ]).toArray();}refreshWithMerge();printjson(mv.find({}).sort({tenantId:1,day:1}).toArray());refreshWithMerge();print("after identical rerun",mv.countDocuments({}));

tenant-a 2026-08-01 -> orderCount=2 revenueCents=2500tenant-a 2026-08-02 -> orderCount=1 revenueCents=2000tenant-b 2026-08-01 -> orderCount=1 revenueCents=700tenant-b 2026-08-03 -> orderCount=1 revenueCents=800A second identical refresh keeps the same four logical keys.

4. Incremental recomputation updates emitted keys

src.insertOne({_id:"o7",tenantId:"tenant-a",status:"paid",createdAt:ISODate("2026-08-02T15:00:00Z"),totalCents:500});refreshWithMerge();printjson(mv.findOne({tenantId:"tenant-a",day:"2026-08-02"}));

tenant-a / 2026-08-02 -> orderCount=2, revenueCents=2500

This is restartable because the output for a given key is deterministic and replace overwrites that key with the current aggregate. It is not “exactly once” magic; the target’s unique key and deterministic recomputation make retries converge for emitted keys.

5. Edge case: $merge does not delete keys that disappear

Delete the only source order for tenant-b on August 3, then rerun the same merge. The aggregation emits no tenant-b/Aug03 document, so $merge has nothing to match and the old materialized row remains stale.

src.deleteMany({tenantId:"tenant-b",createdAt:{$gte:ISODate("2026-08-03T00:00:00Z"),$lt:ISODate("2026-08-04T00:00:00Z")}});refreshWithMerge();print("source tenant-b Aug03",src.countDocuments({tenantId:"tenant-b",createdAt:{$gte:ISODate("2026-08-03T00:00:00Z"),$lt:ISODate("2026-08-04T00:00:00Z")}}));print("materialized row still exists?",mv.countDocuments({tenantId:"tenant-b",day:"2026-08-03"}));

6. $out full rebuild removes stale keys

Because the target is disposable derived state, a full rebuild can replace it from the complete source query. When an existing target is replaced, MongoDB builds a temporary collection, copies the existing indexes, inserts the new results, and then renames the temporary collection over the target. If the build fails, the pre-existing target remains unchanged. $out cannot target a sharded output collection; use $merge for sharded targets.

src.aggregate([ {$match:{status:"paid"}}, {$group:{_id:{tenantId:"$tenantId",day:{$dateToString:{format:"%Y-%m-%d",date:"$createdAt",timezone:"UTC"}}},orderCount:{$sum:1},revenueCents:{$sum:"$totalCents"}}}, {$project:{_id:0,tenantId:"$_id.tenantId",day:"$_id.day",orderCount:1,revenueCents:1}}, {$out:"daily_sales_mv_ch09_l5"}]);printjson(mv.find({}).sort({tenantId:1,day:1}).toArray());print("stale tenant-b Aug03 rows",mv.countDocuments({tenantId:"tenant-b",day:"2026-08-03"}));

tenant-b / 2026-08-03 no longer exists in the materialized view.The unique {tenantId,day} index remains on the replaced target.

7. Controlled destructive demonstration on a disposable victim

Never demonstrate source destruction on the real source. Instead, make a disposable victim copy and show what collection replacement means: the second $out replaces the victim with only paid rows. This is exactly why a mistaken target name is dangerous.

src.aggregate([{$out:"orders_ch09_l5_victim"}]);const victim=db.orders_ch09_l5_victim;print("victim before",victim.countDocuments({}));src.aggregate([{$match:{status:"paid"}},{$out:"orders_ch09_l5_victim"}]);print("victim after replacement with only paid rows",victim.countDocuments({}));print("source unchanged",src.countDocuments({}));

8. $merge failure and partial-success semantics

$merge is not one giant transaction over every output document. Current documentation warns that if an error occurs after some writes have completed, those writes are not automatically rolled back. Unique-index or validation failures therefore demand restartable/idempotent design and target-state verification. A safer rebuild strategy often writes to a staging target, validates counts/checksums/invariants, then performs a controlled promotion rather than incrementally mutating the only serving copy.

9. Operational checklist for materialized views

10. Verification, cleanup, and production judgment

Verification checklist

• The initial $merge creates four expected tenant/day keys.
• An identical rerun does not create duplicate logical keys.
• Adding one order updates the existing tenant-a/Aug02 aggregate.
• Deleting the only tenant-b/Aug03 source row exposes a stale-row limitation of merge-only refresh.
• A full $out rebuild removes the stale row while preserving target indexes.
• The destructive replacement demonstration targets only a disposable victim collection; the authoritative source count remains unchanged.

Production judgment. $merge is appropriate for incremental or keyed materialized-view updates when the identity is unique, recomputation is deterministic, stale-key deletion is handled, and retries are safe. $out is appropriate for full replacement of a disposable derived collection when whole-target rebuild cost is acceptable and the output is not sharded. Neither provides business freshness automatically. Measure rebuild duration, source lag, partial-write errors, validation failures, target cardinality/checksums, and serving-query correctness. Separate source and target privileges, test restore/promotion, and price temporary I/O plus duplicate storage during rebuilds.

Chapter 10 moves to index fundamentals. The advanced pipelines in this chapter make the motivation concrete: join, sort, and selective-prefix performance depend on indexes whose field order, uniqueness, and multikey behavior must be designed deliberately.

docker rm -f atlasmart-mongo-ch09-l5docker volume rm atlasmart-mongo-ch09-l5-data

Authoritative references

• MongoDB 8.3 release notes — Current 8.3 behavior and version-sensitive aggregation changes; re-check before reproducing.
• MongoDB aggregation pipeline — Ordered-stage execution model used throughout the chapter.
• Aggregation pipeline limits — Memory, disk-spill, stage-count, and 16 MiB output-document constraints.
• mongosh release notes — mongosh version used for the chapter commands.
• $merge stage — Materialized-view updates, on/whenMatched/whenNotMatched semantics, unique-index requirements, partial-write behavior, sharded targets, and restrictions.
• $out stage — Whole-collection replacement process, index copying, failure behavior, target restrictions, and transaction restrictions.
• On-demand materialized views — Materialized-view concepts and refresh patterns.
• Schema validation — Validation interactions for write-back targets.