Data mapping is the practice of defining how each field in a source system corresponds to a field in a target system, including any transformation applied along the way. A mapping states that VENDOR_NAME in one system becomes supplier_name in another, that it is trimmed to eighty characters, and that a null becomes the literal “Unknown”.
The most useful thing to understand about data mapping is that it produces an artefact, not just an activity. The mapping is a document that outlives the project that created it, and a mapping held only in a developer’s head or buried in transformation code is the root of a large share of data quality problems. Somebody has to own it.
What a data mapping document contains
| Element | What it records | Why it matters |
| Source and target identifiers | System, table and field on both sides | Makes the mapping unambiguous |
| Data types, lengths and formats | Type on each side, and any conversion | Where silent truncation originates |
| Transformation logic | The rule applied between the two | The part most often left undocumented |
| Default and null handling | What happens when the source is empty | Prevents inconsistent gap filling |
| Ownership and version history | Who owns it and what changed when | Makes the document maintainable |
The transformation logic column is the one that carries the value. Two field names side by side tell a reader almost nothing; the rule between them is the knowledge worth keeping.
The types of data mapping
- One-to-one mapping moves a source field to a target field unchanged, or with a simple type conversion. Most mappings are of this kind and need little documentation beyond the pair itself.
- One-to-many and many-to-one mapping splits a single source field into several targets, such as separating a combined address, or combines several sources into one target, such as building a full name.
- Derived and calculated mapping produces a target value that does not exist in the source, such as a margin calculated from price and cost, or a fiscal period derived from a transaction date.
- Lookup and reference mapping translates a code into its meaning or into another system’s code, typically through a reference table. Currency codes, cost centre translations and status codes are the common cases.
How data mapping is done
- Profile the source data. Examine actual values before designing anything. Field names mislead, and real data reveals nulls, unexpected formats and values outside the documented range.
- Define the target schema. Know what the destination requires, including mandatory fields and constraints, before deciding how to fill it.
- Match fields and record the rule. Pair each target field with its source and write the transformation next to it, in language a non-developer can check.
- Handle the exceptions. Decide explicitly what happens to unmapped values, out-of-range dates and records failing a lookup. Rejected, defaulted or quarantined are the three options, and choosing by default is how bad data spreads.
- Test with real volumes. A mapping that works on twenty sample rows often fails on two million, through performance, duplicate keys or values that only appear at scale.
Where data mapping is used
Data mapping appears wherever two systems must agree about the same information. System integration is the most visible case: any interface between applications is a mapping, whether or not it is written down. Data warehouse loads depend on it heavily, since source structures rarely resemble the reporting model they feed, and this is where the ETL process consumes the mapping directly.
Reporting and analytics layers map physical fields to business-friendly names, which is what allows a user to select “Net Revenue” rather than a column code. Data migration between ERP releases is the most demanding case, because the mapping has to cover every field in scope with no gaps, and errors surface only after cutover. Increasingly the same mappings feed automated and AI data pipeline workloads, where an undocumented transformation is even harder to trace after the fact.
Data mapping in Oracle ERP environments
Oracle environments make mapping harder in three specific ways. E-Business Suite table structures are normalized for transaction processing, so a single business concept such as an invoice spans several tables with keys that must be joined correctly before mapping means anything. Knowing which tables hold the authoritative version is specialist knowledge.
Fusion Cloud presents a different problem. Access is through objects and services rather than direct table reads, so mapping works against a published interface whose shape is controlled by Oracle. When entities exist on both platforms, two different mappings must produce the same target structure.
The third issue is drift. Oracle applies quarterly updates, and a mapping built against a specific structure can quietly stop matching. Orbit Analytics maintains pre-built Oracle schema knowledge inside its data pipeline, so the mapping from EBS and Fusion Cloud structures into a reporting model is maintained centrally rather than rebuilt after each update.
Common data mapping problems
Undocumented transformations are the most damaging. The mapping exists in code, the code works, and nobody can explain why a field is multiplied by 1.05 until the person who wrote it has left.
Silent truncation and type coercion cause errors nobody notices. A ninety-character description loaded into an eighty-character field arrives shortened, with no error raised, and the loss is only found when someone reads a truncated record months later.
Mappings that drift from the source are the slow failure. A new source field is added and never mapped, so the target quietly stops being complete. One spreadsheet with no owner is the organizational version: the mapping document exists, several versions circulate, and nobody knows which is current. Warehouse loads into Snowflake, Databricks or Redshift are where this shows up first, because those targets are consumed directly by reporting, and Orbit Analytics pushes curated data to those destinations from mappings held centrally rather than per project.
Data mapping vs. data modeling vs. data lineage
These three sit next to each other and get conflated regularly, which matters because they answer different questions.
Mapping connects two schemas. It is a correspondence between an existing source and an existing target, field by field, with rules attached. It assumes both structures already exist.
Modeling designs one schema. It decides what entities exist, what attributes they carry and how they relate. Modeling comes first when the target is new; mapping comes after, to fill it.
Lineage traces a value end to end. It answers where a specific figure came from, through every system and transformation it passed. Lineage is derived from mappings but is broader: a single reported number may pass through four mappings, and lineage is the record of the whole path.
In practice a mature environment needs all three: a model that defines the target, mappings that populate it, and lineage that lets anyone trace a number back to its origin.
Frequently Asked Questions
Q1. What is data mapping?
Data mapping defines how each field in a source system corresponds to a field in a target system, including any transformation applied. It produces a documented artefact, not just a one-off activity.
Q2. What is a data mapping document?
It is the record of those field pairs, holding source and target identifiers, data types, transformation logic, default and null handling, plus ownership and version history.
Q3. What are the types of data mapping?
One-to-one, one-to-many and many-to-one, derived or calculated mappings that produce values not present in the source, and lookup mappings that translate codes through a reference table.
Q4. What is the difference between data mapping and data modeling?
Modeling designs a schema, deciding what entities and attributes exist. Mapping connects two schemas that already exist. Modeling usually comes first when the target is new.
Q5. What is the difference between data mapping and data lineage?
A mapping describes one source-to-target correspondence. Lineage traces a value across every system and transformation it passed through, and is assembled from multiple mappings.
Q6. Why is data mapping important in ETL?
The transform stage of ETL executes the mapping rules. Without a documented mapping, the transformation logic exists only in code, which makes it unverifiable and hard to change safely.
Mappings from Oracle structures into a reporting model are the part most teams rebuild after every quarterly update. Orbit Analytics maintains that layer centrally across EBS and Fusion Cloud, with lineage from any reported figure back to source. Request a demo to see it on your own data.
