Building a Product Around Other People's Messy Data

If you build a product on top of other people’s data and you assume it’ll arrive in your shape, with your names for things, to your standards, you’ve already lost. You just haven’t hit the failure yet.

People and companies model the world differently. They named their fields years before your product existed, for reasons that made sense to them, and they aren’t going to change to suit you. The moment you depend on their data, their inconsistencies become your problem. Expect that from the start and you build something that bends. Assume it away and you build something that snaps the first time a source does something entirely reasonable that you didn’t predict.

You don’t get to define the input

The instinct is to write down the shape you want and treat anything that doesn’t match as broken. It’s a comfortable way to think and it doesn’t survive contact with real sources.

The same business concept shows up under different names, at different granularity, with different assumptions baked in. One system calls it a store, another a location, another a site. One tracks stock as a whole number, another as a string with the odd blank. One sends you a tidy export, another sends the same data with three extra fields that mean nothing to you and one missing field that means everything. None of this is malice. It’s just what happens when different people solve the same problem without talking to each other. Your job isn’t to correct them. It’s to absorb them.

Correct is a moving target

The trap is aiming for correct, as if a source has one true shape you can map once and forget. Sources drift. A field that was always an integer starts arriving as a string. A flat record grows a region layer. An endpoint you relied on gets deprecated with a month’s notice buried in a changelog nobody reads.

So the goal isn’t a correct model, it’s one that can change without a rewrite. That has a real consequence for how you treat your own schema: you’ll be adding columns and reshaping your model as new sources and new shapes turn up, and you want that to be a routine, safe operation rather than a scary one. This is where safe migrations stop being a database chore and start being a product capability. If growing the model is cheap and safe, absorbing a new source is cheap and safe. If it isn’t, every integration becomes a fight.

Translate into a model you own

The single most useful decision is to stop letting sources define your world. Pick your own shape, decide what a store and a product and a quantity mean in your product, once, and translate everything into that.

Here’s the difference in practice. Two systems describe the same stock level for the same product at the same store:

// source A
{
	"store_no": "0421",
	"product": "Coffee Beans 1kg",
	"qty_on_hand": "37",
	"updated": "2026/03/01"
}

// source B
{
	"location": { "id": 421, "region": "VIC" },
	"sku": "CB-1KG",
	"name": "1kg Coffee Beans",
	"stock": 37,
	"lastSync": "2026-03-01T09:00:00Z"
}

Different names, different types, different date formats, a store identified by a zero-padded string in one and a nested integer in the other. Your product shouldn’t have to reason over either of these. It should reason over one shape that belongs to you:

// your canonical record
{
	"storeId": "421",
	"productId": "CB-1KG",
	"productName": "Coffee Beans 1kg",
	"quantity": 37,
	"asOf": "2026-03-01T09:00:00Z",
	"source": "pos_a"
}

The subtle part isn’t the renaming. It’s that “product” in source A is a display label, while source B carries a coded sku and a separate name. Deciding that your model trusts the SKU and treats the label as descriptive is a product decision, not a mapping detail. It’s the same reason two systems can use different words for the same thing and both be right. The canonical model is where you make those calls deliberately, in one place, instead of scattering them through the codebase as accidents.

Give the mess one place to stop

Once you have a model you own, every source gets its own translator whose only job is to turn that source’s shape into yours. Source A’s quirks live in Source A’s translator and nowhere else. Source B’s live in Source B’s.

That boundary is the whole point. When a source changes, and it will, you fix one translator instead of chasing the change through every feature that touched the data. The core of the product never learns that Source A pads its store numbers or that Source B nests its location. It only ever sees your clean record. The mess is real, but it stops at the door.

Validate at the door

A translator shouldn’t just reshape a payload, it should refuse one that doesn’t make sense. A quantity that came through as null, a store ID that maps to nothing, a date that won’t parse: catch it at the boundary and fail loudly, rather than passing it through and letting it quietly poison everything downstream.

This is the same lesson as building a good data pipeline. A failed import is visible and rerunnable. Bad data that looked fine is the expensive one, because nobody notices until a customer is staring at a number that’s wrong and you’re working backwards through three systems to find out why.

Keep the original

Always store the raw payload next to the record you derived from it. It feels redundant right up until the first time you need it, and then it’s the only thing that saves you.

You will get a mapping wrong. You’ll decide six months in that “product” should mean something different, or a source will reveal a field you should have been reading all along. When that happens, you want to reprocess from the raw you already have, not re-fetch from a source that has since moved on and can no longer give you the version you had. The raw is your ground truth. The normalised record is just your current opinion of it.

An integration is a relationship, not a feature

All of these patterns are ways of admitting the same thing. You ship a feature once. An integration you keep, for as long as both systems are alive.

The source will change owners, change formats, add fields, drop the endpoint you depended on, tighten the permissions that used to let you read everything. Designing for change, translating into a model you own, validating at the door, and keeping the raw are all just ways of staying ready for the next time it happens, because there’s always a next time. Treat an integration like a relationship and it stays workable. Treat it like a feature you finished, and it rots quietly until the day it breaks in front of a customer.