What is Data Mapping?

Data mapping is crucial to the success of many data processes. One misstep in data mapping can ripple throughout your organization, leading to replicated errors, and ultimately, to inaccurate analysis.

Nearly every enterprise will, at some point, move data between systems. And different systems store similar data in different ways. So to move and consolidate data for analysis or other tasks, a roadmap is needed to ensure the data gets to its destination accurately.

For processes like data integration, data migration, data warehouse automation, data synchronization, automated data extraction, or other data management projects, quality in data mapping will determine the quality of the data to be analyzed for insights.

Understanding data mapping for the modern enterprise

Data mapping is the process of matching fields from one database to another. It's the first step to facilitate data migration, data integration, and other data management tasks.

Before data can be analyzed for business insights, it must be homogenized in a way that makes it accessible to decision makers. Data now comes from many sources, and each source can define similar data points in different ways. For example, the state field in a source system may show Illinois as "Illinois," but the destination may store it as "IL."

Data mapping bridges the differences between two systems, or data models, so that when data is moved from a source, it is accurate and usable at the destination.

Data mapping has been a common business function for some time, but as the amount of data and sources increase, the process of data mapping has become more complex, requiring automated tools to make it feasible for large data sets.

Data mapping is the key to data management

Data mapping is an essential part of many data management processes. If not properly mapped, data may become corrupted as it moves to its destination. Quality in data mapping is key in getting the most out of your data in data migrations, integrations, transformations, and in populating a data warehouse.

Data migration

Data migration is the process of moving data from one system to another as a one-time event. Generally, this is data that doesn't change over time. After the migration, the destination is the new source of migrated data, and the original source is retired. Data mapping supports the migration process by mapping source fields to destination fields.

Data integration

Data integration is an ongoing process of regularly moving data from one system to another. The integration can be scheduled, such as quarterly or monthly, or can be triggered by an event. Data is stored and maintained at both the source and destination. Like data migration, data maps for integrations match source fields with destination fields.

Data transformation

Data transformation is the process of converting data from a source format to a destination format. This can include cleansing data by changing data types, deleting nulls or duplicates, aggregating data, enriching the data, or other transformations. For example, "Illinois" can be transformed to "IL" to match the destination format. These transformation formulas are part of the data map. As data is moved, the data map uses the transformation formulas to get the data in the correct format for analysis.

Data warehousing

If the goal is to pool data into one source for analysis or other tasks, it is generally pooled in a data warehouse. When you run a query, a report, or do analysis, the data comes from the warehouse. Data in the warehouse is already migrated, integrated, and transformed. Data mapping ensures that as data comes into the warehouse, it gets to its destination the way it was intended.

What are the steps of data mapping?

• Step 1: Define — Define the data to be moved, including the tables, the fields within each table, and the format of the field after it's moved. For data integrations, the frequency of data transfer is also defined.

• Step 2: Map the Data — Match source fields to destination fields.

• Step 3: Transformation — If a field requires transformation, the transformation formula or rule is coded.  

• Step 4: Test — Using a test system and sample data from the source, run the transfer to see how it works and make adjustments as necessary.

• Step 5: Deploy — Once it's determined that the data transformation is working as planned, schedule a migration or integration go-live event.

• Step 6: Maintain and Update — For ongoing data integration, the data map is a living entity that will require updates and changes as new data sources are added, as data sources change, or as requirements at the destination change.

How the right data mapping tool can help

Advanced cloud-based data mapping and transformation tools can help enterprises get more out of their data without stretching the budget. This data mapping example shows data fields being mapped from the source to a destination.

In the past, organizations documented data mappings on paper, which was sufficient at the time. But the landscape has become much more complex. With more data, more mappings, and constant changes, paper-based systems can't keep pace. They lack transparency and don't track the inevitable changes in the data models. Mapping by hand also means coding transformations by hand, which is time consuming and fraught with error.

Transparency for analysts and architects

Since data quality is important, data analysts and architects need a precise, real time view of the data at its source and destination. Data mapping tools provide a common view into the data structures being mapped so that analysts and architects can all see the data content, flow, and transformations.

Optimization of complex formats

With so much data streaming from diverse sources, data compatibility becomes a potential problem. Good data mapping tools streamline the transformation process by providing built-in tools to ensure the accurate transformation of complex formats, which saves time and reduces the possibility of human error.

Fewer challenges for changing data models

Data maps are not a one-and-done deal. Changes in data standards, reporting requirements, and systems mean that maps need maintenance. With a cloud-based data mapping tool, stakeholders no longer run the risk of losing documentation about changes. Good data mapping tools allow users to track the impact of changes as maps are updated. Data mapping tools also allow users to reuse maps, so you don't have to start from scratch each time.

What to look for in a data mapping tool

Cloud-based data mapping software tools are fast, flexible, and scalable, and are built to handle demanding mapping needs without stretching the budget. While the features and functionality of a data mapping tool is dependent on the organization's needs, there are some common must-haves to look for.

Wide format support

Most tools support basic file types such as Excel, delimited text files, XML, JSON, EBCDIC, and others. Look for a tool that handles common formats in your environment, such as SQL Server, Sybase, Oracle, DB2, or other formats. A good mapping tool will also handle enterprise software such as SAP, SAS, Marketo, Microsoft CRM, or SugarCRM, or data from cloud services such as Salesforce or Database.com.

Intuitive and automated

An intuitive, cloud-based tool is designed to automate repetitive tasks to save time, tedium, and the risk of human error. Look for drag and drop functionality that allows users to quickly match fields and apply built-in transformation, so no coding is required.

Workflow and scheduling

To round out automation capabilities, look for a tool that can create a complete mapping workflow with the ability to schedule mapping jobs triggered by the calendar or an event.

Enterprise data mapping for better data management

Data mapping is an essential part of ensuring that in the process of moving data from a source to a destination, data accuracy is maintained. Good data mapping ensures good data quality in the data warehouse.

You can leverage all the cloud has to offer and put more data to work with an end-to-end solution for data integration and management. From connecting the broadest set of data sources and platforms to intuitive self-service data access, Talend Data Fabric is a unified suite of apps that helps you manage all your enterprise data in one environment. Try Talend Data Fabric today.