Data mapping

Why it matters

Every integration and every migration moves data from a source structure to a target structure, and the two never match exactly. The old system stores "Rivera, Ana" in one field; the new one wants first and last name. The old system uses status code A; the new one wants active. A source-to-target mappingA document listing, for each target field, its source field, transformation rule and null or default handling. (STTM) is the document that says, field by field, how one becomes the other. Developers build from it, testers test against it, and auditors read it later. Getting it wrong means wrong data in the new system on day one.

The core idea: one row per target field

A mapping is usually a spreadsheet with one row per target field:

Mapping types you will meet:

• Direct (1:1): copy as is, perhaps renaming.
• Transformation: reformat, convert units, split or concatenate.
• LookupA mapping that translates source codes into target values through a reference table. / code mapping: translate codes via a reference table.
• Derived: calculate from several fields.
• Default / constant: fill a target field the source does not have.
• Not migrated: record source fields you deliberately drop, and why.

Profile the data first

Never write rules from field names alone. Data profiling means querying the source to see what is really there:

SELECT status_code, COUNT(*) FROM legacy_customers GROUP BY status_code;
SELECT DISTINCT state_name FROM legacy_customers ORDER BY state_name;
SELECT COUNT(*) FROM legacy_customers WHERE full_name NOT LIKE '%,%';

At Harbor Outfitters this revealed a status code X that nobody remembered (it turned out to mean "closed by fraud team") and state names typed in lower case. Both became explicit rules in the mapping rather than surprises in testing.

Worked example: the mapping as a query

Writing the mapping as a SQL SELECT is a quick way to test it, because you can run it against a copy of the source and look at the output. A few rules from the table:

SELECT
  'HO-' || cust_no AS external_id,
  CASE status_code
    WHEN 'A' THEN 'active'
    WHEN 'I' THEN 'inactive'
    ELSE 'review'
  END AS status
FROM legacy_customers;

Text functions do the splitting and reformatting. In SQLite, INSTR(full_name, ',') gives the position of the comma, SUBSTR(text, start, length) cuts out part of a string, TRIM removes spaces, and || concatenates. Lookups use a LEFT JOIN so that unmatched source rows are kept, with COALESCE(code, 'UNKNOWN') supplying the default. An inner join would silently drop customers whose state is missing from the lookup table, which is one of the most common mapping bugs.

Rules for good mappings

• Write the rule so two people would implement it identically. "Clean up the name" is not a rule.
• Say what happens for nulls, blanks, unknown codes and values too long for the target field.
• Keep a traceability keyA field that links every target record back to its source record. (here external_id) so every target record can be traced to its source.
• Version the mapping and get the data owner to sign it off; it is a requirement, not a developer note.

Common mistakes

• Inner joins on lookups, losing records.
• Assuming one date format when the source mixes several.
• Ignoring character sets and accents (Ana vs Aña) and field lengths.
• No rule for duplicates: two legacy rows for the same person need a de-duplication rule agreed with the business.

Quick recap

• An STTM lists each target field with its source, transformation, default and null handling.
• Profile real source data before writing rules.
• Test the mapping as a query; use LEFT JOINA join that keeps every row from the left table even when the right table has no match. plus defaults for lookups and keep a traceability key.

Try it

In the Do tab you implement the Harbor Outfitters mapping as one SQLite query.