Building a Data Integration Team

Chapter 2: Key Terms (20 pages)



Integration developed from a combination of both technical and business mindsets and consequently has several terms that require some understanding. This section is designed to clarify what these terms mean and why they matter in the integration process.

Some of the terms to be discussed include

1)      Metadata

2)      Source

3)      Target

4)      Mapping

5)      Extractions, Transformation, and Loading (ETL)

Chapter 3: Team Qualifications (20 pages)

The integration team consists of individuals who are technical engineers, business-focused analysts, great communicators, and experienced coders.  While some teams are quite small (I've seen those with two people or less), others can be much larger (a typical size of 5-9 could be the norm). This chapter discusses the roles and responsibilities required to create a great integration team.

1)      Data Integration Architect

2)      Developer

3)      Release Manager4)      Project Manager



5)      Stakeholders

Chapter 4: Finding your Purpose: Project Deliverables (30 pages)

With a team structure firmly in mind, the next step is to determine what type of functional, business, and technical requirements should be captured and documented. Data integrations tend to be very fluid, and often, mappings can change multiple times before the business provides final signoff. If large datasets reveal inexplicable errors, developers must quickly research, code, and communicate workarounds to stakeholders. The best way to do this is through a combination of good business requirements, an understanding of who can support change requests, and the right documents to communicate the integration approach.

1)      The purpose of Business Requirements

2)      A good communication plan: Knowing your Points of Contact

3)      Primary documents

a.       High-Level Design Document

b.      Source to Target Mapping

c.       Sequence Diagram

d.      Architecture Diagram

e.      ETL Screenshots

Chapter 5: ETL (35 pages)

Depending on the type of integration required, developers and architects need to have experience with database services, cloud platforms, XML, and underlying communication protocols like REST and SOAP.  Tying these technologies together requires the right ETL tool. This chapter discusses how ETL typically operates and includes a sample script to demonstrate the steps to build a simple service. We then go through the current ETL software leaders in the marketplace ranging from the least amount of experience (but with little flexibility) to the more advanced (but with plenty of bells and whistles). The key here is to help establish the right tool for the right job without having to learn a series of new technologies overnight.

1)      An introduction to ETL

a.       Extraction (Connectors, Part 1)

b.      Transformation

c.       Load (Connectors, Part 2)

2)      Sample exercise

3)      Popular Softwarea.       Jitterbit



b.      Talend

c.       Dell Boomi

d.      Pentaho

e.      SQL Server (SSIS)

f.        MuleSoft

g.       Informatica

h.      Scripting languages (Python, Perl)

Chapter 6: Platform Automation (20 pages)

One of the hallmarks of a good integration design is that it should be repeatable. The initial load scripts should be tested rigourously on Develoment and Test environments, and daily processes should run continuously on Production Environments, changing where needed to fit the growing needs of the business. Much of this approach relates directly to a DevOps model and bears discussing as it relates to data integration.

1)      Environment Builds

2)      Running your pipeline through orchestrations

3)      File Version Control

4)      Testing

a.       Back-End

b.      Front-End

5)      Deploying through release management

Chapter 7: Monitoring Results (25 pages)

The design is complete, the business is satisfied with the requirements, and the integration has gone live.  The only thing left to do is to start monitoring the results of the integration. In this chapter, we implement a PDCA (Plan-Do-Check-Act cycle) to improve the integration output, providing daily success and error counts to users through emails and other notification channels. We discuss ways to identify Type I and Type II errors and making sure the owners of the data systems know how to resolve their issues once discovered. We end with a brief discussion on using the integration as a feeder into business intelligence, potentially using predictive analytics to discover gaps in the data that can lead to additional integration projects.

1)      The Continous Improvement Model

2)      Identifying Successes and Errors

3)      Alerting Teams Through Different Notification Channels4)      Using Analytics to generate future integrations



Chapter 8: Building Outward: Marketing to the Enterprise (20 pages)

By this point, the integration team should have deployed at least a few projects and have earned accolades for their successful accomplishments. This celebration is no time to rest on their laurels, however. Before the excitement dies down, teams should reach out to departments across the enterprise, identifying future projects that make the best candidates for data integration.  Part of this approach will involve advertising to make external teams aware of the work the integration team has done, performing educational activities such as Lunch-and-Learns, and become involved with building an Integration Center of Excellence (I will discuss this in some length). Understanding the goals of the enterprise for the upcoming financial year would also have value; with some research and creativity, the team can construct an integration data roadmap. This visual summary, similar to a product roadmap, maps out the vision and direction of integration offerings, which departments they serve, and the estimated time it would take to complete.

1)      Advertising

2)      Education

3)      Building an Integration Center of Excellence

4)      Data Roadmaps

Appendix A: Sample Templates

There will be some follow-up in the Appendix including the code and sample templates (which are also stored in GitHub).  I'd also like to include some additional weblinks to the ETL software (we might want to move that section over to the Appendix if it doesn't fit with the rest of the book).