Source to target mapping – definition

Before we dive into the process of source to target mapping, let’s look at its definition. Simply put, it’s:

This mapping is typically performed when data needs to be transformed from one system or format to another.

As such, source to target mapping involves identifying the source systems or databases, understanding their structure and content, and determining how they map or transform to the target systems or databases.

Source to target mapping – the process

Source to Target Mapping process is composed of different steps. Let’s look at them in more detail:

1. Getting ready

Before you start your source to target mapping process, you need to cover the following aspects:

• Understanding of the domain, naming and processes in the old and new system. A good idea is to create a template of how the mapping is going to look like.
• Understanding of the structure of the source and target database/system. A great help will be ERD diagrams, database schema, system access and database access.
• Determining whether all data from the source system should be included in the target system, or maybe only data the target system needs.
• Checking how many source systems are there.
• Checking whether the nomenclature in the old and new systems is the same or different.
• Checking whether data between the systems duplicates, complements each other, or is independent of each other.
• Defining data transformation rules or logic that needs to be applied.
• Establishing what mapping objects you have and making sure that for each object there must be a separate sheet in Excel or a separate file. The objects can include customers, suppliers, products, etc.

2. Mapping process

For the actual mapping process, you need the following:

• Excel for mapping, prepared so that it contains:
  • a changelog to describe changes,
  • a notes tab,
  • a mapping tab separate for each object – very often the source is an export of a schema from a database,
  • a transformations tab – if the required transformations are more complex, it is worth describing them in a separate sheet;
  • depending on whether you need to migrate everything or only the things required by the new system, you need to look for fields corresponding with the expected ones and mark them in Excel.

3. Other aspects to consider

When working on source to target mapping it is also important to remember about the following:

• Data filtering,
• Transformation: if you have, for example, product categories, it is necessary to mark which ones from the source correspond to those from the target,
• Data types,
• The number of characters in the fields – it’s fine if the new system has a field length greater than the old one. Much worse is the scenario in which the field length in the new system equals, for example, 8, and in the old system 16. In this case, you must either modify the new system to the desired length of characters or think about how to write the same information in fewer characters.
• A great idea is to add notes that will later allow people using this documentation to better understand the context.

In all those steps, creativity is key, as not all aspects you will encounter are repetitive and very often you will need to find solution to some less obvious challenges.

Check out related articles on the different stages of data transformation:

• What is data discovery & why it matters?
• What is data strategy? Framework, components and best practices
• Data modelling: a guide to techniques, models and best practices
• Data reporting guide: how to create effective data reports?

4. After mapping

When the mapping is already done, or is being done, it’s also worth considering the following steps:

Documentation verification

It is worth to verify documentation of source to target mappings, i.e. use it to perform real mappings. On this basis, you can introduce changes to the mappings and to the process of creating them.

Maintenance of mappings

Modifications may be needed over time. It’s much easier if the structure on the left side in Excel will not change, as you can modify what is on the right side by supplementing the information in the changelog. You can also have version history enabled on the Excel file. If the structure on the left side of the document changes, it is better to create a new sheet in Excel and increment its version.

The source-to-destination mapping process is critical when moving data between different database systems. It’s important to remember that the source-to-target mapping process can vary depending on the specific project requirements, systems involved, and data complexity.

It’s also of paramount importance to involve relevant stakeholders and subject matter experts throughout the process to ensure accurate and meaningful mappings.

If you are at the beginning of your source to target mapping process and need experts that will talk you through it and advice on the best approach, do not hesitate to get in touch with our team. We will be happy to help you any time!