Data Acquisition and Storage

Introduction

Data science begins before analysis. A model, dashboard, or statistical report is only as trustworthy as the data acquired, documented, stored, and made available for use. Data acquisition is the controlled process of obtaining data from sources such as files, databases, application programming interfaces (APIs), websites, sensors, and organisational systems. Data storage is the design and operation of systems that preserve data so that it remains accessible, secure, interpretable, and fit for purpose.

This chapter connects practical acquisition techniques with foundational data-management concepts. It explains how to import common file formats, query relational databases, use APIs, scrape websites responsibly, understand schemas and keys, manage metadata and character encodings, and select secure storage practices. The central principle is that acquisition is not merely a technical download task: it is a process of establishing provenance, meaning, quality, legality, and operational control.

Learning Objectives

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

1. Identify appropriate sources and acquisition methods for a data task.
2. Import and validate CSV, spreadsheet, JSON, and database data.
3. Explain schemas, tables, relationships, primary keys, foreign keys, and normalisation.
4. Construct basic SQL queries and distinguish filtering, grouping, joining, and aggregation.
5. Describe APIs, pagination, rate limits, and responsible web scraping.
6. Explain metadata, provenance, character encoding, and data types.
7. Select suitable storage formats and apply basic security, backup, and governance controls.
8. Evaluate an acquisition workflow for reliability, ethics, legality, and reproducibility.

1. The Data Acquisition Lifecycle

A professional acquisition workflow usually follows these stages:

1. Define the purpose. Specify the business or research question, required variables, time period, granularity, and acceptable quality.
2. Discover sources. Locate internal systems, open-data portals, APIs, published files, surveys, or permitted web resources.
3. Assess the source. Examine authority, ownership, update frequency, completeness, known bias, licensing, and access conditions.
4. Acquire the data. Download, query, receive, or collect the data using a documented procedure.
5. Validate the acquisition. Check file size, row counts, schema, missingness, duplicates, date ranges, and checksums where available.
6. Record metadata and provenance. Store the source URL or system name, acquisition time, version, licence, transformations, and responsible person.
7. Store securely. Apply suitable access controls, encryption, backup, retention, and recovery procedures.
8. Monitor and update. Detect changes in source structure, data quality, or access policy.

A useful distinction is between source data, which is preserved in its received form, and working data, which may be cleaned or transformed. Keeping an immutable raw copy supports auditing and reproducibility.

2. Common Data Sources and Formats

2.1 Delimited text files

CSV (comma-separated values) is widely used because it is simple and portable. A file may contain a header row followed by records:

customer_id,signup_date,country,amount
C001,2025-01-04,GH,125.50
C002,2025-01-05,NG,80.00

CSV has no universal standard for every detail. A robust import must establish the delimiter, quotation character, decimal convention, encoding, missing-value representation, date format, and whether embedded line breaks are permitted. A comma inside a quoted field must not be treated as a column separator.

2.2 Spreadsheets

XLSX files can contain several sheets, formulas, merged cells, formatting, hidden rows, and explanatory text. Before importing, identify the actual data rectangle and determine whether calculated values or formulas are required. Spreadsheet formatting is not a substitute for a schema: a column that appears numeric may contain text such as N/A, and a date may be stored as a serial number.

2.3 JSON

JSON represents objects, arrays, strings, numbers, Boolean values, and null. It is common in APIs because it can represent nested structures. For example:

{
  "id": "P17",
  "name": "Notebook",
  "categories": ["stationery", "office"],
  "supplier": {"country": "GH"}
}

Analytical tools often require nested JSON to be flattened into relational columns or separate tables. Flattening should preserve the relationship between parent and child records; otherwise, repeated values or one-to-many relationships may be misrepresented.

2.4 Parquet and other columnar formats

Parquet stores data by column and includes schema information and compression. It is efficient when an analysis reads only some columns from a large dataset. Row-oriented formats are often convenient for record-by-record operations, while columnar formats are usually advantageous for analytical scans. Format choice depends on access patterns, tools, interoperability, and governance requirements.

2.5 Databases and APIs

Operational data commonly resides in relational database management systems (RDBMSs), while document, key-value, graph, and time-series databases serve other structures and workloads. APIs provide programmatic access to data and usually specify endpoints, parameters, authentication, response formats, quotas, and error codes.

3. Schemas, Data Types, and Data Quality

A schema describes the structure and constraints of data. It may specify field names, data types, allowed values, nullability, relationships, units, and constraints. A schema is both a technical contract and a communication device.

Typical types include:

• Integer: whole numbers, such as a count.
• Decimal: numbers requiring exact or controlled precision, such as money.
• Floating point: approximate real numbers, useful for many measurements but subject to rounding.
• String: character data.
• Boolean: true or false.
• Date/time: calendar dates and timestamps, ideally with an explicit time zone.
• Categorical: values selected from a defined set.
• Binary: arbitrary bytes such as an image or document.

Data type decisions affect storage, validation, sorting, joining, and calculation. A postal code should normally be stored as a string because leading zeroes are meaningful and arithmetic is not intended. Monetary values should not be silently converted to binary floating point if exact financial calculations are required.

A basic quality profile may include:

• completeness: proportion of required values present;
• validity: conformity to permitted types and ranges;
• uniqueness: absence of unintended duplicate records;
• consistency: agreement across fields and sources;
• timeliness: suitability of the data’s age;
• accuracy: closeness to the real-world value.

These dimensions are related but not interchangeable. A dataset can be complete but inaccurate, or accurate for a historical period but no longer timely.

4. Relational Models and Keys

A relational database represents data as relations, commonly presented as tables. A table consists of rows, called tuples, and columns, called attributes. Each table should represent a coherent entity or relationship, such as Customer, Order, or Product.

A primary key uniquely identifies each row. It must be unique and should not be null. A key may be a natural key, such as a government-issued code, or a surrogate key generated for database use. Natural keys can carry business meaning but may change; surrogate keys are stable identifiers but require a separate mapping to real-world identifiers.

A foreign key is an attribute whose values refer to a candidate or primary key in another table. It enforces referential integrity: an order should not refer to a customer that does not exist, unless the system explicitly permits an unknown customer value.

Relationships include:

• one-to-one: one record corresponds to at most one record in another table;
• one-to-many: one customer can have many orders;
• many-to-many: students can enrol in many modules and modules can contain many students. This is represented using a junction table such as Enrollment.

Normalisation

Normalisation reduces unnecessary duplication and update anomalies. In simplified terms, a well-designed table stores facts about one subject and uses keys to connect related facts. First Normal Form requires atomic, non-repeating values. Further normal forms address dependencies among non-key attributes. Complete normalisation is not always optimal for analytical performance; data warehouses may deliberately use denormalised star schemas to simplify reporting. The design should therefore reflect the workload while preserving clear meaning and controlled duplication.

5. Querying Relational Data with SQL

SQL (Structured Query Language) expresses operations on relational data. A simple query is:

SELECT customer_id, amount
FROM orders
WHERE order_date >= '2025-01-01'
  AND status = 'PAID';

SELECT chooses columns, FROM identifies a table, and WHERE filters rows. A query can aggregate records:

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(amount) AS total_amount
FROM orders
WHERE status = 'PAID'
GROUP BY customer_id
HAVING SUM(amount) > 1000;

GROUP BY forms groups, aggregate functions calculate summaries, and HAVING filters groups after aggregation. In contrast, WHERE filters individual rows before grouping.

A join combines related tables:

SELECT o.order_id, c.customer_name, o.amount
FROM orders AS o
JOIN customers AS c
  ON o.customer_id = c.customer_id;

An inner JOIN returns matching rows. A LEFT JOIN retains every row from the left table and supplies nulls when no matching right-side row exists. Analysts must check join cardinality: an unintended many-to-many join can multiply rows and inflate totals.

Worked example: detecting join multiplication

Suppose one customer has 2 orders and 3 support tickets. Joining orders directly to tickets on customer_id produces 2 × 3 = 6 paired rows for that customer, not 2 orders. If order revenue is summed after this join, it may be counted three times. A safer design aggregates each one-to-many table first, or joins through a correctly defined grain. The grain of a table is the real-world meaning of one row; every query should make that grain explicit.

SQL should be parameterised when values originate from users or external input. Concatenating untrusted input into a query can permit SQL injection. Database accounts should also receive only the permissions required for their task.

6. APIs and Programmatic Acquisition

An API is a defined interface through which software requests data. A request may include an endpoint, HTTP method, query parameters, headers, and authentication. Common methods include GET for retrieval and POST for submitting data, although exact semantics depend on the API specification.

Professional API acquisition should address:

• authentication and secret management;
• pagination, including page size and continuation tokens;
• rate limits and respectful request frequency;
• retries with backoff for temporary failures;
• timeouts and connection errors;
• response validation;
• versioning and deprecation;
• logging without exposing personal or secret data.

Pagination is essential because an endpoint may return only a limited number of records. A script must continue until the API indicates that no further page exists, while preventing infinite loops. Results should be saved with request time, endpoint version, parameters, and response status.

An API response may return a successful HTTP status but still contain an application-level error. Therefore, validation should inspect both the transport status and the response schema.

7. Web Scraping Where Permitted

Web scraping is the automated extraction of information from web pages. It can be appropriate for public information when the website’s terms, robots guidance, copyright conditions, privacy requirements, and applicable law permit it. Publicly visible does not automatically mean unrestricted for any purpose.

A responsible scraping process should:

1. Prefer an official API or downloadable dataset when available.
2. Read terms of service and relevant access instructions.
3. Request only necessary pages and use a reasonable rate.
4. Identify the client where appropriate and handle errors politely.
5. Avoid bypassing authentication, paywalls, technical controls, or access restrictions.
6. Minimise personal-data collection and define retention limits.
7. Preserve the page date, URL, extraction rule, and source version.
8. Test selectors against structural changes and monitor failures.

HTML is presentation-oriented and may change without notice. A scraper that depends on a CSS class used only for styling is fragile. Extracted values should be validated against expected types, ranges, and record counts. Scraping can also reproduce website bias, omit dynamically loaded content, or violate intellectual-property and privacy expectations.

8. Metadata, Provenance, and Encoding

Metadata is data about data. Structural metadata describes fields and types; descriptive metadata explains meaning and subject; administrative metadata records ownership, licence, access, and retention; technical metadata records format, compression, and processing details.

A practical dataset record should include:

Provenance records where data came from and what happened to it. A provenance log may contain source identifiers, timestamps, software versions, query text, transformation steps, checksums, and approvals. Provenance is vital when results must be reproduced or challenged.

Character encoding

Encoding maps characters to bytes. UTF-8 is a widely supported Unicode encoding and can represent characters from many writing systems. If bytes are decoded using the wrong encoding, names may appear corrupted, for example Müller instead of Müller. Acquisition procedures should identify the source encoding, preserve the original where possible, and validate representative non-ASCII text.

Encoding is distinct from delimiter and locale. A file may be UTF-8, use semicolons as delimiters, and represent decimals with commas. Dates such as 04/05/2025 are ambiguous without a stated convention. ISO 8601 forms such as 2025-05-04 are preferable, and timestamps should include a time zone when events occur across regions.

9. Storage Architecture and Security

Storage should be selected according to data volume, access pattern, latency, structure, cost, and governance. A small analytical project may use a local relational database; an organisation may use a layered data platform:

• raw layer: immutable source copies;
• cleansed or standardised layer: validated types, names, and formats;
• curated layer: business-ready tables and documented metrics;
• serving layer: dashboards, applications, or analytical extracts.

Object storage is suitable for large files and data lakes. Relational databases are strong for structured records, transactions, constraints, and SQL. Data warehouses are optimised for analytical queries. A lakehouse combines flexible object storage with governance and table-management features. No platform removes the need for schema, metadata, and access control.

Security controls

Security should follow defence in depth and least privilege. Important controls include:

• identity-based access and role-based permissions;
• multi-factor authentication for privileged access;
• encryption in transit using secure protocols;
• encryption at rest and controlled key management;
• secret storage outside source code and notebooks;
• audit logs for access and modification;
• network segmentation and restricted endpoints;
• patching and vulnerability management;
• tested backups and disaster-recovery procedures;
• retention and secure deletion policies.

The confidentiality, integrity, and availability model is useful: confidentiality limits unauthorised disclosure, integrity protects correctness and detects unauthorised change, and availability ensures authorised access when needed.

Backups should be separate from the primary system and periodically restored in tests. A backup that has never been restored is an assumption, not verified recovery capability. Sensitive fields should be minimised, masked, tokenised, or pseudonymised where the analytical purpose allows. Pseudonymisation reduces direct identification but does not make data anonymous if re-identification remains possible.

10. Ethical, Legal, and Governance Considerations

Data acquisition must respect privacy, fairness, intellectual property, confidentiality, and applicable data-protection law. Key governance questions include:

• What lawful or authorised basis permits collection and use?
• Was the data subject informed where required?
• Is the intended use compatible with the collection purpose?
• Are only necessary fields being acquired?
• How long should the data be retained?
• Can individuals exercise access, correction, or deletion rights?
• Are cross-border transfers and third-party processors controlled?
• Could collection systematically exclude or harm a group?

Consent is not a universal solution: it must be informed, specific, freely given where required, and capable of withdrawal. Organisations should conduct risk assessments for personal, confidential, or high-impact data. Data quality and provenance are also ethical concerns because inaccurate acquisition can lead to discriminatory or harmful decisions.

11. Detailed Applied Case: Municipal Transport Data

The following is an illustrative case.

Background

A city transport authority wants to understand delays on bus routes. It receives GPS event data from buses, scheduled timetables from a CSV file, and weather observations through an API.

Problem

Initial reports show implausibly high average delays. Analysts discover that GPS events use UTC, timetable times use local time, and a join between stop events and weather observations duplicates records. Some route identifiers also changed during the year.

Analysis

The team first defines the grain of each source: one row per GPS event, one row per scheduled stop, and one observation per weather station and time interval. It documents time zones, units, identifiers, and coverage. Raw files and API responses are preserved. A data-quality profile identifies missing vehicle IDs, duplicate event IDs, and route-code changes.

Approach and solution

The team creates a reference table mapping historical route codes to current codes. Timestamps are converted to a common standard while retaining the original value. GPS events are matched to timetable records using vehicle, route, stop, and a permitted time window. Weather is aggregated to one record per station and interval before joining, preventing many-to-many multiplication. A relational database stores validated tables, while Parquet files retain partitioned historical events for large-scale analysis.

Access is restricted because vehicle identifiers and precise movement records may be sensitive. API credentials are stored in a secret manager, not in scripts. Each daily load records source versions, row counts, validation results, and failure alerts.

Outcome

After correcting time zones and join grain, reported delays become plausible and reproducible. Analysts can distinguish missing data from genuine on-time performance. Transport managers use the curated dataset to prioritise routes for investigation, while the authority retains an audit trail of transformations.

Learning points

The principal lesson is that acquisition and storage decisions directly affect analytical validity. Correct SQL cannot repair an undefined grain, missing provenance, or inconsistent time standard. A documented schema, controlled identifiers, validation, and secure storage are prerequisites for trustworthy visualisation and modelling.

12. Contemporary Developments and Best Practices

Modern data platforms increasingly support schema registries, automated data contracts, lineage graphs, quality tests, and observability. A data contract specifies expected fields, types, ownership, freshness, and acceptable changes between producers and consumers. Automated tests can fail a pipeline when a required column disappears, a key becomes duplicated, or a freshness threshold is exceeded.

Artificial intelligence can assist with schema matching, entity resolution, anomaly detection, and metadata generation. However, generated mappings must be reviewed: an AI system may infer a plausible but incorrect meaning, expose sensitive content, or propagate source bias. Human ownership, documented tests, and approval remain necessary.

A practical checklist is:

• define the analytical question and data grain;
• prefer authoritative, permitted sources;
• preserve raw inputs;
• validate schema, encoding, types, keys, and row counts;
• record metadata, provenance, licence, and timestamps;
• use parameterised queries and least privilege;
• manage API limits and scraping obligations;
• standardise time zones, units, and identifiers;
• encrypt sensitive data and test restoration;
• monitor source changes and data quality;
• document limitations before publishing results.

Chapter Summary

Data acquisition is a governed lifecycle involving source discovery, assessment, extraction, validation, documentation, storage, and monitoring. Common sources include delimited files, spreadsheets, JSON, APIs, databases, and permitted web pages. Each source requires attention to structure, data types, encoding, missing values, and update behaviour.

Schemas describe data structure and constraints. Relational models organise facts into tables connected by primary and foreign keys. SQL supports selection, filtering, grouping, aggregation, and joins, but analysts must understand table grain to prevent duplicated results. APIs require authentication, pagination, error handling, rate-limit compliance, and version tracking. Web scraping should be used only where permitted and should respect legal, ethical, privacy, and technical boundaries.

Metadata and provenance make data interpretable and reproducible. Storage architecture should preserve raw data while providing validated and curated layers. Security requires least privilege, encryption, secret management, logging, backups, recovery testing, retention controls, and privacy-aware processing. Reliable data work therefore combines technical skill with governance, ethical judgement, and careful documentation.

Key Terms

Schema: A description of a dataset’s fields, types, constraints, and relationships.

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

Foreign key: A field referring to a key in another table.

Relational database: A database that organises data into related tables.

SQL: A language for defining, querying, and manipulating relational data.

API: A programmatic interface for requesting or submitting data.

Pagination: Dividing a large API or query result into manageable pages.

Web scraping: Automated extraction of information from web pages.

Metadata: Information describing a dataset’s meaning, structure, ownership, or management.

Provenance: A record of a dataset’s origin and transformation history.

Encoding: A mapping between characters and bytes.

UTF-8: A Unicode character encoding widely used for exchanging text.

Data grain: The real-world meaning represented by one row.

Normalisation: Relational design that reduces unnecessary duplication and anomalies.

Referential integrity: The requirement that foreign-key references remain valid.

Raw layer: A preserved representation of acquired source data.

Least privilege: Granting users or systems only the access required for their duties.

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

Data contract: An agreed specification of a dataset’s structure, quality, ownership, and change expectations.

Review Questions

Short Answer Questions

1. What is the purpose of preserving an immutable raw copy of acquired data?
2. Distinguish a primary key from a foreign key.
3. Why can a postal code be better represented as a string than an integer?
4. State two responsibilities of an API client when handling paginated data.
5. What is meant by data provenance?

Long Answer Questions

1. Explain the stages of a professional data acquisition lifecycle and show how validation and metadata contribute to reproducibility.
2. Compare CSV, JSON, Parquet, and relational database storage for analytical work.
3. Explain relational normalisation, table grain, and the risks of unintended many-to-many joins.
4. Discuss responsible web scraping, including technical, legal, privacy, and ethical considerations.
5. Design a secure storage approach for a dataset containing personal information, including access, encryption, backup, retention, and audit controls.

Analytical / Application Questions

1. A sales query joins Orders to OrderItems and then sums order-level totals. Explain how the result may be inflated and propose a corrected query strategy.
2. An API begins returning a new field, changes date formats, and occasionally returns duplicate pages. Design validation and monitoring checks to detect and manage these changes.
3. A dataset contains names with corrupted accents, ambiguous dates, local timestamps, and decimal commas. Propose an import and standardisation procedure that preserves the original values and produces analysis-ready fields.

Multiple Choice Questions with Answers

Question 1

Which property is required of a primary key?

A. It must contain descriptive text

B. It must uniquely identify each row and not be null

C. It must be duplicated in every related table

D. It must always be generated randomly

Correct Answer: B. It must uniquely identify each row and not be null

Question 2

Which SQL clause filters individual rows before grouping?

A. HAVING

B. ORDER BY

C. WHERE

D. GROUP BY

Correct Answer: C. WHERE

Question 3

Why can a direct join between two one-to-many tables inflate an aggregate?

A. It converts all values to strings

B. It removes primary keys

C. It creates multiple paired rows for the same entity

D. It prevents filtering

Correct Answer: C. It creates multiple paired rows for the same entity

Question 4

Which format is commonly column-oriented and suitable for analytical scans?

A. Parquet

B. Plain text log only

C. HTML

D. BMP

Correct Answer: A. Parquet

Question 5

What does metadata primarily provide?

A. A replacement for data validation

B. Information about a dataset’s meaning, structure, and management

C. Automatic encryption of every file

D. A guarantee that data is accurate

Correct Answer: B. Information about a dataset’s meaning, structure, and management

Question 6

Which practice best reduces SQL injection risk?

A. Concatenating user input into SQL strings

B. Disabling all database constraints

C. Using parameterised queries

D. Storing passwords in query text

Correct Answer: C. Using parameterised queries

Question 7

What is the main purpose of pagination in an API?

A. To encrypt the response

B. To divide a large result into manageable sections

C. To change JSON into CSV automatically

D. To remove duplicate records automatically

Correct Answer: B. To divide a large result into manageable sections

Question 8

Which statement about web scraping is most appropriate?

A. Public visibility always grants unrestricted reuse

B. Scraping should bypass access controls when necessary

C. An official API should generally be preferred when available and permitted

D. Scraped data requires no provenance

Correct Answer: C. An official API should generally be preferred when available and permitted

Question 9

What does UTF-8 provide?

A. A relational key constraint

B. A character encoding for representing Unicode text

C. A database backup method

D. An API authentication protocol

Correct Answer: B. A character encoding for representing Unicode text

Question 10

Which control follows the principle of least privilege?

A. Giving every analyst administrator access

B. Allowing access only to resources required for a defined task

C. Sharing one password across a team

D. Disabling audit logs

Correct Answer: B. Allowing access only to resources required for a defined task