Building Data Integration Solutions (for True Epub) (Jay Borthen)（Z-Library）

Building Data Integration Solutions Unifying Data for Enhanced Decision Making Jay Borthen OceanofPDF.com

Building Data Integration Solutions by Jay Borthen Copyright © 2026 Jay Borthen. All rights reserved. Published by O’Reilly Media, Inc., 141 Stony Circle, Suite 195, Santa Rosa, CA 95401. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (https://oreilly.com). For more information, contact our corporate/institutional sales department: 800-998-9938 or corporate@oreilly.com. Acquisitions Editor: Andy Kwan Development Editor: Jeff Bleiel Production Editor: Elizabeth Faerm Copyeditor: Vanessa Moore Proofreader: Arthur Johnson Indexer: nSight, Inc. Cover Designer: Susan Brown Cover Illustrator: José Marzan Jr. Interior Designer: David Futato Interior Illustrator: Kate Dullea November 2025: First Edition

Revision History for the First Edition 2025-10-28: First Release See http://oreilly.com/catalog/errata.csp?isbn=9781098173067 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. Building Data Integration Solutions, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. The views expressed in this work are those of the author, and do not represent the publisher’s views. While the publisher and the author have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the author disclaim all responsibility for errors or omissions, including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the information and instructions contained in this work is at your own risk. If any code samples or other technology this work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use thereof complies with such licenses and/or rights. 978-1-098-17306-7 [LSI] OceanofPDF.com

Preface This book presents a pragmatic, hands-on approach to data integration by first baselining the reader’s knowledge with important terminology and concepts and eventually walking through the building of a plausible, real- life data integration solution, step by step. For the hands-on parts in the later chapters of the book, familiarity with Linux,1 Python, Structured Query Language (SQL), and Amazon Web Services (AWS) would be beneficial, but I’ll attempt to explain what takes place at each step in simple terms. The combinations of tools and techniques that are described in this book are almost surely not “the best” for your specific use case. There are far too many variables and trade-offs to consider for an adequate presentation of all possible solutions. However, many of the technologies that are discussed are considered dominant players by some of the leading advisory and consultancy firms and have a significant presence within the US federal government. In this book, I prioritize the tools and technologies that meet current government mandates such as HIPAA and FedRAMP (see “Security and Compliance” for a more in-depth discussion regarding government regulations). It should be noted that containerization is not utilized in the hands-on sections of this book. However, it may be prudent for the practitioner to use containers (e.g., Docker) and perhaps even a distributed container management tool like Kubernetes for large, enterprise data integration initiatives. Also, while the book focuses on aspects directly related to data integration, because of inherent complexity within data engineering and data management, topic tangents and parallel subject matter discussions are inevitable. There are also many concepts included in the book that live within the blurred lines between data engineering and software development.

Further, my intention is not to delve too deeply into any single concept but rather to brush the surface enough to understand where the concepts are applicable within a data integration solution and to assist with the hands-on integrations in later chapters.

Overview of the Book Structure and What Readers Can Expect to Learn In the first part of the book, readers will explore the foundational principles of data integration and its role in modern data management. I explain the importance of data integration methods in unifying and organizing diverse data sources to ensure data accuracy, accessibility, and consistency. Part I, “Foundations of Data Integration”, focuses on key concepts and processes involved in data integration and its connection to related subjects such as data analytics and data governance. It also covers essential topics and terminology helpful for data engineers including data properties, structures, types, and encodings. It emphasizes the classification of data into structured, unstructured, and semistructured categories and the significance of understanding these classifications for effective implementation of data integration solutions. The second half of Part I delves into the challenges and limitations of data integration, particularly the difficulties in incorporating data from legacy systems and adapting to diverse, rapidly changing data sources. It also examines organizational issues, including the impact of policies and data governance and management practices. Part I lays a solid foundation for understanding the critical aspects of data integration, setting the stage for exploring tools, technologies, and practical implementation strategies in subsequent parts of the book. Part II, “Tools, Technologies, and Frameworks”, gives an in-depth examination of various data integration software tools and technologies. It starts by describing many of the options available in regards to the different tools and technologies, such as open source versus commercial tools.

NOTE There is also a hardware aspect to data integration, but that is not the focus of this book. Open source solutions are praised for being cost-effective, flexible, and supported by active communities, making them ideal for organizations with skilled technical staff and limited budgets. Conversely, commercial tools are noted for their user-friendliness, dedicated customer support, and advanced features such as low-code and no-code interfaces, as well as typically strong security measures, which help minimize operational risks and ensure compliance. The section then delves into the growing popularity of low-code and no- code platforms. These platforms enable nontechnical users to perform data integration tasks through intuitive interfaces and prebuilt connectors, significantly speeding up integration processes and reducing reliance on technical teams. The book outlines how these platforms can democratize data access and simplify complex data workflows, making data integration more accessible to a broader range of users within organizations. A detailed comparison of cloud and on-premises integration solutions follows, discussing their respective advantages and drawbacks. Cloud integration solutions are recognized for their scalability, flexibility, and cost-effectiveness, but they also raise concerns regarding data security, compliance, and vendor lock-in. On-premises solutions, while offering enhanced control and compliance, are criticized for being less scalable and more expensive to maintain. The book further explores the capabilities of major cloud service providers such as AWS and Microsoft Azure. It evaluates their unique strengths and ideal use cases, such as the extensive ecosystem and global reach offered by AWS, and Azure’s seamless integration with Microsoft products. This comparative analysis helps readers understand which cloud services might best suit their specific data integration needs.

Part II provides readers with a balanced understanding of various data integration tools and technologies, along with their benefits, limitations, and best use cases. By exploring both traditional and modern solutions, the section equips readers with the knowledge to make informed decisions on the right tools and platforms for their organizational requirements. This knowledge is invaluable for those looking to navigate the complex landscape of data integration technology. Part III, “Introducing the Example Data Integration Solution”, presents a comprehensive guide to setting up a data integration solution, providing a practical example infrastructure that highlights key components, configurations, and tools necessary for seamless dataflow across various systems. It starts with the foundational elements of infrastructure setup for data integration projects, including database selection, cloud services, and network considerations, emphasizing scalability and maintainability. The section introduces publicly available datasets from the International Energy Agency (IEA) and the US Energy Information Administration (EIA). These datasets are used in the example integration solution. The architecture employs an Amazon EC2 instance, hosting Qlik Replicate, and showcases a hybrid deployment using both Linux and Windows Server environments. Security is mentioned as an essential aspect, though the primary focus remains on the technical components and configuration of data integration technologies. The part delves into practical implementation with AWS, Confluent Kafka, Databricks, and Qlik. It provides step-by-step instructions for setting up Qlik tools alongside an integration of Databricks for unified data analytics. Confluent Kafka is introduced for streamlining event-driven data pipelines, with examples of configurations on Ubuntu Linux. This part serves as a practical resource for setting up a robust, scalable data integration infrastructure, enabling organizations to unify and optimize their data pipelines for better analytics and decision making. It balances technical detail with actionable insights, offering a blueprint adaptable to various organizational needs.

Conventions Used in This Book The following typographical conventions are used in this book: Italic Indicates new terms, URLs, email addresses, filenames, and file extensions. Constant width Used for program listings, as well as within paragraphs to refer to program elements such as variable or function names, databases, data types, environment variables, statements, and keywords. Constant width bold Shows commands or other text that should be typed literally by the user. Constant width italic Shows text that should be replaced with user-supplied values or by values determined by context. TIP This element signifies a tip or suggestion. NOTE This element signifies a general note. WARNING This element indicates a warning or caution.

Using Code Examples Supplemental material (code examples, exercises, etc.) is available for download at https://github.com/jlb226/building_data_integration_solutions. If you have a technical question or a problem using the code examples, please send email to support@oreilly.com. This book is here to help you get your job done. In general, if example code is offered with this book, you may use it in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission. We appreciate, but generally do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Building Data Integration Solutions by Jay Borthen (O’Reilly). Copyright 2026 Jay Borthen, 978-1-098-17306-7.” If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at permissions@oreilly.com. O’Reilly Online Learning NOTE For more than 40 years, O’Reilly Media has provided technology and business training, knowledge, and insight to help companies succeed.

Our unique network of experts and innovators share their knowledge and expertise through books, articles, and our online learning platform. O’Reilly’s online learning platform gives you on-demand access to live training courses, in-depth learning paths, interactive coding environments, and a vast collection of text and video from O’Reilly and 200+ other publishers. For more information, visit https://oreilly.com. How to Contact Us Please address comments and questions concerning this book to the publisher: O’Reilly Media, Inc. 141 Stony Circle, Suite 195 Santa Rosa, CA 95401 800-889-8969 (in the United States or Canada) 707-827-7019 (international or local) 707-829-0104 (fax) support@oreilly.com https://oreilly.com/about/contact.html We have a web page for this book, where we list errata and any additional information. You can access this page at https://oreil.ly/building-data- integration-solutions. For news and information about our books and courses, visit https://oreilly.com. Find us on LinkedIn: https://linkedin.com/company/oreilly-media.

Watch us on YouTube: https://youtube.com/oreillymedia. Acknowledgments I owe a special debt to Sean Applegate, Bharath Chandra Memmadisetty, Aarohi Tripathi, and Mark Zalubus for their clear-eyed reviews, technical depth, and practical advice. To Cara, my wife—thank you for the patience and perspective that made the writing possible. My sincere thanks to the O’Reilly editorial and production team for their guidance from concept to copyedit. Your clarity, rigor, and craftsmanship raised the quality of this book at every step. 1 O’Reilly Media has some great resources to explore Linux, particularly its Linux Sandbox. OceanofPDF.com

Part I. Foundations of Data Integration Chapter 1, “Introduction to Data Integration”, delves into the fundamentals of data integration, exploring its critical functions within the data life cycle and its alignment with broader organizational goals. By unifying and organizing diverse data sources, data integration ensures that data is accurate, accessible, and consistent, ultimately enhancing decision-making processes. The chapter outlines key concepts and processes involved in data integration and illustrates its importance in transforming raw data into valuable insights and driving business efficiency. Additionally, it provides an overview of related fields, such as data analytics and data governance, and emphasizes the interconnected nature of these disciplines within a robust data management framework. Chapter 2, “Key Concepts in Data Integration”, introduces key concepts of data integration that form the foundation of effective data management strategies and encompasses a variety of terms and practices essential for data engineers. The chapter highlights the importance of understanding and correctly applying terms related to data properties, data structures, data types, and encodings. The chapter covers the classification of data into structured, unstructured, and semistructured categories and explains their unique characteristics and relevance in the data ecosystem. Additionally, Chapter 2 covers data file formats, metadata, and the context of data usage and how these elements play critical roles in data integration processes. Establishing a clear understanding of these fundamental concepts will help data engineers better architect and implement durable data integration solutions tailored to organizational needs.

Chapter 3, “Data Integration Challenges”, addresses the common obstacles and limitations that organizations face when attempting to integrate data from multiple sources. We will examine technical, data, and organizational challenges and provide insights into why data integration can be a complex and resource-intensive process. The chapter explores strategies for overcoming these barriers to ensure successful data integration projects. The chapter also addresses the technical complexities associated with various data formats, protocols, and standards alongside the challenges posed by ensuring data quality, consistency, and scalability. By identifying and understanding these challenges, organizations can better navigate the intricate landscape of data integration to maximize the value derived from their data assets. Chapter 4, “Models, Architectures, Methods, and Patterns”, aims to clarify data integration concepts by exploring foundational elements including models, architectures, methods, and patterns. Each of these components plays a crucial role in shaping how data flows between systems and how efficiency, consistency, and scalability are maintained. Together, the chapters in Part I of this book provide a solid foundation for understanding important aspects of data integration and set the stage for exploring the tools, technologies, frameworks, and practical implementation strategies covered in Parts II and III. OceanofPDF.com

Chapter 1. Introduction to Data Integration This chapter provides an overview of what data integration actually is, what role it plays in the overall data life cycle, and how it relates to an organization’s data strategy. It aims to equip you with the basic understanding necessary to effectively implement a data integration solution and align the solution to broader organizational objectives. Data Integration and Data Management Data life cycle management encompasses all the disciplines related to obtaining and maintaining value from data. Effective data management ensures that data is accurate, available, and accessible and is a primary component in the decision-making process. You may have heard the term DataOps, which is a style of data management that focuses on collaboration between stakeholders throughout the data life cycle, much the same way that DevOps is centered around collaboration between software development teams. DataOps emphasizes automation, quality, and continuous delivery in data processes, similar to DevOps in software development. I prefer to partition the management of the data life cycle into three segments. As you can see in Figure 1-1, the segments include data integration, data analytics, and data governance. Each has distinct objectives and consists of lower-level processes that combine to form data pipelines.1 The lower-level processes sometime live in the gray area between the components. Data analytics and data governance are no less important than data integration is to the overall data life cycle. Let’s begin with brief descriptions of data analytics and data governance.

Figure 1-1. Components of data life cycle management Data analytics consists of all the tasks you would expect a typical data scientist or data analyst to perform, from creating data visualizations2 to developing machine learning (ML) models. You could consider analytics to be the frontend of data management. It is the component of data life cycle management that is typically most familiar to the end users and decision makers. NOTE Like a web browser is to the internet, data analytics (and, in particular, data visualization) is to data life cycle management. Data governance, on the other hand, refers to the policies, standards, and practices that ensure data is handled properly throughout the data life cycle while simultaneously aligning with organizational objectives and risk management strategies. It involves all activities related to enforcing data integrity, privacy, compliance with regulations, and maintaining authority and control over the management of data assets. For example, the US Department of Defense (DOD) published seven data governance goals they

must achieve to become data-centric. The data must be visible, accessible, understandable, linked, trustworthy, interoperable, and secure. These goals are collectively known as VAULTIS, and, although they do not speak to specific laws or regulations, they are designed to ensure that the data is suitable to literally, and figuratively, represent the DOD. NOTE In the parlance of data, life cycle does not necessarily imply a cycle. Defining Data Integration Digital data exists in the cloud (i.e., in remote datacenters), in on-premises computing environments, in external hard drives, on thumb drives, and in edge devices that can include active and passive sensors, smart phones, and other systems that interface with the real world. Data integration is the process of unifying and organizing the data and its infrastructure. It involves activities related to discovering, profiling, collecting, consolidating, combining, cleaning, sorting, moving, migrating, replicating, masking, filtering, mapping, syncing, and automating data.3 Data integration solutions are typically created using a combination of software tools that often include data quality management capabilities and data governance features. The best integration tools will also automate many of the processes involved and will help identify and connect data sources, map data elements between source and target systems, and implement transformation rules to ensure consistency and accuracy. We’ll go over many of the specific tools in Part II. A typical data integration process involves several key steps to ensure data from various sources is accurately and effectively combined for analysis and reporting. We’ll cover many of these steps in detail in later chapters, but it starts with identifying the data sources such as databases, spreadsheets, cloud services, and APIs. Then, data extraction is performed to retrieve data

from the identified sources, often using a variety of tools and methods. Data mapping then occurs where schemas are created to align data elements from different systems and ensure consistency in field names and structures. To check for errors and inconsistencies, data validation and quality assurance are carried out. In the data transformation phase, extracted data is converted into a common format via conditioning, enriching, and normalizing. After transformation, the data loading step involves transferring the converted data into a data store, like a data warehouse, or using the data immediately for further processing or analysis, either in batches or in streams. To keep the integrated data current, data synchronization is employed that periodically, or in near real time, updates a dataset as the data becomes available. Ensuring compliance with regulations and safeguarding data integrity and security then becomes paramount, especially when handling sensitive data.4 The discoverability and usability of integrated data is enhanced by metadata that provides contextual information about its source and meaning. These integrated datasets are made available and accessible for analysis using BI and analytics tools to derive insights that inform decision making and business strategies. In summary, data integration involves a combination of technical processes, tools, and strategies to ensure that the data from potentially diverse sources is harmonized, accurate, and available for meaningful analysis and decision making. We’ll delve further into these processes in the coming chapters. Why Data Integration Is Important The primary factors that drive the need for data integration solutions include: Significant growth of the volume of data Increasingly diverse types of data

Proliferating expectation for near-real-time5 processing Increasing adoption of cloud services Changing regulatory environments Continuous evolution of technologies NOTE According to Precedence Research, the “global data integration market size was evaluated at USD 13.6 billion in 2023 and is expected to hit around USD 43.38 billion by 2033, growing at a CAGR of 12.32%.” I don’t know what percentage of that is related to government procurement, but from what I continue to witness in my current role (as head of Data Science and Engineering), the number of government agency requests (RFIs, RFPs, etc.) related to data integration, as a rough percentage of overall requests, continues to increase. Given the current generative AI craze that relies on huge training sets of data, I would imagine the need for effective data integration solutions won’t be going anywhere any time soon. As Martin Kleppmann states in Designing Data-Intensive Applications (O’Reilly), the need for data integration “often only becomes apparent if you zoom out and consider the dataflows across an entire organization.” This has been occurring in the federal government as an increasing number of senior officials embrace a data-centric culture. And with the establishment of the Federal CDO Council and the DOD’s Chief Digital and Artificial Intelligence Office (CDAO), the US government has been making a concerted effort to become data driven. Some of the main benefits of effective data integration include: Increased data utilization Better data quality and integrity Increased consistency, accuracy, and trust Reduced number of data silos and increased collaborative decision making