Business Data Acquisition and Integration

Introduction

Business analytics begins before modelling. An analyst must obtain data from appropriate sources, determine what the data means, combine records correctly, and preserve evidence about how the dataset was produced. These activities are collectively called data acquisition and integration.

A typical organization stores information in transactional databases, spreadsheets, CSV files, JSON documents, application programming interfaces (APIs), cloud warehouses, and software-as-a-service platforms. These sources differ in structure, access method, reliability, update frequency, and legal restrictions. Python provides a common working environment for connecting to these sources, inspecting their contents, transforming records, and loading integrated datasets for analysis.

Integration is not simply a technical joining operation. A dataset may be syntactically valid but analytically misleading because customer identifiers differ, dates use incompatible time zones, currencies are mixed, or duplicate transactions exist. Effective analysts therefore treat acquisition as a combination of programming, data modelling, governance, and critical evaluation.

Learning Objectives

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

• acquire data from CSV, spreadsheet, JSON, API, database, and cloud sources;
• distinguish structured, semi-structured, and unstructured data;
• use Python libraries and SQL to retrieve and combine business data;
• design and validate schemas and keys;
• identify problems involving types, missing values, duplicates, units, and time;
• document metadata, lineage, and data provenance;
• apply access-control, privacy, security, and ethical principles; and
• evaluate the limitations and risks of an integrated analytical dataset.

1. Data Sources and Acquisition Patterns

1.1 Structured, semi-structured, and unstructured data

Structured data follows a predefined tabular schema. Relational database tables and many CSV files are examples. Each row usually represents an entity or event, and each column represents an attribute.

Semi-structured data contains organizational markers but does not require every record to have the same fields. JSON and XML are common examples. A JSON document may contain nested objects, arrays, and optional attributes.

Unstructured data does not naturally consist of rows and columns. Examples include emails, PDF documents, images, audio, and free-text reviews. Such data may be converted into structured features through text processing or machine learning, but the conversion introduces interpretation and quality issues.

The source format does not by itself determine data quality. A carefully governed spreadsheet can be more useful than a poorly designed database table. Nevertheless, format affects how an analyst must inspect, validate, and process the data.

1.2 CSV files

Comma-separated values files are portable and widely used, but they have no universal built-in specification for data types, keys, constraints, or metadata. Important questions include:

• Is the delimiter a comma, semicolon, or tab?
• Are text values enclosed in quotation marks?
• What encoding is used?
• Which row contains column names?
• Are dates stored as day-month-year or month-day-year?
• Are missing values represented by blanks, NA, -, or another marker?

import pandas as pd

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

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

An analyst should inspect a file before relying on it. head(), info(), describe(), isna().sum(), and duplicated().sum() provide a first assessment. Numeric-looking columns may have been read as text because of currency symbols or thousands separators. Explicit conversion is safer than assuming inference is correct.

sales["revenue"] = (
    sales["revenue"].astype("string")
    .str.replace(",", "", regex=False)
    .str.replace("$", "", regex=False)
    .astype("float64")
)

1.3 Spreadsheets

Spreadsheets are useful for manually maintained reference data and small operational datasets. However, merged cells, multiple tables on one worksheet, formulas, hidden rows, formatting-dependent meaning, and inconsistent labels make automated use risky.

products = pd.read_excel("product_master.xlsx", sheet_name="Products")

A professional workflow preserves the original workbook, identifies the worksheet and cell range used, and checks whether formulas are being imported as calculated values or formulas. A spreadsheet should not be treated as a database merely because it appears tabular.

1.4 JSON and nested records

JSON represents objects as key-value pairs and arrays as ordered lists. It is common in web applications and APIs.

import json

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

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

Nested data may require normalization. For example, a customer object may contain an embedded address, while an order may contain an array of line items. Flattening everything into one table can duplicate order-level values once for every line item. The analyst must decide whether to create separate orders, customers, and order_lines tables instead.

1.5 APIs

An API provides programmatic access to a service. A request commonly includes an endpoint, parameters, authentication credentials, and headers. The response may contain data, status information, pagination details, and error messages.

import os
import requests

url = "https://api.example.com/v1/orders"
headers = {"Authorization": f"Bearer {os.environ['API_TOKEN']}"}
response = requests.get(url, headers=headers, params={"limit": 100}, timeout=30)
response.raise_for_status()
orders = response.json()

Good API practice includes reading the provider’s documentation, respecting rate limits, handling pagination, setting timeouts, retrying temporary failures cautiously, and recording the retrieval timestamp. Credentials should be stored in environment variables or a secrets manager rather than source code. An API response can change over time; therefore, saving the raw response may be necessary for reproducibility.

1.6 Databases and SQL

A relational database stores data in tables connected by keys. SQL is often the most efficient way to filter, aggregate, and join data close to where it is stored. Python can connect to databases through database-specific drivers or SQLAlchemy.

from sqlalchemy import create_engine, text

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

query = text("""
    SELECT customer_id, order_date, total_amount
    FROM orders
    WHERE order_date >= :start_date
""")

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

Parameterized queries are essential. Constructing SQL by concatenating user-supplied text can create SQL injection vulnerabilities. Database credentials should use the minimum permissions required for the task, preferably read-only access for analytical extraction.

SQL can reduce the amount of data transferred:

SELECT customer_id, SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id;

A join combines rows based on a relationship. An inner join returns matching rows from both tables. A left join preserves every row from the left table and adds matching values from the right table. Before joining, the analyst must establish the expected cardinality: one-to-one, one-to-many, or many-to-many. An unexpected many-to-many join can multiply values and inflate totals.

1.7 Cloud sources

Cloud sources include object storage, cloud data warehouses, managed databases, and SaaS platforms. Object stores commonly contain files in CSV, JSON, Parquet, or other formats. Cloud warehouses support SQL over large datasets and often separate storage from computation.

A cloud workflow should specify the account, project, bucket or database, region, dataset, table, and authorization method. It should also consider cost: querying a large table repeatedly may incur charges. Partition filters, column selection, caching, and incremental extraction can reduce both processing time and expense.

2. Schemas, Keys, and Data Contracts

A schema describes the organization and meaning of data. At minimum, it should document field names, data types, allowed values, nullability, units, and key relationships. A relational schema may identify primary keys and foreign keys; an API schema may describe JSON fields and response types.

Consider an order table:

A data contract is an explicit agreement between a producing system and a consuming team. It may specify column names, types, valid ranges, update frequency, service-level expectations, and procedures for breaking changes. Contracts reduce the risk that a source system silently changes a field from numeric to text or renames a category.

Keys deserve special attention. A primary key should uniquely identify a record within its table. A natural key has business meaning, such as an invoice number; a surrogate key is an artificial identifier generated for database use. Natural keys can change or be reused, whereas surrogate keys require a reliable mapping to source identifiers.

3. Integration and Transformation

Integration commonly follows an extract, transform, and load pattern. In ETL, data is transformed before loading into a target system. In ELT, raw data is loaded first and transformed in the target platform. ELT is common in cloud warehouses because scalable computation can be applied after storage.

A robust integration process generally includes:

1. identifying sources and business definitions;
2. extracting raw data without unnecessary alteration;
3. profiling structure and quality;
4. standardizing names, types, units, dates, and categories;
5. validating keys and business rules;
6. joining or unioning compatible datasets;
7. loading a curated output; and
8. recording lineage, tests, and exceptions.

3.1 Joining datasets in pandas

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

customers["customer_id"] = customers["customer_id"].str.strip().str.upper()
orders["customer_id"] = orders["customer_id"].str.strip().str.upper()

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

The validate argument expresses an expectation: many orders may belong to one customer. If the customer table contains duplicate identifiers, the operation raises an error rather than silently multiplying rows. An unmatched-key report should be examined rather than automatically discarded.

unmatched = customer_orders[customer_orders["segment"].isna()]
print(unmatched["customer_id"].nunique())

3.2 Common integration problems

Type mismatch occurs when one source stores an identifier as an integer and another stores it as text. Converting postal codes to numbers can remove leading zeros, so identifiers should usually be treated as strings.

Granularity mismatch occurs when one table contains one row per order and another contains one row per order line. Joining them without understanding the grain can duplicate measures.

Date and time mismatch occurs when sources use different time zones, calendars, or date definitions. A timestamp should ideally include its time zone or be converted to an agreed standard such as UTC. Business dates, such as “sales day,” may require a documented local-time rule.

Unit and currency mismatch occurs when one source reports kilograms and another pounds, or when monetary values use different currencies. Conversion requires a stated rate, effective date, and rounding policy.

Category mismatch occurs when one system uses UK and another uses United Kingdom. Mapping tables are preferable to ad hoc replacements because mappings can be reviewed and versioned.

Duplicate records may arise from repeated API pages, reprocessing, or legitimate repeated events. Deduplication requires a business definition of sameness; dropping duplicate rows indiscriminately can destroy valid transactions.

4. Metadata and Data Provenance

Metadata is data about data. Technical metadata includes file names, column types, row counts, and refresh times. Business metadata explains definitions such as whether revenue includes tax and returns. Operational metadata records pipeline status, duration, and errors.

Data provenance or lineage describes where a value came from and how it was changed. A provenance record might state that a revenue table was derived from an API extraction and two database tables, filtered to completed orders, converted from local currency using a specified rate table, and aggregated by customer.

A practical extraction log may contain:

Raw data should normally be retained as an immutable landing layer, subject to retention and privacy rules. Curated layers should be reproducible from raw inputs and versioned transformation logic. This separation makes it possible to investigate errors and explain published results.

5. Access Control, Privacy, and Security

Data acquisition must follow the principle of least privilege: a user or program receives only the access required for its legitimate task. Role-based access control can distinguish readers, analysts, data engineers, and administrators. Other safeguards include multi-factor authentication, encryption in transit and at rest, network restrictions, audit logs, and credential rotation.

Personally identifiable information (PII), financial records, health information, and employee data require special care. Analysts should collect only necessary fields, restrict exports, mask or pseudonymize identifiers where possible, and apply retention limits. Pseudonymization reduces direct identification but does not guarantee anonymity if auxiliary datasets permit re-identification.

Governance is both an ethical and legal responsibility. Applicable obligations vary by jurisdiction and sector, but commonly include lawful processing, purpose limitation, transparency, access controls, and breach response. A technically successful extraction can still be unacceptable if it violates consent, contractual terms, API conditions, or organizational policy.

6. Validation and Testing

Validation should occur at several levels:

• Structural: expected columns, data types, file format, and schema version.
• Completeness: required fields and expected extraction periods are present.
• Validity: values fall within allowed formats or ranges.
• Uniqueness: keys meet their intended uniqueness rule.
• Consistency: related fields agree, such as shipment date not preceding order date.
• Reconciliation: totals agree with a trusted source within an explained tolerance.

Example assertions can be integrated into a pipeline:

required = {"order_id", "customer_id", "order_date", "total_amount"}
assert required.issubset(orders.columns)
assert orders["order_id"].notna().all()
assert orders["order_id"].is_unique
assert (orders["total_amount"] >= 0).all()

Assertions should produce informative reports in production rather than merely stopping without context. Quality rules should distinguish errors from acceptable exceptions. For example, a negative transaction may be invalid in an order table but valid as a refund table.

7. Detailed Applied Case: Integrating Retail Sales Data

The following is an illustrative case.

Background

A retailer wants a weekly customer-segment report. Orders are stored in PostgreSQL, customer segments in an Excel workbook maintained by marketing, and daily foreign-exchange rates are available through an API. The retailer sells in three currencies.

Problem

The first report overstates revenue and contains unexplained missing segments. Investigation shows that order lines were joined directly to order-level totals, customer identifiers contained spaces, and currency conversion used the current exchange rate rather than the rate applicable on the order date.

Analysis and approach

The analytics team first documented table grain: one row per order in PostgreSQL and one row per order line in a reporting view. They selected order-level records for revenue reporting and loaded the workbook as a reference table. Identifiers were standardized as strings, and a uniqueness test was applied to the customer master.

The team then retrieved historical exchange rates with the API, saved the raw JSON response, and recorded its retrieval date and provider version. Each order was matched to the rate for its transaction date and currency. Orders with unsupported currencies were placed in an exception table rather than silently omitted.

A controlled integration pipeline performed the following operations:

1. extract completed orders from the database;
2. load and validate the customer-segment workbook;
3. retrieve and archive dated exchange-rate data;
4. standardize identifiers and dates;
5. convert monetary values using documented historical rates;
6. left-join customer segments with many-to-one validation;
7. reconcile converted totals with the finance control report; and
8. publish the curated table with a provenance record.

Outcome

The duplicated revenue disappeared because the pipeline used the correct order grain. Missing segments were quantified and traced to customers absent from the workbook. The report included an exception count, conversion-rate source, refresh timestamp, and code version. Managers could therefore distinguish genuine commercial changes from data-processing problems.

Learning points

The case demonstrates that integration quality depends on business definitions as much as Python syntax. Correct joins require grain and cardinality analysis. External data requires temporal matching and source documentation. Exceptions should be visible and governed, not hidden to make a dashboard appear complete.

8. Contemporary Developments and Professional Practice

Modern platforms increasingly support open table formats, automated schema registries, orchestration tools, data catalogs, and observability systems. These technologies can detect schema drift, track lineage, and monitor freshness. AI-assisted coding can suggest extraction or transformation logic, but generated code must be reviewed for security, incorrect joins, privacy leakage, and unsupported assumptions. An AI tool does not establish that a source is authorized or that a business definition is correct.

Best practice is to treat acquisition as a reproducible software process. Use version control, configuration files, environment-specific secrets, tests, logging, documented schemas, incremental loading where appropriate, and code review. Separate raw, standardized, and analytical layers. Design pipelines to be idempotent where possible: rerunning the same input should not create duplicate business events.

Challenges and Limitations

No integration method eliminates uncertainty. Source systems may contain historical corrections, undocumented changes, missing data, or conflicting definitions. APIs may be unavailable or rate-limited. Cloud services introduce dependency, cost, and regional considerations. Matching records across systems may require probabilistic linkage, which can create false matches or missed matches. A clean integrated table can therefore create false confidence if provenance and quality limitations are not communicated.

The analyst should report both results and conditions: extraction period, excluded records, assumptions, transformation rules, quality exceptions, and known limitations. Transparency is especially important when integrated data supports credit, employment, pricing, health, or other high-impact decisions.

Chapter Summary

Business data acquisition involves obtaining information from files, applications, databases, APIs, and cloud platforms in a controlled and documented manner. CSV and spreadsheets are accessible but often lack strong schema controls; JSON and APIs require attention to nesting, pagination, authentication, and changing responses; databases and SQL support efficient, governed retrieval; and cloud sources require attention to permissions, scale, and cost.

Successful integration depends on schemas, keys, grain, data types, units, dates, and cardinality. Python libraries such as pandas, requests, and SQLAlchemy provide practical tools, while SQL can filter, aggregate, and join data efficiently at the source. Validation must test structure, completeness, validity, uniqueness, consistency, and reconciliation. Metadata and provenance make analytical outputs explainable and reproducible. Access control, privacy, security, and ethical governance are inseparable from technical acquisition. The central professional principle is that an integrated dataset is trustworthy only when its meaning, transformations, limitations, and authorization are clear.

Key Terms

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

Cardinality: The expected relationship between records in joined tables, such as one-to-many.

Cloud warehouse: A managed cloud platform for storing and querying analytical data.

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

Data grain: The level represented by one row, such as one order or one order line.

Data lineage: The documented path from source data through transformations to output.

Data provenance: Evidence about the origin, processing, and history of data.

ELT: Extract, load, and transform; transformation occurs after loading into the target platform.

ETL: Extract, transform, and load.

Foreign key: A field that references a key in another table.

JSON: A semi-structured text format based on objects and arrays.

Metadata: Information describing data’s structure, meaning, origin, or processing.

Primary key: A field or field combination intended to uniquely identify rows.

Pseudonymization: Replacing direct identifiers with controlled substitutes.

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

Schema drift: An unexpected change in a source’s structure or data types.

SQL: A language for managing and querying relational data.

Structured data: Data organized according to a defined tabular or relational schema.

Validation: Systematic checking that data satisfies structural and business rules.

Review Questions

Short Answer Questions

1. What distinguishes structured, semi-structured, and unstructured data?
2. Why should API credentials not be written directly in Python source code?
3. What is the purpose of a primary key?
4. Explain the difference between metadata and data provenance.
5. What does a left join preserve?

Long Answer Questions

1. Explain the main stages of a reproducible ETL or ELT workflow and identify quality risks at each stage.
2. Compare CSV files, spreadsheets, JSON APIs, and relational databases as business data sources.
3. Discuss why grain and cardinality must be established before joining tables.
4. Explain how access control, privacy, and provenance contribute to trustworthy analytics.
5. Describe a validation framework for a customer-revenue dataset.

Analytical / Application Questions

1. An order table contains one row per order and a product table contains one row per product. A line-item table contains several rows per order. Design a safe integration approach for calculating customer revenue and explain how you would prevent double counting.
2. A monthly API extraction returns 10,000 records, but the previous extraction returned 15,000. Propose a diagnostic process covering pagination, filters, schema changes, source outages, and duplicate handling.
3. A marketing spreadsheet contains customer IDs with leading zeros, while the database stores them as integers. Explain the likely integration failure and design a correction that preserves identifier meaning and documents the transformation.

Multiple Choice Questions with Answers

Question 1

Which source is most naturally represented as semi-structured data?

A. A relational table with enforced data types

B. A JSON document containing nested arrays

C. A printed financial report

D. A fixed-width numerical matrix

Correct Answer: B. A JSON document containing nested arrays

Question 2

What is the main purpose of using a parameterized SQL query?

A. To increase spreadsheet formatting options

B. To eliminate the need for database indexes

C. To reduce SQL injection risk

D. To convert all values to strings

Correct Answer: C. To reduce SQL injection risk

Question 3

What does a many-to-one merge validation mean in pandas?

A. Many left rows may match one right row

B. One left row must match many right rows

C. Neither table may contain a key

D. Every row must match every other row

Correct Answer: A. Many left rows may match one right row

Question 4

Why can joining order-level totals directly to order-line records overstate revenue?

A. Dates are automatically converted to text

B. The order total is repeated for each line item

C. Primary keys are always removed

D. Currency conversion is disabled

Correct Answer: B. The order total is repeated for each line item

Question 5

Which practice best protects an API credential?

A. Embedding it in a public notebook

B. Including it in a file committed to source control

C. Storing it in an environment variable or secrets manager

D. Printing it in the extraction log

Correct Answer: C. Storing it in an environment variable or secrets manager

Question 6

Which item is an example of business metadata?

A. The number of CPU cores used

B. The definition of revenue and whether it includes tax

C. The file’s binary encoding only

D. The network cable type

Correct Answer: B. The definition of revenue and whether it includes tax

Question 7

What is the principle of least privilege?

A. Give every analyst administrator access

B. Grant only the access required for an authorized task

C. Allow access only during weekends

D. Remove all audit logging

Correct Answer: B. Grant only the access required for an authorized task

Question 8

Why should identifiers such as postal codes often be stored as strings?

A. Strings always require less storage

B. Numeric conversion can remove leading zeros

C. Strings automatically validate business rules

D. Databases cannot store numeric identifiers

Correct Answer: B. Numeric conversion can remove leading zeros

Question 9

Which validation checks whether a key appears only once?

A. Completeness validation

B. Uniqueness validation

C. Currency validation

D. Latency validation

Correct Answer: B. Uniqueness validation

Question 10

What is a key benefit of retaining raw source data as an immutable landing layer?

A. It guarantees that all source errors disappear

B. It supports investigation and reproducibility of transformations

C. It removes the need for access controls

D. It prevents all schema changes

Correct Answer: B. It supports investigation and reproducibility of transformations