An Executive’s Guide to the Data Integration Process

Data integration is the process of combining data from multiple, disparate sources into a data warehouse destination. It's a key part of the process of turning raw data into insights that drive better, faster decision-making.

Data integration and related terms

Data integration is a broad topic, with an abundance of related concepts, so it's easy to get confused. Let's begin by defining some terms.

Data integration and ETL

In the ETL process, an ETL tool extracts data from a source, loads it into physical or virtual storage, and transforms it as necessary for analysis. The sequence of these steps may vary, depending on the destination: on-premises or in the cloud.

ETL — extract, transform, load

ETL — extract, transform, load — is effective for integrating data to on-premises data centers. On-premises hardware has a limited ability to scale when processing demands spike, so it makes sense to do as much processing as possible in the data pipeline. Also, security concerns or regulatory requirements may make it impossible to store raw data in a destination, so the tool must transform the data before loading it to the destination.

The downside to ETL is that the transformation process can be resource-intensive, slowing the data integration process.

ELT — extract, load transform

When the destination is a cloud-native data warehouse like Amazon Redshift, Google BigQuery, Snowflake, or Microsoft Azure SQL Data Warehouse, ELT is a better approach. Organizations can transform their raw data at any time, when and as necessary for their use case, and not as a step in the data pipeline.

Data integration and business

Data integration was a $6.56 billion global industry in 2017, and that figure could reach $18.45 billion by 2025, according to the report "Global Data Integration Market – Size, Outlook, Trends and Forecasts (2019 – 2025)" by Envision Intelligence, a market research firm.

The data integration tools and technology are designed to turn raw data into insights. Business reports and dashboards tell decision-makers what's happening in their business, and also may shed light on why it's happening.

Data is a valuable enterprise resource, but its usefulness is limited unless the data is current, accurate, and available to everyone in the organization who would benefit from analyzing it.

Busting information silos

Enterprise data often is siloed in organizational departments: financial data stays in the finance department, and marketing data stays in the marketing department. Data integration busts information silos by making data available to more decision-makers, who can use BI tools to get insight into improving operations, improving products, saving money, and discovering hidden opportunities. By assembling and analyzing data from multiple sources through integration, business leaders can discover trends and patterns that might have been impossible to find before.

Handling the constant flow of data

Data on sales, expenses, and other business activities is in constant motion. Literally every second, transactional systems generate and store data, and organizations add new data sources all the time. Integration brings in fresh data for analysis as it's needed.

The data integration process

As you think about the data integration process, begin with a checklist to ensure that you don’t overlook any business or technical considerations. Here is an example of a basic checklist:

Stakeholders

• Who will lead the data integration project? Who else should be involved?
• Will the proposed process/solution be used by IT staff only, or will other business departments need access?
• Will roles be clearly defined?

Business goals and objectives

• What's the main reason for this data integration project? Are there any secondary reasons?
• How will you measure the success of this project?
• What specific outcomes do you seek?
• What's the expected timeline for the data integration project?

The existing process

• What processes, if any, are currently used for data integration?
• What hardware/software systems are in place?
• Are there any existing cloud-based data stores?
• In what ways will current processes/systems change when a new data integration process is in place?
• Will additional, fewer, or different employees be needed to ensure the success of the new process?

Identify the data

• Can you identify the source(s) of your data?
• What do you currently get from your data?
• What would stakeholders like to get from the data?

Evaluate data integration solutions

• Will you build your own, or purchase on-premises or cloud-based solution/tools?
• What is the cost of the proposed solution?
• Will the proposed data integration solution be able to scale to satisfy changing needs?
• Will you need additional hardware or software?
• How much maintenance will be required?
• Can your team use and maintain the data integration solution, or will it require additional hires or outside assistance?

Monitor/manage/maintain

• Have you established KPIs and metrics?
• Who will manage the new solution? Who will know when the solution needs to scale up or down?
• How often will maintenance be required? What sort of expertise is required for this?

This checklist is not exhaustive, and every organization will have unique things to consider. But using a similar checklist to begin the data integration process will help to get stakeholders on the same page, and may spur conversations and generate useful ideas.

Getting started with data integration

Building your own data integration solution may seem tempting if you're facing a smaller project with few data sources. In fact, however, creating an ETL platform from scratch requires no small amount of software development expertise, and adds another in-house application that needs ongoing maintenance.

It doesn’t have to be this way. Stitch is a cloud ETL service that provides connectors from more than 90 sources to the most popular data warehouse destinations. We make it easy to extract data from more than 90 sources and load it to an EDW in a flash. Our approach is simple, straightforward, and ready to go right out of the box. Give Stitch a try, on us — set up a free trial in minutes and get your data into an EDW today.