Data Sources, Acquisition and Integration

Introduction

Business analytics is only as reliable as the data on which it depends. Analytical models may be sophisticated, but they cannot compensate for incomplete transactions, duplicated events, misunderstood fields, delayed feeds, or improperly joined customer records. Data acquisition and integration therefore constitute a strategic and technical foundation of big data analytics.

An organization’s data is distributed across operational databases, enterprise applications, application logs, websites, mobile devices, social platforms, sensors, partner systems, public datasets, and application programming interfaces (APIs). These sources differ in structure, velocity, ownership, reliability, semantics, and legal status. A useful acquisition workflow must preserve meaning while moving data into an environment where it can be governed, stored, transformed, and analyzed.

This chapter examines the design of acquisition workflows for transactional systems, logs, web and social media, sensors, APIs, and third-party data. It explains ETL and ELT, batch and streaming acquisition, change data capture, messaging, metadata, schema evolution, data quality, integration patterns, governance, and practical architecture.

Learning Objectives

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

1. Classify major data sources according to structure, velocity, ownership, and analytical use.
2. Design acquisition workflows using batch, streaming, ETL, ELT, and change data capture.
3. Explain the roles of messaging systems, metadata, data contracts, and schema evolution.
4. Select suitable integration techniques for heterogeneous data.
5. Evaluate data quality, reliability, security, privacy, and governance risks.
6. Propose an end-to-end acquisition architecture for a business scenario.

1. The Data-Source Landscape

A data source is a system, device, person, or external provider that produces data. Sources can be classified along several dimensions.

Transactional systems

Transactional systems support operational processes such as sales, payments, inventory, banking, and customer service. They commonly use relational databases with tables, primary keys, foreign keys, constraints, and transaction processing. Their data is usually structured and authoritative for operational facts, but the schema is optimized for frequent updates rather than analytical queries.

A sales database may contain Order, OrderLine, Customer, and Product tables. A single business event can therefore be represented across several tables. Integration requires attention to keys, transaction boundaries, late updates, deletions, and slowly changing descriptive attributes such as customer address.

Logs and event records

Application, security, network, and infrastructure logs record events such as login attempts, errors, page requests, and service calls. Logs are often semi-structured, commonly using JSON or key-value formats. They are valuable for observability, fraud detection, cybersecurity, and customer-journey analysis. However, formats may differ between software versions, timestamps may use different time zones, and duplicate or out-of-order records may occur.

Web and social media

Web data includes page views, search activity, clickstream events, forms, and publicly accessible pages. Social data can include posts, comments, reactions, profiles, and engagement events, subject to platform policies and privacy law. Web and social data may be high-volume and unstructured, but apparent availability does not imply unrestricted rights to collect or reuse it. Terms of service, consent, copyright, platform access restrictions, and data minimization must be considered.

Sensors and Internet of Things devices

Sensors generate measurements such as temperature, location, pressure, vibration, or energy consumption. Sensor data is often time-series data and may arrive continuously. A reading is meaningful only with context: device identifier, measurement unit, calibration status, location, timestamp, and sometimes operating conditions. Connectivity failures can create gaps, while device clocks can produce inaccurate event times.

APIs and third-party data

An API provides a defined interface for requesting or submitting data. APIs may be synchronous or asynchronous, paginated, rate-limited, authenticated, and versioned. Third-party datasets may include demographic, geographic, economic, weather, market, or industry information. Their usefulness depends on provenance, update frequency, licensing, coverage, definitions, and service-level commitments.

2. Acquisition Patterns: Batch, Streaming, and Hybrid Workflows

Batch acquisition

Batch acquisition collects data over an interval and processes it together. Examples include a nightly export from an enterprise resource planning system or a daily partner file. Batch processing is simple, economical, and appropriate when decisions do not require immediate information. Its principal limitation is latency.

If a job runs every 24 hours and completes in two hours, the age of a record at availability can vary substantially. A record arriving immediately after a run may wait almost 24 hours, whereas one arriving before the run may be processed quickly. Thus, a stated “daily” schedule does not imply a fixed latency.

Streaming acquisition

Streaming acquisition processes records as they are produced or shortly afterward. It is appropriate for fraud alerts, equipment monitoring, real-time recommendations, and operational dashboards. Streaming systems must handle bursts, failures, duplicates, out-of-order events, and consumers operating at different speeds.

A useful distinction is between event time and processing time. Event time is when an activity occurred; processing time is when the platform handled the record. Analytics based on event time can be more accurate, but require techniques such as watermarks and late-arrival handling.

Hybrid architectures

Many organizations use both patterns. Operational events may be captured continuously into a durable event platform, while historical data is transformed in scheduled batches. A hybrid design can provide low-latency operational views and cost-effective large-scale recomputation.

Figure: A generalized acquisition and integration architecture.

Delivery guarantees

Messaging and ingestion systems often describe delivery as:

• At-most-once: a record is delivered zero or one time; loss is possible, but duplicates are minimized.
• At-least-once: a record is delivered one or more times; duplicates are possible, so consumers need idempotency.
• Exactly-once processing: the intended effect is applied once within a defined system boundary. This is difficult across independent systems and should not be assumed merely because a platform uses the phrase.

Idempotency means that repeating the same operation produces the same final effect. A consumer can achieve it by storing a unique event identifier and rejecting an already-processed identifier, or by using an upsert keyed by a stable business key.

3. ETL, ELT, and Data Movement

Extract, transform, load (ETL) extracts data from sources, transforms it in a processing layer, and loads the result into a target warehouse or mart. ETL is useful when the target requires strict structure, when sensitive fields should be removed before loading, or when transformation infrastructure is separate from analytical storage.

Extract, load, transform (ELT) first loads relatively raw data into a scalable lake or warehouse and transforms it there. ELT supports reprocessing, preserves source detail, and makes use of scalable target compute. It requires strong access control, storage organization, metadata, and controls against creating an unusable “data swamp.”

A robust workflow commonly has three conceptual layers:

1. Raw or landing layer: preserves received data, ingestion time, source identity, and often the original payload.
2. Standardized layer: applies parsing, type normalization, deduplication, validation, and common naming.
3. Curated or serving layer: presents business entities and measures for reporting, analytics, or machine learning.

Keeping raw data does not mean keeping it indefinitely. Retention should reflect legal obligations, analytical value, storage cost, and privacy risk.

4. Change Data Capture and Incremental Acquisition

Extracting an entire source table every day is expensive and can place unnecessary load on an operational system. Incremental acquisition transfers only new or changed records.

Timestamp and high-water-mark extraction

A source may contain an updated_at field. The pipeline stores the greatest successfully processed timestamp, called a high-water mark, and requests records after that point. Because timestamps can have limited precision and transactions can commit out of order, a practical design often uses an overlap window and deduplication.

For example, if the last successful high-water mark is 10:00:00, the next request may begin at 09:59:00. The overlap protects against clock precision and delayed visibility; an idempotent merge removes repeated records.

Log-based change data capture

Log-based CDC reads a database’s transaction log or equivalent change stream. It can capture inserts, updates, and deletes with less impact on application tables than repeated full queries. CDC records should include an operation type, source position, transaction or event identifier, key, changed values or row image, and source timestamp where available.

Snapshot and CDC combination

A common initialization pattern is:

1. Record a consistent source position.
2. Take a snapshot of existing rows.
3. Read changes from the recorded position.
4. Apply the changes after the snapshot, preserving their order where required.

This prevents a gap between the initial extract and the beginning of change capture. The precise method depends on the database and CDC technology.

Deletes and slowly changing dimensions

Deletes are easily missed when an extraction only selects currently existing rows. CDC should represent deletes explicitly, often with a tombstone or operation flag. Analytical models must decide whether to physically remove the record, mark it inactive, or preserve historical existence.

For descriptive entities such as customers, a slowly changing dimension may preserve history. Under a Type 1 approach, the old value is overwritten. Under a Type 2 approach, a new version is inserted with effective-start and effective-end dates and a current-row indicator. Type 2 supports “what was known at the time” analysis but increases complexity and storage.

5. Messaging and Event-Driven Acquisition

A message broker decouples producers from consumers. Producers publish records to topics, queues, or streams; consumers subscribe independently. This decoupling enables multiple uses—for example, fraud detection, inventory updates, and archival storage—without each consumer querying the source system separately.

Important design concepts include:

• Partitioning: divides a stream for parallel processing. A suitable key, such as account ID, can preserve order for records sharing that key.
• Offset or position: identifies a consumer’s location and supports restart or replay.
• Retention: determines how long records remain available for recovery or new consumers.
• Back-pressure: slows or buffers producers when consumers cannot keep up.
• Dead-letter handling: isolates records that repeatedly fail validation or processing.

Ordering is usually limited. A system may preserve order within one partition but not across all partitions. Designers should therefore avoid assuming global order unless it is explicitly guaranteed.

6. Metadata, Data Contracts, and Schema Evolution

Metadata is data about data. It includes technical information such as data types and locations; business definitions such as the meaning of “active customer”; operational information such as freshness and pipeline status; and governance information such as owner, classification, retention, and permitted use.

A data catalog organizes metadata so analysts and engineers can discover and understand assets. Data lineage describes where data originated, how it changed, and which reports or models depend on it. Lineage supports impact analysis, audit, troubleshooting, and regulatory response.

Data contracts

A data contract is an explicit agreement between a producer and consumer about fields, types, meanings, keys, permitted nulls, event identifiers, delivery expectations, and change procedures. It turns informal assumptions into testable expectations. A contract should identify an owner and a versioning policy.

Schema evolution

Schemas change because systems add fields, rename attributes, alter types, or remove obsolete data. Compatibility must be assessed from the perspective of existing producers and consumers.

• Backward compatibility: a new schema can be read by consumers using the previous schema.
• Forward compatibility: data written under the new schema can be interpreted by older consumers, usually by allowing unknown fields to be ignored.
• Full compatibility: both directions are supported under a stated versioning policy.

Generally safer changes include adding an optional field with a documented default. Riskier changes include renaming a field, changing a numeric field to text, changing units, or reinterpreting a code. A field called amount is not sufficiently defined unless currency, scale, sign convention, and timing are known.

Schema registries, versioned API specifications, automated contract tests, and deprecation periods reduce integration failures. Compatibility is not only syntactic: a field can retain its type while changing its business meaning, which is a semantic breaking change.

7. Data Integration Techniques

Key-based integration

Relational integration commonly uses primary and foreign keys. A reliable key is stable, unique within its scope, and consistently represented. Natural keys, such as email addresses, can change or be duplicated; surrogate keys provide internal stability but require mapping to source identifiers.

Entity resolution

Entity resolution links records that refer to the same real-world entity despite spelling differences, missing values, or multiple identifiers. Deterministic matching may use exact government or account identifiers. Probabilistic matching may combine name, address, phone, and date of birth, but creates false-match and missed-match risks. Decisions should be auditable, and sensitive attributes require special protection.

Standardization and normalization

Integration may require standardizing dates, time zones, currencies, units, country codes, and categorical labels. The original value should often be retained alongside the standardized value. Conversion must be explicit: converting Fahrenheit to Celsius, for example, uses C = (F - 32) × 5/9, where F and C are temperatures in degrees Fahrenheit and Celsius. The formula applies under the standard temperature-scale definitions; it is not a generic rescaling of arbitrary measurements.

Joins and grain

Before joining datasets, identify their grain—the level represented by one row. A transaction table may have one row per order line, while a customer table has one row per customer. Joining them is valid only when the key relationship is understood. Aggregating after an unintended many-to-many join can multiply revenue and produce a plausible but incorrect result.

Time alignment

Data sources may use event time, ingestion time, local time, or UTC. Integration should preserve the original timestamp and record the normalized time zone. A time-series join must define a tolerance, such as matching a sensor reading to the nearest valid machine state within a stated interval. Without such rules, temporal relationships remain ambiguous.

8. Data Quality and Operational Reliability

Data quality is multidimensional. Common dimensions include completeness, validity, accuracy, consistency, uniqueness, timeliness, and conformity to business rules.

A simple completeness rate for a field can be calculated as:

The numerator counts records with a non-null value; the denominator is the number of records that should contain the field. The measure is meaningful only when “required” and “expected records” are defined. A field may be complete but inaccurate.

A pipeline should monitor volume, latency, freshness, error rates, duplicate rates, schema changes, null proportions, referential integrity, and distribution shifts. Operational metrics should distinguish source failure from transformation failure and destination failure.

A reliable pipeline typically includes:

• checkpoints and restartable processing;
• immutable or replayable raw data where appropriate;
• idempotent writes;
• quarantine for invalid records;
• reconciliation against source counts or control totals;
• alert thresholds and escalation ownership;
• testing with representative and boundary cases;
• documented recovery-time and recovery-point objectives.

For a batch transfer, a basic reconciliation compares source and target record counts, but equal counts do not prove correctness. Control totals, key comparisons, checksums, and business totals provide stronger evidence.

9. Security, Privacy, and Governance

Acquisition expands the organization’s data footprint and therefore its risk. Security controls should include encrypted transport, encryption at rest, strong authentication, least-privilege access, secrets management, network controls, audit logging, and separation of development and production data.

Privacy governance begins before collection. The organization should identify purpose, lawful basis or applicable authorization, proportionality, consent requirements, retention, data-subject rights, and cross-border restrictions. Personal data should not be collected merely because it is technically accessible. Pseudonymization reduces direct identifiers but does not guarantee anonymity, especially when datasets can be linked.

Governance responsibilities should be assigned. Data owners define business meaning and access expectations; data stewards manage quality and definitions; platform teams operate pipelines; security and privacy teams assess controls; analysts use data within approved purposes.

Third-party acquisition requires due diligence concerning provenance, licensing, permitted uses, update commitments, geographic coverage, bias, and vendor continuity. A low-cost dataset can be expensive if its definitions cannot support defensible decisions.

10. Applied Case: Retail Inventory and Demand Visibility

Background

The following is an illustrative case. A regional retailer operates stores, an e-commerce platform, a warehouse management system, smart shelf sensors, and a customer-service application. Managers complain that dashboards show inconsistent inventory and that replenishment decisions are delayed.

Problem

The point-of-sale database is extracted nightly. E-commerce orders arrive through an API. Warehouse updates are delivered as files every four hours. Shelf sensors emit readings every minute. Customer-service records contain returns and cancellation information. The existing dashboard joins these sources using product descriptions and does not represent cancellations consistently.

Analysis

The data team identifies several risks:

1. Product descriptions are not stable integration keys.
2. Nightly extraction misses same-day sales and corrections.
3. Sensor readings can be duplicated after network recovery.
4. Warehouse files can arrive late or be replaced.
5. The meaning of “available inventory” differs between store, warehouse, and online systems.
6. Returns and cancellations alter net demand but are not modeled as explicit events.

The team defines a business glossary. “On-hand inventory” means physically recorded units; “available-to-promise” means units that can be committed to customers after reservations and operational constraints. These are not interchangeable measures.

Approach and solution

First, a historical snapshot is loaded into a raw storage layer. Product and location master data are integrated using governed surrogate keys mapped to source identifiers. Point-of-sale and order databases use log-based CDC, including inserts, updates, and deletes. The API is acquired using pagination, rate-limit handling, request logging, and a watermark based on the provider’s update time.

Warehouse files are stored with delivery metadata, checksum, schema version, and arrival time. A file is accepted only when its control totals and expected columns pass validation. Sensor data is ingested through a message stream keyed by device or shelf, with event identifiers used for deduplication. Events arriving beyond the normal lateness threshold are placed in a late-event path and included in periodic corrections.

The curated model represents inventory movements as events: sale, receipt, transfer, adjustment, reservation, cancellation, and return. A daily reconciliation compares calculated balances with authoritative operational balances. Dashboards expose freshness and confidence indicators rather than hiding missing data.

Outcome

The retailer can provide near-real-time sales visibility, trace inventory discrepancies to source events, and distinguish physical stock from customer-available stock. The architecture does not eliminate all uncertainty: sensor failures, late warehouse updates, and manual adjustments still require operational review. However, the uncertainty becomes visible and manageable.

Learning points

• Integration begins with business definitions and grain, not with tools.
• Stable identifiers are more valuable than convenient descriptions.
• CDC and event capture reduce latency but require replay, deduplication, and delete handling.
• A raw layer supports audit and correction, while curated models support consistent use.
• Quality and freshness indicators should accompany analytical outputs.

11. Contemporary Developments and Professional Practice

Modern lakehouse architectures combine low-cost object storage with warehouse-like governance and query performance. Data mesh approaches emphasize domain ownership and data products, but they do not remove the need for shared standards, contracts, security, and interoperability. Streaming analytics increasingly combines event processing with historical context, while reverse data movement sends curated insights back into operational systems.

Artificial intelligence can assist with schema matching, anomaly detection, metadata classification, and natural-language discovery. These uses require human review because automated systems may infer incorrect meanings, expose sensitive values, or reinforce biased assumptions. AI-generated mappings should be treated as proposals subject to tests and approval, not as authoritative integration logic.

A professional acquisition design should be evaluated against five questions:

1. Can the organization explain where every important field came from?
2. Can the pipeline recover without silently losing or duplicating data?
3. Are business definitions, keys, units, and time semantics explicit?
4. Are privacy, security, licensing, and retention requirements enforceable?
5. Can the design evolve without breaking dependent consumers?

Chapter Summary

Data acquisition connects diverse operational and external sources to analytical environments. Transactional databases provide structured operational facts; logs, web activity, social platforms, sensors, APIs, and third-party datasets introduce different challenges of scale, structure, reliability, and governance. Batch, streaming, and hybrid patterns should be selected according to latency and recovery requirements.

ETL transforms before loading, whereas ELT loads data before transformation. Raw, standardized, and curated layers support preservation, quality control, and consistent analytical use. Change data capture enables incremental acquisition and must address updates, deletes, ordering, and initialization. Messaging systems provide decoupling, replay, and scalable consumption, but delivery guarantees and ordering must be understood precisely.

Metadata, lineage, data contracts, and schema compatibility make integration understandable and maintainable. Key design, entity resolution, standardization, time alignment, and grain analysis prevent common integration errors. Quality monitoring, idempotency, reconciliation, security, privacy, licensing, and accountable governance are essential professional practices. The central principle is that successful integration preserves both data and meaning.

Key Terms

Data source: A system, device, provider, or process that produces data.

Batch processing: Collecting and processing data at scheduled intervals.

Streaming: Processing data continuously or shortly after production.

ETL: Extract, transform, and load.

ELT: Extract, load, and transform.

Change data capture: Detecting and transferring source inserts, updates, and deletes.

High-water mark: A stored position indicating the greatest successfully processed source value.

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

Idempotency: A property in which repeating an operation produces the same final effect.

Event time: The time at which an event occurred.

Processing time: The time at which a system processes an event.

Metadata: Information describing data, its meaning, origin, quality, or management.

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

Data contract: An agreement specifying a data producer’s structure, meaning, and delivery expectations.

Schema evolution: The controlled modification of a data structure over time.

Entity resolution: Linking records that represent the same real-world entity.

Data grain: The level of detail represented by one record.

Data quality: The degree to which data is fit for its intended use.

Dead-letter record: A record isolated because it cannot be processed successfully.

Pseudonymization: Replacing direct identifiers while retaining a controlled possibility of linkage.

Review Questions

Short Answer Questions

1. Distinguish event time from processing time.
2. What is the principal difference between ETL and ELT?
3. Why are deletes important in change data capture?
4. Define idempotency in a data pipeline.
5. What is the purpose of a data contract?

Long Answer Questions

1. Compare batch, streaming, and hybrid acquisition architectures, including appropriate business applications and risks.
2. Explain how a raw, standardized, and curated data-layer design supports reliable analytics.
3. Describe a safe initialization process combining a source snapshot with change data capture.
4. Discuss how metadata, lineage, and schema evolution practices reduce integration risk.
5. Explain the security, privacy, and governance obligations involved in acquiring third-party data.

Analytical / Application Questions

1. A company joins customer records to transactions using email address and observes unexpectedly high revenue totals. Diagnose the likely integration problem and propose tests and corrective actions.
2. Design an acquisition workflow for a fleet of delivery vehicles that transmits location every 30 seconds but frequently loses connectivity. Address timestamps, duplicates, late events, storage, and monitoring.
3. A partner adds a required field and changes the unit of an existing numeric field without notice. Analyze the consequences and design a data-contract and schema-evolution response.

Multiple Choice Questions with Answers

Question 1

Which acquisition pattern processes records at scheduled intervals rather than continuously?

A. Batch processing

B. Streaming processing

C. Entity resolution

D. Schema evolution

Correct Answer: A. Batch processing

Question 2

What is the main purpose of change data capture?

A. To replace all source tables with unstructured files

B. To transfer inserts, updates, and deletes incrementally

C. To remove the need for source-system keys

D. To guarantee that all events arrive in global order

Correct Answer: B. To transfer inserts, updates, and deletes incrementally

Question 3

A consumer receives the same event twice. Which property helps ensure that the repeated operation does not create a second business effect?

A. Partitioning

B. Pseudonymization

C. Idempotency

D. Pagination

Correct Answer: C. Idempotency

Question 4

Which statement best describes ELT?

A. Data is transformed before it is extracted

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

C. Only streaming data can be processed

D. Data cannot be retained in raw form

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

Question 5

Which metadata capability shows how a field moved from a source system through transformations to a report?

A. Data lineage

B. Rate limiting

C. Back-pressure

D. Dead-letter handling

Correct Answer: A. Data lineage

Question 6

Why can joining an order-line table directly to another many-row-per-order table produce inflated totals?

A. The join may create a many-to-many multiplication of rows

B. The tables are necessarily stored in different file formats

C. Event time is always later than processing time

D. A message broker removes duplicate keys

Correct Answer: A. The join may create a many-to-many multiplication of rows

Question 7

Which change is generally the safest schema evolution under a policy that permits optional fields?

A. Changing a currency field from dollars to euros without notice

B. Renaming a field while leaving consumers unchanged

C. Adding a documented optional field with a default

D. Changing a numeric identifier into an unstructured paragraph

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

Question 8

A sensor sends a temperature of 68 °F. Using C = (F - 32) × 5/9, what is the corresponding Celsius temperature?

A. 10 °C

B. 20 °C

C. 32 °C

D. 68 °C

Correct Answer: B. 20 °C

Question 9

Which practice best protects against a gap between an initial database snapshot and subsequent change capture?

A. Discarding all update records during the snapshot

B. Recording a consistent source position before the snapshot and reading changes from it

C. Using product descriptions as primary keys

D. Processing only records with non-null values

Correct Answer: B. Recording a consistent source position before the snapshot and reading changes from it

Question 10

Which statement about at-least-once delivery is correct?

A. Records may be delivered more than once, so consumers should handle duplicates

B. Records are guaranteed never to be delivered

C. Global ordering is guaranteed across all partitions

D. It eliminates the need for recovery mechanisms

Correct Answer: A. Records may be delivered more than once, so consumers should handle duplicates