Data Acquisition, Storage and Management

Introduction

Data science begins before modelling. The quality, scope, provenance and accessibility of data determine what can be learned and what decisions can be defended. Data acquisition concerns obtaining data from databases, applications, devices, files, APIs and online sources. Data storage concerns preserving data so that it remains available, secure, interpretable and economical. Data management connects these activities through modelling, metadata, quality controls, governance, integration and lifecycle policies.

For postgraduate data scientists, acquisition and storage are not merely infrastructure concerns. They are analytical design decisions. A sampling mechanism can introduce bias; an inappropriate schema can obscure relationships; weak lineage can make results irreproducible; and uncontrolled retention can create legal and security exposure. Modern organisations commonly combine relational databases, object storage, streaming platforms, warehouses, data lakes and lakehouses. Each serves different access, consistency, performance and governance requirements.

Learning Objectives

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

1. Distinguish structured, semi-structured and unstructured data.
2. Select appropriate acquisition methods for databases, APIs, files, sensors and web sources.
3. Write and evaluate fundamental SQL queries.
4. Design relational schemas using keys, constraints and normalization principles.
5. Explain metadata, lineage, data quality and master-data practices.
6. Compare data warehouses, data lakes and lakehouses.
7. Design an ingestion and storage architecture for analytical workloads.
8. Evaluate security, privacy, ethical and regulatory risks in data management.

1. The Nature of Data

1.1 Structured, semi-structured and unstructured data

Structured data has a predefined schema. A sales table with customer identifiers, transaction timestamps, quantities and prices is structured because attributes have defined names, types and relationships. Relational databases are designed for this form.

Semi-structured data does not conform to one rigid tabular schema but contains organizational markers. JSON documents, XML files, event records and many application logs use fields, nested objects or tags. Two records may contain different attributes, although common conventions often exist.

Unstructured data lacks a regular tabular organization. Text documents, images, audio, video, scanned forms and free-form correspondence are examples. They may still contain valuable structure extracted through natural-language processing, computer vision or speech recognition. The original artifact should normally be retained alongside extracted features.

1.2 Data as an information asset

Data has technical, economic and social properties. It can be copied at low marginal cost, but collection, cleaning, annotation, storage and governance are expensive. Its value is context-dependent: a location coordinate may be useful for logistics but sensitive for an individual. Data quality is multidimensional. Accuracy, completeness, consistency, timeliness, validity, uniqueness and fitness for purpose should be assessed rather than treated as a single score.

A useful conceptual model is that a data product has both content and context. Content includes values and files; context includes definitions, ownership, provenance, access conditions, units, time semantics and known limitations. Without context, a dataset may be technically available yet analytically unusable.

2. Data Acquisition Methods

2.1 Operational databases

Operational systems capture transactions and support business processes. Common systems include relational databases, key-value stores, document databases and graph databases. Direct analytical querying against a production system can degrade operational performance, so organizations often use read replicas, change-data-capture (CDC), scheduled extracts or an integration platform.

Batch extraction collects records periodically. It is simple and suitable when latency requirements are measured in hours or days. CDC captures inserts, updates and deletes from transaction logs or database mechanisms. It reduces repeated full-table scans but requires careful handling of ordering, duplicate events, schema changes and deletes.

2.2 APIs

An application programming interface provides a controlled way to request data or invoke a service. A robust API client must understand authentication, authorization, endpoints, parameters, pagination, rate limits, status codes, retries, timeouts, versioning and response schemas.

A failed request should not automatically be retried indefinitely. Exponential backoff, bounded retries and idempotent operations reduce overload and prevent duplicate effects. API responses should be stored with request time, endpoint version, parameters and status information so that acquisition can be audited and repeated.

2.3 Files and external exchanges

CSV, TSV, spreadsheets, JSON, XML, Parquet and Avro are common file formats. Delimiters, quotation rules, character encoding, decimal conventions, missing-value markers and date formats must be identified explicitly. Columnar formats such as Parquet are generally efficient for analytical scans because they store values by column and support compression and predicate pushdown. Row-oriented formats are often convenient for record-by-record exchange.

File ingestion should include checks for expected file count, naming patterns, checksums, schema compatibility and duplicate delivery. A file should be treated as an immutable input when possible; transformations should produce new outputs rather than silently overwriting the source.

2.4 Sensors and streaming systems

Sensors produce observations associated with time and often location. Important fields include device identifier, event time, ingestion time, measurement, unit, calibration state and quality flag. Event time and processing time are different: an observation may arrive late because of network delay. Streaming systems therefore require policies for late data, out-of-order events, replay, deduplication and windowing.

A simple event record may be represented as:

{device_id, event_time, ingest_time, temperature, unit, sequence_number}

Sensor data also presents calibration, drift, battery, connectivity and environmental challenges. A statistically sophisticated model cannot correct a sensor that systematically measures the wrong quantity unless calibration information is available.

2.5 Web sources

Web acquisition may involve public APIs, downloadable datasets or web crawling. The source's terms of use, robots policies, copyright, privacy expectations and rate limits must be respected. Pages can change structure without notice, so extraction should record URLs, retrieval timestamps, parser versions and source snapshots where lawful. Web data may be duplicated, strategically presented, incomplete or generated for human rather than machine interpretation.

2.6 Sampling and acquisition bias

Acquisition is also a statistical process. A dataset may underrepresent people without internet access, devices with poor connectivity, regions not covered by an API or events that fail validation. Selection bias, survivorship bias, nonresponse bias and temporal drift can invalidate conclusions even when storage is technically perfect. Data scientists should document the target population, sampling frame, inclusion rules and missingness mechanism.

3. SQL and Relational Data Management

3.1 Relational concepts

A relational database represents information through relations commonly implemented as tables. A row represents a tuple or record, and a column represents an attribute. A primary key uniquely identifies each row. A foreign key references a key in another table and expresses a relationship. Constraints such as NOT NULL, UNIQUE, CHECK and referential integrity protect valid states.

Consider a retail model with Customer, Product, Order and OrderLine entities. An order belongs to one customer, while an order can contain many products. OrderLine resolves the many-to-many relationship between Order and Product and stores quantity and unit price at the time of sale.

3.2 SQL operations

A query may filter, join, aggregate and order data:

SELECT c.customer_id,
       c.customer_name,
       SUM(ol.quantity * ol.unit_price) AS revenue
FROM Customer AS c
JOIN Orders AS o ON o.customer_id = c.customer_id
JOIN OrderLine AS ol ON ol.order_id = o.order_id
WHERE o.order_date >= '2025-01-01'
GROUP BY c.customer_id, c.customer_name
ORDER BY revenue DESC;

The query assumes that quantity and unit_price are recorded at line level, that monetary values use a consistent currency, and that each join relationship is correctly constrained. A one-to-many join can multiply rows; therefore, aggregation must be designed with cardinality in mind.

SQL's logical processing is conceptually based on FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT and ORDER BY, although database engines may optimize physical execution differently. NULL represents missing or unknown information, not zero or an empty string. Comparisons involving NULL generally evaluate to unknown; IS NULL and IS NOT NULL should be used for explicit tests.

3.3 Transactions and consistency

A transaction groups operations into an atomic unit. The ACID properties are:

• Atomicity: all operations occur or none does.
• Consistency: committed transactions preserve declared rules.
• Isolation: concurrent transactions behave according to the chosen isolation level.
• Durability: committed results survive failures according to the system's guarantees.

Strong transactional consistency is essential for many financial and operational processes. Analytical systems may use different trade-offs, such as append-oriented ingestion and eventual availability. The correct choice depends on business consequences, not fashion.

3.4 Data modelling and normalization

A conceptual model describes entities, attributes and relationships independently of implementation. A logical model maps these ideas to tables and keys. A physical model specifies indexes, partitions, storage formats and engine-specific features.

Normalization reduces redundancy and update anomalies. In a simplified interpretation, first normal form requires atomic values and no repeating groups; second normal form removes dependencies on only part of a composite key; third normal form removes relevant transitive dependencies. Normalization is not an absolute rule. Analytical warehouses often deliberately denormalize or use star schemas to simplify queries and improve performance.

A star schema contains a central fact table, such as Sales, connected to dimension tables such as Date, Product, Store and Customer. The fact table should have a clearly defined grain—for example, “one row per product per order line.” Ambiguous grain is a major source of incorrect metrics.

4. Metadata, Lineage and Data Quality

4.1 Metadata

Metadata is data about data. Technical metadata includes data types, schemas, partitions and file formats. Business metadata includes definitions, owners, classifications and acceptable uses. Operational metadata includes refresh time, job status, row counts and failure history. A data catalogue makes metadata discoverable, but a catalogue is useful only when descriptions are maintained and linked to actual assets.

A data dictionary should define fields such as:

4.2 Lineage and reproducibility

Lineage records where data originated, which transformations were applied, which code version was used and where outputs were published. It can be represented at dataset, table, column or record level. Lineage supports impact analysis, audit, debugging and scientific reproducibility.

A reproducible pipeline should pin or record source versions, transformation code, configuration, dependencies and execution timestamps. Reproducibility does not mean that every result is permanently identical: external sources, models and environments may change. It means that the conditions of a result are documented sufficiently for examination and controlled reruns.

4.3 Quality controls

Quality checks should be applied at ingestion and at meaningful transformation boundaries. Examples include:

• completeness: required fields are present;
• validity: values conform to type and domain rules;
• uniqueness: identifiers are not duplicated;
• consistency: related fields agree;
• timeliness: data arrives within an expected interval;
• referential integrity: foreign keys have valid parents;
• distribution checks: unusual shifts are investigated.

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

The formula assumes one value is evaluated per record and that missingness has been defined consistently. It measures presence, not correctness. A completeness rate of 100% can coexist with systematically false values.

5. Storage Architectures

5.1 Data warehouses

A data warehouse is a curated analytical repository optimized for reporting, business intelligence and governed queries. Data is commonly transformed into consistent dimensions and facts before or during loading. Warehouses provide strong SQL support, workload management, security controls and performance optimization, but historically required more schema design and transformation before ingestion.

5.2 Data lakes

A data lake stores large volumes of raw or lightly processed data, often in object storage. It can accommodate structured, semi-structured and unstructured material at relatively low storage cost. Its flexibility can become a liability if data is undocumented, duplicated, inaccessible or retained without purpose. The term “data swamp” describes a lake lacking discoverability, quality and governance.

A practical lake commonly separates zones such as raw, standardized and curated. Raw data should preserve source fidelity; standardized data resolves formats and basic quality issues; curated data is organized for documented analytical use. These zones are conventions, not universal standards.

5.3 Lakehouses

A lakehouse seeks to combine the openness and format flexibility of a lake with warehouse-like management, including transactional tables, schema enforcement or evolution, catalogues, governance and efficient analytical queries. Its value depends on implementation details: transaction support, concurrency, metadata scalability, interoperability and operational maturity. “Lakehouse” is an architectural pattern rather than a guarantee of quality.

5.4 Storage design decisions

Storage selection should consider latency, volume, variety, concurrency, durability, recovery objectives, cost, portability and compliance. Partitioning divides data into manageable logical units, often by date or region. Poor partitioning creates many tiny files or forces large scans. Indexes accelerate selected access patterns but add storage and write overhead. Compression reduces storage and transfer cost, but its benefit depends on data characteristics and computational resources.

Retention should be purpose-based. A retention policy specifies what is kept, for how long, why it is kept, who may access it and how it is securely deleted or anonymized. Backups are not substitutes for retention governance; retaining every backup forever can preserve data that should have been removed.

6. A Reference Acquisition-to-Analytics Pipeline

A robust pipeline commonly follows these stages:

1. Define the analytical purpose, population, grain and service-level needs.
2. Identify sources, owners, legal basis, access mechanisms and limitations.
3. Ingest data with authentication, logging, validation and deduplication.
4. Preserve an immutable or controlled raw representation.
5. Standardize schemas, encodings, timestamps, units and identifiers.
6. Apply quality rules and quarantine failed records rather than silently discarding them.
7. Curate data into documented tables or features.
8. Publish catalogue entries, lineage, quality metrics and access policies.
9. Monitor freshness, volume, failures, drift, cost and security events.
10. Archive or delete data according to approved lifecycle rules.

This pipeline can be batch, streaming or hybrid. Orchestration should make dependencies explicit and support retries, alerting, backfills and recovery. Observability is essential: a pipeline that runs successfully but loads zero records is not operationally healthy.

7. Applied Case: Illustrative Retail Lakehouse

Background

A fictional national retailer operates stores, an online channel and a loyalty programme. Store transactions are held in a relational point-of-sale system, web events are emitted as JSON, inventory devices send sensor readings, and suppliers deliver CSV files. Executives want near-daily sales reporting, stockout prediction and personalized promotions.

Problem

The retailer has inconsistent product identifiers, duplicated customer records, incompatible time zones and reports that disagree. Direct queries against point-of-sale databases slow checkout operations. Analysts download uncontrolled extracts, while privacy teams cannot reliably identify where customer data is used.

Analysis

The first requirement is to define grains and ownership. A sales fact row represents one product on one order line. Inventory observations represent one device reading at one event time. Customer identity is sensitive and should not be copied unnecessarily into every analytical table. Source-system identifiers must be retained for lineage, while governed surrogate keys can support integrated analysis.

Approach and solution

The retailer establishes CDC from point-of-sale databases into a raw object-storage zone, preserving transaction log positions and source timestamps. Web events are ingested through a streaming platform with event-time handling and a bounded lateness policy. Supplier files are validated for schema, checksum and duplicate delivery before acceptance.

A standardization layer converts timestamps to UTC while retaining the source time zone, maps product codes to a governed product master, and records rejected records in quarantine. Curated lakehouse tables contain Sales, Product, Store, Date, InventoryObservation and consent-controlled Customer dimensions. Data contracts specify required fields, units, ownership, refresh expectations and change procedures.

Role-based access limits raw personal data. Analysts receive tokenized customer identifiers and aggregated views unless a documented use requires more detail. Catalogue entries describe definitions, freshness, lineage and quality results. Automated tests check duplicate order lines, orphan product keys, negative quantities, freshness and abnormal row-count changes.

Outcome

Reporting no longer competes with checkout workloads, and analysts use a common definition of revenue. Inventory models can combine sales and sensor data while accounting for late events. Privacy reviews become more efficient because lineage and access records identify affected assets. The architecture does not eliminate disagreement or bias; it makes assumptions and failures more visible and manageable.

Learning points

The most important design decisions concerned semantics, ownership and governance rather than product branding. A lakehouse did not automatically solve identity resolution, time semantics or privacy. Clear grain, metadata, quality controls and lifecycle policies were prerequisites for useful machine learning.

8. Security, Ethics and Regulation

Data acquisition must follow principles of purpose limitation, data minimization, proportionality, accountability and security. Depending on jurisdiction and context, privacy obligations may include lawful collection, notice, access, correction, deletion, retention limits, breach response and restrictions on sensitive or cross-border processing. Legal requirements differ; organisations should obtain qualified legal advice rather than treating this chapter as legal counsel.

Technical safeguards include encryption in transit and at rest, key management, least-privilege access, network segmentation, secrets management, audit logs, tokenization and tested recovery procedures. De-identification is not automatically irreversible. Combining quasi-identifiers with external datasets can permit re-identification.

Ethically, data scientists should ask who is absent, who bears risk, whether consent matches the proposed use, and whether collection changes behaviour. Scraped public data may still contain personal or sensitive information. Model performance should be evaluated across relevant groups, and provenance should be preserved for training and evaluation datasets.

9. Contemporary Developments and Critical Evaluation

Data products increasingly support real-time decision-making, vector search, multimodal analytics and foundation-model applications. These developments increase the importance of data contracts, semantic metadata, feature and embedding lineage, access controls and evaluation datasets. Synthetic data may help testing or reduce exposure, but it can reproduce source bias and fail to represent rare cases.

Data mesh approaches emphasize domain ownership, data as a product, self-service platforms and federated governance. They can improve accountability in large organizations but may fragment definitions unless interoperability and central policy are strong. Data fabric approaches emphasize metadata-driven integration across distributed environments. Neither is a substitute for clear ownership, quality measurement and investment in operating processes.

A mature data strategy treats architecture as socio-technical. Tools matter, but definitions, incentives, skills, stewardship and responsible decision-making determine whether data creates durable value.

Best Practices Checklist

• Define purpose, population, grain and latency before selecting technology.
• Preserve source fidelity and record provenance.
• Separate event time from ingestion or processing time.
• Use explicit schemas, units, time zones and missing-value conventions.
• Protect production systems from uncontrolled analytical queries.
• Test joins and cardinalities before trusting aggregates.
• Maintain catalogues, dictionaries, lineage and ownership.
• Quarantine invalid data and monitor quality trends.
• Apply least privilege, minimization and retention controls.
• Design for replay, backfill, recovery and schema evolution.
• Evaluate acquisition bias and representativeness.
• Treat data definitions as governed organizational assets.

Chapter Summary

Data acquisition is the disciplined process of obtaining data from operational systems, APIs, files, sensors and web sources. Structured, semi-structured and unstructured data require different representation and processing strategies. SQL and relational modelling provide integrity, expressive querying and transaction support; normalization reduces redundancy, while star schemas often support analytical performance.

Metadata, lineage and quality controls make data discoverable, interpretable, auditable and reproducible. Warehouses prioritize governed analytical access, lakes provide flexible storage for varied data, and lakehouses seek to combine these properties through managed tables and metadata. Successful architectures define grain, ownership, schemas, lifecycle, security and observability.

Data management is inseparable from ethics and governance. Privacy, access control, retention, provenance, sampling bias and re-identification risk must be considered from acquisition onward. Contemporary platforms can improve scale and integration, but they do not replace sound definitions, stewardship and critical evaluation.

Key Terms

Structured data: Data organized according to a predefined schema, commonly in tables.

Semi-structured data: Data with organizational markers but without one rigid tabular schema.

Unstructured data: Data such as text, images, audio or video without a regular tabular structure.

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

Change-data capture: A method of recording inserts, updates and deletes from a source system.

Event time: The time at which an event actually occurred.

Processing time: The time at which a system processes an event.

Primary key: An attribute or attribute set that uniquely identifies a row.

Foreign key: An attribute that references a key in another table.

Normalization: Relational design practice that reduces redundancy and update anomalies.

Fact table: A table containing measurable events at a defined grain.

Dimension table: A table containing descriptive context for facts.

Metadata: Information describing a dataset, including its meaning, structure and use.

Data lineage: A record of data origins, transformations and destinations.

Data quality: The degree to which data is fit for its intended purpose.

Data warehouse: A governed repository optimized for analytical queries.

Data lake: Flexible storage for large volumes of data in varied formats.

Lakehouse: An architectural pattern combining lake flexibility with managed analytical capabilities.

Data contract: An agreement specifying data structure, semantics, quality and operational expectations.

Data minimization: Collecting and retaining no more personal data than the purpose requires.

Review Questions

Short Answer Questions

1. Distinguish structured, semi-structured and unstructured data, giving one example of each.
2. Why should event time and processing time be stored separately in streaming systems?
3. What are the four ACID properties of a database transaction?
4. Explain the difference between a primary key and a foreign key.
5. Why is metadata necessary for analytical reuse?

Long Answer Questions

1. Compare data warehouses, data lakes and lakehouses in terms of schema, governance, cost, flexibility and typical workloads.
2. Explain how a relational retail schema could represent customers, orders, products and order lines. Identify the grain of the order-line fact.
3. Design an ingestion strategy combining APIs, batch files and sensor streams. Include validation, retry, deduplication and late-data policies.
4. Discuss normalization and denormalization, including circumstances in which each is appropriate.
5. Explain how privacy, security, lineage and retention should be incorporated throughout the data lifecycle.

Analytical / Application Questions

1. A dashboard reports revenue twice as high as the finance system after a new product table is joined to sales. Diagnose likely cardinality and key problems, and propose tests and corrections.
2. Design a lakehouse architecture for a hospital research project using clinical records, medical images and wearable streams. Address access control, consent, time semantics, metadata and retention.
3. An API delivers 10,000 records per page, permits 60 requests per minute and occasionally repeats a page after a timeout. Propose an acquisition algorithm that handles pagination, rate limiting, retries, idempotency and auditability.

Multiple Choice Questions with Answers

Question 1

Which characteristic most directly distinguishes semi-structured data from structured data?

A. It contains no information about organization.

B. It uses organizational markers but does not require one rigid tabular schema.

C. It can only be stored in relational databases.

D. It is always generated by sensors.

Correct Answer: B. It uses organizational markers but does not require one rigid tabular schema.

Question 2

What is the primary purpose of change-data capture?

A. To encrypt every database column

B. To replace all database constraints

C. To record source inserts, updates and deletes for downstream systems

D. To convert images into tables

Correct Answer: C. To record source inserts, updates and deletes for downstream systems

Question 3

In a streaming pipeline, event time refers to:

A. When the event actually occurred

B. When the database backup ran

C. When the analyst queried the event

D. When the storage system was installed

Correct Answer: A. When the event actually occurred

Question 4

Which SQL condition correctly tests whether a value is missing?

A. value = NULL

B. value == NULL

C. value IS NULL

D. value LIKE NULL

Correct Answer: C. value IS NULL

Question 5

Which table normally resolves a many-to-many relationship between orders and products?

A. A calendar table

B. An order-line table

C. A user-permission table

D. A source-log table

Correct Answer: B. An order-line table

Question 6

A data warehouse is primarily optimized for:

A. Governed analytical querying and reporting

B. Uncontrolled storage of every binary file

C. Replacing all operational transactions

D. Capturing raw sensor signals only

Correct Answer: A. Governed analytical querying and reporting

Question 7

Which statement about completeness is correct?

A. It proves that every value is accurate.

B. It measures the proportion of expected values that are present.

C. It is identical to uniqueness.

D. It can only be calculated for images.

Correct Answer: B. It measures the proportion of expected values that are present.

Question 8

What does data lineage document?

A. Only the physical location of a server

B. The origin, transformations and destinations of data

C. The market price of a dataset

D. The personal opinions of analysts

Correct Answer: B. The origin, transformations and destinations of data

Question 9

Which practice best supports protection of personal data?

A. Granting all analysts unrestricted raw access

B. Keeping all data indefinitely

C. Applying least privilege and collecting only what the purpose requires

D. Removing all metadata

Correct Answer: C. Applying least privilege and collecting only what the purpose requires

Question 10

In an analytical fact table, why must grain be explicitly defined?

A. To determine what one row represents and prevent incorrect aggregation

B. To guarantee that all values are strings

C. To eliminate the need for primary keys

D. To ensure that no data is ever late

Correct Answer: A. To determine what one row represents and prevent incorrect aggregation