Data Acquisition, Storage and Management

Introduction

Data science begins long before analysis or machine learning. A model can be mathematically sophisticated and still produce unreliable results if its data is incomplete, poorly defined, insecurely stored, or collected without permission. Data acquisition concerns how data is obtained; storage concerns how it is retained and made available; and management concerns its quality, structure, security, governance, and lifecycle.

A useful data environment connects sources, ingestion processes, storage systems, transformation pipelines, users, and controls. The central professional principle is that data should be fit for purpose: sufficiently accurate, timely, complete, accessible, secure, and ethically obtained for the decision it supports.

Learning Objectives

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

• distinguish internal, external, open, proprietary, batch, and real-time data sources;
• select suitable file formats and introductory database structures;
• write basic SQL queries for retrieving and summarising data;
• explain how APIs support controlled data access;
• compare databases, data warehouses, data lakes, and lakehouses;
• describe extraction, transformation, loading, and data pipeline processes;
• identify important cloud, security, privacy, quality, and governance practices.

1. Data Sources and Acquisition

1.1 Internal and external sources

Internal data is generated within an organisation. Examples include sales transactions, customer-support records, inventory systems, employee records, website logs, and financial ledgers. Internal data is often valuable because it is closely related to organisational processes, but it may be stored in disconnected systems and may reflect operational biases.

External data is obtained from outside the organisation. Examples include government statistics, weather services, market research, social-media data, geospatial datasets, commercial data vendors, and partner organisations. External data can provide context and benchmarking, but its definitions, collection methods, licensing conditions, and quality must be checked carefully.

Data may also be classified by access rights:

A source is not automatically reliable because it is official, popular, or large. Before acquisition, a data scientist should ask: Who collected it? For what purpose? When and where? Using which definitions and sampling method? Has it been altered? What permissions apply?

1.2 Structured, semi-structured, and unstructured data

Structured data follows a fixed schema, such as rows and columns in a relational table. Semi-structured data has organisational markers but not necessarily a rigid table, as in JSON or XML documents. Unstructured data includes free text, images, audio, and video. These categories influence storage, processing, search, and quality-control choices.

A data source can also be classified by arrival pattern:

• Batch data is collected and processed periodically, such as a nightly sales extract.
• Streaming data arrives continuously or in very small intervals, such as sensor readings.
• Real-time data is made available with sufficiently low delay for the intended decision. “Real-time” is therefore relative to a business requirement; a response within five seconds may be real-time for a dashboard but not for an automated safety system.

1.3 Acquisition methods

Common acquisition methods include direct entry, database extraction, file transfer, web forms, sensors, software development kits, web scraping, and APIs. Each method creates different risks. Manual entry may introduce transcription errors; sensors require calibration; scraping may violate terms of service; and APIs may enforce rate limits or return changing schemas.

A responsible acquisition plan records the source, owner, collection date, refresh frequency, fields, units, definitions, licence, sensitivity classification, and known limitations. This documentation is part of data provenance, the record of where data came from and how it changed.

2. Files and Data Representation

2.1 Common file formats

CSV is a plain-text tabular format. It is portable and easy to inspect, but it has weaknesses: data types are not strongly specified, delimiters and quotation rules can cause parsing problems, and large files can be inefficient.

JSON represents objects and arrays using key-value structures. It is widely used by web APIs and is suitable for nested records, although it may be verbose and less convenient for relational analysis.

XML uses labelled elements and attributes. It remains important in many enterprise and public-sector systems, but is generally more verbose than JSON.

Parquet is a columnar binary format commonly used in analytical systems. It stores data by column, supports compression and typed schemas, and can improve queries that need only selected columns. Avro is a row-oriented, schema-based format often used for event exchange and streaming systems.

File handling requires attention to character encoding, missing-value conventions, date formats, decimal separators, duplicate records, and column names. For example, “03/04/2025” can mean 3 April or 4 March depending on locale. Dates should preferably be stored in an unambiguous standard such as ISO 8601, for example 2025-04-03.

2.2 Data validation at ingestion

Validation checks whether incoming data conforms to expected rules. Examples include:

• Type checks: an age field should be numeric;
• Range checks: a percentage should normally lie between 0 and 100;
• Domain checks: a country code must belong to an approved list;
• Uniqueness checks: a transaction identifier should not repeat where uniqueness is required;
• Referential checks: a product identifier should exist in the product table;
• Completeness checks: required fields should not be null;
• Freshness checks: the latest timestamp should meet the agreed service level.

Validation should distinguish an invalid record from a legitimately missing value. Rejecting every incomplete record may create bias, while accepting everything allows errors to spread into downstream systems.

3. Relational Databases and SQL

3.1 Relational concepts

A relational database stores data in tables. A row represents an instance, and a column represents an attribute. A primary key uniquely identifies a row. A foreign key references a key in another table and establishes a relationship.

For example, a small retail database might contain:

• Customers(customer_id, name, region);
• Orders(order_id, customer_id, order_date, total_amount);
• Products(product_id, product_name, category, price);
• OrderItems(order_id, product_id, quantity).

Separating entities reduces repeated data and supports consistency. This design process is associated with normalisation. Normalisation can reduce update anomalies, although highly normalised structures may require more joins for analytical queries. Analytical systems often use dimensional models with a central fact table and descriptive dimension tables.

3.2 Basic SQL

SQL is a declarative language: the user states what information is required rather than specifying every computational step. A basic query is:

SELECT region, COUNT(*) AS customer_count
FROM Customers
GROUP BY region
ORDER BY customer_count DESC;

A filter uses WHERE:

SELECT order_id, order_date, total_amount
FROM Orders
WHERE total_amount >= 1000
  AND order_date >= '2025-01-01';

A join combines related tables:

SELECT o.order_id, c.region, o.total_amount
FROM Orders AS o
JOIN Customers AS c
  ON o.customer_id = c.customer_id;

An aggregation calculates summaries. For values x₁, x₂, …, x_n, the arithmetic mean is:

Here, n is the number of observations and xᵢ is observation i. The formula assumes that the observations are numeric and that each observation is intended to contribute equally. In SQL, AVG(total_amount) implements this operation over the selected non-null values; therefore, the analyst must understand how nulls are treated.

A grouped query might be:

SELECT customer_id,
       SUM(total_amount) AS revenue,
       AVG(total_amount) AS average_order
FROM Orders
GROUP BY customer_id;

3.3 Transactions, indexes, and constraints

A database transaction is a logical unit of work. The ACID properties are:

• Atomicity: all parts succeed or none is committed;
• Consistency: database rules remain satisfied;
• Isolation: concurrent transactions do not produce unacceptable interference;
• Durability: committed changes survive an ordinary failure.

Indexes improve retrieval by creating an additional access structure, but they consume storage and can slow inserts or updates. Indexes should therefore be designed around common queries, not placed on every column. Constraints such as NOT NULL, UNIQUE, CHECK, and foreign-key rules enforce quality close to the point of entry.

4. APIs and Programmatic Access

An application programming interface (API) defines how one software system requests services or data from another. A common web API uses HTTP methods:

• GET retrieves data;
• POST submits or creates data;
• PUT or PATCH updates data;
• DELETE requests removal, subject to permissions and policy.

Many APIs use REST principles and return JSON. A request may include query parameters such as date range, location, page number, or fields to return. A response commonly includes a status code, metadata, and the requested records.

Professional API use requires authentication, authorisation, rate-limit compliance, pagination, timeout handling, retries with backoff, logging, and schema validation. An API key identifies an application but should not be treated as a complete security solution. Secrets must not be embedded in public source code or committed to a repository.

APIs may change. Version numbers, deprecation notices, response schemas, and service-level agreements should be monitored. Reproducible acquisition also requires storing request time, endpoint version, parameters, and response metadata where permitted.

5. Storage Architectures

5.1 Operational databases

Operational or transactional databases support frequent inserts, updates, and point lookups. They prioritise consistency and transaction performance. They are usually not the best place for large, complex analytical queries that compete with operational workloads.

5.2 Data warehouses

A data warehouse is a curated repository designed primarily for reporting and analysis. Data is commonly extracted from operational systems, transformed into consistent definitions, and loaded into structured tables. Warehouses support historical analysis, governed metrics, access controls, and query optimisation.

A star schema contains a central fact table, such as sales, linked to dimension tables, such as date, customer, product, and location. A fact table should have a clearly stated grain—for example, “one row per product per order”—because ambiguous grain causes incorrect aggregation.

5.3 Data lakes and lakehouses

A data lake stores large amounts of raw or lightly processed data in its original or near-original form. It can accommodate structured, semi-structured, and unstructured data and is useful when future analytical requirements are uncertain. Without catalogues, quality controls, and ownership, a lake can become a “data swamp.”

A lakehouse combines low-cost, flexible lake storage with warehouse-like table management, transactions, governance, and analytical performance. The distinction among platforms changes over time, so architecture should be selected according to workload, governance, skills, cost, and interoperability rather than fashionable terminology.

6. Data Pipelines and Lifecycle Management

A data pipeline moves data from source to destination through controlled stages:

1. Extract or ingest data from files, databases, devices, or APIs.
2. Validate schema, quality, access rights, and freshness.
3. Transform types, units, names, formats, and business rules.
4. Load or publish data into a target store or analytical service.
5. Monitor failures, latency, volume, quality, and cost.
6. Document and govern lineage, ownership, retention, and access.

ETL performs extract, transform, then load. ELT loads data first and transforms it within the target analytical platform. ETL may be appropriate when data must be cleaned before entering a controlled warehouse; ELT is attractive when the target platform has scalable processing and raw data must be retained.

Pipelines may be scheduled, event-driven, or streaming. A robust pipeline should be idempotent where possible: rerunning the same input should not create unintended duplicates. This may require stable event identifiers, checkpoints, deduplication rules, and transaction controls.

Important operational measures include:

• Latency: delay between source creation and availability;
• Throughput: records or bytes processed per unit of time;
• Failure rate: proportion of runs or records that fail;
• Freshness: age of the newest accepted data;
• Completeness: proportion of expected data received;
• Recovery point objective (RPO): maximum acceptable data loss measured in time;
• Recovery time objective (RTO): maximum acceptable restoration time.

These measures must be tied to business requirements. A monthly report and a fraud-detection system do not need the same latency or recovery targets.

7. Cloud Platforms and Distributed Storage

Cloud platforms provide computing, storage, databases, networking, identity services, monitoring, and managed analytical tools on demand. Their advantages include elasticity, geographic availability, managed maintenance, and rapid experimentation. Risks include uncontrolled spending, vendor lock-in, misconfigured permissions, data-residency issues, and dependence on network connectivity.

Cloud responsibility is shared. The provider may secure physical facilities and core infrastructure, while the customer remains responsible for identities, data classification, configuration, application code, and appropriate use. The exact boundary depends on whether the service is infrastructure, platform, or software as a service.

Object storage is commonly used for data lakes because it is durable, scalable, and suitable for files. Good practice includes logical partitioning, lifecycle rules, encryption, versioning where required, access logging, and a catalogue that records schemas and ownership. Partitioning by date can improve retrieval, but excessive small partitions can harm performance.

8. Security, Privacy, and Governance

Data security protects confidentiality, integrity, and availability. Core controls include least-privilege access, strong authentication, role-based permissions, encryption in transit and at rest, network restrictions, audit logs, vulnerability management, backup testing, and incident-response procedures.

Authentication verifies identity; authorisation determines permitted actions. These must not be confused. A user may be authenticated but not authorised to view personal salary records.

Sensitive fields should be classified and handled according to organisational policy and applicable law. Techniques include masking, pseudonymisation, aggregation, tokenisation, and—when appropriate—anonymisation. Pseudonymised data may still be personal data if re-identification remains possible.

Privacy principles commonly include purpose limitation, data minimisation, accuracy, storage limitation, transparency, and accountability. A data scientist should collect only what is needed, explain intended use, restrict secondary use, and define retention and deletion procedures. Legal requirements vary by jurisdiction; therefore, a project should involve the relevant privacy or compliance officer rather than assuming that one regulation applies everywhere.

Governance assigns responsibility for definitions, quality, access, retention, and lifecycle decisions. A data catalogue helps users discover datasets, while a business glossary defines terms such as “active customer” or “net revenue.” Lineage shows how a dashboard field was derived from source data. Together, these controls improve trust and reproducibility.

9. Applied Case: Municipal Water-Use Monitoring

Background

This illustrative case concerns a city water utility that wants to identify abnormal consumption and reduce leakage. Its sources include monthly billing records, smart-meter readings every 15 minutes, weather data from an open government API, and maintenance records held in an internal relational database.

Problem

The utility’s reports disagree because customer identifiers and property addresses are represented differently across systems. Some meters transmit late or duplicate readings. Analysts also download weather files manually, making the process difficult to reproduce. Access to household-level consumption is sensitive.

Analysis and approach

The team first defines the grain of the analytical dataset as “one meter for one fifteen-minute interval.” A data catalogue records source owners, units, refresh frequencies, licences, and sensitivity levels. A controlled ingestion service retrieves weather data through the API and stores request metadata.

Raw meter messages are retained in object storage in a schema-preserving format. A validation layer checks meter identifiers, timestamps, units, permitted ranges, duplicate event IDs, and missing intervals. Invalid records are quarantined for review rather than silently discarded. A curated warehouse table standardises identifiers and links meters to properties through a governed reference table.

SQL is used to aggregate interval readings into daily consumption. A warehouse dashboard receives only the fields required for operational monitoring, while household-level records are restricted through role-based access. Analysts receive pseudonymised identifiers unless direct identification is necessary and approved.

The pipeline is monitored for freshness, completeness, duplicate rate, and failed API requests. If a source is late, the dashboard displays its data age rather than implying current coverage. Historical corrections are versioned so that published reports can be reproduced.

Outcome and learning points

The utility obtains a consistent data product, detects leakage candidates earlier, and reduces manual collection. More importantly, the improvement comes not from a single tool but from explicit data grain, provenance, validation, security, and operational monitoring. The case demonstrates that data management is a socio-technical activity involving people, definitions, processes, technology, and accountability.

10. Challenges and Best Practices

Common challenges include schema drift, missing data, duplicate events, incompatible identifiers, changing API behaviour, undocumented transformations, high storage costs, vendor dependence, and unclear ownership. Large volume does not compensate for poor relevance or biased collection.

Recommended practices are:

1. Define the intended decision and required data before collecting.
2. Record provenance, ownership, licence, schema, units, and refresh frequency.
3. Establish data contracts for important producer-consumer interfaces.
4. Validate at ingestion and monitor quality continuously.
5. Separate raw, validated, and curated layers.
6. State the grain of every analytical table.
7. Use least privilege, secure secrets, encryption, and audit logs.
8. Design for retries, duplicate handling, failure recovery, and schema change.
9. Retain only what is justified and define deletion procedures.
10. Test backups, disaster recovery, and access controls rather than merely documenting them.

Artificial intelligence can assist with schema matching, anomaly detection, metadata generation, and natural-language querying. However, AI-generated mappings and summaries require human validation because they may misinterpret fields, expose sensitive data, or create confident but incorrect documentation. AI does not remove the need for governance.

Chapter Summary

Data acquisition includes identifying appropriate internal, external, open, proprietary, batch, streaming, and real-time sources. Reliable acquisition requires attention to provenance, licensing, definitions, quality, and consent. CSV, JSON, XML, Parquet, and Avro serve different structural and performance needs. Relational databases organise entities using tables, keys, constraints, and SQL; transactions support reliable operational updates.

APIs provide controlled programmatic access but require authentication, authorisation, pagination, rate-limit compliance, schema validation, and monitoring. Operational databases, warehouses, lakes, and lakehouses serve different workloads. ETL and ELT pipelines move data through ingestion, validation, transformation, publication, monitoring, and governance stages. Cloud services offer scalability but introduce configuration, cost, residency, and vendor-dependence risks.

Effective data management combines architecture with quality controls, lineage, catalogues, security, privacy, retention, and clear ownership. The objective is not simply to store more data, but to make trustworthy data available safely and reproducibly for legitimate decisions.

Key Terms

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

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

Batch processing: Processing data collected over a period at scheduled intervals.

Streaming data: Data that arrives continuously or in small successive events.

Structured data: Data organised according to a defined schema, commonly rows and columns.

Semi-structured data: Data containing organisational tags or keys without a rigid tabular schema.

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

Relational database: A database that stores related data in tables.

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

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

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

Normalisation: Structuring relational data to reduce unnecessary repetition and anomalies.

Data warehouse: A curated analytical repository containing integrated, historical data.

Data lake: A scalable repository for raw or lightly processed data in varied formats.

Lakehouse: An architecture combining flexible lake storage with managed analytical features.

ETL: Extract, transform, and load.

ELT: Extract, load, and transform within the target platform.

Data lineage: A record of how data moves and changes from source to output.

Authentication: Verification of a user or system identity.

Authorisation: Determination of what an authenticated identity may access or do.

Review Questions

Short Answer Questions

1. Distinguish internal data from external data and give one example of each.
2. What is the difference between batch processing and streaming data?
3. Explain the roles of primary keys and foreign keys.
4. State two advantages of Parquet for analytical workloads.
5. Distinguish authentication from authorisation.

Long Answer Questions

1. Compare operational databases, data warehouses, data lakes, and lakehouses in terms of purpose, structure, and typical users.
2. Explain the stages of an ETL or ELT pipeline and identify controls needed at each stage.
3. Discuss how provenance, catalogues, glossaries, and lineage contribute to trustworthy data use.
4. Explain the major security and privacy practices required when managing personal data.
5. Describe the strengths and limitations of APIs as a method of data acquisition.

Analytical / Application Questions

1. A company receives customer data as CSV files from three regions. Dates, currencies, missing-value codes, and customer identifiers differ by region. Design an ingestion and validation process that produces a governed analytical table.
2. A dashboard reports inconsistent monthly sales totals after a new source system is introduced. Analyse possible causes involving table grain, joins, duplicates, nulls, and historical revisions, and propose diagnostic SQL checks.
3. A health organisation wants to store raw wearable-device data, curated clinical measures, and dashboard summaries in the cloud. Recommend an architecture and access-control strategy, explaining trade-offs involving flexibility, performance, privacy, cost, and recovery.

Multiple Choice Questions with Answers

Question 1

Which source is an example of internal data?

A. A national weather service API

B. An organisation’s sales transaction system

C. A commercial market-research report

D. An open government census dataset

Correct Answer: B. An organisation’s sales transaction system

Question 2

Which format is generally most suitable for efficient column-oriented analytical storage?

A. CSV

B. JSON

C. Parquet

D. Plain text log

Correct Answer: C. Parquet

Question 3

What is the primary purpose of a foreign key?

A. To encrypt a database column

B. To uniquely identify every database server

C. To reference a related key in another table

D. To sort all rows alphabetically

Correct Answer: C. To reference a related key in another table

Question 4

Which SQL clause filters rows before grouping?

A. WHERE

B. ORDER BY

C. GROUP BY

D. AS

Correct Answer: A. WHERE

Question 5

Which property of a transaction means that all parts succeed or none is committed?

A. Durability

B. Isolation

C. Atomicity

D. Availability

Correct Answer: C. Atomicity

Question 6

What is a key characteristic of a data warehouse?

A. It is designed mainly for curated analytical queries

B. It stores only unstructured video

C. It cannot contain historical data

D. It replaces all operational systems

Correct Answer: A. It is designed mainly for curated analytical queries

Question 7

Which practice best protects an API credential?

A. Embedding it in a public webpage

B. Committing it to a shared code repository

C. Storing it in a managed secret store

D. Printing it in application logs

Correct Answer: C. Storing it in a managed secret store

Question 8

In a data pipeline, what is the purpose of a quarantine area?

A. To publish all records immediately

B. To isolate records that fail validation

C. To delete all historical data

D. To increase user permissions

Correct Answer: B. To isolate records that fail validation

Question 9

Which statement correctly distinguishes authentication and authorisation?

A. Authentication verifies identity; authorisation determines permitted actions

B. Authentication determines permitted actions; authorisation verifies identity

C. Both terms mean data encryption

D. Both terms refer only to database backups

Correct Answer: A. Authentication verifies identity; authorisation determines permitted actions

Question 10

A table has 8 observations with a total value of 240. What is its arithmetic mean?

A. 20

B. 30

C. 40

D. 1920

Correct Answer: B. 30