Data Acquisition and Integration

Introduction

Business analytics begins before analysis. A model, dashboard, or management report is only as reliable as the data on which it is based. Data acquisition is the process of obtaining data from internal and external sources, while data integration combines those sources into a consistent analytical dataset. In Python, this work commonly involves files, databases, web APIs, and approved web sources.

Data acquisition is not simply a technical download task. An analyst must determine whether the source is authoritative, whether its use is permitted, how frequently it changes, how fields are defined, and whether the resulting data is complete and accurate. Integration introduces additional issues, including different data types, naming conventions, time zones, identifiers, duplicate records, missing values, and conflicting business definitions.

This chapter presents practical methods for acquiring and integrating data in Python. It also introduces SQL connectivity, authentication, rate limits, data contracts, validation, governance, and ethical use of external information.

Learning Objectives

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

1. Explain the role of data acquisition and integration in the analytics lifecycle.
2. Read and write CSV, Excel, and JSON data using Python.
3. Connect Python to relational databases and execute parameterised SQL queries.
4. Acquire data from web APIs and approved web sources responsibly.
5. Manage authentication, pagination, rate limits, retries, and failures.
6. Identify integration-quality problems involving keys, types, dates, duplicates, and missing values.
7. Design a basic, documented, and auditable acquisition pipeline.

1. The Acquisition and Integration Lifecycle

A professional acquisition process usually contains the following stages:

1. Define the information requirement. Identify the business question, required fields, reporting period, level of detail, and acceptable latency.
2. Identify and assess sources. Consider ownership, authority, accessibility, cost, update frequency, licensing, and expected quality.
3. Acquire the data. Obtain files, query databases, call APIs, or retrieve approved web content.
4. Profile the data. Inspect columns, data types, ranges, missingness, duplicates, and relationships.
5. Standardise and integrate. Align names, types, units, dates, identifiers, and reference data.
6. Validate and reconcile. Compare totals and record counts with trusted controls.
7. Store and document. Preserve raw inputs, transformed outputs, metadata, and processing logs.
8. Monitor and maintain. Detect schema changes, stale sources, authentication failures, and quality deterioration.

A useful distinction is between raw, staged, and curated data. Raw data is retained as received. Staged data has undergone technical cleaning, such as type conversion. Curated data has been shaped for a defined analytical purpose and should have documented business rules.

2. Common Data Sources

A source should be selected because it is appropriate for the decision, not merely because it is easy to download. For example, a manually edited spreadsheet may be suitable for a small monthly planning process but unsuitable as the permanent source for a high-volume financial system.

3. Working with CSV Files

Comma-separated values files store records as text rows and fields separated by a delimiter. Despite their simplicity, CSV files do not inherently store data types, primary keys, units, or relationships.

import pandas as pd

sales = pd.read_csv(
    "data/sales.csv",
    encoding="utf-8",
    na_values=["", "NA", "N/A", "null"]
)

print(sales.head())
print(sales.dtypes)

Important parameters include sep for a non-comma delimiter, usecols to restrict columns, dtype to preserve identifiers as text, and parse_dates for date columns. An account number such as 001245 must not be read as an integer, because the leading zeros are meaningful.

customers = pd.read_csv(
    "customers.csv",
    dtype={"customer_id": "string", "postcode": "string"}
)

Writing data requires care about indexes and reproducibility:

sales.to_csv("outputs/sales_clean.csv", index=False)

CSV quality checks should include delimiter detection, encoding, expected column names, record counts, blank lines, quotation marks, and whether decimal separators match the expected convention.

4. Working with Excel Workbooks

Excel files may contain several worksheets, formulas, formatting, explanatory text, and multiple tables. A worksheet is not automatically a clean database table.

import pandas as pd

workbook = pd.ExcelFile("data/operations.xlsx")
print(workbook.sheet_names)

orders = pd.read_excel(
    "data/operations.xlsx",
    sheet_name="Orders",
    dtype={"order_id": "string"}
)

If a worksheet contains a title above the header, use skiprows; if a named range or fixed region is known, restrict the import. Analysts should determine whether formulas are being read as calculated values or formula text, and should record the workbook version and extraction date.

orders.to_excel("outputs/orders_clean.xlsx", index=False)

Excel is often a valuable operational input but a weak system of record. Business users may overwrite values, change column names, insert blank rows, or add notes inside the data region. A controlled template, validation rules, and version history reduce these risks.

5. Working with JSON

JSON represents objects, arrays, strings, numbers, Boolean values, and nulls. It is common in web APIs and may be nested.

import json

with open("data/customer.json", encoding="utf-8") as file:
    document = json.load(file)

print(document.keys())

For tabular JSON, pandas can often normalise records directly:

import pandas as pd

transactions = pd.json_normalize(document["transactions"])

Nested objects may require explicit flattening. For example, a customer object containing an address object can produce columns such as address.city and address.country. Arrays create a design decision: retain them as nested values, create a separate child table, or expand them into repeated rows. The correct choice depends on the analytical grain.

The grain is the level represented by one row, such as one order, one order line, or one customer-day. Integration errors frequently occur when data at different grains is joined without aggregation. Joining one customer row to several order-line rows is valid if the intended result is order-line grain; it is not valid if the analyst later interprets the repeated customer values as independent customers.

6. Relational Databases and SQL Connectivity

A relational database stores data in tables connected through keys. SQL is used to select, filter, join, aggregate, and update data. Python should normally obtain only the fields and rows required for the analysis rather than extracting an entire production database.

A database connection typically requires a driver, a connection string, credentials, and sometimes network or encryption settings. Credentials should not be embedded in source code.

import os
from sqlalchemy import create_engine, text

engine = create_engine(os.environ["ANALYTICS_DATABASE_URL"])

query = text("""
    SELECT order_id, customer_id, order_date, amount
    FROM sales.orders
    WHERE order_date >= :start_date
      AND order_date < :end_date
""")

with engine.connect() as connection:
    orders = pd.read_sql(
        query,
        connection,
        params={"start_date": "2025-01-01", "end_date": "2025-02-01"}
    )

The query uses parameters rather than string concatenation. This is important for security and correctness. A query such as "... WHERE customer_id = '" + user_input + "'" can permit SQL injection and can fail when input contains quotation marks.

A database join should be based on documented keys:

result = orders.merge(
    customers[["customer_id", "segment"]],
    on="customer_id",
    how="left",
    validate="many_to_one"
)

validate="many_to_one" asserts that each customer identifier occurs at most once in the customer table. Such assertions turn silent data problems into visible errors.

A database may contain sensitive or operationally critical data. Read-only credentials, least-privilege permissions, query timeouts, transaction controls, and workload scheduling are important professional practices. Analysts should avoid unfiltered queries during peak operational periods.

7. Web APIs

An application programming interface (API) provides a defined method for one software system to request data from another. A typical HTTP API request contains an endpoint, method, parameters, headers, and possibly a request body. The response commonly includes a status code, headers, and JSON content.

import requests

response = requests.get(
    "https://api.example.org/v1/products",
    params={"category": "office", "limit": 100},
    timeout=30
)
response.raise_for_status()
data = response.json()

A robust API client should account for:

• Authentication: API keys, OAuth access tokens, signed requests, or service accounts.
• Authorisation: whether the identity is permitted to access a particular resource.
• Pagination: retrieving all pages when a response is limited.
• Rate limits: restrictions on requests per minute, hour, or billing period.
• Timeouts and retries: temporary network failures should be handled without creating duplicate actions.
• Versioning: endpoint and field changes may affect downstream code.
• Schema validation: a successful HTTP response does not guarantee correct business content.

A simple page-based pattern is:

import time
import requests

rows = []
for page in range(1, 6):
    response = requests.get(
        "https://api.example.org/v1/orders",
        params={"page": page, "page_size": 100},
        headers={"Authorization": f"Bearer {token}"},
        timeout=30
    )
    response.raise_for_status()
    payload = response.json()
    rows.extend(payload["items"])
    if not payload.get("next_page"):
        break
    time.sleep(0.5)

The example is illustrative; the actual pagination fields depend on the API specification. A production implementation should respect the provider's stated limit rather than assuming that a fixed delay is sufficient. When a server returns a Retry-After instruction, the client should follow it. Exponential backoff is commonly used for transient failures, but retries should not be applied blindly to permanent errors such as invalid credentials.

An API response may contain an HTTP success status while reporting an application-level error in its JSON body. Both transport-level and content-level checks are therefore required.

8. Approved Web Sources

Web pages are designed primarily for human viewing, not necessarily for stable data extraction. Before collecting data, confirm that the activity is permitted by the website's terms of use, robots guidance where relevant, applicable law, copyright conditions, and organisational policy. Do not bypass authentication, access controls, paywalls, or technical restrictions.

Prefer an official API, downloadable dataset, or open-data portal over HTML extraction. If HTML extraction is approved, use stable selectors and document the source URL, retrieval time, extraction method, and assumptions.

import pandas as pd

tables = pd.read_html("https://www.example.org/public-report")
public_table = tables[0]

This approach may fail when a page is dynamically rendered, redesigned, or protected. It can also capture a visually similar table with a changed meaning. Web-derived data therefore requires stronger provenance and validation than a controlled database extract.

9. Integration Quality and Data Contracts

Integration quality concerns whether data remains fit for its intended use after acquisition. Key dimensions include:

• Completeness: required records and fields are present.
• Validity: values conform to permitted formats and ranges.
• Accuracy: values represent the real-world event correctly.
• Consistency: the same concept has compatible values across sources.
• Uniqueness: records are not unintentionally duplicated.
• Timeliness: data is sufficiently current for the decision.
• Integrity: relationships between entities remain valid.
• Traceability: data can be linked to its source and transformation history.

A data contract specifies expected fields, types, allowed values, key rules, refresh frequency, ownership, and failure handling. For example, an order contract may state that order_id is a non-null string and unique within the source, amount is a non-negative decimal in the stated currency, and order_date is an ISO-formatted date.

Basic profiling can be performed in pandas:

print(orders.shape)
print(orders.isna().sum())
print(orders["order_id"].duplicated().sum())
print(orders["amount"].describe())

Dates should be parsed explicitly and time zones considered:

orders["order_date"] = pd.to_datetime(
    orders["order_date"], utc=True, errors="coerce"
)

errors="coerce" converts invalid values to missing values. This avoids a crash but does not solve the problem; the number and identity of coerced records must be reported and investigated.

Keys and joins

A primary key identifies a row uniquely. A foreign key refers to a key in another table. A natural key has business meaning, such as an email address, whereas a surrogate key is generated for system purposes. Natural keys can change or be duplicated; surrogate keys require reliable mapping to business entities.

Join errors are especially dangerous because they may produce plausible totals. Analysts should compare row counts before and after joins, inspect unmatched keys, and use validation options. A left join retains all rows from the left table; an inner join retains only matching rows; an outer join exposes records found in only one source.

Units, currencies, and dates

Values such as revenue require a currency and tax interpretation. Quantities require units. Dates may represent transaction time, shipment time, or reporting time. A timestamp without a time zone can be ambiguous when systems operate across regions. These attributes belong in the data dictionary, not merely in an analyst's memory.

10. A Reproducible Integration Pattern

A small but professional pipeline separates acquisition from transformation and output:

from pathlib import Path
from datetime import datetime, timezone
import pandas as pd

RAW = Path("data/raw")
OUT = Path("data/curated")
OUT.mkdir(parents=True, exist_ok=True)

source = RAW / "orders.csv"
orders = pd.read_csv(source, dtype={"order_id": "string"})

required = {"order_id", "customer_id", "order_date", "amount"}
missing = required - set(orders.columns)
if missing:
    raise ValueError(f"Missing columns: {missing}")

orders["order_date"] = pd.to_datetime(
    orders["order_date"], errors="coerce", utc=True
)
if orders["order_id"].isna().any():
    raise ValueError("Order identifiers cannot be missing")

orders["amount"] = pd.to_numeric(orders["amount"], errors="coerce")
orders.to_parquet(OUT / "orders.parquet", index=False)

print("Processed at", datetime.now(timezone.utc).isoformat())

A production pipeline would additionally record source file hashes, row counts, rejected records, schema versions, processing duration, and responsible owner. Raw data should generally be immutable so that a result can be reproduced.

11. Authentication, Security, and Governance

Authentication establishes identity; authorisation determines permitted actions. These concepts should not be confused. Good practice includes:

• store secrets in environment variables or an approved secret manager;
• never commit passwords, tokens, or private keys to source control;
• use short-lived tokens where available;
• restrict database permissions to required tables and operations;
• encrypt data in transit and at rest where required;
• mask or minimise personal data;
• log access and extraction events without exposing secrets;
• define retention and deletion rules;
• review vendor terms, licensing, and data-processing obligations.

Personal data requires particular care. The analyst should collect only what is necessary, establish a lawful and documented purpose, restrict access, and avoid using identifiable data in examples or shared notebooks. De-identification is not automatically irreversible; combinations of attributes can sometimes re-identify individuals.

12. Applied Case: Integrating Retail Orders

This is an illustrative case. A regional retailer wants a weekly profitability dashboard. Orders are stored in a PostgreSQL database, product costs are maintained in an Excel workbook, and exchange rates are provided through an approved financial API.

Background and problem

The existing dashboard reports sales but not margin. Managers suspect that products sold in different currencies are being compared incorrectly. The three sources use different identifiers and refresh schedules.

Analysis

The analyst establishes the required grain as one order line. The order database contains order_id, product_code, quantity, net_sales, currency, and order_timestamp. The workbook contains product cost by product code, but includes duplicate product codes from an obsolete sheet. The API returns daily exchange rates in JSON and limits requests to 100 days per call.

Approach and solution

First, the analyst extracts only the required order columns using a parameterised SQL query. The workbook is imported from the controlled Current Costs sheet, and duplicate product codes are rejected rather than silently deduplicated. The API is called once per required date range, with authentication stored outside the code and the response saved in raw form.

The pipeline then standardises timestamps to UTC, confirms that quantity is numeric and non-negative, maps currencies to ISO codes, and joins costs using product_code. Exchange rates are joined by currency and calendar date. Records with no matching rate are placed in an exception report. The pipeline checks that order-line counts do not change unexpectedly and reconciles total sales to the source database.

If net_sales is already expressed in the transaction currency, a converted value can be calculated as:

Here, sales_base is sales in the reporting currency, sales_local is sales in the transaction currency, and r_local→base is the number of base-currency units per local-currency unit. The rate convention must be verified; using its reciprocal would produce an incorrect result.

Outcome

The dashboard now shows margin by product and region, while an exception table identifies missing costs, rates, and invalid identifiers. Managers can distinguish genuine margin changes from currency-conversion errors. The retained raw files and processing log allow the weekly result to be audited.

Learning points

The central lesson is that integration quality depends on business definitions as much as on code. Correct joins require a known grain, stable keys, compatible units, and explicit handling of exceptions. A successful download is not evidence of a successful integration.

13. Challenges and Limitations

Data sources change. Columns may be renamed, APIs may deprecate endpoints, websites may redesign pages, and business systems may alter definitions without notice. A pipeline that worked last month may therefore fail or, more dangerously, continue while producing subtly different results.

Other limitations include incomplete historical data, undocumented manual corrections, sampling bias in public sources, vendor outages, regional access restrictions, and costs associated with high-volume extraction. Automated validation reduces risk but cannot prove that a value is factually correct. Human review and communication with data owners remain necessary.

AI-assisted coding can help generate import statements, explain errors, and suggest validation tests. However, generated code may mishandle credentials, ignore licensing, use an incorrect endpoint, or make unsafe assumptions about schema and units. Analysts remain responsible for reviewing code, protecting data, testing edge cases, and documenting decisions.

Best-Practice Checklist

Before declaring an integration complete, ask:

• Is the business purpose and row grain explicit?
• Is each source approved, authoritative, and appropriately licensed?
• Are credentials protected and permissions limited?
• Are pagination, rate limits, timeouts, and retries handled?
• Are keys, types, dates, currencies, and units standardised?
• Are duplicates, missing values, and unmatched joins measured?
• Are source totals reconciled to output totals?
• Are raw inputs, code versions, logs, and exceptions retained?
• Can another analyst reproduce the result?
• Are privacy, retention, and access requirements satisfied?

Chapter Summary

Data acquisition obtains information from files, databases, APIs, and approved web sources; data integration makes those inputs analytically consistent. CSV, Excel, and JSON require explicit attention to structure and types. Database access should use least privilege, parameterised SQL, and controlled queries. API work requires authentication, pagination, rate-limit compliance, retries, and schema validation. Web extraction should be a permitted last resort, with strong provenance.

Reliable integration depends on grain, keys, data types, dates, units, currencies, and documented business rules. Profiling and validation should measure completeness, validity, uniqueness, consistency, timeliness, and referential integrity. Secure credential management, privacy protection, licensing, audit trails, and reproducibility are professional responsibilities. The quality of an analytical result depends not only on the model but also on the integrity of the acquisition pipeline that supplies it.

Key Terms

Data acquisition: The process of obtaining data from one or more sources.

Data integration: Combining data from different sources into a consistent analytical structure.

CSV: A text-based, delimited file format for tabular records.

JSON: A structured text format representing objects, arrays, and primitive values.

API: An interface that enables software systems to exchange requests and data.

Endpoint: A specific API address representing a resource or operation.

Authentication: The process of establishing the identity of a user or system.

Authorisation: The process of determining permitted actions or resources.

Pagination: Dividing a large API result into multiple responses or pages.

Rate limit: A restriction on the number or frequency of requests.

Primary key: A field or combination of fields that uniquely identifies a row.

Foreign key: A field that refers to a key in another table.

Grain: The level of detail represented by one row in a dataset.

Data contract: A documented agreement describing expected data structure and quality.

Schema: The defined structure, fields, types, and relationships of data.

Data provenance: Information about a dataset’s origin, transformations, and history.

Parameterised query: SQL containing separate parameters rather than concatenated user input.

Referential integrity: The condition that relationships between related records remain valid.

Idempotence: The property that repeating an operation produces the same intended result without harmful duplication.

Data profiling: Systematic inspection of structure, quality, distributions, and anomalies.

Review Questions

Short Answer Questions

1. What is the difference between raw, staged, and curated data?
2. Why should an account number with leading zeros be stored as text?
3. Distinguish authentication from authorisation.
4. What is meant by the grain of a dataset?
5. Why are parameterised SQL queries preferred to string concatenation?

Long Answer Questions

1. Explain the main stages of a professional data acquisition and integration lifecycle.
2. Compare CSV, Excel, JSON, relational databases, and APIs as business data sources.
3. Discuss the quality risks associated with joining datasets from different systems.
4. Explain how authentication, rate limits, pagination, retries, and versioning affect API integration.
5. Describe the governance and privacy controls that should apply to an analytics acquisition pipeline.

Analytical / Application Questions

1. A customer table contains duplicate customer identifiers, while an order table contains one row per order. Design validation checks before joining the tables and explain the likely consequences of an uncontrolled join.
2. An API returns 10,000 records but the business expects 12,500. Develop a diagnostic procedure involving pagination, date boundaries, rate limits, duplicate detection, and reconciliation.
3. Design an integration pipeline for a retailer using a database for orders, Excel for product costs, and an API for exchange rates. Specify the data grain, key fields, quality checks, security controls, and exception-handling process.

Multiple Choice Questions with Answers

Question 1

Which source most directly supports structured, parameterised retrieval from related tables?

A. A relational database

B. A formatted presentation

C. A plain-text memo

D. An image file

Correct Answer: A. A relational database

Question 2

Why should an identifier such as 001245 usually be imported as text?

A. Text uses less storage than every numeric type

B. Leading zeros may be meaningful and would be lost as a number

C. Numeric values cannot be exported

D. Text automatically validates the identifier

Correct Answer: B. Leading zeros may be meaningful and would be lost as a number

Question 3

What is the main purpose of API pagination?

A. To encrypt credentials

B. To convert JSON into Excel

C. To retrieve results split across multiple responses

D. To remove duplicate database keys

Correct Answer: C. To retrieve results split across multiple responses

Question 4

Which practice best reduces SQL injection risk?

A. Concatenating user input into SQL strings

B. Using parameterised queries

C. Removing all WHERE clauses

D. Storing passwords in the query text

Correct Answer: B. Using parameterised queries

Question 5

What does a many-to-one join validation assert in the illustrated pandas pattern?

A. Every left row must have a unique left key

B. The right-side key occurs at most once for each matching value

C. Both tables must contain exactly the same rows

D. No rows may be missing from either table

Correct Answer: B. The right-side key occurs at most once for each matching value

Question 6

Which dimension asks whether required records and fields are present?

A. Completeness

B. Uniqueness

C. Timeliness

D. Accuracy

Correct Answer: A. Completeness

Question 7

What should an API client normally do when a server supplies a Retry-After instruction?

A. Immediately repeat the request continuously

B. Ignore the response and change the endpoint

C. Wait for the specified period before retrying

D. Delete the authentication token

Correct Answer: C. Wait for the specified period before retrying

Question 8

Which item is most important when joining exchange rates to transaction data?

A. The font used in the source workbook

B. The rate convention, currency, and applicable date

C. The number of worksheet colours

D. The order in which columns appear

Correct Answer: B. The rate convention, currency, and applicable date

Question 9

What is the principal role of raw-data retention in a pipeline?

A. To guarantee that all source values are accurate

B. To preserve the received input for audit and reproducibility

C. To eliminate the need for validation

D. To make credentials public

Correct Answer: B. To preserve the received input for audit and reproducibility

Question 10

Which action is most appropriate before extracting data from a public website?

A. Bypass access controls if the data is useful

B. Confirm permission, terms, licensing, and organisational policy

C. Assume every public page permits automated extraction

D. Remove the source URL from documentation

Correct Answer: B. Confirm permission, terms, licensing, and organisational policy