Section 10.1: Integration Patterns & Technologies

Data Integration and Interoperability (DII) Fundamentals

According to the DAMA DMBoK2, Data Integration and Interoperability (DII) encompasses the processes, technologies, and architectures required to move, consolidate, and share data across applications, databases, and organizations. The primary objective is to maintain a consistent state of data throughout the enterprise, enabling business processes to run on accurate, unified information. While data integration focuses on combining data from multiple sources into a single, cohesive datastore (such as a data warehouse or data lake), data interoperability emphasizes the capability of distinct systems to communicate, exchange data, and interpret that data in real-time, often using standardized services and protocols.

Without an effective DII strategy, enterprises suffer from data fragmentation, high application maintenance costs, and poor decision-making due to inconsistent data views. Achieving high interoperability requires organizations to move beyond ad-hoc, point-to-point connections toward structured, repeatable architectural patterns.

Batch Integration: ETL vs. ELT

Batch integration remains a core pattern for bulk data movement. The two dominant paradigms are Extract, Transform, Load (ETL) and Extract, Load, Transform (ELT). The choice between them impacts database performance, network bandwidth, and the design of the target storage system.

• Extract, Transform, Load (ETL): In a traditional ETL flow, data is extracted from source systems, moved to a dedicated middle-tier staging server (or ETL engine), transformed, and finally loaded into the target data warehouse. The transformation phase includes cleansing, validation, reformatting, denormalization, and applying business logic. Because transformations occur prior to loading, only clean, structured data enters the target database. This minimizes the compute overhead on the target system and prevents dirty or sensitive data from being stored in the data warehouse. However, ETL requires a specialized middleware engine and can create performance bottlenecks during the transformation phase if memory or disk limits are reached on the middle-tier server.
• Extract, Load, Transform (ELT): With the rise of highly scalable, cloud-based data storage and query platforms, ELT has become the preferred choice for modern architectures. In ELT, data is extracted from sources and loaded directly into the target database in its raw format. The transformation of the data is then executed inside the target system using its own compute engine, typically via SQL queries or push-down optimization. ELT offers faster load times because there is no intermediary staging server, and it provides higher flexibility as data analysts can access raw history to create new views on demand. However, it requires highly performant target engines and can lead to increased cloud compute costs if transformations are inefficient.

Real-Time Integration: Change Data Capture (CDC)

Change Data Capture (CDC) is a critical technology for keeping databases in sync with minimal impact on operational sources. Instead of extracting entire tables periodically, CDC tracks and extracts only the data that has changed (INSERTs, UPDATEs, DELETEs) since the last extraction. DAMA identifies three main methods of CDC:

1. Log-Based CDC: This is the gold standard for database integration. Database engines write every transaction to a binary transaction log (e.g., Oracle Redo log, SQL Server transaction log) for recovery purposes. Log-based CDC software reads these logs directly, extracting changes asynchronously without querying the active database tables. It has virtually zero performance overhead on the source database, does not require schema changes, and captures hard deletes. However, it requires database-specific log readers and elevated admin privileges.
2. Trigger-Based CDC: This method utilizes database triggers that automatically fire on write operations, inserting the change details into a dedicated shadow/audit table. The integration tool then pulls data from the shadow table. While trigger-based CDC is database-independent and easy to configure, it incurs a significant performance penalty on source transactional systems because triggers execute synchronously as part of the primary write transaction.
3. Query-Based (Timestamp/Status) CDC: In this approach, the integration engine queries source tables looking for records where last_updated_timestamp is greater than the previous extraction run. This method is simple and requires no database-level configuration, but it places a heavy query load on the source database, fails to capture hard deletes (where records are physically deleted from the table), and relies on developers consistently updating the timestamp column on every write operation.

Middleware Patterns: ESB and Message Queuing/Pub-Sub

For application-to-application (A2A) integration, middleware technologies facilitate communication and message routing between disparate systems.

• Enterprise Service Bus (ESB): An ESB is a centralized, service-oriented middleware architecture that enables applications to communicate through a shared bus. It acts as a router, translator, and orchestrator. For example, if Application A sends a message in XML format via SOAP, the ESB can translate it to JSON and route it to Application B via REST. The ESB enforces routing rules, handles security, and coordinates multi-step business transactions. While powerful, ESBs can become monolithic single points of failure and performance bottlenecks if overloaded with complex business logic.
• Message Queuing and Publish-Subscribe (Pub-Sub): To decouple systems, organizations utilize message-oriented middleware. In a point-to-point message queuing model, a sender places a message in a specific queue, and a single receiver consumes it. In contrast, the Publish-Subscribe (Pub-Sub) pattern decouples publishers and subscribers through topics. A publisher sends a message to a topic, and the message broker broadcasts it to all subscribers registered to that topic. This allows asynchronous, event-driven data integration where systems do not need to wait for a response, increasing overall system resilience and horizontal scalability.