How to Plan Data Mapping for a Systems Integration

Follow one completed record across both systems

Choose a representative source and destination pair, such as a customer, order, invoice, item, or service request. If the destination record does not exist yet, assemble the expected pair from approved test records. Synthetic records in an authorized test environment are safer when removing identifiers would break relationships needed for the mapping. Put the two views beside each other and trace the fields people use during the next business action.

Start with the fields needed for that action. A full database inventory can bury the few values that decide whether an order can be processed or an invoice can be reviewed. Add other fields when a defined use requires them.

Keep an exception record nearby. A missing identifier, corrected address, split shipment, closed account, or duplicate entry often reveals a mapping rule that an ordinary record does not show.

Field labels are only clues

For every value, write a plain-language definition and name the person or team that can confirm it. Include the event that creates the value and the point when it may change. A field called total might include tax in one system and exclude tax, freight, or credits in another.

Record units, currency, time zone, precision, allowed range, maximum length, uniqueness, permitted codes, scalar or list structure, character handling, locale, and whether blank has a business meaning. Dates need particular care because a date-only value, a local timestamp, and a UTC timestamp behave differently near cutoffs and daylight-saving changes.

Which system controls the value

The mapping row should name the source of truth for the field and the systems allowed to change it. Direction matters. A one-way feed, a return update, and two-way synchronization create different conflict and correction problems.

Some fields have different owners at different stages. Sales may control a requested delivery date until an order is approved, while operations controls the scheduled date afterward. Record the transition event and the behavior for edits that arrive after ownership changes.

What belongs in a mapping row

Use one row per destination write or relationship rule. Put every source input, transformation, and resulting destination field in that row so a derived value does not become disconnected from the rule that creates it. Link to system documentation when it exists. Store sample records in an approved location rather than copying sensitive data into the worksheet.

For example, one row might combine a source quantity and unit price, apply an approved rounding rule, and write the result to the destination line amount. The row should name the owner of that meaning and the evidence used to test it.

• Business meaning and owner
• Source system, object, field or fields, and data type
• Destination system, object, field, and data type
• Direction and event that starts the transfer
• Transformation or lookup rule with its approved source
• Allowed blank, default, and invalid-value behavior
• Identifier used to find the destination record
• Relationship cardinality and lifecycle behavior
• Create, update, upsert, correction, and deletion behavior
• Idempotency or deduplication key and unknown-outcome handling
• Validation check and evidence retained
• Correction path, review owner, and mapping version

Transformations need business rules

A transformation changes a value instead of copying it directly. It may combine first and last names, convert units, translate a status code, round an amount, split an address, or look up an internal identifier. Write the rule in terms the responsible business owner can approve.

Lookup tables need an owner and effective date. When a new source code appears, the workflow should hold or route the record according to an agreed rule. Quietly choosing a nearby destination value can produce a valid-looking record with the wrong meaning.

Keep calculations reproducible. Record the input fields, order of operations, rounding point, and exchange-rate source when applicable. The accountable business, audit, legal, or compliance owner should decide when processed records must retain the mapping version and input evidence.

Identifiers and relationships

A customer name or product description is rarely a dependable match by itself. Prefer stable identifiers issued by the controlling system. When the systems use different identifiers, keep a controlled cross-reference that records how the pair was established and corrected.

Relationships also need mapping. An invoice may depend on a customer, location, order, currency, and line items already existing in the destination. Mark each relationship as one-to-one, one-to-many, or many-to-one. Define creation order and the behavior when a related record is added, merged, deactivated, cleared, removed, missing, duplicated, or still being created by another run.

If matching uses several fields, document normalization and tie-breaking rules. Ambiguous matches should remain visible for review, with the originating source record preserved in the evidence for any destination update.

Blank, default, and invalid values

Blank can mean unknown, unavailable, not applicable, intentionally cleared, or never collected. The map should distinguish an omitted field, explicit null, empty string, zero, false, and a clear or delete instruction because destinations may handle them differently. Decide which meanings the source can express and which the destination can preserve.

For required fields, define whether the record is rejected, held, partially created, or created as a draft. Name the review queue and the person responsible for correcting the source. If the destination rejects a value, capture its response with enough context to retry safely after correction.

Review status lists and boolean fields within the same rule. Active, closed, pending, yes, no, and unknown can carry workflow consequences beyond their display labels. A default value may keep a record moving while hiding missing information from the next person.

Corrections, late updates, and uncertain retries

State whether the destination operation creates, updates, or upserts a record. Record the idempotency key, uniqueness constraint, or controlled ledger that prevents a retry from repeating a completed write. After a timeout with an unknown outcome, read the authoritative destination state before deciding whether another write is safe.

Corrections may overwrite a field, append a version, create a reversing entry, or wait for approval. Define what happens when source records are merged, closed, cleared, or deleted, and whether those changes propagate to the destination. A physical deletion may be inappropriate where financial, legal, audit, or operational history must remain intact.

For late or out-of-order events, name the timestamp, sequence, version, or business state used to decide which update applies. Keep conflicting cases visible when the available evidence cannot establish the correct order.

Test the map with a small reconciliation set

Build a test set that covers ordinary records, boundary values, missing fields, new lookup codes, ambiguous identifiers, corrections, duplicates, and a destination failure partway through a batch. State the expected destination value and expected workflow action for every case.

After the transfer, read the result from the authoritative destination path and compare it with the expected record. Querying the destination API or system of record provides stronger evidence than trusting the sender's success response alone. Account for documented processing or settlement delays. Check counts and totals where they provide useful evidence, then inspect individual fields and relationships that could still be wrong while the totals agree.

The person who owns the business meaning should inspect the verified destination state and approve that its fields and relationships carry the intended meaning.

Keep the map with the running integration

Assign the mapping a version and effective date. When either system changes a field, type, status list, identifier, or API behavior, assess the map before releasing the change. Include representative mapping cases in regression testing.

Record where operators can see the mapping version, failed records, corrections, and the last successful reconciliation. Set a review owner and date for mappings that depend on changing codes, organizational rules, currencies, or vendor behavior.

Before expanding the integration, take one recently processed record and trace it through the current map. If the documented meaning no longer matches what the systems or staff use, correct the map and decide how already processed records will be handled before adding more fields.