Data Acquisition and Storage

Introduction

Data science begins before analysis. A model, dashboard, or research conclusion can be only as reliable as the data acquired, described, stored, protected, and prepared for use. Data acquisition is the systematic process of obtaining data from files, databases, application programming interfaces (APIs), websites, sensors, and event streams. Data storage concerns how that data is organized so that it remains available, understandable, secure, scalable, and fit for purpose.

Modern organizations rarely depend on a single source. A retail business may combine point-of-sale transactions, customer profiles, website events, supplier files, social-media text, and delivery telemetry. These sources differ in structure, speed, ownership, reliability, and legal sensitivity. Consequently, data professionals must make architectural and ethical decisions, not merely write import statements.

This chapter develops practical foundations for acquiring structured, semi-structured, and unstructured data with Python and SQL. It also explains databases, warehouses, lakes, lakehouses, metadata, data quality, privacy, access control, and streaming systems.

Learning Objectives

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

1. Distinguish structured, semi-structured, and unstructured data.
2. Acquire data from common files, relational databases, APIs, web sources, and streams.
3. Use Python libraries and SQL connectors responsibly.
4. Select among databases, data warehouses, data lakes, and lakehouses.
5. Explain metadata, schemas, lineage, governance, privacy, and access control.
6. Assess data quality and design reliable ingestion workflows.
7. Identify practical, ethical, and regulatory risks in data acquisition.

1. Data as an Acquired Resource

Data acquisition includes discovery, authorization, extraction, transfer, validation, and registration of data. It should not be confused with data analysis. Acquisition answers questions such as: Where did the data originate? May it be collected? How frequently does it change? What format and schema does it use? How can its integrity be verified?

1.1 Types of data

Structured data has a defined schema, although a CSV file may still contain missing values, inconsistent dates, or wrongly encoded characters. Semi-structured data preserves relationships through nested objects, arrays, tags, or key-value pairs. Unstructured data may require specialized processing before analytical features can be created. The categories are practical rather than absolute: a JSON document is semi-structured as stored, but selected fields can be transformed into structured columns.

1.2 Batch and streaming acquisition

Batch acquisition collects a bounded set of records at intervals, such as a daily sales file. Streaming acquisition processes records continuously or in short time windows, such as vehicle locations or payment events. Batch systems are often simpler and cheaper; streaming systems support low-latency decisions but require stronger controls for duplicates, ordering, failure recovery, and monitoring.

A useful ingestion design distinguishes the source of truth from derived copies. A copied dataset should retain source identifiers, extraction time, and processing status so that it can be audited and, if necessary, reprocessed.

2. File-Based Acquisition with Python

Files remain common because they are portable and easy to exchange. Typical formats include CSV, Excel, JSON, XML, Parquet, and plain text.

2.1 CSV and tabular files

A CSV file is a text representation of rows and fields. It has no universal agreement about delimiters, quoting, date formats, character encoding, or missing-value markers. Therefore, importing a CSV should involve explicit assumptions.

import pandas as pd

sales = pd.read_csv(
    "sales.csv",
    dtype={"customer_id": "string", "quantity": "Int64"},
    parse_dates=["sale_time"],
    na_values=["", "NA", "N/A"]
)

Specifying types prevents identifiers such as 00127 from being silently converted to the number 127. Parsing dates at ingestion makes later filtering and validation more reliable. For very large files, use chunks:

for chunk in pd.read_csv("events.csv", chunksize=100_000):
    process(chunk)

Chunking limits memory use, but it changes the programming model: calculations requiring the complete dataset must be performed incrementally or after aggregation.

2.2 JSON and nested data

JSON is widely used by APIs and application logs. A response may contain objects nested inside arrays:

{"order_id": 17, "customer": {"id": 4}, "items": [{"sku": "A1", "qty": 2}]}

Python can parse it with the standard library:

import json

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

For collections of nested records, pandas.json_normalize() can flatten selected paths. Flattening is not always desirable: it may duplicate parent attributes across child rows. The correct design depends on whether the analytical grain is an order, an item, or an event.

2.3 Columnar formats

Parquet stores data by column and includes schema information and compression. It is usually more efficient than CSV for analytical workloads because queries often read only selected columns. A practical pipeline may preserve raw JSON for fidelity and create Parquet files for repeated analysis.

2.4 File safety and validation

Files should be validated before loading into trusted tables. Useful checks include filename and source verification, file size limits, checksum verification, encoding detection, header validation, row-count thresholds, allowed columns, and quarantine of malformed records. Never treat an uploaded file as harmless merely because it has a familiar extension; file content and declared type may differ.

3. Relational Databases and SQL Connectors

A relational database stores data in tables governed by schemas and relationships. A primary key identifies a row, while a foreign key links a row to another table. Constraints such as NOT NULL, UNIQUE, and CHECK can prevent invalid states at the source.

SQL is declarative: the user describes the required result rather than the precise retrieval procedure. A typical query is:

SELECT customer_id, SUM(amount) AS total_spend
FROM sales
WHERE sale_time >= '2025-01-01'
GROUP BY customer_id;

The database optimizer chooses an execution plan. Indexes can accelerate selective lookups, but they impose storage and update costs. A data professional should understand query plans, filtering, joins, transactions, and pagination rather than relying on unrestricted SELECT * queries.

3.1 Python database access

Python commonly uses DB-API-compatible drivers or SQLAlchemy. A parameterized query separates code from values and helps prevent SQL injection:

from sqlalchemy import create_engine, text

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

with engine.begin() as connection:
    result = connection.execute(
        text("SELECT * FROM sales WHERE customer_id = :cid"),
        {"cid": "C104"}
    )
    rows = result.fetchall()

Credentials should not be embedded in source code. Use a secret manager or environment-based configuration, with least-privilege database accounts. engine.begin() provides a transaction context; the exact commit and rollback behavior should be understood for the selected driver.

3.2 Incremental extraction

Repeatedly copying an entire table is expensive and may overload a production system. Incremental extraction can use a reliable modification timestamp, increasing sequence, database change-data capture, or log-based replication. A timestamp-only strategy is unsafe if records can arrive late or clocks are inconsistent. A robust process may use an overlap window and deduplicate by a stable key.

A conceptual incremental condition is:

WHERE updated_at >= :previous_watermark
  AND updated_at <  :current_watermark

The watermarks must be recorded only after successful validation and loading. Otherwise, failed batches can create permanent gaps.

4. APIs and Web Sources

An API is a defined interface through which software requests data or services. HTTP APIs commonly use methods such as GET, authentication tokens, query parameters, pagination, and JSON responses.

import requests

response = requests.get(
    "https://api.example.org/v1/orders",
    params={"page": 1, "limit": 100},
    headers={"Accept": "application/json"},
    timeout=30
)
response.raise_for_status()
data = response.json()

A production client should handle timeouts, status codes, rate limits, pagination, retries, schema changes, and logging without exposing credentials or personal information. Retries should use bounded exponential backoff and should generally be restricted to transient failures. Repeating a non-idempotent operation can create duplicates; acquisition designs should prefer idempotent requests where possible.

APIs may impose contractual limits on fields, frequency, retention, and redistribution. Public availability does not automatically mean unrestricted ethical or legal use.

Web scraping

Web scraping retrieves information from web pages when an appropriate API or licensed dataset is unavailable. Ethical and technically responsible scraping requires checking terms of use, robots guidance where relevant, copyright restrictions, privacy implications, server load, and authentication boundaries. Respectful scrapers identify themselves when appropriate, limit request rates, cache responses, and preserve retrieval timestamps. They must not bypass access controls or collect unnecessary personal data.

5. Event Streams and Real-Time Data

An event is a record of something that happened, such as payment_authorized or temperature_measured. Streaming platforms use producers, brokers, topics or streams, and consumers. A consumer may process each event immediately or store it for later batch processing.

Important stream concepts include:

• Event time: when the event occurred.
• Processing time: when the system processed it.
• Watermark: an operational estimate of how far event-time processing has progressed.
• Partition: a subdivision that permits parallel processing while preserving order within that partition.
• Offset: a position used to track consumption.
• At-least-once delivery: records may be delivered again, so consumers must tolerate duplicates.

Exactly-once behavior is difficult across independent systems. In practice, idempotent writes, unique event identifiers, deduplication, checkpoints, and replayable raw data are central safeguards. A streaming design must also define how late, malformed, or out-of-order events are handled.

6. Storage Architectures

6.1 Operational databases

Operational databases support frequent transactions and current-state queries. They prioritize consistency, concurrency, and predictable updates. They are usually unsuitable for unrestricted analytical scans because those scans can interfere with operational workloads.

6.2 Data warehouses

A data warehouse is a managed analytical repository containing integrated, historical, and often transformed data. Warehouses commonly use columnar storage, parallel query execution, dimensional models, and governance controls. A star schema contains fact tables, which record measurable events, and dimension tables, which describe entities such as products or customers.

6.3 Data lakes

A data lake stores large volumes of raw or lightly processed data, frequently in object storage. It can accommodate files, logs, images, and other formats. Its flexibility is valuable, but a lake without catalogues, ownership, quality rules, and lifecycle management can become a “data swamp.”

6.4 Lakehouses

A lakehouse combines low-cost, flexible lake storage with warehouse-like table management, transactions, schema enforcement, and analytical performance. Its value depends on implementation quality: transactional metadata, cataloguing, access controls, and compatibility must be maintained rather than assumed.

7. Metadata, Lineage, and Governance

Metadata is data about data. It includes technical metadata—column names, types, partitions, and file formats—business metadata such as definitions and owners, and operational metadata such as refresh time, row counts, quality results, and processing status.

A data catalogue makes datasets discoverable. A business glossary distinguishes terms such as “customer,” “active account,” and “net revenue.” Data lineage records movement and transformation from source to output. Lineage supports impact analysis, incident investigation, reproducibility, and regulatory responses.

Governance assigns decision rights and accountability. Useful roles include data owner, data steward, platform administrator, producer, and consumer. Governance should be risk-based: highly sensitive health or financial data needs stronger controls than an openly licensed weather dataset.

8. Data Quality and Reliability

Data quality is multidimensional. Common dimensions are:

• Accuracy: correspondence with the real-world value.
• Completeness: required values are present.
• Consistency: values agree across records and systems.
• Validity: values conform to permitted formats or rules.
• Uniqueness: duplicate records are controlled.
• Timeliness: data is available and current enough for its use.

For a dataset with N records, a simple completeness rate for a required field is:

This measure assumes that a null value is identifiable and that every record requires the field. It does not prove that non-null values are accurate. For example, 000000 may pass a non-null test but be invalid as a telephone number.

Quality rules should be explicit, testable, and monitored. Examples include: order identifiers are unique; quantity is a positive integer; currency is from an approved code list; event time is within an acceptable range; and foreign keys resolve to known entities. Failed records should be quarantined with an error reason rather than silently discarded.

9. Privacy, Security, and Access Control

Data acquisition must follow purpose limitation, data minimization, transparency, retention control, and lawful processing requirements applicable to the jurisdiction. Personal data should be collected only for a defined purpose and retained only as long as justified. Sensitive data requires heightened safeguards.

Security controls include encryption in transit and at rest, secret management, audit logging, network segmentation, backup protection, and vulnerability management. Access control should follow least privilege: a service that only reads a staging table should not have permission to delete production data.

Role-based access control assigns permissions to roles rather than individuals. Attribute-based controls can additionally consider department, region, classification, device, or purpose. Masking, tokenization, pseudonymization, and aggregation can reduce exposure, but pseudonymized data may still be personal data if re-identification remains possible.

A sound workflow separates raw, restricted data from curated analytical products. It also defines retention and deletion procedures. Removing a visible table may not remove copies in backups, caches, extracts, or downstream systems; deletion processes must account for the full data lineage.

10. An End-to-End Acquisition Pattern

A professional pipeline commonly follows these stages:

1. Discover: identify source, owner, purpose, sensitivity, and expected schema.
2. Authorize: confirm permissions, contracts, and ethical basis.
3. Extract: acquire data with bounded scope, timeouts, and logging.
4. Land: preserve an immutable or controlled raw copy where appropriate.
5. Validate: test schema, quality, counts, duplicates, and security conditions.
6. Transform: standardize types, names, units, and business rules.
7. Publish: expose governed tables, files, or APIs to approved users.
8. Monitor: track freshness, failures, drift, cost, and access.
9. Reconcile: compare totals or control counts with the source.

Schema evolution deserves particular attention. Adding an optional field may be backward compatible, whereas renaming or changing a field's meaning can silently corrupt downstream analysis. Data contracts—documented agreements between producers and consumers—can specify schema, semantics, quality expectations, and change procedures.

Applied Case: Regional Retail Data Platform

Background. This illustrative case concerns a retail chain with point-of-sale databases, online orders, daily supplier CSV files, and website clickstream events. Managers want near-daily sales reporting and analysts want product-demand models.

Problem. The existing process exports spreadsheets manually. Product identifiers differ between systems, late supplier files are overwritten, and analysts cannot determine which version of a dataset produced a report. Customer email addresses are copied into general-purpose folders.

Analysis. The sources have different velocities and structures. Point-of-sale data is structured and transactional; supplier files are batch-oriented; clickstream data is semi-structured and high volume. Email addresses are personal data and should not be broadly available. A single ungoverned folder would provide storage but not dependable data management.

Approach. The organization establishes a controlled landing area for immutable raw files and events, records source, extraction time, checksum, and schema version, and uses database connectors for incremental point-of-sale extraction. Supplier files are validated for required columns, duplicate invoice numbers, currency, and date ranges. A product master table maps source identifiers to a canonical product key. Curated facts and dimensions are published to a warehouse. Analysts receive pseudonymized customer keys rather than email addresses. A catalogue records definitions, owners, quality results, lineage, and retention rules.

Clickstream consumers use event identifiers and idempotent writes to handle at-least-once delivery. Failed records enter a quarantine area. Dashboards display freshness and quality status instead of implying that every value is current.

Outcome. Reporting becomes reproducible, supplier errors become visible, and access to personal data is reduced. The platform does not eliminate all risk: product mappings still require stewardship, source systems may change without notice, and streaming costs require monitoring.

Learning points. Architecture should reflect source characteristics; raw preservation supports recovery; quality must be measured at the point of ingestion; and governance is an operational capability rather than a document created after deployment.

Challenges and Contemporary Developments

Data acquisition increasingly involves cloud object storage, managed warehouses, streaming platforms, synthetic data, and machine-learning feature pipelines. Automated schema inference can accelerate onboarding but may infer incorrect types or expose sensitive fields. Generative AI can assist with document extraction and metadata creation, but generated values require validation and must not be treated as authoritative.

Other persistent challenges include vendor lock-in, cross-border transfers, hidden sampling bias, duplicated identities, data drift, rising storage costs, and environmental impacts of computation. A technically elegant pipeline can still produce unfair outcomes if its source data systematically excludes a population. Data professionals should document limitations and uncertainty, not merely maximize volume.

Best-Practice Checklist

• Define the purpose, owner, sensitivity, and permitted use before extraction.
• Prefer official, stable interfaces over fragile screen scraping.
• Use parameterized SQL and secret management.
• Preserve source identifiers, timestamps, versions, and lineage.
• Validate schemas and quality before publication.
• Design for retries, duplicates, late data, and replay.
• Separate raw, restricted, curated, and analytical zones.
• Apply least privilege, encryption, logging, and retention controls.
• Monitor freshness, completeness, failures, cost, and schema drift.
• Communicate limitations and obtain human review for consequential uses.

Chapter Summary

Data acquisition is a governed engineering process involving discovery, authorization, extraction, validation, storage, and monitoring. Structured, semi-structured, and unstructured data require different tools and modelling decisions. Python supports file handling, HTTP requests, transformation, and database connectivity, while SQL provides controlled access to relational systems.

Operational databases, warehouses, lakes, and lakehouses serve different purposes. Metadata, catalogues, lineage, and data contracts make datasets understandable and maintainable. Data quality should be measured across accuracy, completeness, consistency, validity, uniqueness, and timeliness. Streaming systems require attention to event time, offsets, retries, duplicates, and late arrivals. Privacy, security, least privilege, retention, and ethical collection are essential properties of a professional data platform. Reliable acquisition is therefore both a technical and organizational discipline.

Key Terms

Data acquisition: The process of discovering, obtaining, transferring, and validating data.

Structured data: Data organized in a predictable schema such as rows and columns.

Semi-structured data: Data with flexible organization using keys, tags, or nested objects.

Unstructured data: Data without a fixed analytical schema, such as images or free text.

Batch processing: Processing a bounded collection of data at scheduled intervals.

Stream processing: Processing continuously arriving events.

API: A defined software interface for requesting data or services.

Primary key: Attribute or combination of attributes that uniquely identifies a row.

Foreign key: Attribute referencing a key in another table.

Data warehouse: Governed analytical storage for integrated and historical data.

Data lake: Flexible storage for raw or lightly processed data in many formats.

Lakehouse: Architecture combining lake flexibility with managed analytical table features.

Metadata: Information describing data, its meaning, structure, origin, and use.

Data lineage: Record of data movement and transformation across systems.

Schema evolution: Change to the structure or meaning of a dataset over time.

Idempotence: Property whereby repeating an operation produces the same intended result.

Data quality: Fitness of data for a defined purpose across multiple dimensions.

Least privilege: Granting only the access required for an authorized task.

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

Data contract: Agreement specifying data structure, meaning, quality, and change expectations.

Review Questions

Short Answer Questions

1. Distinguish structured, semi-structured, and unstructured data, giving one example of each.
2. Why should database queries use parameters rather than string concatenation?
3. What is the difference between event time and processing time?
4. Name four dimensions of data quality.
5. What is the purpose of metadata and data lineage?

Long Answer Questions

1. Compare data warehouses, data lakes, and lakehouses in terms of structure, governance, cost, and analytical use.
2. Explain how a Python pipeline should safely acquire data from an HTTP API.
3. Describe an incremental extraction strategy and explain how it can avoid missed or duplicated records.
4. Discuss privacy, access control, retention, and pseudonymization in a data platform.
5. Design an end-to-end ingestion architecture for an organization using relational databases, CSV files, and event streams.

Analytical / Application Questions

1. A daily file contains 1,000 records, of which 940 have a required customer identifier. Calculate the completeness rate and explain why this metric alone does not establish accuracy.
2. A stream consumer occasionally processes the same event twice. Propose a design using event identifiers, idempotent writes, and monitoring to prevent duplicated business outcomes.
3. A company wants to scrape a website containing personal profiles. Analyze the technical, ethical, contractual, and privacy questions that should be resolved before collection.

Multiple Choice Questions with Answers

Question 1

Which characteristic best describes semi-structured data?

A. It always consists of fixed relational tables

B. It uses flexible structures such as keys, tags, or nested objects

C. It contains no information that can be processed computationally

D. It can only be stored in a data warehouse

Correct Answer: B. It uses flexible structures such as keys, tags, or nested objects

Question 2

Which practice most directly reduces SQL injection risk?

A. Using SELECT * for every query

B. Saving passwords in the Python file

C. Using parameterized queries

D. Removing all database indexes

Correct Answer: C. Using parameterized queries

Question 3

What is the main purpose of a data warehouse?

A. To support integrated historical analytical queries

B. To replace every operational transaction system

C. To store only unstructured images

D. To provide unrestricted public access to data

Correct Answer: A. To support integrated historical analytical queries

Question 4

A dataset has 1,000 records and 940 non-null values in a required field. What is its completeness rate?

A. 9.4%

B. 40%

C. 94%

D. 106.4%

Correct Answer: C. 94%

Question 5

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

A. Every event is guaranteed to arrive exactly once

B. Events may be delivered again, so consumers should tolerate duplicates

C. Events are never delivered after a failure

D. Event ordering is guaranteed across all partitions

Correct Answer: B. Events may be delivered again, so consumers should tolerate duplicates

Question 6

Which item is an example of operational metadata?

A. A dataset's refresh time and row count

B. A customer's home address

C. A product's marketing slogan

D. An image's artistic style

Correct Answer: A. A dataset's refresh time and row count

Question 7

What does least privilege mean?

A. Every employee receives administrator access

B. Access is granted only to the level required for an authorized task

C. All data is made publicly readable

D. Passwords are shared within a team

Correct Answer: B. Access is granted only to the level required for an authorized task

Question 8

Which storage format is generally well suited to column-oriented analytical workloads?

A. Parquet

B. Plain text without delimiters

C. An executable program

D. A password file

Correct Answer: A. Parquet

Question 9

Why is preserving source identifiers useful in an ingestion pipeline?

A. It prevents all schema changes

B. It supports traceability, deduplication, and reconciliation

C. It eliminates the need for validation

D. It guarantees that data is accurate

Correct Answer: B. It supports traceability, deduplication, and reconciliation

Question 10

Which approach is most responsible when collecting data from a website?

A. Bypassing authentication to obtain more records

B. Ignoring terms of use because the page is visible

C. Checking authorization, privacy, usage terms, and request-rate limits

D. Collecting every available personal attribute by default

Correct Answer: C. Checking authorization, privacy, usage terms, and request-rate limits