Field Mapping and Data Transformation: The First Mile of Heterogeneous System Integration
Why Field Mapping Is the "First Mile"
Getting two systems' APIs to respond usually takes a day or two; aligning field semantics often drags on for weeks. The same business concept differs across systems in name, type, granularity, and value range. "Order amount" may be a tax-inclusive string in yuan on one side and a tax-exclusive integer in cents on the other. Get the mapping wrong and a perfectly stable interface stably produces wrong data.
Step 1: Field Inventory and the Mapping Worksheet
For both source and target, list: field name, business meaning, data type, required or not, value range or enum, and a sample value. Then build row-by-row mappings: source field → transformation rule → target field. This worksheet is the baseline for everything that follows and the vehicle for confirming semantics with the business.
Step 2: Four Typical Transformations
| Type | Example | Approach |
|---|---|---|
| Rename | order_no → FBillNo | Direct mapping |
| Type/unit | Amount in yuan (string) → cents (integer) | Multiply by 100, convert, mind float precision |
| Enum translation | "WAIT_SELLER_SEND_GOODS" → "Paid" | Value-mapping table with a fallback for misses |
| Split/merge | Name + phone concatenated into one address line | Validate splitting rules against edge samples |
Step 3: Required Fields and Defaults
Fields required by the target may not exist in the source. Resolution priority: derive from other source fields, then generate by rule (e.g. bill number patterns), then global constants, then manual completion during mapping. Never pad a field with a placeholder just to make the API pass — that dirty data resurfaces in reconciliation.
Step 4: Validate with Edge Samples
Run the mapping against real boundary data: maximum-length strings, empty vs. null (an empty string may overwrite existing values), special characters, extreme numbers, and time-zone-sensitive timestamps.
Mapping as a Managed Asset
Mapping is not one-off work: upgrades add fields and the business changes definitions. Version the mapping tables, record why each change was made, and log raw values that miss the mapping table instead of silently dropping them — they are the earliest signal of semantic drift.