Data Mapping and the Big Picture of Clean Data#

🧽 Data Cleaning & Preparation 🧹 Dirty Data & Spreadsheet Cleaning Lesson 017

Important

✨ AI-generated content. This page was written with the assistance of an AI language model and is provided as a learning aid. Despite careful review, it may still contain mistakes, omissions, or out-of-date information. Whether you are new to the topic, a team lead, or a senior practitioner, treat it as a starting point rather than an authoritative reference: read it critically and independently verify anything you act on (code, commands, figures, and factual claims) against official documentation and primary sources before relying on it.

Making sources speak the same language#

When data comes from several sources, cleaning each one is not enough — the sources must be made to fit together. Data mapping is the process of matching fields from one data source to the corresponding fields in another, so that data from different systems can be combined coherently. It is the “big picture” of clean data: not just tidying values within a table, but ensuring the tables mean the same things so a merge produces sense rather than nonsense.

What data mapping does#

Two systems rarely describe the same reality identically. One calls a field customer_name, another client; one stores state as "NY", another as "New York"; one records dates as MM/DD/YYYY, another as ISO. Data mapping is the explicit specification of which field corresponds to which, and how their values translate:

Field mapping — source.client ↔ target.customer_name: recording that these differently-named columns hold the same thing.
Value mapping — "NY" ↔ "New York": recording how one source’s codes translate to another’s.
Format mapping — MM/DD/YYYY → ISO dates: recording the transformation each field needs to align.

The map is a plan for integration, made before combining, so the merge is deliberate rather than improvised.

Why the big picture matters#

Cleaning field-by-field without the big picture produces tidy tables that still will not combine — each internally consistent, but mutually incompatible. Data mapping is what prevents that: it forces you to see how all the pieces relate before merging, catching mismatches (a field with no counterpart, two fields that seem to match but mean subtly different things) while they are cheap to fix. It is the difference between cleaning as isolated janitorial work and cleaning as preparing a coherent whole ready for analysis — the same big-picture-first discipline the foundations urged, applied to data integration.

Mapping and documentation#

A data map is also documentation — a record of how sources relate that outlives the immediate merge. When next month’s data arrives in the same sources, the map tells you exactly how to combine it again; when a colleague inherits the work, the map explains how the pieces fit. This connects data mapping to the metadata and chain-of-custody disciplines: the map is metadata about relationships between datasets, and keeping it is what makes a multi-source pipeline reproducible.

The caveat#

Data mapping surfaces a hard truth: sometimes fields that look like they correspond do not quite — two “revenue” columns computed under different definitions, two “customer” fields counting different populations. Mapping them as if identical silently corrupts the merged data. The discipline is to map on meaning, not just name similarity — verifying that matched fields genuinely measure the same thing, using the metadata and definitions from the prep section. This completes the spreadsheet-and-concepts side of cleaning; the next lessons scale cleaning up to SQL, where these operations run on data far too large for a sheet.

Hint