Data Mapping Techniques

Section 5: Designing Data Migration Solutions. Lesson 26.

Data mapping is the process of matching fields in a source system to the corresponding fields in a target system. Informatica describes it as connecting fields from one source to fields in another. It is one of the most important steps in migration design, because the mapping document is the blueprint that every ETL script, SQL query and pipeline is built from. Developers need a clear mapping before they write any code.

A simple example

Source and target rarely share table or column names, data types or business rules. An old database may keep a customer's name in a single full_name column while the new one needs first_name and last_name. The mapping document says exactly how each old field becomes a new one.

What a mapping document contains

The four core columns:

• Source table
• Source column
• Target table
• Target column

Plus the details that make it usable:

• Transformation rule: the business logic applied, such as split on the first space, or map M to male
• Data types and lengths on both sides, such as VARCHAR2(80) to VARCHAR(50)
• Validation rule: how to prove the field migrated correctly, such as target not null, or counts match
• Nullability and default value
• Owner or approver, and notes

src_table | src_column  | src_type      | tgt_table | tgt_column    | tgt_type     | rule                         | validation
----------|-------------|---------------|-----------|---------------|--------------|------------------------------|---------------------------
CUST      | CUSTOMER_ID | NUMBER(10)    | customers | id            | INTEGER      | direct                       | unique, count matches
CUST      | FULL_NAME   | VARCHAR2(80)  | customers | first_name    | VARCHAR(50)  | split: text before 1st space | not null
CUST      | FULL_NAME   | VARCHAR2(80)  | customers | last_name     | VARCHAR(50)  | split: text after 1st space  | null allowed, flag 1-word
CUST      | EMAIL       | VARCHAR2(100) | customers | email_address | VARCHAR(255) | rename; lower(trim())        | unique, valid format
CUST      | CITY, ROAD  | VARCHAR2(60)  | customers | address       | VARCHAR(200) | concat: ROAD || ', ' || CITY | not null
CUST      | GNDR        | CHAR(1)       | customers | gender        | VARCHAR(20)  | lookup: M/F/U → words        | in allowed list

In practice this lives in a spreadsheet or a version-controlled YAML or CSV file, signed off by the business owner before development starts.

Types of mapping

-- One mapping document row becomes one expression in the load
SELECT
  CUSTOMER_ID                                         AS id,             -- one-to-one
  split_part(FULL_NAME, ' ', 1)                       AS first_name,     -- split
  nullif(substring(FULL_NAME from position(' ' in FULL_NAME) + 1), '')
                                                      AS last_name,      -- split
  lower(trim(EMAIL))                                  AS email_address,  -- rename + standardise
  ROAD || ', ' || CITY                                AS address,        -- merge
  CASE GNDR WHEN 'M' THEN 'male' WHEN 'F' THEN 'female'
            ELSE 'unknown' END                        AS gender,         -- lookup
  'legacy_crm'                                        AS source_system   -- constant
FROM staging.cust;

Data type mapping across databases

When moving between different engines (SQL Server, MySQL, Oracle, cloud databases), every column's type must be mapped explicitly (see the tables and the Type Translator in lesson 22). Tools can generate most of it:

The AWS Schema Conversion Tool (SCT) is a free, standalone application that automates heterogeneous migrations. It converts source schemas and code objects, such as procedures and views, into a form compatible with a target AWS database engine, and lets you define custom mapping rules where the platforms differ. It is now also available as DMS Schema Conversion inside AWS DMS.

Source DB ──▶ AWS SCT assessment ──▶ automatic conversion ──▶ manual edits (if needed) ──▶ Target DB
               (what converts,         (schema + code        (the items the
                what doesn't)           transformed)          report flagged)

Visual mapping tools

Azure Data Factory's mapping data flows are visually designed transformation jobs. Engineers build scalable ETL logic without writing code, and ADF runs it on managed Apache Spark clusters behind the scenes. Similar visual mappers exist in Informatica, Talend, SSIS and AWS Glue Studio.

Mapping best practices

• Map every source column, including those you drop, and mark dropped ones explicitly with the reason and the approver.
• Map every target column, including new columns with no source (defaults or derived values).
• Write rules precise enough to code from: "split on first space", not "split name".
• Record edge cases: single-word names, null cities, codes outside the lookup.
• Attach a validation rule to each row and test it (lesson 12 profiling helps choose them).
• Version the document and get business sign-off; later changes go through change control.

Key takeaways

• Data mapping matches source fields to target fields, and it is the blueprint for all ETL code.
• A mapping document holds source table and column, target table and column, plus transformation rule, data types and validation rule.
• Mapping types: one-to-one, split, merge, lookup, derived, conditional, default, and data type mapping.
• Tools such as AWS SCT and Azure Data Factory mapping data flows automate much of the work, but the rules still need human sign-off.

Next: 27. Designing Data Pipelines.