Write a source-to-target mapping with transformation rules and test it in SQL.
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 mapping (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:
| Target field | Source field(s) | Transformation rule | Default / null handling | Notes |
|---|---|---|---|---|
external_id | cust_no | prefix with HO- | required | used to trace back to legacy |
first_name | full_name | text after the comma, trimmed | blank if no comma | |
last_name | full_name | text before the comma, trimmed | whole value if no comma | |
state_code | state_name | look up in state table, case-insensitive | UNKNOWN, flagged for review | |
status | status_code | A->active, I->inactive | anything else -> review | X seen in data profiling |
joined_on | joined | MM/DD/YYYY -> YYYY-MM-DD | required |
Mapping types you will meet:
- Direct (1:1): copy as is, perhaps renaming.
- Transformation: reformat, convert units, split or concatenate.
- Lookup / 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 key (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 JOIN 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.