Data Integration & Pipelines

Data integration pipelines move and transform data between systems — on a schedule (batch) or continuously (streaming) — so downstream consumers get accurate, timely data.

At a glance

• What it is: The extract/transform/load (ETL) or extract/load/transform (ELT) processes, change-data-capture (CDC), and streaming mechanisms that carry data across system boundaries.
• Why it matters: Most “the dashboard shows the wrong number” incidents trace back to a broken, stale, or misconfigured pipeline — not a bad analysis or a bad model.
• For developers: changing a source table’s column name, type, or meaning is a breaking contract change for every pipeline reading it — treat it like a public API change, not an internal refactor.
• When to use it: Whenever data needs to cross a system boundary — from an OLTP database into a warehouse, between microservices, or from an external partner feed.
• When not to use it: For trivial cases where a consumer can simply call the source system’s API directly with acceptable latency and load — don’t build a pipeline you don’t need.
• Prerequisites: Data Storage Architectures.

Mental model

graph LR
    Source["Source system"] --> Extract["Extract / Capture\n(batch or CDC)"]
    Extract --> Transform["Transform\n(clean, reshape, validate)"]
    Transform --> Load["Load"]
    Load --> Target["Target: warehouse, lake, or service"]

Batch pipelines run on a schedule (hourly, nightly) and trade freshness for simplicity and lower cost. Streaming/CDC pipelines move data continuously as it changes, trading added complexity for near-real-time freshness. Choose based on how stale the data is allowed to be, not on which pattern sounds more advanced.

Step-by-step walkthrough

1. Identify the source and target systems, and how stale the target data is allowed to be.
2. Choose a batch or streaming/CDC pattern based on that freshness requirement.
3. Define the transformation rules — cleaning, reshaping, validating data in flight.
4. Version the schema/contract between source and pipeline so source changes don’t silently break it.
5. Add monitoring and alerting so a failed or stalled pipeline is caught before someone notices bad data downstream.
6. Define a backfill/replay strategy for recovering from failures or reprocessing historical data.

Worked example

Northwind’s logistics team wants a live “where’s my package” map, but the current shipment-tracking report is a nightly batch job — 12 hours stale by the time anyone looks at it.

• Input: a nightly batch ETL job loading shipment events into the warehouse once a day.
• Process: replace the nightly batch job with a streaming/CDC ingestion pipeline that picks up shipment events as they happen and loads them into the lakehouse continuously.
• Output: the tracking dashboard reflects shipment status within seconds instead of hours, at the cost of added pipeline complexity (ordering, backpressure handling) that the batch job didn’t need.

// Shipment service publishes each event instead of waiting for the nightly batch job
await using var producer = new EventHubProducerClient(connectionString, "shipment-events");
using EventDataBatch batch = await producer.CreateBatchAsync();
batch.TryAdd(new EventData(JsonSerializer.SerializeToUtf8Bytes(shipmentEvent)));
await producer.SendAsync(batch);

On Azure, shipment events flow through Azure Event Hubs, get transformed in near-real-time by Azure Stream Analytics, and land continuously in the lakehouse — replacing the nightly Azure Data Factory batch pipeline for this workload.

An alternative to a dedicated streaming consumer service is an event-triggered Azure Function, which scales automatically with event volume instead of needing pre-provisioned capacity:

[Function("ProcessShipmentEvent")]
public async Task Run([EventHubTrigger("shipment-events", Connection = "EventHubConnection")] string[] events)
{
    foreach (var e in events)
    {
        var shipmentEvent = JsonSerializer.Deserialize<ShipmentEvent>(e);
        await _lakehouseWriter.UpsertAsync(shipmentEvent); // idempotent by shipment ID
    }
}

The Function App only needs a small Azure Storage Account for its own runtime state (AzureWebJobsStorage) — the shipment data itself still lands in the lakehouse.

resource ehNamespace 'Microsoft.EventHub/namespaces@2024-01-01' = {
  name: 'northwind-events'
  location: resourceGroup().location
  sku: { name: 'Standard', tier: 'Standard', capacity: 1 }
}

resource shipmentHub 'Microsoft.EventHub/namespaces/eventhubs@2024-01-01' = {
  parent: ehNamespace
  name: 'shipment-events'
  properties: { partitionCount: 4, messageRetentionInDays: 3 }
}

Common use cases

Use case	When to use	Notes
Nightly warehouse load	Reporting where next-day freshness is acceptable	Simplest and cheapest pattern; start here unless real-time is a real requirement
Real-time event streaming	Live dashboards, fraud detection, operational alerting	Needs ordering, backpressure, and dead-letter handling
Change-data-capture (CDC) replication	Keeping a downstream copy in sync with a source database	Lower load on the source than repeated full-table batch extracts
Reverse ETL	Pushing warehouse-computed data (e.g. a customer score) back into an operational system	Closes the loop between analytics and operations

Advanced details

• Idempotency and replay: pipelines should be safe to re-run without creating duplicates — design loads to be idempotent (e.g. upsert by key) rather than blind appends.
• Schema evolution: version the contract between source and pipeline explicitly; additive changes are usually safe, but renames/type changes need a coordinated migration.
• Backpressure and dead-letter handling: streaming pipelines need a strategy for what happens when the target can’t keep up, or a record fails transformation — route failures to a dead-letter queue rather than silently dropping them.
• Orchestration: batch pipelines are typically scheduled and monitored by an orchestrator that tracks dependencies and retries failed steps.
• Observability: instrument both batch jobs and streaming consumers with Application Insights (custom events/metrics per pipeline run, dependency tracking for downstream writes) so a stalled or failing pipeline surfaces as an alert, not as a stakeholder noticing stale data first.

Quick reference

Term	Meaning
ETL	Extract, Transform, Load — transform before loading into the target
ELT	Extract, Load, Transform — load raw data first, transform inside the target
CDC	Change Data Capture — streaming database changes as they happen
Batch	Scheduled, periodic data movement
Streaming	Continuous, near-real-time data movement
Idempotency	Safe to re-run without creating duplicate or inconsistent results

Troubleshooting

Symptom	Likely cause	Fix
Dashboard shows stale or missing data	A batch job silently failed or stalled	Add monitoring/alerting on pipeline runs, not just on the dashboard’s data freshness
Duplicate records appear after a rerun	Pipeline load step isn’t idempotent	Change loads to upsert-by-key instead of blind append
Pipeline breaks whenever a source table changes	No schema contract or versioning between source and pipeline	Treat the source schema as a versioned contract; coordinate breaking changes with consumers

Check your understanding

• Why might a “real-time” streaming pipeline be the wrong choice for a report that’s only reviewed once a month?
• For a pipeline you rely on, what happens today if it fails silently at 2 a.m. — would anyone find out before a stakeholder does?

Related pages

Part of Introduction to Data Architecture. See also Data Storage Architectures for the sources and targets pipelines connect, Analytics & BI Architecture for what consumes pipeline output, and the Glossary for term definitions.