Why field mapping integration decides go-live day

When an integration slips its date, the cause is rarely the transport. Webhooks fire, queues drain, retries behave. What stops the project is field mapping integration work that was left half-finished during discovery and rediscovered under pressure in acceptance testing. Someone opens a failed batch, finds forty-two orders rejected because a delivery address line exceeded the character limit in the receiving system, and the go-live weekend becomes a go-live fortnight.

Field mapping is unglamorous because it produces no demonstrable artefact until the very end. There is nothing to show a steering group in week two except a spreadsheet. It attracts little attention in proposals and almost none in vendor marketing. Yet it is the single piece of work that determines whether the first thousand records land cleanly, and it is the work most often compressed when a timeline tightens.

The compression happens for an understandable reason. Mapping appears to be clerical: take the field on the left, name the field on the right, draw a line. In practice each line carries a set of decisions about data type, length, permitted values, nullability, cardinality, transformation, authority and failure behaviour. A map that records only the two field names has deferred eight decisions per line, and deferred decisions surface as defects.

The argument of this article is straightforward. Treat the field map as a contract, produce it before any code is written, validate it against extracted production data rather than schema documentation, and derive your acceptance tests directly from it. That sequence costs more in week one and considerably less in week ten.

What a field map must record beyond source and target

A usable field map is a table with one row per target field, not per source field. This distinction matters. Target-first mapping forces you to account for every field the receiving system requires, including the ones no source system supplies, which is precisely where defaults and derivation rules hide. Source-first mapping produces a tidy list of things you can send and quietly omits the mandatory field that will reject the entire payload.

For each row, the following attributes should be recorded and signed off by someone who owns the data, not only by the integrator.

Two columns deserve particular emphasis. Authority answers the question of which system wins when both hold a value, and it should be recorded per field rather than per entity, because a customer record frequently has its name owned by the CRM and its credit limit owned by the finance system. Failure behaviour answers what happens when the rule cannot be applied: reject the record, hold it for review, substitute a default, or pass it through with a flag. Left unspecified, the default is usually an unhandled exception at three in the morning.

  • Type and format — Not simply 'date' but the expected representation: ISO 8601 with offset, or a local date with no time component, and what the receiving system does with the difference.
  • Length and precision — Maximum characters for strings, decimal places for numeric values, and what happens on overflow. Truncation silently applied in code is a defect, not a rule.
  • Nullability and mandatory status — Whether the target accepts an empty value, and whether an empty value is semantically different from a zero or a missing key.
  • Cardinality — Whether the relationship is one-to-one, or whether several source rows collapse into a single target row, which is where order lines, tax lines and discount allocations cause trouble.
  • Permitted values — The enumerated list on both sides, and the explicit translation table between them, including the behaviour for values not on the list.
  • Transformation rule — Written as a sentence a finance manager can verify, not as pseudocode. 'Net of discount, excluding tax, rounded to two decimal places, half-up' is testable.
  • Authority — Which system is the source of record for this field, and whether the mapping is one-way or bidirectional.
  • Failure behaviour — Reject, quarantine, default or flag, with the named person or queue that receives the exception.

The mismatches that cause the most rework

Length constraints are the most common and the most avoidable. Sage 200 customer account references are conventionally short and fixed-format, while a Shopify customer record carries an email address and a numeric identifier and nothing resembling an account code. The mapping decision is therefore not 'which field goes where' but 'how is an account reference derived, who allocates it, and what happens for a customer who has never bought before'. That is a business rule requiring a finance owner, and it should be agreed in writing before a line of code exists.

Identifier mismatch runs a close second. NetSuite distinguishes internal identifiers from external identifiers, and a field map that records only 'customer ID' without specifying which one has recorded nothing useful. The same applies to line-level identifiers: a Shopify order line has its own identifier that is stable within the order but meaningless outside it, and using it as a foreign key in the receiving system creates a dependency that breaks on refund or edit.

Enumerations are the quiet one. Order statuses, shipment statuses, payment methods and carrier service codes all exist as closed lists on both sides, and the lists almost never align. A Mintsoft despatch status and a Royal Mail tracking event describe overlapping but different things, and the translation table between them is a business decision about what 'shipped' means to the customer service team. Write the table out in full, including the row that says 'any unrecognised value routes to the exception queue'.

Finally, composite and derived fields. Address formatting is the classic case: three source lines into two target lines, or a separate county field the source system does not hold. Tax is worse, because a total can be derived in several defensible ways that differ by a penny, and a penny discrepancy on ten thousand orders is a reconciliation exercise nobody budgeted for. Agree the derivation with the person who will sign off the month-end position.

Validate the map against extracted data, not documentation

Schema documentation describes what a system will accept. It says nothing about what a system actually contains. The gap between the two is where go-live defects live. A field documented as a two-character country code will, in a system that has been running for nine years, contain full country names, lower-case codes, blank values and at least one entry reading 'see notes'. Mapping against documentation produces a map that is correct and useless.

The discipline is to extract a representative sample of live records early — typically several thousand rows spanning at least twelve months to capture seasonal and year-end behaviour — and profile every field you intend to map. For each field, record the distinct value count, the maximum observed length, the null rate, and the ten most frequent values. This takes a day and routinely halves the defect count in acceptance testing.

Profiling also exposes a category of problem that no amount of design discussion will surface: fields that have been repurposed. A 'notes' field used by the warehouse team to store a pallet reference, a customer reference field containing a mixture of account codes and purchase order numbers, a discontinued flag that means 'discontinued' for products added after 2021 and 'seasonal' for everything before. These are not errors in the source system. They are operational adaptations, and the integration must accommodate them or explicitly reject them.

Where profiling reveals data that cannot be mapped cleanly, there are three honest options: cleanse the source before go-live, define a deterministic transformation that handles the variance, or quarantine the affected records with a named owner. The dishonest fourth option is to assume the volume is small enough not to matter. It is worth establishing the actual count during discovery so that the conversation is about a known number rather than an impression.

Turning the map into acceptance tests and a handover artefact

A signed field map converts directly into an acceptance test pack. Each row with a transformation rule becomes at least three cases: the expected value, the boundary value and the failure value. A customer reference derivation rule is tested with a new customer, an existing customer and a customer whose derived reference collides with an existing record. The point is not exhaustive coverage but traceability, so that when a defect is raised you can identify which mapping row it belongs to and who agreed it.

This also changes the nature of acceptance sign-off. Rather than a demonstration that orders appear in the receiving system, the operations or finance owner reviews a set of test results against rules they previously approved. Disagreements at that stage are about whether the rule was right, not about whether the developer understood it, and that is a considerably shorter conversation.

The same document is the core of handover. Six months after go-live, when a new product category needs a different nominal code or a carrier adds a service, the field map tells the internal team exactly where the rule lives, what it currently does, and who last changed it. Without it, the integration becomes a black box that only the original developer can safely alter, which is how organisations end up rebuilding working systems.

Keep the map under version control alongside the code, with the date and approver on each change. A field map that diverges from the running integration is worse than none at all, because it invites confident decisions based on stale information. Reviewing it as part of any change request is a five-minute habit that preserves years of value.

If you are scoping this work and want a view on how long the mapping stage should take for your particular pair of systems, we are happy to talk it through on 01303 883111 or at hello@api-integrations.co.uk.

Key points

  • Build the field map target-first, with one row per destination field, so mandatory fields and derivation rules surface before build rather than during acceptance testing.
  • Record type, length, nullability, cardinality, permitted values, transformation, authority and failure behaviour for every row — a map with only two field names has deferred eight decisions.
  • Validate the map against profiled production data rather than schema documentation, and convert each signed row into acceptance tests that remain the handover artefact after go-live.