Data Ingestion, Integration and Engineering

Introduction

Big-data systems are valuable only when data arrives reliably, retains sufficient meaning, and can be transformed into trustworthy information. Data ingestion is the movement of data from operational sources into a platform for storage and processing. Data integration combines data from different sources so that it can be interpreted consistently. Data engineering supplies the architecture, code, metadata, controls, and operational practices that make both activities dependable.

A modern organization may ingest customer records from a relational database, transactions through an API, application logs as events, documents from object storage, and machine measurements through a messaging system. These sources differ in format, speed, reliability, ownership, and semantics. A successful pipeline therefore does more than copy bytes: it establishes contracts, preserves lineage, detects errors, manages change, and supports replay.

This chapter examines batch and event-driven ingestion, ETL and ELT, APIs, files, logs, change data capture, messaging, schema evolution, metadata, orchestration, validation, data quality, governance, and operational design.

Learning Objectives

After studying this chapter, you should be able to:

• distinguish batch, streaming, and event-driven ingestion;
• select suitable ingestion methods for APIs, files, logs, databases, and messaging systems;
• compare ETL and ELT architectures;
• explain change data capture and delivery semantics;
• design schemas, contracts, metadata, and evolution policies;
• apply validation, data-quality dimensions, and reconciliation controls;
• describe orchestration, monitoring, recovery, and replay strategies;
• evaluate a pipeline against reliability, security, cost, and governance requirements.

1. The Ingestion Problem

An ingestion pipeline has five logical responsibilities:

1. Acquire data from a source.
2. Transport it reliably to a landing or messaging layer.
3. Persist an immutable or recoverable representation.
4. Transform and integrate it into usable data products.
5. Observe and govern the entire process.

The source-to-target path should be treated as a system boundary. A source may be unavailable, produce malformed records, change its schema, repeat messages, or report success before downstream consumers have received the data. Consequently, “the job completed” is not equivalent to “the data is correct and available.”

Batch and event-driven ingestion

Batch ingestion collects data over an interval and processes it together. Examples include a nightly export of sales transactions or a daily file from a business partner. Batch is often simple, economical, and appropriate when decisions do not require immediate data.

Event-driven ingestion processes individual events or small groups of events as they become available. An event records something that happened, such as OrderPlaced, PaymentAuthorized, or TemperatureMeasured. Event-driven systems reduce latency and permit independent consumers, but they introduce ordering, duplication, back-pressure, and late-arrival concerns.

Streaming is a processing style in which records are handled continuously. Event-driven ingestion commonly uses streaming, but the terms are not identical: an event may be stored and processed later, while a stream is a potentially unbounded sequence.

A useful selection criterion is freshness. If a source produces N records per hour and the pipeline processes them in batches of B records, the approximate number of batches per hour is N/B, assuming a stable production rate and full batches. This is only a planning approximation; scheduling delay, retries, and variable batch sizes affect actual latency.

Figure: Batch and event-driven ingestion paths converging on a data platform.

2. Source Patterns and Ingestion Methods

APIs

An application programming interface exposes data or operations through a defined protocol. REST APIs commonly use HTTP and JSON; other systems use GraphQL, gRPC, SOAP, or vendor-specific interfaces. A production API extractor should address authentication, pagination, rate limits, timeouts, retries, request idempotency, and incremental extraction.

Pagination may be page-number based, offset based, or cursor based. Cursor pagination is usually safer when records are changing because an offset can skip or repeat records as rows are inserted. Incremental extraction should use a stable watermark such as updated_at, a monotonically increasing identifier, or an API-provided change token. Watermarks must be stored only after the corresponding data has been durably written.

Retries should distinguish transient failures, such as a temporary network error, from permanent failures, such as invalid credentials. Exponential backoff reduces pressure on a recovering service. A practical retry delay can be represented as d_k = min(d_max, d₀ × 2^k), where d₀ is the initial delay, k is the retry number, and d_max is the maximum delay. Random jitter should be added in real systems to avoid synchronized retries. This formula assumes exponential backoff is appropriate and does not itself determine the maximum number of attempts.

Files and object storage

Files remain common because they are easy to exchange across organizational boundaries. CSV is human-readable but weakly typed and ambiguous about escaping, encodings, and nulls. JSON supports nested structures but may be verbose. Columnar formats such as Parquet are efficient for analytical workloads because they support column projection and compression. Avro is frequently used for row-oriented interchange with an explicit schema.

A robust file contract specifies file format, encoding, delimiter, compression, schema, naming convention, partitioning, delivery location, completeness marker, and retention. Producers should write to a temporary name and atomically publish a completion marker or rename the object. Consumers should not process a file merely because it appears in a directory.

Partitioning organizes data by fields such as date, region, or tenant. It can reduce query work, but excessive partition counts create metadata overhead and many small files. A pipeline should compact small files where the target system benefits from larger objects.

Logs

Logs are semi-structured records describing application or infrastructure activity. Structured logs with fields such as timestamp, service, severity, request identifier, and message are easier to parse and correlate than free text. Log pipelines must consider multiline stack traces, clock skew, sensitive values, retention, and volume spikes. Log ingestion should preserve the original event when possible, while adding parsed fields and ingestion metadata separately.

Change data capture

Change data capture (CDC) identifies inserts, updates, and deletes in an operational database. Log-based CDC reads a database transaction log or write-ahead log and generally imposes less query load than repeated full-table extraction. A CDC record commonly includes the table, operation, key, changed values or row image, transaction position, source timestamp, and schema version.

CDC is not the same as a database snapshot. A snapshot establishes an initial state; CDC maintains subsequent changes. A common initialization sequence is: obtain a consistent snapshot, record the source log position associated with that snapshot, then consume changes after that position. If the position is mishandled, updates can be lost or applied twice.

Deletes require explicit representation, such as tombstone events or operation codes. Downstream tables may use append-only history, current-state upserts, or a combination of both. A current-state table needs a deterministic rule for choosing the latest record, usually based on source sequence or transaction position rather than arrival time alone.

Messaging systems

A message broker decouples producers from consumers. Important concepts include topic or queue, partition, offset, consumer group, retention, replication, ordering, and acknowledgement. Partitioning increases parallelism, but ordering is normally guaranteed only within a partition. A key can route related events to the same partition, preserving order for that key.

At-most-once delivery may lose messages but avoids duplicates; at-least-once delivery may repeat messages but is more tolerant of failures; exactly-once processing requires carefully coordinated guarantees across source, broker, processor, and sink. In practice, idempotent consumers and deduplication are often more portable than assuming end-to-end exactly-once behavior.

3. ETL, ELT, and Layered Architecture

Extract, transform, load (ETL) transforms data before loading it into the target. It is useful when the target has limited processing capacity, when sensitive fields must be removed early, or when transformation logic belongs close to the source integration layer.

Extract, load, transform (ELT) loads data into a lake, warehouse, or lakehouse before transforming it. ELT preserves raw evidence, uses scalable target compute, and supports multiple downstream models. It also requires storage governance, access controls, and discipline to prevent a disorganized raw zone.

A layered design often includes:

• Landing or raw layer: source-faithful data, ingestion timestamps, source identifiers, and minimal alteration.
• Standardized or refined layer: parsed types, normalized names, quality flags, deduplicated records, and conformed keys.
• Curated or serving layer: business-ready facts, dimensions, aggregates, and data products.

The raw layer should not be treated as automatically trustworthy. “Raw” means close to source, not correct. Validation results and quarantine locations should accompany it.

Integration requires semantic decisions. Two systems may both use customer_id while using different identifier domains. A currency amount without currency code is incomplete. A timestamp without timezone or event-time semantics can produce incorrect daily reporting. Conformed dimensions, reference data, mapping tables, and master-data rules help establish shared meaning.

4. Schemas, Contracts, and Metadata

A schema describes fields, types, constraints, and sometimes semantics. A data contract extends the schema with ownership, delivery expectations, compatibility rules, quality requirements, and change communication. Contracts reduce the assumption that consumers can adapt instantly to every source change.

Schema evolution must distinguish compatible and incompatible changes. Adding an optional field is often backward compatible. Renaming a field, changing its meaning, narrowing its type, or changing a unit is potentially breaking. A compatibility policy should specify whether old readers must read new data, whether new readers must read old data, and how long old versions remain supported.

Useful evolution practices include:

• use explicit versions rather than inferring meaning from filenames;
• prefer additive changes and deprecation periods;
• prohibit silent type or semantic changes;
• validate schemas at publication and consumption;
• retain a registry or catalog of versions;
• test representative old and new messages.

Metadata has several forms. Technical metadata describes schemas, partitions, formats, and pipeline runs. Business metadata explains definitions, owners, sensitivity, and permissible use. Operational metadata records latency, row counts, failures, offsets, and quality outcomes. Lineage records how data moved and changed from source to output.

A useful ingestion record includes source name, source position, event identifier, ingestion time, event time, schema version, pipeline version, producer, and quality status. These fields support audit, replay, troubleshooting, and regulatory inquiries.

5. Validation and Data Quality

Data quality is multidimensional. Common dimensions are:

A simple completeness rate for required non-null values is:

The formula assumes that each required value contributes equally and that the denominator is defined consistently. For example, 9,800 non-null values among 10,000 required values produce 98%. This metric does not prove accuracy or uniqueness.

Validation should occur at multiple stages. Ingestion validation checks file integrity, schema, authentication, and transport. Standardization validation checks types, timestamps, and reference codes. Business validation checks rules such as non-negative quantity or an allowed order-state transition. Reconciliation compares counts, sums, balances, or CDC positions with the source.

Bad records should not simply disappear. Depending on risk, the system may reject the batch, quarantine individual records, attach an error reason, or accept the record with a quality flag. Quarantine data requires access control, retention, ownership, and a remediation workflow.

Duplicates arise from retries, replay, source defects, or consumer failures. Idempotency means applying the same logical input more than once produces the same final result. A common pattern is a durable deduplication key such as event ID, source transaction ID, or a composite business key plus version. Deduplication windows must be long enough for expected replay and late-arrival behavior.

6. Orchestration and Operational Reliability

Orchestration coordinates dependencies, scheduling, retries, parameters, and alerts. A directed acyclic graph (DAG) represents tasks and dependencies without cycles. For example, extraction must complete before validation, validation before standardization, and standardization before publication. Orchestration should manage work; transformation logic should remain testable outside the scheduler.

Important operational controls include:

• checkpointing source positions and processing progress;
• retrying transient failures with bounded backoff;
• isolating failed partitions or records where safe;
• using dead-letter queues for unprocessable events;
• supporting replay from immutable storage or retained offsets;
• separating code deployment from data backfill;
• alerting on freshness, volume, quality, and error-rate thresholds;
• documenting runbooks and ownership.

Pipeline capacity can be considered through arrival and service rates. Let λ be the average arrival rate in records per second and μ the average processing rate per worker. If λ ≥ μ for a sustained period, backlog grows unless capacity, batching, or workload is changed. This comparison assumes rates are measured over comparable intervals and that one worker is being considered; multiple workers and partition constraints alter the effective service rate.

Latency should be decomposed into source delay, transport delay, queue delay, processing delay, and publication delay. Averages can hide failures, so percentiles such as the 95th percentile are often more informative for service objectives. A freshness objective might require that 99% of events become queryable within five minutes, rather than merely specifying an average.

7. Security, Governance, and Responsible Engineering

Ingestion systems often handle personal, financial, health, or confidential data. Security begins with data minimization: collect only what is necessary for a defined purpose. Apply encryption in transit and at rest, secret management, least-privilege identities, network controls, and audit logging. Sensitive fields may require masking, tokenization, or encryption before broader storage.

Governance should define ownership, retention, deletion, lawful purpose, access review, and incident response. A raw zone can create compliance risk because it may contain fields that downstream users no longer need. Retention policies must cover raw data, backups, logs, dead-letter queues, and derived copies.

Data lineage supports impact analysis and accountability, but lineage itself can expose sensitive information. Access to metadata should therefore be governed. Automated quality rules can also encode organizational bias: for example, rejecting records from a minority region because its address format differs from the dominant format. Quality controls should distinguish genuine invalidity from legitimate diversity.

8. Applied Case: Retail Order Integration

Background. This illustrative case concerns a retailer with an e-commerce database, mobile application events, payment-provider API, and warehouse files. Management wants near-real-time order monitoring and a daily revenue report.

Problem. The systems use different order identifiers, currencies, timestamps, and status values. The payment API is rate-limited, warehouse files occasionally arrive late, and application events are sometimes delivered twice.

Analysis. A nightly full extraction would provide the report but would not support operational monitoring. Directly joining source tables would create inconsistent business logic. The organization therefore identifies source ownership and defines a canonical order contract. The contract requires an order identifier, event type, event time with timezone, currency, amount, customer reference, schema version, and producer identifier.

Approach. Database changes are captured from the transaction log and published to a broker. Events are partitioned by canonical order identifier, preserving per-order ordering. The mobile application publishes OrderPlaced and OrderCancelled events with unique event IDs. A consumer writes the original events to immutable object storage and a refined table. A durable event-ID constraint makes reprocessing idempotent.

The payment API is extracted incrementally using its provider cursor. The cursor is committed only after the response has been stored. Rate-limit responses trigger bounded exponential backoff. Warehouse files are accepted only after a completion marker appears; each file is checked against its declared schema and expected control totals.

A standardization layer maps payment and warehouse statuses to a controlled vocabulary. Currency is retained explicitly, and conversion to a reporting currency is performed using a versioned exchange-rate table. Late events are admitted for a defined correction period, after which they are routed for exception handling. Daily revenue is published only after reconciliation of accepted payment totals and warehouse shipment totals, with unresolved differences reported rather than hidden.

Outcome. Operations receives low-latency order status, while analysts receive reproducible daily data. Duplicate mobile events no longer inflate counts. A schema registry detects incompatible producer changes before publication. Lineage identifies which reports depend on payment data, and replay is possible from retained events.

Learning points. The case demonstrates that reliability is architectural: it depends on contracts, durable raw data, identity, ordering, watermarking, reconciliation, and observability together. No single tool can compensate for undefined semantics or missing ownership.

9. Contemporary Developments and Design Choices

Lakehouse architectures increasingly combine inexpensive object storage with transactional table formats, schema enforcement, time travel, and analytical processing. They can simplify batch and streaming convergence, but they still require partition management, compaction, governance, and careful concurrency control.

Data contracts and data-product thinking move responsibility toward source teams. This can improve quality when ownership is real and measurable, but it may create fragmentation if every team defines incompatible conventions. Open table formats, schema registries, catalogues, and lineage platforms improve interoperability, although tooling does not replace semantic agreement.

Artificial intelligence can assist anomaly detection, schema matching, documentation, and incident triage. Such assistance should be supervised: an automated system may mistake a legitimate seasonal volume change for failure or infer an incorrect mapping between fields. AI-generated transformations require testing, lineage, access review, and human approval, especially when personal or regulated data is involved.

10. Best-Practice Design Checklist

A production ingestion pipeline should answer the following questions:

• What is the source system and authoritative owner?
• Is the requirement batch, streaming, or event-driven?
• What identifies a record or event uniquely?
• How are progress, watermarks, offsets, and retries stored?
• Can the pipeline be replayed without corrupting the target?
• What happens when a schema changes?
• Which records are rejected, quarantined, or flagged?
• How are freshness, completeness, and reconciliation measured?
• What are the security classification, retention, and deletion rules?
• Who receives alerts and owns remediation?

The best design is not necessarily the most complex. It is the simplest design that satisfies freshness, correctness, recoverability, security, cost, and governance requirements.

Chapter Summary

Data ingestion moves data from diverse sources into a platform, while integration gives that data consistent structure and meaning. Batch ingestion is suitable for scheduled collections; event-driven ingestion supports low-latency reactions but requires attention to ordering, duplication, back-pressure, and late events. APIs, files, logs, CDC, and message brokers each impose distinct engineering concerns.

ETL transforms before loading, whereas ELT preserves source data and uses target compute. Layered architectures commonly separate raw, refined, and curated data. Schemas and data contracts govern compatibility, while metadata and lineage support discovery, auditing, replay, and impact analysis. Validation should address completeness, validity, accuracy, consistency, timeliness, and uniqueness. Idempotency, checkpoints, reconciliation, quarantine, orchestration, monitoring, and bounded retries are central reliability practices.

Finally, ingestion is a governance responsibility as well as a technical one. Security, privacy, retention, ownership, and responsible use must be designed into the pipeline. Effective data engineering produces data that is not merely available, but trustworthy, explainable, recoverable, and fit for use.

Key Terms

Data ingestion: The acquisition and movement of data into a storage or processing platform.

Batch processing: Processing data collected over a defined interval.

Event-driven ingestion: Ingestion triggered by events as they occur.

API: A defined interface through which software requests data or services.

Watermark: A stored progress value used to identify what has been extracted or processed.

Change data capture: A method for recording inserts, updates, and deletes from a source system.

Message broker: Infrastructure that stores and delivers messages between producers and consumers.

Partition: A logical subdivision of messages or data used for scale and, often, ordering.

Idempotency: The property that repeated application of the same logical input has the same effect as one application.

ETL: Extract, transform, and load.

ELT: Extract, load, and transform.

Data contract: An agreement defining a dataset’s schema, semantics, quality, ownership, and change rules.

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

Metadata: Information describing data, its operation, ownership, and use.

Data lineage: A record of data origins, transformations, and destinations.

Quarantine: Controlled isolation of invalid or suspicious records.

Orchestration: Coordination of pipeline tasks, dependencies, schedules, and recovery.

Reconciliation: Comparison of source and target values to identify discrepancies.

Back-pressure: A condition in which producers must slow because consumers cannot keep up.

Dead-letter queue: A destination for messages that cannot be successfully processed.

Review Questions

Short Answer Questions

1. Distinguish batch ingestion from event-driven ingestion.
2. Why is a stored watermark important in incremental API extraction?
3. What is the difference between a snapshot and change data capture?
4. Define idempotency in the context of a data pipeline.
5. Name four dimensions of data quality.

Long Answer Questions

1. Compare ETL and ELT, including their benefits, risks, and suitable use cases.
2. Explain how schema contracts and schema registries support safe evolution.
3. Describe the design of a reliable file-ingestion pipeline from publication to validation.
4. Explain how orchestration, checkpointing, retries, and replay contribute to recovery.
5. Discuss security, privacy, retention, and lineage requirements for a big-data ingestion platform.

Analytical / Application Questions

1. A payment API returns pages of records and occasionally times out. Design an extraction strategy that handles pagination, retries, rate limits, and safe watermark advancement.
2. A streaming consumer processes 500 records per second while arrivals average 700 records per second for two hours. Analyze the operational risk and propose at least three corrective actions. State the assumptions behind your analysis.
3. Design an integrated retail pipeline using CDC, application events, and warehouse files. Specify identifiers, ordering, schema evolution, quality controls, reconciliation, and replay behavior.

Multiple Choice Questions with Answers

Question 1

Which ingestion method is most appropriate for maintaining near-real-time changes from a transactional database while avoiding repeated full-table scans?

A. Log-based change data capture

B. Monthly CSV export

C. Manual spreadsheet entry

D. Full-table extraction every year

Correct Answer: A. Log-based change data capture

Question 2

Why should an API extraction watermark normally be committed only after the retrieved data is durably stored?

A. To increase API response size

B. To prevent progress from advancing past data that was not safely persisted

C. To remove the need for authentication

D. To guarantee that the API never changes

Correct Answer: B. To prevent progress from advancing past data that was not safely persisted

Question 3

Which statement best describes ELT?

A. Data is transformed before it leaves the source system

B. Data is extracted, loaded into a target platform, and transformed there

C. Data is never stored before reporting

D. Only event data can be processed

Correct Answer: B. Data is extracted, loaded into a target platform, and transformed there

Question 4

Which practice most directly supports idempotent event processing?

A. Ignoring event identifiers

B. Using a durable event-ID deduplication mechanism

C. Deleting raw events after first use

D. Randomly changing partition keys

Correct Answer: B. Using a durable event-ID deduplication mechanism

Question 5

In a partitioned messaging system, what is ordering commonly guaranteed for?

A. All messages across every topic

B. Messages with the same key in the same partition

C. Messages after they are deleted

D. Every consumer regardless of configuration

Correct Answer: B. Messages with the same key in the same partition

Question 6

Which change is generally the safest schema evolution?

A. Changing a monetary field from dollars to kilograms

B. Renaming a required field without notice

C. Adding an optional field while preserving existing meanings

D. Changing an integer identifier into an unrelated date

Correct Answer: C. Adding an optional field while preserving existing meanings

Question 7

A dataset has 9,800 non-null required values out of 10,000 required values. What is its required-field completeness rate?

A. 2%

B. 9.8%

C. 98%

D. 99.8%

Correct Answer: C. 98%

Question 8

If a pipeline’s sustained arrival rate is greater than its sustained processing rate, what normally happens?

A. The backlog grows

B. The backlog must become zero

C. All records are automatically deduplicated

D. Schema compatibility is guaranteed

Correct Answer: A. The backlog grows

Question 9

What is the primary purpose of a dead-letter queue?

A. To store successfully processed records permanently

B. To isolate messages that cannot be processed successfully

C. To replace all data-quality rules

D. To increase database normalization

Correct Answer: B. To isolate messages that cannot be processed successfully

Question 10

Which control best helps determine whether a source and target contain equivalent totals?

A. Reconciliation

B. Random renaming of columns

C. Disabling audit logs

D. Removing source timestamps

Correct Answer: A. Reconciliation