Data mapping: define fields before joining business records

The same column name can carry a different unit, event date, or business meaning in each source.

Kartik Deshpande··5 min read

Data mapping defines how source fields become fields in a destination or shared business model. A usable map records the source path, destination meaning, data type, unit, identity key, transformation, and treatment of missing or invalid values. Approve those meanings before you combine records, since matching column names alone does not establish that their contents are comparable.

An amount field in Shopify, an invoice export, and a spreadsheet can mean gross order value, posted revenue, or a manually adjusted total. Renaming all three to revenue preserves the disagreement under one heading. Start with the question the output must answer, then identify which source event and value can support it.

Set the business meaning and record grain

Choose the population and unit of a destination record. An order, order line, shipment, and invoice are related objects, but they are not interchangeable rows. Document whether the destination stores one row for each source line or aggregates lines under an order key. Keep the source identifiers needed to trace a destination value back to its inputs.

Microsoft’s schema and data type mapping documentation distinguishes default name-based mapping from explicit field mapping. Explicit mappings can rename fields and reshape tabular or hierarchical data. That is a technical mechanism; the business owner still has to establish what each field means and whether the chosen transformation preserves that meaning.

Identify the owner of each destination definition. Finance may own the selected revenue event, operations the accepted shipment status, and inventory the base unit. Record the decision when their definitions differ. A shared field should either adopt one approved definition or expose separate measures with names that identify their scope.

Create a source-to-destination mapping register

For each field, retain its source application, table or object, exact field path, destination name, type, allowed values, conversion, and null behavior. Add the identity and relationship keys needed by the join. A mapping from customer name to customer identifier needs an actual resolution rule or lookup, not a type conversion that turns both values into text.

Preserve units and currencies explicitly. A quantity of two cartons cannot be added to twenty pieces until the item-specific carton size and its effective date are known. A currency code without an agreed conversion date and rate source does not establish a common monetary basis. Keep unknown conversions visible instead of defaulting them to one.

Date fields need their event, time zone, and precision (including whether the source supplies a time of day). Order created, invoice posted, and cash received answer different period questions. A date-only source cannot support an intraday ordering claim, and a timestamp stored without a time zone needs a documented interpretation. Test the period boundary before using it to group daily totals.

Move from field meaning to verified output
Define meaning
Declare conversion
Verify records
A successful type conversion can still produce the wrong business value when the event or unit is misidentified.

Map carton quantities without losing the original value

Assume a hypothetical purchasing export with one line for item A: quantity 12, unit cartons, and a supplier item code. The shared inventory output requires pieces under the internal item identifier. The approved mapping links the supplier code to internal item A and establishes ten pieces per carton for that revision and effective date. The expected quantity is therefore 120 pieces.

Source or ruleValueDestination treatment
Source quantity12 cartonsRetain original quantity and unit
Approved item mappingSupplier code to item AUse the internal item identifier
Effective conversion10 pieces per cartonMultiply 12 by 10
Normalized quantity120 piecesStore with the mapping reference

Now assume the next row has the same supplier code but no unit. It could mean cartons or pieces, so the expected normalized quantity is unknown. Do not apply the carton conversion because the previous row used it. Route the row to the source owner and retain the raw value until the unit is confirmed under an approved rule.

A later packaging change to eight pieces per carton should not rewrite historical quantities that used ten. Version the conversion by the relevant item revision or effective period and retain the source value. If the original source was wrong, record a correction separately from a policy change so the reviewer can reconstruct why the destination amount changed.

Inspect a mapping on a small record sample

Start with permitted source fields, their definitions, and the output you need to reproduce.

Test mappings against exceptions and changed schemas

Validate more than a representative clean row. Include missing identifiers, repeated keys, unknown status codes, invalid dates, zero quantities, and values near period boundaries. Expected results should come from the approved meaning and transformation. A test that repeats the mapping code without an independent expected outcome does not verify the business decision.

Check uniqueness and join coverage before accepting aggregated totals. A repeated item mapping can multiply order lines, while an inner join can remove unmatched records. Preserve unmatched source rows as an exception population and reconcile their counts and values. The data reconciliation article explains how totals and record comparisons complement one another.

Assign a response when a provider adds a field, renames a status, or changes a type. Accepting every new field can expose data the output never needed, while dropping it can hide a changed business meaning. Review the change against the mapping contract and permissions. Keep a versioned acceptance record before promoting the altered output to its consumers.

The ERP integration strategy article covers continuing ownership of cross-system flows. During an ERP migration, the same mapping decisions also need cutover validation and a plan for unresolved source values. A diagram of matching field names cannot substitute for those acceptance checks.

Where Permute reuses declared mappings

Permute sells connected business data and custom applications. We can bring permitted source records together and apply declared mappings for identities, fields, units, and business definitions. Source evidence and unresolved values remain part of the output rather than disappearing when a field is renamed.

We can reuse accepted transformations across reports and scoped applications. An operations owner could inspect which supplier rows lack a valid unit conversion and open the supporting source record within their permissions. Confirm the available fields, historical coverage, and refresh behavior before depending on that review; a mapping cannot recreate an identifier the source never supplied.

Keep business definitions and corrections under ownership

We do not choose accounting treatments or infer an authoritative business definition from ambiguous field names. Mapping rules need approval from the responsible owners. This analytical workflow does not correct a source system or create permission to expose extra fields; supported external changes require their own approval and current access checks.

Define a data mapping by its business meaning, record grain, keys, and conversion rules. Retain original values and the version that explains each normalized result. Test unknown values and changed schemas before accepting the output. Reconcile the mapped records so a destination with valid field types cannot conceal dropped rows or multiplied amounts.

Review a field that changes meaning across applications

Bring source examples and the intended output to establish the definition, conversion, and exception evidence needed.

Questions about mapping business fields

Can automatic mapping settle the business meaning?

It can propose correspondences, but a similar name or compatible type does not establish the same event, unit, or population. Have the definition owner validate those dimensions. Keep uncertain suggestions unapproved until evidence supports them.

Should missing amounts become zero?

Only when the approved meaning makes that valid. Missing can mean unknown, unavailable, or not applicable, whereas zero asserts a measured or defined absence. Preserve the difference when it affects totals or decisions.

Can one source field map to several destination fields?

Yes, when each destination has a stated derivation and purpose. For example, a timestamp can support a retained original value and a reporting date under a declared time zone. Test both outputs and avoid treating them as independent source evidence.