How to Merge Data Sources With Different Schemas Into One Searchable Index

The Short Answer

Merging sources with different schemas into one searchable index means picking a canonical field set and declaring, per source, which of its fields map onto which canonical field. One system calls it headline, another calls it name, a third calls it asset_title. You decide the merged view has title, and you write down the three mappings that get you there.

The decision that matters most is where the mapping lives. Doing it in an ETL job before ingest means the index only ever sees clean data, and it also means every schema change is a code change and a backfill. Declaring the mapping on the collection itself means the raw records stay as they are and the merged view is derived, so adding a fourth source is a config change.

Two details decide whether the result is trustworthy. Handle the collision case explicitly, because two sources mapping to the same target field is either a deliberate merge or a mistake and the system cannot tell which. And keep lineage, because the moment a result set spans three systems, "where did this row come from" stops being a debugging question and becomes an access-control and correctness question.

Why Different Schemas Are The Normal Case, Not The Exception

Teams usually discover this problem late, because each system was reasonable on its own. The DAM was configured by the media team and uses asset_title. The ad platform exports headline because that is the ad-industry word. The raw footage in object storage has no metadata at all beyond a filename and whatever the camera wrote.

Nobody made a mistake. They optimized locally, and the schemas diverged because there was never a moment where one person owned all three.

Multimodal makes it worse in a specific way. Text corpora tend to converge on a few conventions, because everyone is broadly indexing documents. Media systems carry domain vocabularies that genuinely differ: a broadcast catalogue has programme_id and segment_in, an ad platform has creative_id and variant, and neither is wrong. The fields are not the same field with different spellings, they are different concepts that partially overlap, and the mapping has to encode a judgment rather than a rename.

The practical consequence: schema reconciliation is a design task with a person's opinion in it, and treating it as a mechanical string-matching problem produces a merged view that is technically populated and semantically incoherent.

The Four Ways Teams Solve It

Transform before ingest. A pipeline reads each source, rewrites it into the canonical shape, and writes clean records. The index never sees the messy version. This is the most common answer and it works at small scale. The cost arrives when the fourth source shows up, or when a source adds a field: you are editing pipeline code and backfilling history for what is conceptually a rename.

Index everything separately and merge at query time. Keep one index per source and fan a query out to all of them, reconciling in the application. Nothing is lost and nothing is normalized, which means every consumer of the results reimplements the mapping, and they will not agree with each other. Ranking across separate indexes is also its own problem, because scores from different corpora are not comparable without score normalization.

One index, one union schema. Ingest everything into a single collection and keep every field from every source, so the merged documents have headline and name and asset_title sitting side by side, mostly null. Queries then have to know all three names. This scales badly in a way that is invisible at first: the schema grows with every integration and every query grows an OR clause.

Declare a field map on the collection. The sources stay as they are, and the collection carries a per-source declaration of which fields to keep and what to call them in the merged view. Adding a source is adding a mapping. This is the shape that stays cheap as the source count grows, and it is what the rest of this guide is about.

Designing The Canonical Field Set

The canonical set is a product decision wearing an engineering costume. A few rules that hold up.

Name for the query, not the source. The canonical field should be what someone searching would call it. title beats asset_title even if two of your three sources use the latter, because the merged view is read by people and agents who do not know which system a row came from.

Keep it small and add deliberately. Every canonical field is a field that must be populated, defaulted or explicitly absent for every source. A twelve-field canonical set across four sources is forty-eight decisions. Most merged views need far fewer fields than the union suggests, because the long tail is source-specific and belongs in a lineage-preserved raw blob rather than in the canonical set.

Do not map concepts that merely rhyme. If one source's duration is the whole asset and another's is a single segment, mapping both to duration produces a field that is silently wrong in a way no test catches. Either normalize the meaning at the mapping (segment duration and asset duration are two canonical fields) or leave the ambiguous one out.

Decide what an omitted source means. If a source has no mapping declared, does it contribute nothing, or does it pass everything through unchanged? Both are defensible and they produce very different merged views. Whatever the answer, it should be a documented default rather than an emergent behaviour.

What Happens When Two Sources Want The Same Field

This is the case that separates a system you can trust from one you cannot, and it is worth being blunt about: two sources mapping to the same target field is ambiguous, and no amount of cleverness resolves it from the data alone.

It might be exactly what you want. Three systems each carry a human-written title and you want one title field regardless of origin. That is a deliberate union.

It might also be an accident. Someone adds a fourth source, maps its name to title, and does not notice that another source already claims title. Now a query for a title returns rows where the field means two different things, and nothing failed.

There are three possible behaviours and only one of them is safe by default:

Last writer wins. Whichever source is processed last overwrites. This is the worst option because the result depends on ingestion order, which means it can change between runs without anyone editing anything.

Merge into an array. Defensible, and it changes the field's type depending on how many sources happened to populate it, which breaks every consumer expecting a scalar.

Reject at declaration time. The collision is caught when you declare the mapping, before a single document is written, and you resolve it by choosing different target names or by stating the union explicitly.

Rejecting is the right default because the collision is a design question and declaration time is the only moment a human is present to answer it. Mixpeek returns a 422 at collection-create when two sources map to the same target path, and names the offending field in the error, so the failure arrives while you are still editing the config rather than three hours into a backfill.

Keeping Lineage After The Merge

A merged document should always be able to answer where it came from. This stops being a nice-to-have the moment any of these are true, and at least one of them usually is:

Access control. If one source is licensed content and another is not, a merged result set without lineage cannot be filtered by entitlement, so you either over-restrict everything or leak.

Correctness debugging. When a search result looks wrong, the first question is always which system produced the underlying record. Without lineage, that is a manual hunt through three UIs.

Deletion and retention. A takedown request or a retention policy applies to records from one source. You cannot honour it against a merged view that has forgotten the origin.

Trust in ranking. If one source's metadata is thin, its rows will underperform, and you cannot see that pattern at all unless you can group results by origin.

Practically this means the merged document keeps a reference to its source and its original identifier, and that reference is queryable rather than buried. The related discipline is worth naming: whatever path exposes lineage on read must also be filterable, because a lineage field you can see and cannot filter on is decoration. Our own platform rule is that a field is addressable at the same path on every surface, read, filter, sort and facet alike.

Doing It In Mixpeek

A collection can take several buckets as its source and carry a per-source field map. The shape is a mapping from source id to a list of field passthroughs, each naming a source path, the target path it lands on, and optionally a default.

{
  "collection_name": "merged-catalogue",
  "source": {
    "type": "bucket",
    "bucket_ids": ["bkt_ads", "bkt_catalogue"],
    "field_map": {
      "bkt_ads":       [{ "source_path": "headline", "target_path": "title" }],
      "bkt_catalogue": [{ "source_path": "name", "target_path": "display_name", "default": "untitled" }]
    }
  }
}

The behaviours worth knowing before you design against it:

A source with no entry in the field map passes everything through. Omission means "keep this source as it is" rather than "drop this source", so a partially-declared map does not silently empty the sources you did not mention.

A collision is a 422 at create. Point two sources at the same target_path and the create fails with the field named, before any documents are written.

default fills the gap when a source genuinely lacks the field, which keeps the canonical set populated without inventing a value at query time.

Records keep their identity back to the originating bucket, so the merged collection stays searchable as one set while remaining traceable to each source. Everything after that is the normal path: the collection's extractors run over the merged documents, and retrieval treats them as one corpus. If you are pulling from object storage you already own, MVS is the vector store side of the same idea, and the vector store overview covers how collections and namespaces fit together.

When Merging Is The Wrong Answer

Three cases where a single merged collection costs more than it returns.

The sources have different access rules and you cannot express them as a filter. If entitlement is per-source and your filter path cannot reach the lineage field, separate collections enforce the boundary structurally rather than by remembering to add a predicate.

The sources have different freshness requirements. One source updating hourly and another quarterly in the same collection means either you re-run everything at the fast cadence or the merged view is inconsistent by design. Change data capture helps, and it does not remove the cost of coupling two very different update rates.

You are merging to avoid a product decision. If nobody can say what the canonical title means across three systems, merging does not answer that question, it hides it behind a field name. That is the case to push back on rather than build around.

Frequently Asked Questions

How do I combine data from multiple sources with different schemas?

Pick a canonical field set for the merged view, then declare per-source mappings from each source's field names onto those canonical names. The mapping can live in an ETL job that rewrites records before they are indexed, or it can be declared on the index itself so the raw records stay untouched and the merged view is derived. The declarative version scales better as the number of sources grows, because adding a source becomes a config change rather than a pipeline edit plus a backfill. The two things to get right regardless of where the mapping lives are collision handling, when two sources want the same target field, and lineage, so a merged row can still say which system it came from.

Should I normalize schemas before indexing or map fields at the index?

Normalizing before indexing gives you a clean corpus and costs you a code change and a backfill for every schema change. Mapping at the index keeps raw records as they are and makes the merged view derived, so a new source or a renamed field is a declaration rather than a migration. Map at the index when your source count is growing or unstable, which is the usual case once more than two systems are involved. Normalize beforehand when the transformation is lossy or expensive, for example when you are deriving a field rather than renaming one, since a field map expresses selection and renaming rather than computation.

What happens when two data sources have the same field name?

That depends on the system, and the difference matters more than it sounds. Last-writer-wins makes the result depend on ingestion order, so the same config can produce different data between runs. Merging into an array changes the field's type based on how many sources populated it, which breaks consumers expecting a scalar. Rejecting the mapping at declaration time is the safest default, because a collision is a design question and declaration time is the only point where a person is present to answer it. Mixpeek returns a 422 when two sources map to the same target path at collection creation and names the field, so you find out while editing config rather than partway through a backfill.

How do I keep track of which source a search result came from?

The merged record has to keep a reference to its originating source and the original identifier, and that reference has to be queryable rather than merely visible. Lineage stops being optional as soon as any source has its own access rules, retention policy or takedown process, because all three apply to records from one system and cannot be honoured against a view that forgot where rows came from. It is also the first thing you need when a result looks wrong. Check that the lineage field is filterable and not only readable: a field you can see and cannot filter on will not enforce an entitlement boundary.

Can I search across multiple systems without moving the data?

You can query several indexes and reconcile in the application, and it avoids a copy at the cost of every consumer reimplementing the mapping and of ranking across corpora whose scores are not directly comparable. If the data already sits in object storage, a better framing is that the store you own is the substrate and the search layer reads from it, so there is no move in the first place. That is the design behind object-storage-backed vector search: the vectors and payloads live in your bucket and the query engine runs against them there.

How many fields should a canonical schema have?

Fewer than the union of your sources, and each one added deliberately. Every canonical field is a decision that has to be made for every source, so a twelve-field set across four sources is forty-eight decisions, most of which will be "this source does not have it". The long tail of source-specific fields belongs in a lineage-preserved raw blob rather than in the canonical set, where it would be null for most rows and would still have to be reasoned about. Start with the fields queries actually filter and sort on, and let the rest stay where they came from.