Enterprise Data Integration Architecture: Patterns, Platforms and Pipeline Design

The Cost of Fragmented Data: Why Integration Is the First Priority

A retail enterprise operates 14 source systems: ERP (SAP), CRM (Salesforce), e-commerce platform (Shopify Plus), POS system (Square), marketing automation (HubSpot), inventory management (NetSuite), warehouse management (Manhattan Associates), loyalty program (custom), HR system (Workday), finance (Oracle EPM), web analytics (GA4), support (Zendesk), supply chain (Blue Yonder), and a product information management system (Salsify). Each system has its own definition of "customer," "product," and "order." The marketing team's customer count is 2.3 million. The CRM shows 1.8 million. The loyalty program has 950,000. The finance team reports 1.4 million "active customers." Nobody is wrong — they're each counting different things from different systems with different definitions.

This is the fragmented data estate. Every analytical question that crosses system boundaries — "what's the lifetime value of customers acquired through digital channels?" — requires manual data reconciliation before analysis can begin. Analysts spend 60-70% of their time finding, joining, and reconciling data. The remaining 30-40% is the actual analysis. The data analytics investment can't deliver value because the integration layer that feeds it doesn't exist.

Data integration solves this by creating a unified data layer where "customer" means one thing, "order" has one definition, and analytical questions that cross system boundaries can be answered without 3 weeks of data reconciliation. The architecture patterns described here determine how data flows from 14 source systems into that unified layer — reliably, efficiently, and at the freshness each use case requires.

Five Integration Architecture Patterns

Enterprise data integration isn't one pattern — it's a portfolio of patterns, each suited to different data velocity, volume, and use case requirements. Most enterprises need 3-4 patterns operating simultaneously.

Pattern 1: Batch ETL/ELT — The Foundation

Batch integration — extracting data from source systems on a schedule, transforming it, and loading it into the analytical platform — is the foundation that 80% of enterprise analytics runs on. It's the right pattern when the analytical use case tolerates latency (daily financial reporting, weekly business reviews, monthly trend analysis) and data volumes are large enough to benefit from bulk processing.

ETL vs. ELT: The Architecture Decision

ETL (Extract-Transform-Load) transforms data before loading into the target. The transformation engine (Informatica, SSIS, Talend) processes data in a staging area, applies business rules, and loads clean data into the warehouse. Best when: the target warehouse has limited compute, transformations are complex, and data governance requires quality gates before data reaches the warehouse.

ELT (Extract-Load-Transform) loads raw data first, then transforms using the target platform's compute. Microsoft Fabric, Snowflake, and Databricks make ELT the default for cloud analytics — the platform's distributed compute handles transformations faster and cheaper than a separate ETL engine. Best when: the target platform has elastic compute, you want raw data preserved for flexibility, and transformations benefit from SQL-based processing.

The industry has largely shifted to ELT for new implementations. ETL remains relevant for legacy warehouse environments and scenarios where data must be transformed before it touches the analytical platform (regulatory requirements, PII masking before loading).

Pattern 2: API-Led Integration

API-led integration connects systems through well-defined service interfaces — request/response communication where one system calls another system's API to read or write data. This is the pattern for operational integration: the e-commerce platform checks inventory in real time, the CRM retrieves order history from the ERP, the customer portal shows support tickets from Zendesk.

Three-Layer API Architecture

System APIs expose source system data through standardized interfaces. Each source system gets a thin API layer that abstracts the underlying database or proprietary interface. The CRM system API returns customer data in a standard format regardless of whether the CRM is Salesforce, Dynamics, or HubSpot.

Process APIs orchestrate business logic across multiple system APIs. The "customer 360" process API calls the CRM system API for demographics, the ERP system API for transactions, and the support system API for tickets — composing a unified customer view that no single source system contains.

Experience APIs serve specific front-end needs — mobile app, web portal, partner portal. Each experience API consumes process APIs and shapes the response for its specific consumer. The mobile app gets a lightweight JSON payload. The internal dashboard gets a richer dataset. Same underlying data, different presentations.

MuleSoft, Azure API Management, AWS API Gateway, and Kong are the primary platforms. For enterprises already on the Microsoft stack, Azure API Management integrates natively with Azure services and enterprise integration patterns.

Pattern 3: Event-Driven Architecture

Event-driven integration communicates through events — "order placed," "customer updated," "inventory below threshold" — published to a message broker and consumed by any system that needs to react. Unlike API integration (point-to-point, synchronous), event-driven integration is publish-subscribe (one-to-many, asynchronous). A single "order placed" event can trigger inventory update, shipping notification, loyalty point accrual, and real-time analytics — without the order system knowing or caring about any of these downstream consumers.

Event Streaming Platforms

Apache Kafka is the industry standard for high-throughput event streaming. Billions of events per day, durable storage, exactly-once semantics, and a rich ecosystem of connectors. The trade-off: operational complexity. Kafka clusters require sizing, partition management, and monitoring.

Azure Event Hubs is the managed alternative for Azure-native organizations. Compatible with the Kafka protocol (existing Kafka producers/consumers work without code changes), fully managed (no cluster operations), and integrated with Azure Stream Analytics and Fabric Real-Time Intelligence for stream processing.

When to use event-driven: When multiple consumers need to react to the same business event. When systems must be decoupled (the producer shouldn't know or depend on consumers). When data pipelines need near-real-time freshness for streaming analytics or real-time dashboards.

Pattern 4: Change Data Capture (CDC)

CDC captures row-level changes (inserts, updates, deletes) from source databases and propagates them to target systems in near-real-time. Unlike batch extraction (which re-reads entire tables periodically), CDC captures only what changed since the last sync — reducing source system load, network bandwidth, and target processing time.

CDC Implementation Approaches

Log-based CDC reads the database's transaction log (binlog in MySQL, WAL in PostgreSQL, transaction log in SQL Server) to capture changes. This is the preferred approach because it imposes zero load on the source database — the log is already being written for database recovery purposes. CDC tools read it as a secondary consumer. Debezium (open-source), Azure Data Factory CDC, Fivetran, and Qlik Replicate implement log-based CDC.

Query-based CDC periodically queries the source table for rows where a "last modified" timestamp exceeds the previous sync point. Simpler to implement but has limitations: requires a reliable timestamp column on every table, can't detect deletes (no row to query), and imposes query load on the source database during each sync.

Trigger-based CDC uses database triggers to capture changes into a staging table. Captures all change types (insert, update, delete) but adds overhead to every write operation on the source table — which can degrade transactional performance for high-volume tables.

For enterprise data pipeline architecture, log-based CDC with Debezium (publishing to Kafka) or Azure Data Factory's native CDC connector is the standard recommendation. It provides near-real-time data freshness with zero source system impact — the combination every CDO wants.

Pattern 5: Hybrid — Combining Patterns for Enterprise Reality

No single integration pattern serves all enterprise needs. The realistic architecture combines patterns based on use case requirements:

The hybrid architecture matches integration pattern to use case requirement. Analytical workloads get batch (cost-effective, high-volume). Operational systems get APIs (low-latency, synchronous). Near-real-time analytics get CDC (low-impact, continuous). Cross-system coordination gets events (decoupled, reactive).

Platform Selection: Azure Data Factory, Fabric, MuleSoft, or Informatica

For Microsoft-stack enterprises, the recommended approach: Fabric Data Factory for batch ELT (unified with the analytical platform), Azure Event Hubs for event-driven patterns (managed Kafka-compatible), and Azure Data Factory CDC for near-real-time replication. This combination handles all four patterns from a single cloud ecosystem.

Pipeline Design Principles: Idempotency, Observability, Recovery

Integration pipelines run at scale — thousands of executions per day, millions of rows per run. At that scale, failures are not exceptions; they're expected operations that the pipeline must handle gracefully.

Idempotency: Running a pipeline twice on the same data produces the same result as running it once. No duplicates, no missing records, no corrupted state. Idempotent pipelines use merge/upsert patterns (not append-only), deduplicate on business keys, and maintain watermarks that track exactly which data has been processed.

Observability: Every pipeline execution produces telemetry — rows extracted, rows loaded, execution duration, data quality scores, and any anomalies detected. Dashboards show pipeline health. Alerts fire when pipelines fail, slow down, or process unexpected volumes. Without observability, pipeline failures are discovered when the Power BI dashboard shows stale data — which is the worst time to discover it.

Recovery: When a pipeline fails (and it will — source system outages, network issues, schema changes, data quality exceptions), what happens? The pipeline should log the failure with diagnostic detail, alert the operations team, and support replay from the point of failure — not require a full re-run. Data engineering best practices include checkpoint-based recovery, dead-letter queues for rejected records, and automated retry with exponential backoff.

Integration Roadmap: Decision-Driven, Not Source-Driven

The traditional integration approach starts with source systems: "let's integrate the ERP, then the CRM, then the marketing platform." This produces a data lake containing integrated data that nobody asked for — 200 tables from 14 systems, technically correct, analytically useless because nobody designed the integration for specific analytical questions.

The decision-driven approach starts with the analytical or operational use cases: which decisions need integrated data, which sources do those decisions require, and at what freshness? This produces integration that serves specific outcomes from day one.

Go Deeper

Continue building your understanding with these related resources from our consulting practice.