derivation-join-collections

derivation-join-collections

Estuary derivation that merges documents from multiple source collections into a single collection, keyed on a shared join field. Fields from each source contribute, and Estuary's reduction engine merges them.

Prereq: read derivation-basics first for concepts, the stateless-vs-stateful distinction, and reduction-annotation mechanics.

Docs:

• https://docs.estuary.dev/guides/howto_join_two_collections_typescript/ — official TypeScript walkthrough
• https://docs.estuary.dev/reference/reduction-strategies/ — reduction strategy reference (critical for joins)

Canonical examples in the Flow repo (cover the three join shapes — see "Join types" below):

• join-outer.flow.yaml — full outer (reduction-based, no state)
• join-inner.flow.yaml — inner (SQLite state, both sides must match)
• join-one-sided.flow.yaml — left / right (SQLite state, one side drives emission)

When to use this over alternatives

• Enrichment: attach customer profile fields onto every order
• Denormalisation: combine dimension + fact tables into a wide analytics table
• Customer lifetime metrics: one doc per customer with counts / totals rolled up from orders
• Pre-joined views for BI tools that prefer wide tables over joins at query time

Reach for other skills when:

• Only one source collection, aggregating by key → derivation-aggregate-metrics
• Need an embedded array of matches, not merged fields → combine with append reduction (still this skill, but see Variations)
• You want arbitrary stateful logic beyond reductions (approval flows, balance checks) → derivation-stateful-logic

Join types

Stream joins aren't quite the same as SQL joins. Pick the type before writing the derivation — the pattern differs by type: