6. Data Acquisition, Quality and Preparation

Introduction and Context

Data science begins before modelling. A sophisticated algorithm cannot compensate for data that are incomplete, biased, duplicated, poorly defined, or collected in a manner that permits information from the future to enter the past. Data acquisition and preparation therefore constitute an intellectual and engineering foundation of data science rather than a minor preliminary task.

Data may be obtained from relational databases, enterprise systems, documents, application programming interfaces (APIs), sensors, websites, social platforms, experiments, and third-party providers. These sources differ in structure, reliability, ownership, update frequency, legal status, and semantic meaning. The central challenge is not merely to gather large quantities of data, but to create a trustworthy analytical asset whose values, relationships, provenance, and limitations are understood.

This chapter presents a lifecycle for acquiring, integrating, assessing, cleaning, transforming, and governing data. It also examines missing values, outliers, duplication, inconsistency, leakage, and quality controls. These issues are especially important in AI and machine learning because preparation choices influence model validity, fairness, reproducibility, and operational performance.

Learning Objectives

After studying this chapter, students should be able to:

• distinguish major data sources and acquisition methods;
• design an acquisition and integration workflow;
• assess data quality using appropriate dimensions and controls;
• diagnose and treat missing values, outliers, duplicates, and inconsistencies;
• explain data leakage and prevent it during preparation and modelling;
• apply governance, documentation, ethical, and security principles;
• evaluate preparation decisions in professional and organizational settings.

From Data Source to Analytical Dataset

A useful preparation process begins with a data specification. This specification identifies the unit of analysis, required variables, time period, population, target variable, acceptable latency, ownership, and intended use. For example, a customer-retention project must clarify whether one row represents a customer, an account, or a customer-month. Without this decision, even correctly collected data may be analysed incorrectly.

A typical lifecycle contains six connected activities:

1. Source discovery and assessment: identify relevant sources, owners, access conditions, formats, update schedules, and known limitations.

2. Acquisition: retrieve data through queries, file transfer, APIs, event streams, sensors, or approved platform interfaces.

3. Ingestion and storage: preserve the original data in a controlled raw layer before applying transformations.

4. Profiling and quality assessment: examine schema, distributions, missingness, uniqueness, validity, and relationships.

5. Cleaning and transformation: correct or document errors, standardize representations, integrate sources, and create analysis-ready variables.

6. Validation and publication: test the resulting dataset, record lineage, obtain appropriate approval, and release a version for analysis.

The separation between raw and transformed data is particularly important. If source data are overwritten, analysts cannot reconstruct what was received, investigate an error, or reproduce a previous model. A layered architecture commonly includes a raw layer, a standardized or staging layer, and a curated analytical layer.

Acquisition from Diverse Sources

Databases and Enterprise Systems

Relational databases provide structured records organized into tables with keys and relationships. SQL queries can select relevant columns, filter records, join entities, and aggregate transactions. Analysts must understand primary keys, foreign keys, slowly changing attributes, transaction timing, and the difference between an operational database and an analytical warehouse.

A join can silently create errors. Joining a customer table to a transaction table produces multiple rows per customer, not one row per customer. If customer-level outcomes are required, transactions must first be aggregated or the resulting grain must be explicitly accepted. Poorly designed joins can cause double counting and inflated model performance.

Files and Documents

CSV, spreadsheets, JSON, XML, and compressed files remain common in organizations. File-based acquisition requires attention to delimiters, encodings, decimal conventions, date formats, header rows, hidden spreadsheet formulas, merged cells, and inconsistent column names. Documents may require parsing or optical character recognition, but extracted text should be treated as an interpretation subject to validation rather than as unquestionable truth.

APIs and Digital Platforms

APIs provide programmatic access to applications and services. An acquisition process should manage authentication, pagination, rate limits, retries, version changes, response schemas, and request logging. Time-zone handling is essential when records from multiple platforms are combined. Platform data may also reflect changing user interfaces, moderation policies, sampling rules, or terms of service.

Web scraping and platform collection require authorization and respect for privacy, access controls, intellectual property, and contractual restrictions. A technically successful collection may still be legally or ethically unacceptable.

Sensors and Streaming Data

Sensors generate observations continuously or at scheduled intervals. Important attributes include sampling frequency, calibration, device identity, measurement unit, location, battery status, and transmission delay. Sensor values may be absent because a device failed, because communication was interrupted, or because the measured event did not occur. These causes should not automatically be treated as equivalent missing values.

Streaming systems introduce event time and processing time. A late-arriving event can be valid even if it arrives after an analytical window has closed. Preparation pipelines must therefore define lateness policies and maintain an auditable record of corrections.

Experiments and Human-Generated Data

Survey, experiment, and annotation data require attention to sampling, consent, question wording, inter-rater agreement, and response bias. Human labels may be inconsistent or influenced by the annotator's background. A label-quality process can include clear guidelines, training examples, independent double annotation, adjudication, and monitoring of disagreement.

Data Quality as a Multidimensional Property

Data quality is fitness for a defined purpose. A dataset is not simply “high quality” in the abstract; quality depends on the decisions it must support. Common dimensions include:

• Accuracy: values correctly represent the underlying entity or event.
• Completeness: required records and fields are present.
• Consistency: related representations agree across fields and systems.
• Validity: values conform to permitted types, ranges, formats, and rules.
• Uniqueness: an entity or event is not represented inappropriately more than once.
• Timeliness: data are available and current enough for the intended use.
• Integrity: relationships, keys, and constraints are preserved.
• Provenance: origin, transformations, and custodianship can be traced.

Quality should be measured using explicit rules. Examples include “customer identifier must not be null,” “order quantity must be a positive integer,” “end date cannot precede start date,” and “each transaction identifier must be unique.” A quality score is useful only when its definition, denominator, thresholds, and consequences are clear.

Profiling tools can report column types, distinct-value counts, missingness, quantiles, frequency distributions, and suspicious patterns. Profiling is diagnostic, not corrective: an unusually frequent value may be an error, a legitimate business concentration, or a system default.

Missing Values: Diagnosis Before Treatment

Missingness may arise from non-response, unavailable measurements, failed devices, optional fields, changes in system design, or conditional data collection. The reason matters. A blank income field in a voluntary survey may reflect refusal, whereas a missing laboratory value may reflect that a test was not clinically indicated.

Three classical mechanisms are useful for reasoning: missing completely at random (MCAR), missing at random conditional on observed variables (MAR), and missing not at random (MNAR), where missingness depends on unobserved or unreported values. These are modelling assumptions, not labels that can always be proven from the data.

Treatment options include deletion, simple imputation, model-based imputation, and explicit missingness indicators. Listwise deletion is defensible only when its impact on sample composition and statistical power is acceptable. Mean imputation is simple but reduces variation and can distort relationships. More advanced approaches include multiple imputation, nearest-neighbour methods, and predictive models, but these require validation and must be fitted without using information from evaluation data.

For time series, forward filling may be sensible for a slowly changing status but inappropriate for a rapidly changing measurement. Missing categorical values may be represented as an explicit “unknown” category when absence itself carries meaning. Every treatment should be documented, including the original missingness rate and assumptions made.

Outliers and Anomalous Observations

An outlier is an observation unusually distant from others under a specified context. It is not automatically an error. A high-value transaction may indicate fraud, a genuine premium purchase, or a data-entry problem. Outliers may be global, contextual, or collective: a temperature can be normal globally but abnormal for a particular season, and a sequence can be suspicious even when individual values appear normal.

Detection methods include domain limits, box-plot rules based on the interquartile range, robust z-scores based on the median and median absolute deviation, clustering, and time-series residual analysis. Statistical detection should be combined with domain investigation. Possible responses are correction from a verified source, retention, transformation, winsorization, exclusion under a documented rule, or separate anomaly modelling.

Removing observations solely to improve model accuracy is poor practice. The decision should consider the data-generating process, the intended population, and the cost of false removal.

Duplicates, Conflicts, and Standardization

Duplicates can be exact or approximate. Exact duplicates share all relevant fields, whereas approximate duplicates may contain spelling, formatting, or address variations. Entity resolution links records referring to the same real-world entity. Deterministic matching uses reliable identifiers; probabilistic matching compares fields and estimates the likelihood of identity. False merges can be more damaging than missed matches, particularly in health, finance, and public-sector applications.

Standardization reduces superficial variation. Dates should use an unambiguous representation while preserving time zone where relevant. Units should be converted through documented rules. Text categories such as “M,” “Male,” and “male ” may be harmonized only after confirming that they represent the same concept. A data dictionary should define field meanings, allowed values, units, and business rules.

Inconsistency may be syntactic or semantic. Two systems may both use a field named “customer status” while one means account activity and the other means marketing consent. Integration therefore requires semantic mapping, not merely column renaming.

Integration and Feature Preparation

Data integration combines sources while preserving grain, keys, temporal order, and meaning. A successful integration plan identifies the master entity, matching keys, source precedence, conflict resolution rules, and treatment of late or historical changes.

Feature preparation may include scaling, encoding categorical variables, extracting date components, aggregating events, and constructing text or image representations. Transformations should be appropriate to the algorithm and the domain. Standardization based on mean and standard deviation, for example, should normally be estimated from the training set and then applied to validation and test data.

A central principle is fit transformations on training data only. This principle prevents data leakage: the unintended use of information that would not be available at the time of prediction. Leakage may occur when a target-derived variable is included, when a post-outcome field is used, when duplicates cross train and test partitions, or when imputation, feature selection, scaling, or oversampling is performed before splitting the data.

For temporal problems, random splitting can leak future patterns into the past. Time-based splits, rolling evaluation, and “as-of” data construction are safer. Leakage often produces excellent offline results and disappointing deployment performance, making it one of the most dangerous preparation failures.

A Framework for Quality Control

Quality control should be designed as a set of preventive, detective, and corrective controls. Preventive controls constrain collection through validation forms, schemas, reference tables, and access permissions. Detective controls identify violations through automated tests, profiling, reconciliation, and drift monitoring. Corrective controls repair data, quarantine suspect records, notify owners, or trigger reprocessing.

A practical control framework includes:

• schema checks for names, types, and required fields;
• domain checks for ranges, formats, and permitted categories;
• relational checks for key integrity and referential consistency;
• reconciliation checks against trusted totals or source counts;
• freshness checks for expected delivery time;
• distribution and drift checks for substantial changes;
• leakage checks using temporal availability and feature lineage;
• audit logging for changes, failures, approvals, and releases.

Data contracts formalize expectations between data producers and consumers. They may specify schema, semantics, quality thresholds, ownership, service-level expectations, and change-notification procedures. In automated pipelines, failed critical tests should stop publication or route data to quarantine rather than silently producing a defective dataset.

Governance, Ethics, and Security

Data preparation is also a governance activity. Organizations must establish lawful purpose, data minimization, retention rules, access controls, consent or another appropriate legal basis, and procedures for handling sensitive information. Personally identifiable information should be protected through techniques such as role-based access, encryption, pseudonymization, and controlled environments. Pseudonymization reduces direct identification risk but does not guarantee anonymity.

Bias can enter during acquisition when some populations are systematically underrepresented, when sensors work differently across groups, or when labels reflect unequal institutional decisions. Removing a protected attribute does not necessarily remove its influence because proxy variables may remain. Quality assessment should therefore examine subgroup completeness, error rates, representativeness, and the consequences of preparation decisions.

Documentation should include source, purpose, collection period, transformations, known limitations, responsible owners, and permitted uses. Dataset documentation and model documentation help analysts and decision-makers understand what a data product can and cannot support.

Illustrative Case Study: Predicting Missed Medical Appointments

Background

A regional clinic wants to predict which appointments are likely to be missed so that reminders and transport assistance can be targeted. The proposed dataset combines appointment records, patient demographics, reminder logs, transport information, and historical attendance.

Situation and Problem

An initial model shows very high predictive performance. However, after deployment, performance declines sharply. An investigation also reveals that some patients receive fewer reminders and that addresses are stored in incompatible formats across systems.

Analysis

The data team discovers that the dataset contains a field called `attendance_status_updated`, entered after the appointment. It directly reveals whether the appointment was missed and therefore constitutes target leakage. The team also finds duplicate patient records caused by changes in surname and address formatting. Missing transport information is not random: it is more common for patients using community services. Finally, a random train-test split places appointments from the same patient in both partitions, allowing patient-specific patterns to leak across the evaluation boundary.

Approach and Solution

The team defines the prediction point as 24 hours before the appointment. Only information available at that time is permitted. Records are split chronologically, and repeated appointments from the same period are handled to prevent inappropriate cross-partition duplication. Patient entities are reconciled using a verified identifier supplemented by cautious probabilistic matching.

The team removes post-appointment fields, documents missingness, and creates a transport-information indicator rather than assuming that missing means “no transport need.” Address data are standardized only to the level necessary for service planning. Quality tests check identifier integrity, event-time availability, reminder timestamps, and subgroup performance. A governance review approves restricted use of sensitive variables and requires monitoring for unequal error rates.

Outcome

The revised offline performance is lower but more credible. During a controlled pilot, the clinic measures not only predictive accuracy but also reminder capacity, attendance changes, patient burden, and subgroup effects. The model becomes one component of an operational process rather than an automatic decision-maker.

Learning Points

The case demonstrates that a lower but leakage-free score is more useful than an inflated score. It also shows that data quality includes temporal validity, entity resolution, missingness interpretation, fairness, and operational evaluation. Preparation decisions must be tied to the actual decision point and organizational purpose.

Common Mistakes and Professional Practice

Frequent errors include collecting data before defining the unit of analysis, overwriting raw files, trusting column names without semantic validation, treating all missingness identically, deleting outliers automatically, ignoring time zones, joining tables without checking row multiplication, and evaluating transformations on the full dataset. Another mistake is treating a dashboard or automated pipeline as trustworthy merely because it runs without technical errors.

Professional practice requires reproducible scripts, versioned datasets, documented assumptions, automated tests, peer review, controlled access, and clear ownership. A preparation log should record what changed, why it changed, who approved it, and how the result was validated. Data quality is sustained through organizational accountability, not through one-time cleaning.

Current and Emerging Developments

Modern data platforms increasingly support automated lineage, data observability, schema evolution, and pipeline orchestration. Machine learning is being used to identify anomalous records, suggest entity matches, classify documents, and prioritize quality investigations. However, automated correction can propagate systematic errors at scale. Human review remains important for ambiguous cases and high-impact uses.

Synthetic data and privacy-enhancing technologies may support development when direct access to sensitive data is restricted, but synthetic data must be evaluated for utility, privacy leakage, and representational bias. Data products are also moving toward continuous monitoring, in which freshness, distributional drift, fairness indicators, and model-input quality are tracked after deployment.

Chapter Summary

Reliable data science depends on disciplined acquisition, integration, preparation, and governance. Data sources differ in structure, timing, semantics, reliability, and legal conditions. Quality should be assessed through accuracy, completeness, consistency, validity, uniqueness, timeliness, integrity, and provenance. Missing values require diagnosis of their causes; outliers require contextual investigation; duplicates require careful entity resolution; and inconsistent representations require semantic as well as syntactic standardization.

Preparation must preserve grain, temporal order, and lineage. Leakage prevention requires that transformations be fitted on training data, features be available at the prediction time, and evaluation splits reflect the real deployment context. Automated quality controls, data contracts, documentation, privacy safeguards, and subgroup analysis make data products more dependable. The ultimate objective is not perfectly clean data, but data that are fit for a defined purpose, transparent in their limitations, and safe to use.

Key Terms

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

Data integration: Combining data sources while preserving relationships, meaning, and provenance.

Data quality: The fitness of data for a defined analytical or operational purpose.

Data profiling: Systematic examination of data structure, distributions, missingness, and anomalies.

Data lineage: A record of data origin, movement, transformation, and use.

Data contract: A formal agreement specifying data schema, semantics, quality, ownership, and delivery expectations.

Missing completely at random: A missingness mechanism unrelated to observed or unobserved values.

Missing at random: Missingness explainable by variables observed in the dataset, conditional on those variables.

Missing not at random: Missingness related to unobserved or unreported values.

Outlier: An observation unusually distant from others under a specified context.

Entity resolution: The process of determining which records refer to the same real-world entity.

Data standardization: Converting representations, units, formats, or categories to agreed conventions.

Data leakage: Use of information in preparation or modelling that would not be available at prediction time.

Event time: The time at which an event actually occurred, distinct from its processing time.

Data drift: A meaningful change in data distributions or relationships over time.

Referential integrity: Correctness of relationships between linked records and keys.

Pseudonymization: Replacing direct identifiers with coded references while retaining controlled linkage.

Data observability: Monitoring data systems for freshness, quality, schema, lineage, and operational failures.

Review Questions

Short Answer Questions

1. Why should the unit of analysis be defined before data are integrated?

2. Distinguish accuracy, validity, and completeness as data-quality dimensions.

3. What is the difference between event time and processing time?

4. Why is mean imputation potentially misleading?

5. Define data leakage and give one example.

Long Answer Questions

1. Explain a complete workflow for acquiring, preparing, validating, and publishing an analytical dataset.

2. Discuss the causes of missing values and compare major treatment strategies.

3. Explain how duplicates and semantic inconsistencies can affect multi-source integration.

4. Describe preventive, detective, and corrective data-quality controls with examples.

5. Discuss the ethical, governance, and security responsibilities associated with preparing sensitive data.

Analytical / Application Questions

1. A transaction table is joined to a customer table and the number of customer records triples. Diagnose the likely cause and propose validation steps.

2. A model has excellent random-split performance but fails after deployment in a forecasting setting. Analyse possible leakage mechanisms and design a better evaluation procedure.

3. A sensor dataset contains missing readings, extreme values, and a sudden change in measurement distribution after a device upgrade. Design a preparation and monitoring strategy that distinguishes technical failure from genuine change.

22. Multiple Choice Questions with Answers

Question 1

Which practice most directly prevents information from the evaluation set influencing a standardized feature?

A. Fit the scaler on the training set and apply it to validation and test sets

B. Fit the scaler on the complete dataset before splitting

C. Remove all variables with large variance

D. Replace missing values with zero after evaluation

Correct Answer: A. Fit the scaler on the training set and apply it to validation and test sets

Question 2

A customer table is joined to a transaction table without aggregation. What is the most likely consequence?

A. Every customer will appear exactly once

B. Customers with multiple transactions will produce multiple rows

C. All transaction identifiers will become missing

D. The join will automatically remove duplicate transactions

Correct Answer: B. Customers with multiple transactions will produce multiple rows

Question 3

Which situation is an example of target leakage?

A. Using a customer's age recorded at registration

B. Using appointment status updated after the appointment to predict attendance

C. Encoding a categorical region using training data

D. Aggregating prior transactions before the prediction date

Correct Answer: B. Using appointment status updated after the appointment to predict attendance

Question 4

Why should an outlier not automatically be deleted?

A. Outliers are always caused by formatting errors

B. Outliers can represent genuine and important events

C. Deleting outliers always increases bias

D. Statistical methods cannot identify unusual observations

Correct Answer: B. Outliers can represent genuine and important events

Question 5

Which quality dimension concerns whether a value conforms to permitted formats or ranges?

A. Validity

B. Timeliness

C. Uniqueness

D. Provenance

Correct Answer: A. Validity

Question 6

A missing transport field occurs mainly for patients using community services. What does this suggest?

A. Missingness may be systematically related to the population

B. The field is missing completely at random

C. The field should always be replaced by its mean

D. The records must be exact duplicates

Correct Answer: A. Missingness may be systematically related to the population

Question 7

What is the primary purpose of preserving a raw data layer?

A. To eliminate the need for documentation

B. To ensure all values are immediately standardized

C. To support auditability, reconstruction, and reproducibility

D. To guarantee that the data contain no errors

Correct Answer: C. To support auditability, reconstruction, and reproducibility

Question 8

Which approach is most appropriate for a time-dependent prediction problem?

A. Use a random split regardless of dates

B. Use a chronological split that respects information availability

C. Place future observations in the training set

D. Duplicate recent observations in every partition

Correct Answer: B. Use a chronological split that respects information availability

Question 9

What does entity resolution attempt to determine?

A. Whether a value is normally distributed

B. Whether records refer to the same real-world entity

C. Whether an API has exceeded its rate limit

D. Whether a model has high accuracy

Correct Answer: B. Whether records refer to the same real-world entity

Question 10

Which is a preventive data-quality control?

A. Quarantining records after a failed pipeline

B. Reviewing a distribution after publication

C. Enforcing a schema and permitted-value rules during collection

D. Deleting anomalous records from a final report

Correct Answer: C. Enforcing a schema and permitted-value rules during collection