Data Sources, Ingestion and Integration

Introduction

Big data engineering begins before storage, analytics, or machine learning. It begins with the reliable movement of data from operational environments into platforms where it can be governed, processed, and used. Data may originate in relational databases, enterprise applications, files, application logs, web services, sensors, mobile devices, and event streams. These sources differ in structure, velocity, reliability, ownership, and semantics. A sound ingestion architecture must therefore do more than copy records: it must preserve meaning, detect change, tolerate failure, control duplication, and make data available within an appropriate service-level objective.

Data ingestion is the controlled acquisition and transfer of data into a target platform. Data integration is broader: it includes ingestion, transformation, reconciliation, metadata management, quality assurance, and the presentation of consistent data to downstream consumers. In modern architectures, ingestion commonly supports both batch and streaming workloads. A data lake or lakehouse may receive immutable raw data, while curated tables, analytical warehouses, search systems, and operational applications consume progressively refined representations.

The central engineering challenge is to balance correctness, latency, cost, scalability, and operational complexity. A pipeline that is fast but loses events is unsuitable for financial reporting; a perfectly accurate pipeline that takes several days may be unsuitable for fraud detection.

Learning Objectives

By the end of this chapter, the reader should be able to:

1. Classify common enterprise and machine-generated data sources.
2. Compare batch ingestion, micro-batching, and continuous streaming.
3. Select suitable connectors, extraction strategies, queues, and event brokers.
4. Explain ETL, ELT, change data capture, and event-driven integration.
5. Design for schema evolution, replay, idempotency, ordering, and delivery semantics.
6. Evaluate ingestion quality using latency, throughput, completeness, and freshness measures.
7. Develop a robust ingestion architecture for a realistic enterprise case.

1. The Data-Source Landscape

1.1 Relational and transactional databases

Relational databases remain major sources of enterprise data. They provide tables, constraints, indexes, transactions, and a relatively stable schema. Typical extraction methods include:

• Full extraction, in which all relevant rows are copied.
• Incremental extraction using a timestamp, monotonically increasing identifier, or watermark.
• Change data capture (CDC), which reads inserts, updates, and deletes from transaction logs or database change tables.
• Query-based replication, in which a connector executes controlled queries against the source.

Full extraction is simple but increasingly expensive. If a table contains N rows and an extraction reads every row, the source and network workload is proportional to N even when only a small fraction has changed. Incremental extraction reduces work but depends on a reliable change indicator. A timestamp column can miss updates caused by clock precision, late commits, or application errors. A transaction-log-based CDC system usually provides stronger fidelity because it observes committed database changes directly.

1.2 Files and object storage

Files include CSV, JSON, XML, Avro, Parquet, spreadsheets, images, and compressed archives. Object stores are often used as durable landing zones because they provide high capacity and separation between storage and compute. File ingestion must address naming conventions, partial uploads, duplicate files, compression, encoding, corrupt records, and late arrival.

A robust file contract specifies the producer, path, format, schema, partitioning convention, delivery marker, retention period, and ownership. Producers should write to a temporary name and atomically rename or publish a completion marker. Consumers should not process a file merely because it appears in a directory; appearance does not necessarily mean that writing is complete.

Columnar formats such as Parquet are efficient for analytical workloads because they permit column pruning and compression. Row-oriented formats such as JSON are often convenient for interchange but can be more expensive to scan. The choice is therefore both a data-modeling and ingestion decision.

1.3 APIs and enterprise applications

REST, GraphQL, SOAP, and vendor-specific interfaces expose data owned by CRM, ERP, payment, marketing, and human-resource systems. API ingestion must respect authentication, pagination, rate limits, quotas, retries, timeouts, and version compatibility. A successful HTTP response does not guarantee semantic success: an API may return a valid response containing an error object or incomplete page.

Connectors should record request parameters, response status, extraction time, source version, and pagination state. Retries must distinguish transient failures, such as temporary unavailability, from permanent failures, such as invalid credentials or malformed requests. Exponential backoff with jitter reduces synchronized retry storms.

1.4 Logs and machine-generated events

Application logs, audit logs, clickstreams, network telemetry, and security events are commonly semi-structured. Their value depends heavily on timestamps, correlation identifiers, service identity, severity, and contextual fields. Free-text logs are difficult to govern; structured events with explicit fields are preferable.

A log pipeline should preserve the original event where possible, add ingestion metadata, and separate event time from processing time. Event time describes when an activity occurred; processing time describes when the platform handled it. The distinction is essential for late-arriving events and time-window analytics.

1.5 IoT and edge sources

IoT devices may produce small messages at very high volume, intermittently, and over unreliable networks. Devices may sleep, reconnect, send duplicates, or have inaccurate clocks. Gateways can buffer data, authenticate devices, normalize protocols, and enforce local filtering. The platform must decide whether to retain every measurement, aggregate at the edge, or use a hybrid policy.

Device identity, firmware version, location, calibration status, and measurement units are part of the data—not optional administrative details. Without them, a technically complete stream may be analytically misleading.

2. Ingestion Patterns and Processing Placement

2.1 ETL and ELT

In extract-transform-load (ETL), data is extracted, transformed in an intermediate processing layer, and loaded into the target. ETL is useful when the target has strict schemas, when sensitive data must be removed before landing, or when transformation reduces transfer volume.

In extract-load-transform (ELT), data is loaded in a relatively raw form and transformed within the target platform. ELT supports reprocessing, schema-on-read, and multiple downstream interpretations. It is especially appropriate for scalable object storage and cloud warehouses, provided that governance and access controls protect raw data.

The choice is not binary. Many production systems use a layered approach:

1. Landing/raw layer: immutable or minimally altered source data.
2. Standardized layer: normalized types, names, units, and metadata.
3. Curated layer: validated entities and business rules.
4. Serving layer: purpose-specific tables, indexes, features, or APIs.

2.2 Batch, micro-batch, and streaming

Batch ingestion processes bounded data at scheduled intervals. It is economical and simple for daily financial loads, historical files, and periodic extracts. Micro-batching processes small batches at frequent intervals, offering a compromise between operational simplicity and latency. Continuous streaming processes records or small groups as they arrive.

A useful sizing relationship is:

where T is transfer time, D is data volume, and R is effective throughput. The equation assumes a stable throughput and excludes connection setup, transformation, retries, and downstream bottlenecks. In practice, the effective rate is lower than the nominal network rate. For example, transferring 720 GB at an effective rate of 200 MB/s takes approximately 3,600 seconds, or 1 hour, before overhead. Capacity planning must include peak rather than average arrival rate.

For a streaming system, if λ is the average arrival rate in events per second and μ is the sustainable processing rate, stable operation requires μ > λ over the relevant interval. Short bursts can still create backlog even when average capacity is sufficient. Queue depth, consumer lag, and oldest-event age should therefore be monitored.

2.3 Push and pull integration

In a push model, the producer sends data to the platform. Push reduces polling delay but requires producer-side retry and delivery logic. In a pull model, the consumer queries or retrieves data from the source. Pull is often easier to control and throttle, but it may increase latency and place load on the source. Hybrid designs use source-side event notifications to trigger controlled retrieval.

3. Connectors, Queues, and Event Brokers

A connector adapts a source or destination protocol to a common ingestion framework. It should support authentication, checkpointing, pagination, parallelism, backpressure, metrics, and error handling. A queue or broker decouples producers from consumers. This decoupling absorbs bursts, enables independent scaling, and permits replay when consumers fail.

A queue generally emphasizes delivery to one consumer or consumer group. An event broker commonly supports multiple subscriptions, retention, partitions, offsets, and replay. The distinction depends on product design rather than terminology alone.

Partitioning is important for scale. Events are distributed across partitions, while ordering is normally guaranteed only within a partition. A stable key—such as customer ID or device ID—can preserve per-entity ordering, but it may create a hot partition if one key is disproportionately active. Random distribution improves balance but sacrifices entity ordering.

Backpressure occurs when downstream processing is slower than arrival. Appropriate responses include slowing producers, increasing consumers, buffering, sampling, rejecting low-priority events, or scaling computation. Unbounded buffering is not a solution; it merely postpones failure and may exhaust storage.

4. Change Data Capture and Incremental Integration

CDC captures changes rather than repeatedly copying complete tables. Three broad approaches are common:

A CDC record should ideally contain the source table, operation type, primary key, changed values or row image, transaction identifier, source position, and event timestamp. Deletes require explicit representation; absence of a row in a later extract is not equivalent to a delete event.

A downstream consumer can materialize the current state by applying events in source order. If an event contains operation op and key k, the state transition may be described conceptually as:

where S_t is the current state, e_t is a change event, and apply implements insert, update, or delete semantics. This model assumes that events are sufficiently ordered and that the operation is deterministic. If events can arrive out of order, the system needs source sequence numbers, version checks, reconciliation, or a domain-specific conflict policy.

CDC is not automatically a complete integration strategy. Schema changes, failed transactions, connector restarts, log retention, and downstream idempotency must still be managed.

5. Schema, Metadata, and Data Contracts

A schema describes fields, types, constraints, and relationships. A data contract extends this description with ownership, semantic definitions, quality expectations, compatibility rules, and operational responsibilities. Contracts reduce hidden coupling between producers and consumers.

Schema evolution commonly includes:

• Backward-compatible change: new consumers can read old data.
• Forward-compatible change: old consumers can tolerate new data.
• Full compatibility: old and new consumers can interoperate.
• Breaking change: existing consumers may fail or interpret data incorrectly.

Adding an optional field is often safer than renaming or changing a field’s type. Removing a field can be breaking even when the producer considers it obsolete. Compatibility should be checked automatically through a schema registry or contract-validation process.

Schema evolution is also semantic. Changing a field from dollars to cents, local time to UTC, or gross amount to net amount may preserve the technical type while changing business meaning. Every event should carry sufficient metadata to identify version, source, event type, and units where relevant.

6. Reliability: Idempotency and Delivery Semantics

6.1 Delivery semantics

Common delivery guarantees are:

• At-most-once: an event is delivered zero or one time; loss is possible, duplication is not expected.
• At-least-once: an event is retried until acknowledged; duplication is possible, loss is reduced.
• Exactly-once processing: the intended effect occurs once, usually within a carefully bounded transactional system.

Exactly-once is frequently misunderstood. A broker may avoid duplicate delivery within its own transaction boundary, yet an external database, API, or email service may still observe repeated effects. End-to-end exactly-once behavior requires coordinated transactional guarantees, deterministic effects, or idempotent sinks. Consequently, at-least-once delivery plus idempotent processing is often the practical design.

6.2 Idempotency

An operation is idempotent if applying it repeatedly produces the same result as applying it once. A common design stores a stable event ID or source transaction position in a deduplication table. Before applying an event, the consumer checks whether the identifier has already been committed. The check and the state change must be atomic in the target system; otherwise a crash between them can still produce duplicates.

For an upsert, the natural key and version policy must be explicit. For example, a record may be applied only when its source sequence is greater than the stored sequence. This prevents an old, delayed update from overwriting a newer value.

6.3 Checkpointing and replay

A checkpoint records the source position successfully processed by a consumer. A good checkpoint is durable, monotonic, and tied to the output commit. If the output commits but the checkpoint does not, replay may occur; idempotency should make replay safe. If the checkpoint commits but output does not, data may be lost. This ordering explains why checkpoint and output coordination is central to reliability.

Dead-letter queues should hold records that cannot be processed after controlled retries. They need ownership, retention, reason codes, and reprocessing procedures. Silently discarding malformed records is unacceptable for governed enterprise data.

7. Quality, Observability, and Governance

Ingestion quality should be measured rather than assumed. Useful measures include:

If N_expected records are expected and N_received arrive, a simple record completeness ratio is:

This ratio is meaningful only when the expected population is known and both counts use the same scope and time window. A value of 1 does not prove correctness: duplicated records can inflate the received count, while incorrect values can still pass structural validation.

Observability should include source lag, broker lag, throughput, error rate, retry count, dead-letter volume, schema failures, checkpoint position, processing duration, and data-quality outcomes. Correlation IDs allow an event to be traced across services.

Governance includes data classification, encryption in transit and at rest, least-privilege access, retention, lineage, audit logging, and privacy controls. Personally identifiable information should be minimized, masked, tokenized, or encrypted according to policy and law. Retaining raw data forever is not inherently responsible; retention should reflect legal, operational, and analytical requirements.

8. A Reference Architecture

A robust architecture often contains the following logical stages:

1. Source adapters: database connectors, file watchers, API clients, agents, and IoT gateways.
2. Ingress boundary: authentication, validation, rate control, and initial metadata enrichment.
3. Durable buffer: queue, event broker, or object-storage landing zone.
4. Raw persistence: immutable source representation for audit and replay.
5. Standardization: type conversion, canonical timestamps, unit normalization, and schema validation.
6. Quality and quarantine: rule checks, duplicate handling, and dead-letter routing.
7. Curated integration: joins, entity resolution, CDC application, and business rules.
8. Serving outputs: warehouse tables, lakehouse tables, search indexes, APIs, and feature stores.
9. Control plane: orchestration, metadata, lineage, monitoring, alerting, and access management.

The architecture should make failure visible. A pipeline that continues producing apparently valid output after a source disconnect may be more dangerous than one that stops loudly.

9. Applied Case: Illustrative Retail Order Integration

Background

Consider a fictional national retailer, Northstar Retail, operating online stores, physical outlets, a warehouse-management system, and a customer mobile application. Orders are stored in a relational database, shipment events arrive from a logistics API, store transactions are delivered as files, and clickstream events arrive through an event broker.

Problem

The company’s daily reporting contains duplicate orders, shipment cancellations are sometimes absent, and analysts cannot agree on whether an order timestamp represents customer submission or payment authorization. Marketing requires near-real-time events, while finance requires reproducible daily totals.

Analysis

The existing design performs nightly full extracts. It overloads the order database, cannot represent deletes or corrections reliably, and has no shared event vocabulary. Files are processed when detected, even if uploads are incomplete. The platform also retries failed messages without an idempotency key.

Approach and solution

Northstar adopts a layered integration design:

• Transaction-log CDC captures committed order and payment changes.
• A stable order ID and source transaction position become deduplication and ordering attributes.
• Raw CDC events are retained in object storage and a broker for replay.
• The logistics API is ingested through paginated pulls using a stored cursor and rate-limited retries.
• Store files use a manifest containing file name, row count, checksum, schema version, and completion status.
• A canonical order contract distinguishes order-created, payment-authorized, shipment-dispatched, shipment-cancelled, and order-refunded events.
• Consumers use upserts and reject an event whose source version is older than the stored version.
• Sensitive customer fields are tokenized before broad analytical access.
• Data-quality checks reconcile daily order counts and monetary totals with source control totals.

The platform provides a raw layer for audit, a standardized layer with UTC timestamps and decimal monetary types, and curated order and shipment tables. Marketing consumes broker events, while finance consumes reproducible curated snapshots.

Outcome

The design reduces source load by replacing repeated full extracts with CDC, supports replay after consumer failure, and makes cancellation explicit. Duplicate delivery no longer creates duplicate business effects because consumers apply idempotent upserts. Remaining risks include source-system clock errors, API contract changes, and the need to manage CDC log retention.

Learning points

The case illustrates that ingestion is a product and governance capability, not merely a transport task. Stable identifiers, explicit semantics, durable raw data, and reconciliation controls are more valuable than selecting a fashionable tool without operational design.

10. Contemporary Developments and Critical Considerations

Data mesh principles encourage domain teams to publish discoverable, governed data products with explicit contracts. This can improve ownership but may produce fragmentation if platform standards are weak. Streaming lakehouse architectures combine durable event ingestion with transactional analytical tables, reducing the separation between streaming and batch pipelines. Open table formats and schema registries improve interoperability, but they do not solve semantic disagreement.

Artificial intelligence can assist with schema matching, anomaly detection, log classification, and pipeline documentation. Such assistance must remain subject to human review. Automatically inferring that two fields are equivalent can create silent semantic errors, especially in regulated or financial contexts. AI-generated transformations should be versioned, tested, explainable, and evaluated against representative data.

A mature engineering team treats ingestion contracts, reliability objectives, and data quality as testable software behavior. Unit tests validate transformations; integration tests validate connectors; contract tests validate schemas; failure tests validate restart and replay behavior; and reconciliation tests compare outputs with trusted source totals.

Best-Practice Checklist

• Identify source ownership, business meaning, and sensitivity before building a connector.
• Prefer CDC or well-defined incremental extraction over repeated full copies.
• Preserve raw data and source metadata when lawful and useful.
• Separate event time from processing time.
• Use stable identifiers and design sinks to be idempotent.
• Make deletes, corrections, and late events explicit.
• Use schema compatibility checks and versioned data contracts.
• Bound retries, apply backoff, and route persistent failures to a managed dead-letter process.
• Monitor freshness, lag, completeness, validity, throughput, and cost.
• Test crashes, replay, backpressure, source outages, and partial uploads.
• Apply least privilege, encryption, retention, masking, and lineage controls.
• Select batch or streaming according to business latency and correctness requirements—not fashion.

Chapter Summary

Data ingestion moves information from diverse sources into platforms where it can be processed and governed; integration additionally reconciles structure, meaning, quality, and business identity. Relational databases, files, APIs, logs, IoT devices, and enterprise applications require different extraction and reliability strategies. ETL transforms before loading, whereas ELT loads first and transforms in a scalable target; layered architectures often combine both.

Batch, micro-batch, and streaming differ mainly in latency, complexity, and operational behavior. Connectors, queues, and brokers decouple systems and provide buffering, scaling, and replay. CDC is generally more faithful than timestamp extraction, but it requires careful handling of deletes, ordering, schema change, log retention, and downstream state transitions.

Schema evolution must address both technical compatibility and business semantics. At-least-once delivery with idempotent consumers is a practical reliability pattern. Checkpointing, dead-letter handling, reconciliation, observability, privacy, and access control are essential parts of production ingestion. Finally, successful integration is measured not only by data movement but also by freshness, completeness, validity, traceability, and fitness for purpose.

Key Terms

Data ingestion: Controlled acquisition and transfer of data into a platform.

Data integration: Combining ingestion, transformation, reconciliation, governance, and delivery to consumers.

ETL: Extract, transform, and load processing.

ELT: Extract, load, and transform processing.

Batch processing: Processing bounded data at scheduled intervals.

Streaming: Processing data continuously or with very small delay as it arrives.

Connector: Software adapter between a source or destination and an ingestion framework.

Event broker: Durable intermediary that distributes, retains, and often replays events.

Change data capture: Recording inserts, updates, and deletes from a source system.

Watermark: Recorded position or value used to identify extraction progress.

Schema evolution: Controlled change to the structure or meaning of data over time.

Data contract: Agreement defining schema, semantics, ownership, quality, and compatibility.

Idempotency: Property whereby repeated application has the same effect as one application.

At-least-once delivery: Delivery strategy that may produce duplicates but aims to avoid loss.

Checkpoint: Durable record of successfully processed source progress.

Backpressure: Mechanism for limiting or responding to arrival faster than processing.

Dead-letter queue: Destination for records that cannot be processed normally.

Event time: Time at which an event occurred at its source.

Processing time: Time at which a platform processes an event.

Data lineage: Trace of data’s origin, transformations, and destinations.

Review Questions

Short Answer Questions

1. Distinguish ETL from ELT and give one situation suitable for each.
2. Why can timestamp-based incremental extraction miss database changes?
3. Define at-most-once, at-least-once, and exactly-once delivery.
4. What is the purpose of a dead-letter queue?
5. Explain why event time and processing time should be stored separately.

Long Answer Questions

1. Compare batch, micro-batch, and continuous streaming in terms of latency, cost, scalability, and operational complexity.
2. Explain how transaction-log CDC works conceptually and discuss its principal limitations.
3. Design a schema-evolution policy for an enterprise event platform, including compatible and breaking changes.
4. Discuss how queues and event brokers support resilience, replay, consumer independence, and backpressure.
5. Explain why end-to-end exactly-once processing is difficult and how idempotent sinks reduce the risk of duplicate effects.

Analytical / Application Questions

1. A source produces 50,000 events per second while a consumer processes 42,000 events per second. Analyze the likely effect on backlog and propose at least three technically different responses.
2. Design an ingestion approach for an IoT fleet with intermittent connectivity, duplicate messages, inaccurate device clocks, and strict audit requirements.
3. An API returns paginated shipment data and occasionally changes its response schema. Develop a robust extraction, validation, retry, checkpointing, and schema-compatibility strategy.

Multiple Choice Questions with Answers

Question 1

Which ingestion method generally provides the strongest fidelity for committed database inserts, updates, and deletes?

A. Repeated full-table extraction

B. Transaction-log change data capture

C. A file directory watcher

D. Client-side polling without a watermark

Correct Answer: B. Transaction-log change data capture

Question 2

A consumer receives the same event twice. Which design most directly prevents two business effects?

A. Increasing network bandwidth

B. Using an idempotency key with an atomic deduplication check

C. Reducing the broker retention period

D. Changing event time to processing time

Correct Answer: B. Using an idempotency key with an atomic deduplication check

Question 3

Which statement about event-broker ordering is generally correct?

A. All events across all partitions are always globally ordered

B. Ordering is commonly guaranteed within a partition

C. Ordering is impossible within a partition

D. Ordering depends only on consumer memory

Correct Answer: B. Ordering is commonly guaranteed within a partition

Question 4

What is the principal purpose of a checkpoint?

A. To define a business metric

B. To record durable processing progress

C. To encrypt an event payload

D. To replace schema validation

Correct Answer: B. To record durable processing progress

Question 5

Which change is most likely to be backward-compatible when carefully implemented?

A. Changing a decimal amount into a string with a new meaning

B. Renaming a required field without aliases

C. Adding an optional field with a documented default

D. Changing timestamps from UTC to local time

Correct Answer: C. Adding an optional field with a documented default

Question 6

If 720 GB is transferred at an effective rate of 200 MB/s, ignoring overhead, approximately how long does the transfer take?

A. 36 seconds

B. 360 seconds

C. 3,600 seconds

D. 36,000 seconds

Correct Answer: C. 3,600 seconds

Question 7

Which delivery guarantee permits duplicates because messages may be retried?

A. At-most-once

B. At-least-once

C. No-delivery

D. Schema-on-read

Correct Answer: B. At-least-once

Question 8

What is a principal advantage of ELT in a scalable analytical platform?

A. It prevents all schema changes

B. It preserves relatively raw data for later transformations

C. It eliminates the need for governance

D. It guarantees zero processing latency

Correct Answer: B. It preserves relatively raw data for later transformations

Question 9

Which control is most appropriate for detecting an incomplete file upload?

A. Processing any file immediately when its name appears

B. Using a completion marker or manifest with a checksum

C. Deleting files after listing them

D. Disabling file metadata

Correct Answer: B. Using a completion marker or manifest with a checksum

Question 10

A streaming source averages 10,000 events per second and a consumer sustainably processes 8,000 events per second. What is the immediate engineering concern?

A. Consumer lag and growing backlog

B. Guaranteed data loss without any other information

C. Automatic global ordering

D. A schema becoming backward-compatible

Correct Answer: A. Consumer lag and growing backlog