6. Data Ingestion, Integration and Engineering

Introduction and Context

Data has value only when it can be moved reliably from its sources to the people, applications, and analytical models that use it. In a modern organisation, data is generated by operational databases, enterprise applications, websites, mobile devices, sensors, partner systems, documents, and public platforms. These sources differ in format, speed, ownership, reliability, and business meaning. Data ingestion and integration provide the mechanisms for bringing such data together; data engineering turns it into dependable, usable, and governed data products.

This chapter examines the design of batch and real-time pipelines in cloud and big-data environments. It considers extract-transform-load (ETL), extract-load-transform (ELT), application programming interfaces (APIs), messaging systems, change data capture (CDC), workflow orchestration, data quality, metadata, governance, and reusable data products. The central argument is that a pipeline is not merely a technical route for moving files. It is an operational system with contracts, failure modes, security obligations, observability requirements, and business consequences.

A useful distinction is between a data movement problem and a data product problem. Data movement asks whether records arrive at a destination. Data product engineering asks whether the resulting dataset is understandable, timely, accurate, secure, discoverable, and fit for a stated purpose. Postgraduate data professionals must be able to address both.

Learning Objectives

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

1. distinguish batch, micro-batch, and streaming ingestion;

2. compare ETL and ELT and select an appropriate approach;

3. explain APIs, message brokers, CDC, schemas, and event contracts;

4. design pipelines using reliability, scalability, security, and observability principles;

5. apply data-quality controls and resolve common integration failures;

6. explain the role of orchestration, lineage, metadata, and governance;

7. evaluate a data pipeline in a realistic organisational scenario; and

8. identify emerging practices such as data contracts, event-driven architectures, and data products.

The Pipeline as an Engineered System

A data pipeline normally contains five logical activities: source connection, ingestion, processing, storage, and delivery. These activities need not be separate physical systems, but distinguishing them helps designers reason about responsibilities. Source connection authenticates with a database, API, file system, or event producer. Ingestion captures data and records operational metadata. Processing validates, cleans, joins, enriches, and transforms it. Storage preserves raw and curated representations. Delivery exposes data to dashboards, machine-learning systems, operational applications, or other pipelines.

The pipeline also has non-functional properties. Reliability means that data arrives correctly and recoverably. Scalability means that capacity can grow with volume, velocity, and concurrency. Freshness describes how quickly data becomes available after creation. Durability concerns whether data survives failures. Reproducibility requires that a transformation can be rerun and understood. Security limits access and protects data in transit and at rest. Observability makes pipeline behaviour visible through logs, metrics, traces, alerts, and quality results.

A simple pipeline may be represented as:

Source → Capture → Validate → Transform → Store → Serve → Monitor

In practice, this is not a one-way chain. Failed records may be routed to a quarantine area, late events may update previous results, and corrected source data may trigger backfills. Mature engineering therefore treats pipelines as stateful, recoverable processes rather than scripts that run once.

Batch, Micro-Batch, and Real-Time Ingestion

Batch ingestion collects data over a period and processes it together. A nightly financial reconciliation, for example, may read files generated during the day. Batch systems are relatively simple, efficient for large sequential workloads, and suitable when immediate results are unnecessary. Their principal limitation is latency: a record may wait until the next scheduled run.

Micro-batch processing divides data into small batches, such as one-minute or five-minute windows. It provides lower latency than conventional batch processing while retaining some operational simplicity. Micro-batching is often adequate for business monitoring, periodic recommendations, and operational reporting.

Streaming ingestion processes records continuously or in very small intervals. A message broker receives events and makes them available to consumers. Streaming is appropriate for fraud detection, equipment monitoring, dynamic pricing, and customer interaction systems where delay has business cost. It introduces additional complexity: ordering, duplicates, late events, state management, back-pressure, replay, and exactly-once or effectively-once behaviour must be considered.

The appropriate choice should be driven by a service requirement, not by fashion. A useful design question is: what is the maximum acceptable age of data at the point of use? That requirement should be balanced against cost, source capability, processing complexity, and the consequences of stale or incorrect results.

ETL and ELT

In ETL, data is extracted from sources, transformed in an intermediate processing layer, and loaded into a target repository. Transformation before loading can reduce the volume transferred to the target and can enforce a controlled target schema. It remains useful when the target system has limited processing power, when sensitive fields must be removed early, or when strict transformation logic is required before storage.

ELT loads source data, often in its original or lightly processed form, into a scalable cloud warehouse or lakehouse and performs transformations there. ELT supports exploratory analysis, preserves source evidence, and takes advantage of elastic computing. It requires careful storage governance because raw data may contain sensitive, duplicated, or poorly documented information.

Neither pattern is universally superior. A common modern architecture combines them: immutable raw ingestion is followed by validation and standardisation, after which curated tables are produced inside a warehouse or lakehouse. The choice depends on latency, cost, governance, transformation complexity, source constraints, and the need to reproduce historical results.

Sources and Ingestion Interfaces

Files and Object Storage

CSV, JSON, XML, Parquet, and Avro files remain common exchange formats. File ingestion should record file name, source, arrival time, checksum, schema version, and processing status. Delimiters, character encoding, null representations, date formats, and duplicate file delivery are frequent causes of failure. Columnar formats such as Parquet are generally efficient for analytical workloads because they support column projection and compression.

APIs

An API provides a controlled interface for requesting or submitting data. Robust API ingestion must address authentication, pagination, rate limits, retries, timeouts, response validation, and version changes. A pipeline should not assume that a successful HTTP response means that the content is correct. It should validate status codes, payload structure, record counts, and relevant business fields. Idempotent extraction is important: rerunning a request should not create duplicate records.

Databases and Change Data Capture

A full database extract reads the complete source table, whereas an incremental extract reads only records changed since a known point. Incremental extraction may use update timestamps, monotonically increasing identifiers, transaction logs, or CDC tools. CDC captures inserts, updates, and deletes from database changes and publishes them for downstream processing.

CDC reduces source load and supports near-real-time integration, but it creates semantic questions. An update event may contain only changed columns or a complete row. Deletes may be represented as tombstones. Transactions may involve several tables, so consumers may need transaction identifiers or sequencing information. Schema changes and reordering must also be handled explicitly.

Messaging Systems

A message broker decouples producers from consumers. Producers publish events to topics or queues; consumers read them independently, often at their own pace. Partitioning can increase parallelism, while offsets allow consumers to track progress and replay messages. A queue commonly supports work distribution, whereas a publish-subscribe topic supports multiple independent consumers.

Message delivery semantics are often described as at-most-once, at-least-once, or exactly-once. At-most-once may lose messages but avoids duplicates. At-least-once reduces loss risk but requires consumers to tolerate duplicates. Exactly-once is difficult across system boundaries; many practical designs implement effectively-once processing through stable event identifiers, deduplication, transactions, and idempotent writes.

Schemas, Contracts, and Data Integration

Integration fails when systems agree on the existence of a field but not on its meaning. A field named `customer_id` may refer to a registration number in one system and a marketing identifier in another. Integration therefore requires semantic as well as technical alignment.

A schema defines structure, data types, constraints, and sometimes documentation. Schema evolution occurs when fields are added, removed, renamed, or reinterpreted. Backward-compatible additions are generally safer than changes that invalidate existing consumers. A schema registry can store versions and enforce compatibility rules for events.

A data contract is a formal agreement between a producer and consumers concerning fields, definitions, quality expectations, delivery frequency, privacy classification, and change procedures. Contracts move accountability closer to the source. They do not eliminate change; they make change visible and manageable. Important contract elements include ownership, unique keys, valid ranges, freshness targets, nullability, retention, and escalation arrangements.

Integration may be syntactic, structural, or semantic. Syntactic integration converts formats and encodings. Structural integration aligns schemas and keys. Semantic integration resolves definitions, units, identities, and business rules. Master data management and reference data management are especially relevant when customers, products, locations, or currencies must be represented consistently across systems.

Engineering for Correctness and Recovery

Idempotency and Deduplication

An operation is idempotent when repeating it produces the same intended result as executing it once. Pipelines should use deterministic keys and upsert or merge logic where appropriate. Event identifiers, source transaction identifiers, and ingestion timestamps can support deduplication. Deduplication rules must distinguish a genuine repeated event from two legitimate events with similar values.

Checkpoints, Replay, and Backfills

A checkpoint records how far a pipeline has successfully processed. It may be an API cursor, file manifest, database log position, or message offset. Checkpoints enable recovery without restarting from the beginning. Replay is the deliberate reprocessing of retained input, often after a code correction. A backfill processes historical periods to populate a new dataset or repair an error. Safe backfills require versioned transformations, isolation from current workloads, and clear reconciliation procedures.

Ordering and Time

Event time is when an event occurred; processing time is when the pipeline handled it. They may differ substantially. Streaming applications use watermarks or lateness thresholds to decide when a time window is sufficiently complete. An event that arrives after a result was produced may require an update. Designers should document whether consumers receive corrections and how historical aggregates are recomputed.

Failure Isolation

A malformed record should not necessarily stop an entire feed. Dead-letter queues or quarantine tables can isolate failures for investigation. However, indiscriminate rejection is dangerous: a pipeline may appear healthy while silently discarding important data. Failed records need reasons, source references, retry policies, ownership, and a monitored resolution process.

Workflow Orchestration and Observability

Orchestration coordinates dependent activities, schedules, retries, parameters, and operational states. A directed acyclic graph (DAG) may express that extraction must finish before validation, and validation before publication. Orchestrators also support timeouts, conditional branches, alerts, manual approval, and backfills.

Good orchestration separates business logic from scheduling logic. Transformation code should be testable independently, while the workflow defines when and under what conditions it runs. Retry policies should distinguish transient failures, such as temporary network unavailability, from permanent failures, such as an invalid schema. Repeated retries against a failing source may increase load and worsen the incident.

Observability includes:

• Operational metrics: throughput, latency, processing duration, backlog, failures, and retry counts;
• Data metrics: row counts, null rates, uniqueness, distributions, freshness, and reconciliation totals;
• Logs: structured records of decisions, errors, identifiers, and execution context; and
• Lineage: information about where data originated, how it changed, and which reports or models depend on it.

A service-level objective might state that a daily customer table must be available by 06:00 with at least 99.5 percent successful delivery over a reporting period. Such an objective is more useful than a vague claim that the pipeline is “fast.”

Data Quality and Governance Controls

Data quality is fitness for a defined use. Common dimensions include accuracy, completeness, consistency, validity, uniqueness, timeliness, and integrity. No dataset is simply “high quality” in the abstract; a small degree of missingness may be acceptable for one analysis and unacceptable for regulatory reporting.

Quality checks should occur at multiple stages. At ingestion, validate file structure, schema, encoding, and required metadata. During transformation, test keys, joins, ranges, and business rules. Before publication, test freshness, reconciliation totals, duplicates, and access classification. Quality results should be stored as evidence rather than displayed only as temporary log messages.

Governance establishes accountability and permitted use. It includes ownership, stewardship, cataloguing, lineage, retention, access control, privacy classification, and regulatory compliance. Personally identifiable information should be minimised, encrypted, masked or tokenised where appropriate, and exposed only to authorised roles. Sensitive data should not be copied into development environments without a legitimate and controlled purpose.

A pipeline may be technically successful but governance-inadequate. For example, it may deliver a complete customer dataset while violating purpose limitation or retaining data beyond an approved period. Privacy, security, and quality must therefore be designed into the pipeline rather than inspected only at the end.

Reusable Data Products

A reusable data product is a maintained dataset or data service designed for identifiable consumers. It should have a clear purpose, owner, documentation, schema, quality indicators, freshness expectation, access procedure, and support channel. A product is not simply a table placed in a shared storage location.

Product thinking encourages teams to publish stable, well-defined interfaces rather than forcing every analyst to understand source-system complexity. It also supports domain ownership: the team closest to a business process is often best positioned to define its data. In a distributed model, central platform teams provide infrastructure and standards while domain teams provide meaning and accountability.

A product should expose appropriate levels of detail. Raw data may be retained for engineering and audit purposes, while curated products may use standardised keys, slowly changing dimensions, aggregated measures, or privacy-preserving transformations. Versioning is essential when definitions change.

Illustrative Case Study: Real-Time Retail Inventory Integration

Background

Consider a fictional retail organisation, Northstar Stores, operating physical shops and an online channel. Its inventory data originates in point-of-sale systems, an e-commerce database, warehouse-management software, and supplier feeds. Customers frequently see an item as available online when it has already sold in a store.

Situation and Problem

The existing process performs a nightly full extract. It is reliable for financial reporting but too slow for stock availability. The organisation also discovers inconsistent product identifiers, duplicate supplier files, and occasional negative quantities caused by late returns and corrections. Management wants near-real-time availability without compromising the financial ledger.

Analysis

The financial ledger requires controlled batch reconciliation, whereas customer-facing availability requires low latency. A single pipeline cannot optimise both objectives. The organisation therefore defines two service levels: inventory events should normally be reflected in the availability service within two minutes, while a reconciled financial inventory report must be complete by the following morning.

A data audit identifies product code mappings, source ownership, transaction identifiers, and the meaning of an inventory adjustment. It also shows that point-of-sale updates can be delivered through CDC, while suppliers provide files through a managed transfer service.

Approach and Solution

Northstar introduces an event-driven ingestion layer. Point-of-sale and e-commerce changes are captured through CDC and published with event identifiers, product identifiers, location, quantity delta, event time, and schema version. A product-reference service maps local codes to a governed product key. Supplier files are registered by checksum, validated against a contract, and loaded into a quarantine area when structural checks fail.

A streaming consumer applies idempotent quantity changes to a serving store. It retains offsets and uses event identifiers to prevent duplicate application. Late events generate corrections rather than being discarded. A separate batch process creates a reconciled inventory table from source transactions each night. Differences between the real-time serving view and the reconciled table are reported to inventory stewards.

Orchestration manages supplier-file processing and nightly reconciliation. Monitoring covers event lag, consumer backlog, duplicate rate, rejected records, freshness, and reconciliation differences. Access controls prevent customer-facing applications from viewing supplier pricing or personally identifiable information.

Outcome

The resulting architecture separates low-latency availability from governed financial reporting. It does not claim that the real-time view is always identical to the final ledger; instead, it makes freshness, correction, and reconciliation behaviour explicit. Business users gain a documented inventory product with quality indicators and an escalation route.

Learning Points

The case demonstrates that ingestion architecture follows business service levels. CDC is useful when source changes must be captured efficiently, but it requires identifiers, ordering decisions, and schema governance. Real-time delivery does not remove the need for batch reconciliation. Finally, a data product becomes trustworthy through contracts, observability, ownership, and transparent handling of exceptions.

Common Challenges and Professional Mistakes

A frequent mistake is choosing a streaming platform before defining latency and correctness requirements. Another is treating retries as a complete reliability strategy. Retries without idempotency can create duplicates; retries without back-off can overload a failing dependency.

Other recurring problems include relying on timestamps that are not consistently updated, assuming source schemas will never change, joining on unstable business names, silently dropping malformed records, and failing to distinguish null from zero. Pipelines also become fragile when credentials are embedded in code, transformations are undocumented, or a single engineer is the only person who understands recovery procedures.

Cost is another concern. Continuous processing, excessive data copies, frequent small files, and uncontrolled retention can create substantial cloud expenditure. Cost-aware design includes partitioning, compaction, lifecycle policies, workload scheduling, and monitoring of compute and storage consumption.

Current Developments

Several developments are reshaping data engineering. Lakehouse architectures combine inexpensive object storage with warehouse-like management and analytical performance. Data contracts and automated schema checks are moving quality responsibilities toward producing teams. Data observability platforms increasingly monitor freshness, volume, distribution, lineage, and incidents together.

Event-driven architectures are expanding beyond operational integration into analytics and machine learning. Streaming feature pipelines can support near-real-time models, although they require strict control of training-serving consistency and data leakage. Artificial intelligence can assist with schema mapping, anomaly detection, documentation, and pipeline-code generation, but generated transformations require human review, testing, access controls, and traceability. AI does not remove the need for data ownership or sound engineering judgement.

Best-Practice Design Principles

1. Define business freshness, correctness, and availability requirements before selecting tools.

2. Preserve raw evidence where lawful and useful, while controlling sensitive data and retention.

3. Use explicit schemas, stable identifiers, versioning, and documented data contracts.

4. Design for duplicates, late data, retries, replay, backfill, and partial failure.

5. Make transformations testable, observable, reproducible, and independently deployable.

6. Treat data quality as measurable evidence with named owners and remediation processes.

7. Separate low-latency operational views from authoritative reconciled reporting when their requirements differ.

8. Secure secrets, encrypt sensitive data, apply least privilege, and record access.

9. Publish curated datasets as documented data products rather than undocumented shared tables.

10. Test failure scenarios, schema changes, recovery procedures, and cost behaviour before production release.

Chapter Summary

Data ingestion captures information from files, APIs, databases, and event systems; integration aligns its structure and meaning; engineering makes the resulting pipelines reliable and maintainable. Batch, micro-batch, and streaming designs should be selected according to service requirements. ETL transforms before loading, while ELT uses scalable target platforms for transformation; hybrid approaches are common.

APIs require pagination, rate-limit handling, validation, and idempotent extraction. CDC and messaging support efficient, near-real-time integration but introduce concerns about ordering, duplicates, deletes, replay, and schema evolution. Data contracts, orchestration, checkpoints, dead-letter handling, observability, and quality controls provide the foundation for dependable operation. Governance adds ownership, lineage, privacy, security, retention, and permitted-use controls.

A reusable data product is more than a stored table: it has a purpose, owner, contract, documentation, quality evidence, freshness expectation, and support process. Professional data engineers design for failure and recovery, distinguish operational views from reconciled reporting, and balance technical capability with business value, risk, and cost.

Key Terms

Batch ingestion: Collecting and processing data as a scheduled group.

Micro-batch: Processing data in small, frequent batches to reduce latency.

Streaming ingestion: Continuous or near-continuous processing of individual events or small event groups.

ETL: Extracting, transforming, and then loading data into a target system.

ELT: Extracting and loading data before transforming it in the target environment.

API: A defined software interface through which systems request or exchange data.

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

Message broker: Infrastructure that receives, stores, and distributes messages between producers and consumers.

Idempotency: The property that repeating an operation does not create an unintended additional effect.

Schema: A description of data structure, types, constraints, and relationships.

Data contract: An agreement specifying a dataset’s meaning, structure, quality, ownership, and change expectations.

Checkpoint: A recorded processing position used to resume a pipeline after interruption.

Backfill: Reprocessing historical data to populate or correct a dataset.

Dead-letter queue: A location for messages or records that cannot be processed normally.

Orchestration: Coordinating pipeline tasks, dependencies, schedules, retries, and states.

Data lineage: Information about data origin, transformations, movement, and downstream use.

Data quality: The degree to which data is fit for a defined purpose.

Data product: A maintained, documented, governed dataset or data service designed for identifiable consumers.

Watermark: A processing indicator used to manage event-time progress and late-arriving data.

Schema evolution: Controlled change to a dataset or event structure over time.

Review Questions

Short Answer Questions

1. What is the main difference between batch and streaming ingestion?

2. Define idempotency and explain why it matters in pipeline recovery.

3. What information should a data contract contain?

4. Distinguish event time from processing time.

5. What is the purpose of a dead-letter queue?

Long Answer Questions

1. Compare ETL and ELT, including the circumstances in which a hybrid approach is appropriate.

2. Explain how CDC and messaging systems support near-real-time integration, and discuss their principal risks.

3. Describe the role of orchestration and observability in operating production data pipelines.

4. Explain how data quality, privacy, security, lineage, and retention form part of data governance.

5. Discuss the design principles required to create a reusable and trustworthy data product.

Analytical / Application Questions

1. A company receives a supplier CSV every hour, but files are sometimes delivered twice and occasionally contain new columns. Design controls for ingestion, deduplication, schema evolution, quarantine, and monitoring.

2. A fraud-detection application needs results within thirty seconds, while regulatory reports require reconciled daily totals. Recommend an ingestion and processing architecture and justify the separation of workloads.

3. A streaming pipeline shows no technical errors, but its output contains increasing duplicate transactions and stale aggregates. Diagnose possible causes and propose a recovery and prevention plan.

22. Multiple Choice Questions with Answers

Question 1

A company needs customer-facing inventory updates within two minutes but also requires an authoritative daily financial reconciliation. Which design is most appropriate?

A. Use only a nightly full batch process

B. Use only an approximate streaming view and discard historical data

C. Use a streaming availability pipeline alongside a separate reconciled batch process

D. Transform all records manually before publication

Correct Answer: C. Use a streaming availability pipeline alongside a separate reconciled batch process

Question 2

Which feature most directly prevents a retry from applying the same business event twice?

A. A larger message payload

B. An idempotent operation using a stable event identifier

C. A longer processing delay

D. Removing checkpoints

Correct Answer: B. An idempotent operation using a stable event identifier

Question 3

In an ELT architecture, where are transformations primarily performed?

A. At the source before extraction

B. In the target warehouse, lake, or lakehouse

C. Only inside the message broker

D. Only in the user interface

Correct Answer: B. In the target warehouse, lake, or lakehouse

Question 4

A consumer receives an event with a transaction identifier and a delete marker. Which integration technology most plausibly produced this information?

A. Change data capture

B. A static dashboard export

C. A password manager

D. A presentation file

Correct Answer: A. Change data capture

Question 5

Why is a data contract valuable in a distributed data environment?

A. It guarantees that source data can never change

B. It replaces all data-quality testing

C. It documents structure, meaning, quality expectations, ownership, and change procedures

D. It eliminates the need for access controls

Correct Answer: C. It documents structure, meaning, quality expectations, ownership, and change procedures

Question 6

A pipeline repeatedly retries an invalid schema and increases load on the source system. What is the best engineering response?

A. Retry indefinitely without logging

B. Classify the failure as permanent, quarantine the input, and alert its owner

C. Delete the source schema

D. Ignore the failure and publish partial data without indication

Correct Answer: B. Classify the failure as permanent, quarantine the input, and alert its owner

Question 7

Which metric is primarily a data-quality metric rather than an operational throughput metric?

A. Consumer backlog

B. Processing duration

C. Null rate in a required field

D. Number of worker processes

Correct Answer: C. Null rate in a required field

Question 8

Why may event-time processing need watermarks?

A. To encrypt messages at rest

B. To decide how processing handles progress and late-arriving events

C. To replace all source identifiers

D. To increase API pagination size

Correct Answer: B. To decide how processing handles progress and late-arriving events

Question 9

Which characteristic best distinguishes a data product from an undocumented shared table?

A. It is always stored in a spreadsheet

B. It has an owner, defined purpose, contract, documentation, and quality expectations

C. It contains only raw data

D. It cannot be accessed by applications

Correct Answer: B. It has an owner, defined purpose, contract, documentation, and quality expectations

Question 10

A pipeline successfully transfers records but violates approved retention and exposes personal information to unauthorised analysts. What does this demonstrate?

A. Technical success always implies governance compliance

B. Governance, privacy, and security must be designed into the pipeline

C. Streaming is always safer than batch

D. Data quality concerns only numerical accuracy

Correct Answer: B. Governance, privacy, and security must be designed into the pipeline