5. Data Acquisition and Interchange

Introduction and Context

Data science begins before modelling, visualization, or statistical analysis. It begins with obtaining data that is relevant, sufficiently complete, legally collected, technically interpretable, and suitable for the intended decision. Data acquisition is the process of identifying, accessing, retrieving, and recording data from one or more sources. Data interchange concerns the representation and movement of that data between systems, applications, people, and analytical environments.

A data scientist may receive a comma-separated file from a business department, query a relational database, consume a web API, read a JSON document, collect text from a website, or process events arriving continuously from sensors. These sources differ in structure, reliability, access controls, update frequency, encoding, and legal status. Consequently, acquisition is not merely a programming task. It is an engineering, governance, and analytical activity.

Poor acquisition decisions create downstream problems. An incorrectly decoded file can corrupt names and categories; an ambiguous date format can reverse the order of events; an API can return only the first page of records; and a database join can duplicate observations. A sophisticated model cannot compensate for data that was misunderstood at the point of collection. This chapter develops a systematic approach to acquiring and exchanging structured, semi-structured, and unstructured data using Python and R, while addressing schemas, validation, authentication, security, lawful collection, and emerging streaming practices.

Learning Objectives

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

1. Distinguish structured, semi-structured, and unstructured data and select suitable acquisition methods.

2. Read and write CSV, JSON, XML, Parquet, and spreadsheet data using Python and R.

3. Explain the roles of schemas, metadata, encoding, validation, and provenance.

4. Acquire data from relational databases, web APIs, websites, and streaming systems.

5. Design robust workflows involving authentication, pagination, retries, rate limits, and incremental extraction.

6. Evaluate data quality, security, privacy, ethical, and legal risks during collection.

7. Choose suitable interchange formats for analytical and operational contexts.

From Source Identification to Usable Data

A reliable acquisition workflow begins with a data specification. Before writing code, the analyst should clarify the business or research question, required variables, unit of observation, time period, acceptable latency, expected volume, permitted use, and quality requirements. The unit of observation might be a customer, transaction, web event, image, or sensor reading. Confusing the unit of observation is a frequent source of double counting.

The source is then assessed. Questions include: Who owns the data? How is it generated? Is it authoritative? How often is it updated? Are historical values revised? What access mechanism is provided? Does the source document its fields and limitations? A data catalogue or source register should record the location, owner, refresh schedule, access conditions, schema version, and sensitivity classification.

Acquisition normally follows these stages:

1. Discover and qualify: identify candidate sources and assess relevance, authority, legality, and expected quality.

2. Authenticate and connect: establish a secure connection using credentials, tokens, certificates, or approved public access.

3. Extract: retrieve complete records, preserving source identifiers and retrieval timestamps.

4. Decode and parse: interpret bytes, character encoding, delimiters, nesting, dates, and data types.

5. Validate: test structure, types, ranges, uniqueness, completeness, and relationships.

6. Record provenance: document source, query or endpoint, parameters, software version, and extraction time.

7. Store and exchange: save raw data immutably where appropriate, then produce governed analytical or operational representations.

8. Monitor and maintain: detect schema changes, failed jobs, stale data, and unusual volumes.

This separation between raw, validated, and transformed data is valuable. A raw landing layer preserves evidence of what was received. A validated layer applies documented quality rules. A curated layer presents data in forms optimized for analysis. Keeping these layers distinct improves reproducibility and makes errors easier to investigate.

Data Structures and Interchange Formats

Structured, semi-structured, and unstructured data

Structured data has a regular schema, such as rows and columns in a relational table. It is comparatively easy to query and validate, although regular structure does not guarantee accuracy. Semi-structured data contains organizational markers but allows variable or nested structure. JSON and XML are common examples. Unstructured data lacks a consistent tabular schema and includes free text, images, audio, and video. Such data may require natural language processing, computer vision, or specialized metadata extraction before analysis.

The categories are not absolute. A JSON document may be semi-structured overall but contain a highly structured array of transactions. A spreadsheet may appear structured while containing merged cells, explanatory notes, multiple tables, and formulas. Analysts should therefore inspect actual content rather than relying solely on a file extension.

CSV and delimited text

CSV is widely supported and suitable for simple rectangular data. Its apparent simplicity hides several ambiguities: delimiters may be commas, semicolons, or tabs; quotation rules vary; missing values may be blank, `NA`, `null`, or a special code; and data types are not formally stored. A field containing a comma must be quoted, and a quotation mark inside a quoted field generally requires escaping according to the relevant convention.

Python commonly uses pandas:

```python

import pandas as pd

orders = pd.read_csv(

"orders.csv",

encoding="utf-8",

dtype={"order_id": "string"},

parse_dates=["order_date"],

na_values=["", "NA", "N/A"]

)

orders.to_csv("orders_clean.csv", index=False, encoding="utf-8")

```

In R, the readr package provides explicit and efficient import functions:

```r

library(readr)

orders <- read_csv(

"orders.csv",

col_types = cols(

order_id = col_character(),

order_date = col_date()

),

na = c("", "NA", "N/A")

)

write_csv(orders, "orders_clean.csv")

```

Explicit types are preferable to blind inference when identifiers contain leading zeroes or when numeric-looking codes are not measurements. For large text files, chunked reading permits processing without loading the entire file into memory.

JSON

JSON represents objects as key-value pairs and arrays as ordered collections. It is natural for APIs and nested records. The same field may be absent in one record, null in another, or represented by a different type, which creates analytical difficulties.

```python

import json

with open("events.json", encoding="utf-8") as f:

events = json.load(f)

```

Nested JSON often needs normalization. In pandas, `json_normalize()` can convert nested records into columns, but the analyst must decide how arrays should be expanded. In R, `jsonlite::fromJSON()` and `tidyr::unnest_*()` support analogous operations. Flattening is not a purely technical operation: expanding an array can change the unit of observation and create repeated parent attributes.

XML

XML represents hierarchical data using elements, attributes, namespaces, and text. It remains important in government, finance, healthcare, and enterprise integration. XPath expressions identify parts of an XML document, while parsers such as Python's `ElementTree` or R's `xml2` navigate the tree. XML namespaces must be handled explicitly; otherwise, a correct-looking query may return no records. External entity processing should be disabled or controlled because unsafe XML parsing can expose files or enable server-side requests.

Parquet and columnar storage

Parquet is a binary, column-oriented format designed for analytical workloads. It stores schema information, supports compression, and permits engines to read only required columns. It is generally more efficient than CSV for large datasets, particularly in data lake and distributed processing environments.

```python

df.to_parquet("orders.parquet", index=False)

subset = pd.read_parquet("orders.parquet", columns=["order_id", "amount"])

```

Parquet is not automatically a substitute for every interchange format. It is less convenient for manual inspection and may be unsuitable as a simple interface for non-analytical applications. Compatibility also depends on logical type support, such as timestamps, decimals, and nested fields.

Spreadsheets

Spreadsheets are common operational data sources but often mix data, presentation, formulas, comments, and multiple tables. Import should identify the correct workbook, sheet, header row, skipped notes, formula behavior, and merged-cell effects. A workbook should not be assumed to be a normalized database. Repeated manual edits, hidden rows, and inconsistent labels require inspection and validation.

Schemas, Encoding, and Validation

A schema specifies fields, types, constraints, relationships, and sometimes semantic definitions. A useful schema answers not only whether `amount` is numeric, but also whether it is in a particular currency, whether negative values are permitted, and whether the field is transaction-level or customer-level. Schemas may be enforced at source, documented separately, inferred during ingestion, or expressed through tools such as database DDL, JSON Schema, Avro, or table contracts.

Schema evolution occurs when fields are added, renamed, removed, or retyped. Additive changes may be backward-compatible, while changing an identifier from integer to string can break downstream systems. Production pipelines should compare incoming schemas with an approved contract and route unexpected data for review rather than silently coercing it.

Encoding determines how characters become bytes. UTF-8 is a broadly preferred standard, but legacy files may use Windows-1252, ISO-8859-1, or other encodings. Incorrect encoding can produce replacement characters or corrupt text. A byte-order mark may also affect detection. Encoding should be specified during reading and recorded as metadata. For multilingual data, normalization of Unicode representations may be necessary when comparing names or identifiers.

Validation should occur at several levels:

• Structural: required columns exist and records can be parsed.
• Type: values conform to declared types and formats.
• Domain: values fall within sensible ranges or approved categories.
• Completeness: required fields are not unexpectedly missing.
• Uniqueness: keys are unique where the schema requires uniqueness.
• Relational: foreign keys and parent-child relationships are valid.
• Temporal: timestamps are valid, ordered where appropriate, and assigned a known timezone.
• Statistical: record counts, distributions, and rates do not show unexplained anomalies.

Validation failures should be observable. A pipeline should report rejected records, reasons for rejection, thresholds, and the responsible owner. Silently dropping bad rows undermines auditability and may introduce bias.

Databases and Incremental Extraction

Relational databases provide structured access through SQL, transactions, indexes, constraints, and access privileges. Python applications commonly use DB-API drivers, SQLAlchemy, or pandas database functions; R users may use DBI and database-specific drivers.

```python

from sqlalchemy import create_engine

import pandas as pd

engine = create_engine("postgresql+psycopg2://user:password@host/db")

query = "SELECT order_id, customer_id, amount FROM orders WHERE order_date >= :start"

df = pd.read_sql(query, engine, params={"start": "2025-01-01"})

```

Parameterized queries are essential because they separate SQL instructions from user-supplied values and reduce injection risk. Credentials should not be embedded in source code; environment variables, secret managers, or workload identities are preferable. Least-privilege accounts should have only the permissions required for extraction.

Large queries should select necessary columns, filter at the source, use indexed predicates, and stream or batch results. A full reload is simple but expensive and may repeatedly process unchanged data. Incremental extraction can use a reliable modification timestamp, an increasing key, database change data capture, or a source-provided watermark. Each method has limitations: timestamps may have equal values or late updates, while keys may not capture corrections. A robust design records the extraction state and includes a small overlap window, followed by deduplication when late-arriving records are possible.

Transactions and isolation levels matter when extracting changing tables. Without a consistent snapshot, related tables may be read at different points in time. The appropriate strategy depends on database capability, workload, and acceptable consistency.

Web APIs and Lawful Web Collection

An API is a programmatic interface through which a service exposes data or operations. Before using an API, read its documentation, terms of service, privacy policy, licensing conditions, rate limits, and attribution requirements. An endpoint may require an API key, OAuth access token, signed request, or mutual TLS. Authentication proves identity or authorization; it does not make every requested use lawful.

A robust API client should handle status codes, timeouts, pagination, retries, rate limits, response validation, and logging. Retries should use bounded exponential backoff and should not repeat non-idempotent operations without care. Pagination may be page-number based, offset based, or cursor based. Cursor pagination is often more reliable when records change during collection.

```python

import requests

url = "https://api.example.org/v1/orders"

headers = {"Authorization": f"Bearer {token}"}

params = {"limit": 100, "cursor": None}

records = []

while True:

response = requests.get(url, headers=headers, params=params, timeout=30)

response.raise_for_status()

payload = response.json()

records.extend(payload["data"])

cursor = payload.get("next_cursor")

if not cursor:

break

params["cursor"] = cursor

```

The example is illustrative: production code should also implement rate-limit handling, schema checks, logging that excludes secrets, and a maximum-page or time safeguard. APIs may return successful HTTP responses containing error objects, partial data, or stale cached results, so content validation remains necessary.

Website collection differs from API use. A site may prohibit automated access, require consent, or contain personal and copyrighted material. Collection must respect applicable law, terms, robots directives as an operational signal, access controls, and reasonable request rates. Circumventing authentication, CAPTCHAs, paywalls, or technical restrictions is inappropriate. Public availability does not automatically imply unrestricted reuse. Where a documented API or licensed dataset exists, it is usually preferable to scraping.

Streaming and Event Data

Batch acquisition retrieves a bounded set of records at intervals. Streaming acquisition processes events as they are produced or as a continuously available sequence. Examples include sensor measurements, application logs, payment events, and clickstreams. Streaming systems commonly use producers, brokers or event logs, consumers, partitions, offsets, and schemas.

Important distinctions include event time, when an event occurred, and processing time, when the system handled it. Events can arrive late, out of order, or more than once. Therefore, streaming applications need watermarking, event-time windows, idempotent processing, deduplication keys, and a clear policy for late data. “Exactly once” is a system property with specific boundaries, not a guarantee that errors are impossible.

Schema registries and versioned event contracts reduce coordination problems between producers and consumers. A consumer should tolerate compatible additions but reject dangerous type changes. For sensitive streams, encryption, access control, retention limits, and deletion mechanisms must be designed from the beginning.

Interchange Architecture and Operational Practice

Interchange formats serve different purposes. CSV is accessible and portable; JSON is flexible and API-friendly; XML is expressive and established in document-oriented integration; Parquet is efficient for analytical storage; spreadsheets are familiar but weakly governed. Format selection should consider consumers, volume, nesting, compression, schema enforcement, human readability, and longevity.

A mature acquisition platform commonly includes source connectors, a secure landing area, metadata and cataloguing, validation, orchestration, monitoring, lineage, and governed storage. Automation can schedule extraction, compare schemas, quarantine invalid records, alert on freshness failures, and create data-quality reports. AI-assisted tools may help classify documents, infer fields, or detect anomalies, but inferred meaning must be reviewed. Automated extraction can reproduce bias, misread context, or expose confidential information if deployed without controls.

Illustrative Case Study: Municipal Mobility Data

Background

A city transport authority wants to study bus reliability and publish selected performance indicators. Its sources include GPS events from buses, a relational timetable database, a spreadsheet of route changes, and a public weather API.

Situation and problem

Initial reports show implausibly high delays. Investigation reveals that GPS timestamps use UTC, while timetable times are local; some vehicles transmit duplicate events; route changes are maintained manually in spreadsheets; and the weather API uses cursor pagination. The authority must produce reproducible indicators without exposing passenger information.

Analysis

The team defines the unit of observation as a bus trip at a scheduled stop. GPS events are therefore not directly equivalent to trips and must be matched using vehicle, route, stop, and time. It inventories source owners, update frequencies, schemas, retention rules, and permitted uses. The spreadsheet is identified as a high-risk source because its layout changes without notice.

Approach and solution

Raw GPS events are stored with retrieval time and source event ID. The ingestion process validates required fields, converts timestamps to a documented timezone representation, and removes duplicates using a composite event key. A versioned route-change table is created from the spreadsheet after manual review. Timetable data is extracted incrementally using a modification watermark.

The weather client uses authenticated requests, cursor pagination, timeout handling, rate-limit backoff, and response-schema validation. API credentials are stored in a secret manager. The pipeline joins data only after confirming the expected grain and records unmatched events for investigation. Data-quality checks monitor freshness, duplicate rates, missing route identifiers, and the percentage of trips lacking a valid stop match.

Outcome

The revised process distinguishes late data from genuinely late buses, prevents duplicated GPS events from inflating counts, and preserves a reproducible record of source versions. Public outputs aggregate results sufficiently to reduce re-identification risk. The authority also establishes an owner for each data contract and a review process for route-change spreadsheets.

Learning points

The central lesson is that acquisition and interpretation are inseparable. Timezone conversion, event grain, source versioning, pagination, and privacy controls directly affect the analytical result. A technically successful download is not necessarily a valid dataset.

Risks, Limitations, and Common Mistakes

Common mistakes include relying on file extensions, allowing automatic type inference for identifiers, ignoring encoding, reading only the first API page, treating null and zero as equivalent, using local time without timezone information, and joining tables without checking cardinality. Other failures arise from hard-coded credentials, unbounded retries, unlogged transformations, and replacing raw data with cleaned data so that the original cannot be audited.

Acquisition also has inherent limitations. A source may be incomplete, biased toward users who participate, revised retrospectively, or unavailable during outages. Scraped or volunteered data may not represent the target population. Validation can identify contradictions but cannot prove that a plausible value is true. Data provenance improves accountability, but it does not remove uncertainty.

Ethical, Legal, and Governance Considerations

Data collection should satisfy purpose limitation, data minimization, transparency, appropriate security, and retention principles. Applicable privacy and data-protection laws vary by jurisdiction, but responsible practice requires identifying personal data, sensitive attributes, lawful basis or authorization, access rights, retention periods, and cross-border considerations. De-identification is not automatically irreversible; combinations of quasi-identifiers may permit re-identification.

Researchers and organizations should respect intellectual property, contractual restrictions, sector-specific rules, and public-interest obligations. Consent, where required, must be meaningful and appropriately documented. Governance should assign data owners and custodians, classify sensitivity, maintain lineage, restrict access, and define incident response. Ethical review is especially important when acquisition could affect vulnerable groups or enable surveillance.

Current Developments

Modern data platforms increasingly use lakehouse architectures that combine low-cost object storage with warehouse-like governance and query performance. Table formats support schema evolution, versioning, and transactional updates over files. Data contracts are becoming more important as organizations decentralize data ownership. Privacy-enhancing techniques, federated analysis, synthetic data, and confidential computing seek to reduce unnecessary movement of sensitive records.

Streaming and near-real-time architectures are expanding, but they should be adopted only when decision value justifies their operational complexity. The practical direction is not simply “more data” or “faster data”; it is more reliable, traceable, appropriately governed, and purpose-fit data.

Best Practices

1. Define the analytical purpose, unit of observation, and permitted use before extraction.

2. Prefer documented, stable APIs and database interfaces over uncontrolled scraping.

3. Preserve raw inputs, retrieval metadata, source identifiers, and schema versions.

4. Specify encodings, data types, timezones, delimiters, and missing-value conventions.

5. Validate structure, domains, relationships, completeness, uniqueness, and freshness.

6. Use parameterized queries, least privilege, secure secret storage, encryption, and safe logging.

7. Implement pagination, timeouts, bounded retries, rate-limit compliance, and incremental extraction.

8. Monitor schema changes and quarantine unexpected records rather than silently discarding them.

9. Document transformations, licences, consent or legal basis, retention, and responsible owners.

10. Test acquisition pipelines with representative edge cases, including malformed, late, duplicated, and multilingual data.

Chapter Summary

Data acquisition and interchange provide the foundation for trustworthy data science. Effective practice begins with source qualification and a clear data specification, then proceeds through secure connection, complete extraction, careful parsing, validation, provenance, storage, and monitoring. CSV, JSON, XML, Parquet, spreadsheets, databases, APIs, websites, and streams each have different strengths and risks. Schemas, encoding, timestamps, pagination, authentication, and incremental extraction require explicit treatment. Data quality cannot be separated from ethics, privacy, licensing, and governance. Professional acquisition therefore combines programming technique with systems thinking, legal awareness, operational discipline, and analytical judgement.

Key Terms

Data acquisition: The process of identifying, accessing, retrieving, and recording data for use.

Data interchange: The exchange of data between systems using agreed representations and protocols.

Structured data: Data organized according to a fixed schema, commonly in relational tables.

Semi-structured data: Data with organizational markers but flexible or nested structure, such as JSON or XML.

Unstructured data: Data without a consistent tabular schema, including text, images, audio, and video.

Schema: A formal description of fields, types, constraints, relationships, and meanings.

Schema evolution: The controlled change of a schema over time.

Encoding: The mapping between characters and their binary representations.

Provenance: Information describing a dataset’s origin, processing history, and ownership.

CSV: A delimited text representation of tabular records.

JSON: A lightweight representation of objects, key-value pairs, and arrays.

XML: A hierarchical markup format using elements, attributes, and namespaces.

Parquet: A compressed, column-oriented file format optimized for analytical processing.

API: A defined programmatic interface for accessing data or services.

Pagination: The division of a large API response into multiple retrievable portions.

Watermark: A recorded position, timestamp, or key used to support incremental extraction.

Event time: The time at which an event occurred.

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

Data contract: An agreed specification governing the structure and expectations of exchanged data.

Data lineage: A record of data movement and transformations across systems.

Review Questions

Short Answer Questions

1. What distinguishes structured, semi-structured, and unstructured data?

2. Why should identifiers sometimes be imported as strings rather than numbers?

3. What is the purpose of preserving a raw landing layer?

4. Explain the difference between event time and processing time.

5. Why are parameterized SQL queries important?

Long Answer Questions

1. Describe a complete data-acquisition workflow from source discovery to monitoring.

2. Compare CSV, JSON, XML, Parquet, and spreadsheets for analytical interchange.

3. Explain how schemas, encoding, and validation contribute to reliable ingestion.

4. Discuss the design of a robust API client, including authentication, pagination, retries, and rate limits.

5. Explain the technical, ethical, legal, and governance issues involved in collecting personal data from web sources.

Analytical / Application Questions

1. A CSV file contains customer codes such as `00127`, dates in mixed formats, and the value `-` for missing amounts. Design an import and validation strategy in Python or R.

2. A database table receives late corrections and has no reliable modification timestamp. Compare possible approaches for incremental extraction and recommend a defensible strategy.

3. A streaming application receives duplicate and out-of-order sensor events. Design controls for deduplication, event-time processing, late arrivals, schema evolution, and privacy.

22. Multiple Choice Questions with Answers

Question 1

A data scientist imports product codes such as 00127 from a CSV file. Which approach best preserves their meaning?

A. Infer the field as a floating-point number

B. Import the field explicitly as a string

C. Convert the field to a Boolean

D. Replace leading zeroes before storage

Correct Answer: B. Import the field explicitly as a string

Question 2

Which feature most directly makes Parquet efficient for analytical queries?

A. It requires all values to be stored as plain text

B. It stores data only in spreadsheet-compatible rows

C. It uses column-oriented storage and can read selected columns

D. It prevents schemas from being recorded

Correct Answer: C. It uses column-oriented storage and can read selected columns

Question 3

An API response contains a next_cursor value. What should a complete client generally do?

A. Stop after the first response

B. Repeat the same request indefinitely

C. Use the cursor to request subsequent pages until no cursor remains

D. Convert the response to XML before continuing

Correct Answer: C. Use the cursor to request subsequent pages until no cursor remains

Question 4

Which practice best reduces SQL injection risk during database extraction?

A. Concatenating user input into SQL strings

B. Using parameterized queries

C. Giving the extraction account administrator privileges

D. Disabling database validation

Correct Answer: B. Using parameterized queries

Question 5

Why is preserving raw input valuable in an acquisition pipeline?

A. It guarantees that the source is accurate

B. It eliminates the need for validation

C. It provides an auditable representation of what was received

D. It makes all formats equally efficient

Correct Answer: C. It provides an auditable representation of what was received

Question 6

A GPS event occurred at 10:00 but was processed at 10:07. Which statement is correct?

A. 10:00 is processing time and 10:07 is event time

B. Both times are necessarily identical in a stream

C. 10:00 is event time and 10:07 is processing time

D. Neither time can be used in analysis

Correct Answer: C. 10:00 is event time and 10:07 is processing time

Question 7

Which validation check is specifically designed to detect an invalid relationship between two tables?

A. Character-encoding validation

B. Foreign-key or referential validation

C. File-size validation

D. Delimiter detection

Correct Answer: B. Foreign-key or referential validation

Question 8

A website makes data publicly visible but prohibits automated extraction in its terms. What is the most responsible first action?

A. Circumvent the restriction with multiple accounts

B. Ignore the terms because the data is public

C. Seek permission or use an authorized API or licensed source

D. Increase request frequency to finish quickly

Correct Answer: C. Seek permission or use an authorized API or licensed source

Question 9

Which situation most strongly supports incremental rather than full extraction?

A. A tiny immutable reference table

B. A large table containing a reliable change indicator

C. A source with no stable identifiers or timestamps

D. A one-time manually created file

Correct Answer: B. A large table containing a reliable change indicator

Question 10

What is the principal danger of silently dropping records that fail validation?

A. The file becomes easier to audit

B. The pipeline necessarily becomes faster without consequence

C. Data loss and bias may remain invisible to users

D. The source schema becomes automatically correct

Correct Answer: C. Data loss and bias may remain invisible to users