Data Mapping
Data mapping establishes, at attribute level, the relationship between a source data repository and a target repository: which source attribute feeds which target attribute and under what transformation rule when the value does not pass across unchanged. It applies in two situations: migration, where the source data moves into a new repository, and integration, where it merges into an existing one. Its deliverable is the data mapping specification, a register that declares what is to happen to each piece of data; the extract, transform and load process carries it out.
Goal
Data mapping establishes the correspondence, attribute by attribute, between a source data repository and a target repository. A legacy system holds an amount in centimes in an integer where the platform replacing it expects francs in a decimal; it encodes a missing date as an impossible date where the target accepts a null. Each of these gaps calls for a decision.
For each target attribute, the data mapping specification records two things: whether the value arrives unchanged and, if not, which rule transforms it. It is the only place that carries them, since the load program applies the rule without stating it. The specification is the contract between the analyst and the team that will write the load. Later it becomes the trail for anyone who has to explain where a value came from.
The exercise has a second benefit, one the IIBA guide points out: it brings the quality defects of the source and of the target to the surface. Writing the rule for an attribute forces a look at what the field holds. This is where the never-validated field turns up, the abandoned coding scheme with three values still in the data, the column half the users fill in. The discovery costs little as long as it comes before the first load.
Usage
When to use it
- Data migration to a new system: no batch is shipped before the mapping is written and approved.
- Integration into an existing repository: merging portfolios, consolidation after an acquisition, feeding a shared data warehouse.
- Formats that differ on the two sides: types, lengths and coding schemes diverge.
- A target attribute with no direct equivalent: a calculation or a concatenation to define.
- Traceability required: a regulator or an auditor will ask where each value in the target system came from.
- Doubtful source quality: the exercise measures it field by field, before the defects reach production.
- The load handed to another team or to an external provider: the specification is the only form in which the business expectation reaches whoever writes the load.
When not to use it
- An identical schema on both sides: cloning an environment or restoring one, where a schema comparison is enough.
- Incompatible data models: the target model holds no place for what the source carries, so go back to data modelling.
Description
What the technique produces
The deliverable is a register, most often kept in a spreadsheet, with one row per target attribute. The IIBA guide sets its columns: target entity, target attribute, target data type, source attribute or attributes, direct map, transformation rule.
The direct-map column is the most abused in the register. It reads yes only when the value arrives unchanged, format and unit included. An amount that changes unit, a date that changes format, an identifier that gains separators are indirect mappings, however obvious the transformation. A yes ticked for convenience leaves the rule nowhere: neither in the specification nor in the head of whoever writes the load.
The source and the target
On the source side, the analysis covers three points: the format of the repository (delimited file, spreadsheet, database entity, service), the attributes of current or potential interest, then the type and size of each. The third point goes past what the schema declares: a column declared varchar(50) whose longest value runs to 18 characters and a column with the same declaration filled to the brim pose different problems.
On the target side, the analysis covers the attributes to create to receive the source, the type and size to give them, the source attributes to transform together with their rule and the fields to build by calculation, concatenation or formatting. The guide is explicit about sizing: the size of the target attribute is never smaller than the size of the source. Equality is the normal case; a larger size is a deliberate allowance for growth. A smaller size produces data loss: most loading tools truncate the value without warning. This rule holds for an attribute fed by a single source attribute. An attribute built by concatenation or by calculation is sized on the worst case of its inputs, measured on the real data.
Migration or integration
The guide separates two uses that do not demand the same work. Migration moves the source data into a new repository: the target is shaped in part to receive it, and the conflicts are confined to formats. Integration brings the source data into a repository that is already populated.
Integration first assumes that the data models on the two sides coincide, even where the schemas differ. Two different schemas describe the same reality in two ways, and a mapping reconciles them. Two different models describe two realities: if the source keeps one contract per person and the target one contract per household, no transformation rule closes the gap, and the question goes back to modelling.
It then requires an analysis of the attributes common to both repositories and of the cardinality of their relationship, one to one or zero to many in the guide's examples. Where several source records correspond to a single target record, someone has to name the one that wins, attribute by attribute, decide what becomes of the linked records and provide the table that will keep track of the old key. None of these three decisions follows from the data: they are taken with the business, in the specification.
Running the exercise
- Take stock of both repositories. Format, volume, schema, owner. Obtain the data dictionary on each side or draw one up for the attributes involved.
- List the target attributes to be fed, including those still to be created. This list is settled before anyone looks for the sources.
- Attach each target attribute to its source. One attribute, several attributes or none. The "none" case is legitimate and has to be written down: a constant value, a calculated value, a field left empty at migration and filled in later by the business.
- Compare type, size and value domain. Note every narrowing, every difference of coding, every divergent unit. This step produces most of the non-direct rows.
- Write the transformation rule. It has to be exact enough to be executed without a follow-up question and readable enough to be approved by the business owner of the data. Both requirements hold together when the rule names concrete values rather than an intention.
- Have the business approve the rules. Legacy codes, sentinel values, "miscellaneous" categories and historical exceptions are known to a handful of people, rarely documented and never deducible from the schema.
- Profile the source against the written rule. Count the values that do not conform to it, before the first load attempt. A rule that covers 99.4% of the records leaves a remainder whose treatment has to be decided now.
- Freeze a version, then hold it. The guide names the limitation: the specification has to be updated as soon as a change occurs on one side or the other.
The boundary with ETL, the dictionary and the flow diagram
Data mapping declares, ETL executes. The specification states the correspondence and the rule; the process applies them to the records, with its scheduling, its recovery from errors and its logs. The boundary shows in the deliverable: a document the business reads and approves on one side, a process that runs on the other. The guide places the production of audit trails for modified or replaced data in the load step of ETL, which puts the checks carried out after the load on the process side.
The data dictionary comes earlier. It fixes the name, the aliases, the meaning, the format and the permitted values of each element, and the guide names it as an aid to running a data mapping. The dictionary says what the field is; the specification says what it becomes. Without the first, the mapping is written on column names.
The data flow diagram answers another question: which process moves which data between which data stores, at which level of decomposition. It shows that the insured party's data goes from the legacy system to the target platform. The data mapping specification says what happens to the leaving date along the way.
What makes the exercise fail
The sentinel value carried across unchanged. A legacy system that does not accept a null encodes absence with an impossible value: a date of 31.12.2099, an amount of 0, a code of 999. Copied without a rule, the sentinel becomes plausible data. An employment contract ends in 2099 in the reports, a salary of zero enters a contribution calculation. It is spotted at the profiling stage, by looking at the most frequent values of each field.
The mapping written on the name of the column. Two fields named customer mean the household in one system and the payer in the other; two fields start_date mean the signature in one and the effective date in the other. Similarity of names is a hypothesis to check with the people who fill the field.
The rule that lives in the load code. A transformation decided in a meeting and built into the process, with no return to the register, stops being auditable the day its author moves to another engagement. Field-level traceability assumes the declaration exists somewhere other than in the code that applies it.
Cardinality discovered at load time. A rejection for a unique-constraint violation, the day before go-live, signals an integration analysis that was never done. Naming the record that wins is a management decision; taking it under pressure, on a migration night, amounts to leaving it to whoever is on duty.
The out-of-date specification. A migration runs for months, during which both systems keep moving. Without a checkpoint at every schema change, the register describes a past state and the load fails on columns that have shifted. Tying the specification to the change control of both systems costs little and has to be done at the start.
AI considerations
Integration tools propose candidate mappings from similarity of name, of type and of sampled content; a language model does the same on two lists of columns, recognising abbreviations and inconsistent naming conventions. On a schema of four hundred columns, this automatic matching takes the mechanical part of the work away and leaves the analyst with the rows where a judgement is needed.
Turning "date in dd.mm.yyyy format to ISO, value 31.12.2099 to null" into a SQL expression or into a tool transformation is repetitive code work, described end to end by its specification, whose first draft is quick to read over. Test data covering the edge cases of the rule is obtained the same way. Profiling belongs to the same use: describing an unknown field by its distinct values, its lengths, its null rate and its out-of-domain values can be asked for in plain language, and the result serves to check the written rule against the real content.
The matching rests on names and types, and the tool delivers its proposals with the same assurance whether they are right or wrong. What a field means in the organisation, how long it has been populated, which coding it carried before, is not in the schema. A mapping accepted without a business review produces one error per record. Grouping decisions are equally beyond the tool: choosing which branch of a merged employer becomes the reference record is a management judgement.
There remains the data itself. A production extract handed to an external provider "to help them understand the field" contains AHV numbers, salaries and dates of birth, therefore personal data within the meaning of the Federal Act on Data Protection (FADP). Disclosure to a third party is processing: it requires grounds for justification, information to the data subjects and, where the third party acts on behalf of the pension fund, a processing agreement. The schema on its own, or a masked extract, answers the same question.
Examples
A pension fund migrates its insured-party records from a legacy administration system to a new platform.
| Target entity | Target attribute | Target type | Source attribute(s) | Direct map | Transformation rule |
|---|---|---|---|---|---|
| insured | date_of_birth | date | PERSON.DateOfBirth (date) | yes | |
| insured | ahv_number | char(16) | PERSON.AhvNo (char 13) | no | Insert the separators in the format 756.XXXX.XXXX.XX. Reject the record if the check digit is wrong. |
| insured | full_name | varchar(80) | PERSON.FirstName (varchar 40), PERSON.LastName (varchar 40) | no | Concatenate FirstName, a space, LastName. Truncate to 80 characters and log the record concerned. |
| insured | annual_salary_chf | decimal(12,2) | SALARY.Annual_Salary (integer, centimes) | no | Divide by 100. Example: 8'400'000 becomes 84'000.00, that is CHF 84'000. |
| insured | employment_end_date | date, null allowed | EMPLOYMENT.LeavingDate (date) | no | The value 31.12.2099 means "still employed" and becomes null. Any other date is carried across unchanged. |
| insured | employer_uid | char(15) | EMPLOYER.EmployerNo (integer) | no | Resolve through the employer lookup table: the numbers of the branches of a merged company give a single UID in the format CHE-XXX.XXX.XXX. |
One row in six is direct. The other five each carry a different kind of rule: formatting, concatenation, change of unit, sentinel value, grouping. The third shows where the sizing rule reaches its limit: two sources of 40 characters and one space give 81 characters for a target of 80, so a truncation that is decided and logged. The last does not follow from any data in the legacy system: it rests on a lookup table the business builds from the history of employer mergers. That table is a deliverable of the data mapping as much as the register itself.
Visualisations
The specification belongs in a table: making an image of it would cost it selection, sorting and comparison from one version to the next. What can be drawn is the place of the technique among its neighbours: who defines, who declares, who executes.
A completed register is reviewed through its direct-map column. The rows marked "no" are taken first, and each is checked on two points: that the rule names concrete values rather than an intention and that the target size covers the source size. That leaves the rows with no source attribute, each of which has to carry a written decision, a constant value, a calculated value or a field left empty at migration. The path from a target attribute to its row in the register is three questions asked in order.
Cost
| Phase | Level | Rationale |
|---|---|---|
| Preparation | High | Obtain the schemas and the dictionary on both sides, profile the fields of the source, track down the people who know what the legacy codes hold. On an old system whose documentation has vanished, this item dominates all the others. |
| Execution | Medium | Direct mappings are handled in series and fast. The cost concentrates in the few dozen non-direct rows, each of which requires a judgement with the business owner and a check against the real content. |
| Documentation | High | The register is the deliverable, so the documentation is the work. It carries on after go-live, at every schema change, and the register remains the only piece that explains where a value comes from. |
Tooling
The spreadsheet is the original format of the technique and it is enough for most migrations: one sheet per target entity, one row per attribute, the six columns of the register. Its weaknesses show at scale, when several hundred rows change in parallel with no consistency check and no readable version history. Versioning it in the project repository, in a text format, fixes the second weakness cheaply.
Integration and ETL tools (Talend, Azure Data Factory, SQL Server Integration Services, Informatica, dbt) hold the mapping inside the very object that executes it, which removes the gap between the declared rule and the applied rule. The price is readability: a transformation expressed in the tool is hard for the business owner to approve, and their agreement is what the rule rests on. The practice that works is to keep a readable export of the mapping, generated from the tool, rather than a second document maintained by hand.
Data catalogues and lineage tools hold the mapping as reusable metadata, with the trace of each field's path from system to system. The DMBOK treats this traceability under metadata management. This option earns its place when several successive projects map the same systems.
Data profiling tools make visible in a few minutes the sentinels and the abandoned coding schemes that no reading of a schema reveals.
Sources
- IIBA, Guide to Business Data Analytics, §3.6 Data Mapping: the source for the two uses, migration and integration, for the work at attribute level, for the sizing rule of the target attribute, for the columns of the specification, as well as for the strengths and limitations of the technique.
- IIBA, Guide to Business Data Analytics, §3.10 Extract, Transform, and Load (ETL): the extract, transform and load steps, as well as the production of audit trails for modified or replaced data during the load.
- IIBA, Guide to Business Data Analytics, §3.4 Data Dictionary: the data dictionary as an aid to running a data mapping.
- DAMA International, DAMA-DMBOK: Data Management Body of Knowledge, 2nd edition, chapter 12 Metadata Management: data lineage, the origin of an element and the transformations it has undergone, as an object of metadata management.
- Federal Act on Data Protection (FADP), SR 235.1: art. 9 on processing by a processor, art. 19 on the duty to provide information and art. 31 on grounds for justification, cited for the disclosure of production extracts containing personal data.