Data Sources, Quality and Preparation

Introduction

Predictive analytics is often described as a modelling activity, but the quality of a predictive system is usually determined before a model is trained. Data must be acquired from appropriate sources, combined without distorting meaning, assessed for quality, and transformed into a form that algorithms can use. Poor preparation can produce inaccurate predictions, unfair decisions, misleading performance measures, and operational failures.

This chapter presents a practical framework for preparing structured and unstructured data. It addresses data sources, integration, quality dimensions, missing values, duplicates, outliers, inconsistent formats, sampling, data leakage, and reproducible workflows. The emphasis is on professional judgement: preparation is not merely a sequence of technical operations, but a process of preserving the meaning and provenance of information.

Learning Objectives

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

• distinguish major structured, semi-structured and unstructured data sources;
• assess data quality using appropriate dimensions and metrics;
• integrate data from multiple systems while preserving keys, grain and meaning;
• treat missing values, duplicates, outliers and inconsistent formats appropriately;
• identify sampling problems and data leakage;
• design a documented and reproducible preparation workflow; and
• evaluate ethical, governance and operational implications of data preparation.

1. The Data Preparation Problem

A dataset is not simply a collection of values. Each value has a definition, unit, time reference, source, owner and degree of uncertainty. For example, “customer” may mean a registered account, a paying organisation, or an individual user. “Revenue” may be recorded before or after tax, refunds and discounts. If these meanings are not established, technically correct calculations may still be analytically wrong.

A useful principle is data grain: the level at which one row represents an entity or event. A sales table may have one row per invoice, invoice line, customer, or daily customer summary. Joining tables with different grains can silently duplicate measures. If a customer table is joined to a transaction table, customer attributes may repeat legitimately; however, summing a customer-level balance after the join may overstate the total.

Preparation therefore involves four linked activities:

1. Acquisition: obtaining data lawfully and reliably.
2. Understanding: documenting definitions, grain, provenance and limitations.
3. Transformation: correcting, standardising and restructuring data.
4. Validation: demonstrating that the prepared data is fit for its intended use.

2. Data Sources and Data Structures

Structured data

Structured data follows a defined schema, usually in tables. Examples include customer relationship management records, accounting transactions, inventory systems, banking records and relational databases. Its strengths are consistency, queryability and well-defined fields. Its weaknesses include rigid schemas, missing context and possible biases introduced by business processes.

Semi-structured data

Semi-structured data has organisational markers but does not necessarily follow a fixed table design. Examples include JSON, XML, application logs and event streams. A JSON record may contain nested objects or optional fields. Preparation commonly requires flattening nested structures, managing changing schemas and preserving event timestamps.

Unstructured data

Unstructured data includes free-text reviews, emails, documents, images, audio and video. It requires specialised extraction or representation before conventional modelling. Text may be represented using tokens, term-frequency features or embeddings; images may be represented by pixels or learned feature vectors. Converting unstructured data into features can remove context and introduce bias, so the transformation must be documented and evaluated.

Internal and external sources

Internal sources include operational databases, point-of-sale systems, web analytics, employee records and service tickets. External sources include government statistics, market research, open-data portals, weather services and purchased datasets. External data may improve explanatory power but can introduce licensing restrictions, incompatible definitions and uncertain collection methods.

A source inventory should record the owner, purpose, update frequency, access method, sensitivity, retention requirement, schema and known limitations. A data dictionary should define each field, including type, unit, permitted values and business meaning.

3. Data Quality as Fitness for Use

Data quality is not an absolute property. A dataset can be adequate for exploratory analysis but unsuitable for regulatory reporting or automated decisions. The main dimensions are shown below.

For a field with n records and m missing values, the missingness rate is:

Here, r_missing is the percentage of missing values, m is the number missing, and n is the total number of records. The measure assumes that each record has equal importance. If records have survey weights or different exposure times, a weighted measure may be more appropriate.

Quality assessment should examine patterns, not only overall percentages. Missingness concentrated in a particular region, product, time period or customer group may indicate a process problem or create unfair model performance.

4. Acquiring and Integrating Data

Extract, transform and load

A common architecture is extract, transform and load (ETL): data is extracted from sources, transformed in a processing environment, and loaded into a target system. In extract, load and transform (ELT), raw data is first loaded into a scalable storage platform and transformed there. ELT can preserve raw data for later analysis, while ETL may be preferable when strict controls are required before loading.

A sound workflow retains an immutable raw layer, a cleaned layer and an analysis-ready layer. Raw data should not be overwritten. This separation supports auditing, reprocessing and investigation of errors.

Keys and joins

A primary key uniquely identifies a record within a table. A foreign key refers to a key in another table. Before joining tables, analysts should check:

• the grain of each table;
• whether keys are unique where expected;
• whether key formats match;
• whether the relationship is one-to-one, one-to-many or many-to-many; and
• whether unmatched records are expected.

An accidental many-to-many join can multiply rows. For example, joining a customer table containing one row per customer to two transaction tables containing multiple rows per customer may create every possible combination of transactions. Aggregating each transaction table to the required grain before joining is often safer.

Time alignment

Data integration must distinguish event time, processing time and availability time. A weather value recorded at 10:00 may not have been available to a forecasting system until 10:15. For realistic model evaluation, features must be limited to information available at the prediction time. Time zones, daylight-saving changes and date boundaries must also be standardised.

5. Handling Missing Values

Missingness may arise from non-response, system failure, optional fields, censoring, or the fact that a value does not apply. A blank, zero, “unknown” and “not applicable” are not automatically equivalent.

Three useful conceptual categories are:

• Missing completely at random: missingness is unrelated to observed or unobserved values.
• Missing at random: missingness depends on observed information, such as a field being more often absent for a particular region.
• Missing not at random: missingness depends on the unobserved value itself, such as high-income respondents declining to report income.

These categories are assumptions about the data-generating process, not labels that can always be proven from the dataset.

Common strategies include:

• deleting rows, when the number is small and deletion is unlikely to bias results;
• deleting a field, when it is largely absent and has little analytical value;
• imputing a numeric value using a median or a model;
• imputing a categorical value using a meaningful “Unknown” category; and
• adding a missingness indicator when absence itself may carry information.

Mean imputation is simple but reduces variation and can distort relationships. Median imputation is more resistant to skewed values. Model-based imputation can be more accurate but may create false certainty and must be performed without using information from the validation or test period.

Imputation values must be learned from the training data and then applied to validation and test data. Otherwise, information from evaluation data leaks into training.

6. Duplicates and Record Identity

Duplicates may be exact copies, repeated transactions, or legitimate repeated events. Removing every repeated-looking row is unsafe. A duplicate rule should be based on business identity, such as customer ID plus transaction ID, or product ID plus effective date.

Useful checks include:

• exact row duplicates;
• duplicate business keys;
• near-duplicates caused by spelling or formatting differences; and
• repeated events with different timestamps.

When duplicate records conflict, the resolution rule should be explicit. Possible rules include retaining the latest verified record, selecting the source with higher authority, or combining fields after review. The original records and resolution decision should be retained for auditability.

7. Inconsistent Formats and Standardisation

Inconsistency may occur in dates, currencies, units, category names, text encoding and identifiers. Examples include “2025-03-04”, “04/03/2025” and “March 4, 2025”; these may represent different dates depending on locale.

A preparation workflow should:

• parse dates using an explicit locale and time zone;
• store dates in a standard internal representation;
• convert units using documented conversion factors;
• record currency and exchange-rate dates;
• standardise case, whitespace and punctuation where appropriate;
• map categories through a controlled reference table; and
• preserve the original value alongside the standardised value when traceability matters.

Standardisation must not erase meaningful distinctions. For example, merging “cancelled” and “returned” into “failed” may simplify analysis but destroy information needed for operational decisions.

8. Outliers and Anomalies

An outlier is an observation unusually distant from the general pattern. It may represent a genuine extreme event, a measurement error, fraud, a data-entry mistake or a population difference. It should not automatically be removed.

For a numeric variable x, a standardised score is:

Here, x is an observation, μ is the population or reference mean, and σ is the corresponding standard deviation. The formula is most informative for approximately symmetric distributions and is sensitive to extreme values.

A robust alternative uses the median and median absolute deviation (MAD):

Here, xᵢ is the i-th observation. MAD is less affected by extreme values than the standard deviation. Neither method proves that an observation is erroneous.

Possible responses include correcting a verified data-entry error, retaining a valid value, transforming a heavily skewed variable, capping values under a documented policy, or using a model less sensitive to extremes. Decisions should consider business context and the cost of false alarms.

9. Sampling and Representativeness

A sample is useful only to the extent that it represents the target population for the decision. Problems arise through convenience sampling, non-response, coverage gaps, survivorship bias and temporal drift.

If a population is divided into groups and a sample is drawn from each group, this is stratified sampling. It can improve representation of small but important groups. However, if sampling fractions differ, estimates may require weights. A basic sampling weight for group h is:

where Nₕ is the population size of group h and nₕ is its sample size. This formula assumes that inclusion probabilities are known or adequately represented by the group sample fraction. In predictive modelling, weights must be used carefully because the best training distribution may differ from the population distribution used for evaluation.

Train, validation and test sets should reflect the deployment setting. For time-dependent decisions, chronological splitting is usually preferable to random splitting. Random splitting can place future patterns in the training set and produce overoptimistic results.

10. Data Leakage

Data leakage occurs when information unavailable at prediction time influences model training or evaluation. It is one of the most serious preparation errors because the resulting model may appear highly accurate while failing in practice.

Common examples include:

• using a diagnosis recorded after the prediction date to predict hospital admission;
• calculating a customer’s lifetime value using transactions that occurred after the scoring date;
• imputing or scaling the complete dataset before splitting it; and
• selecting features using the full dataset, including the test set.

The correct principle is to simulate the information boundary of deployment. Every feature must answer: “Would this value have been available, in this form, at the moment the prediction was made?” Preparation operations such as imputation, scaling, feature selection and vocabulary construction should be fitted on training data only.

11. A Reproducible Preparation Workflow

A reproducible workflow can be organised as follows:

1. Define the decision, target population, prediction time and unit of analysis.
2. Register sources, owners, permissions, schemas and update schedules.
3. Preserve raw data and record acquisition timestamps.
4. Profile row counts, types, ranges, missingness, uniqueness and distributions.
5. Resolve key and grain issues before joining.
6. Standardise formats using version-controlled reference rules.
7. Split data according to the deployment context.
8. Fit transformations only on the training portion.
9. Validate constraints, distributions, relationships and leakage risks.
10. Save the prepared dataset, code, configuration, metadata and quality report.
11. Monitor future data for drift and quality degradation.

Reproducibility requires more than saving code. It also requires fixed input versions, documented software environments, explicit random seeds where randomness is used, and records of decisions. A data lineage diagram or transformation log can show how each analysis field was derived.

12. Applied Case: Predicting Retail Stockouts

The following is an illustrative case.

Background

A retailer wants to predict whether a product will experience a stockout in a particular store during the next seven days. Data is available from point-of-sale transactions, inventory records, supplier files, promotions and weather services.

Problem

An initial model reports excellent validation accuracy. However, store managers observe frequent missed stockouts. Investigation shows that the dataset contains multiple grains: sales are recorded per transaction line, inventory snapshots per store and product per hour, and promotions per product per campaign. In addition, a field called “stockout resolution date” is populated after a stockout has occurred.

Analysis and approach

The team defines one row as a store–product–day. Sales and inventory are aggregated to that grain. Promotions are joined using effective dates, and supplier data is standardised by product code. The resolution date is excluded because it is unavailable at prediction time. A chronological split is used: earlier weeks for training, a later period for validation, and the most recent period for testing.

Missing supplier lead times are separated into “unknown” rather than treated as zero. Extreme sales values are reviewed against promotional campaigns; genuine promotion-driven demand is retained. Duplicate product codes are resolved using the product master, while unmatched codes are reported rather than silently discarded.

Outcome

Performance on the realistic test period is lower than the original result but is operationally credible. The retailer can now identify which stores and products require additional inventory review. The preparation pipeline is scheduled daily, and quality checks alert staff when product matching, missingness or data timeliness falls below agreed thresholds.

Learning points

The case demonstrates that aggregation must respect grain, time availability is central to leakage prevention, and unusual observations require context. A lower but credible performance estimate is more valuable than an inflated estimate that cannot survive deployment.

13. Governance, Ethics and Contemporary Practice

Data preparation affects people even when it appears technical. Removing records disproportionately from a protected group can change model outcomes. Standardising names may create identity errors. External datasets may have unclear consent or licensing. Sensitive attributes may be needed to audit fairness but must be protected and used under a legitimate governance process.

Important controls include access restriction, encryption, retention limits, documented lawful purpose, data minimisation and audit trails. Organisations should assess whether data collection and automated decision-making comply with applicable privacy and sector regulation.

Contemporary preparation increasingly uses cloud data platforms, automated quality tests, metadata catalogues, feature stores and machine-learning pipelines. Generative AI can assist with schema discovery, code suggestions and text classification, but generated transformations require human review. Models may reproduce hidden biases, expose confidential data or create non-deterministic outputs. Automated tools should support, not replace, accountable data stewardship.

Challenges and Limitations

No preparation method can remove all uncertainty. Imputation introduces assumptions; outlier rules may suppress legitimate variation; sampling weights can increase variance; and standardisation can hide meaningful distinctions. Data quality metrics may also encourage teams to optimise what is easy to measure rather than what matters to the decision. Finally, a clean historical dataset may still fail to represent future conditions because customer behaviour, technology, regulation and markets change.

The appropriate objective is therefore not perfect data, but data whose limitations are known, documented and acceptable for the intended decision.

Chapter Summary

Predictive analytics depends on disciplined data preparation. Analysts must understand whether data is structured, semi-structured or unstructured; document source, grain, ownership and meaning; and integrate tables using validated keys and time relationships. Quality should be assessed across accuracy, completeness, validity, consistency, uniqueness, timeliness and integrity.

Missing values require attention to their causes, duplicates require business-based identity rules, and outliers require contextual investigation. Formats, units and categories should be standardised without destroying meaningful distinctions. Sampling must reflect the deployment population, while chronological splits are often necessary for time-dependent decisions. Data leakage is prevented by respecting the information available at prediction time and fitting transformations only on training data.

A reproducible workflow preserves raw inputs, versions transformations, validates assumptions and monitors data after deployment. Ethical and governance responsibilities include privacy, fairness, security, licensing and accountability. Effective preparation is consequently both a technical and managerial capability.

Key Terms

Data grain: The level represented by one row in a dataset.

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

Semi-structured data: Data with organisational markers but flexible or nested structure.

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

Data provenance: The recorded origin, history and transformations of data.

Data dictionary: Documentation defining fields, types, units and permitted values.

Completeness: The extent to which required data is present.

Validity: Conformance to specified rules, formats or ranges.

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

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

ETL: Extract, transform and load.

ELT: Extract, load and transform.

Imputation: Replacing missing values using a defined method.

Outlier: An observation unusually distant from a reference pattern.

Sampling bias: Systematic difference between the sample and target population.

Sampling weight: A factor representing the relative population contribution of a sampled unit.

Data leakage: Use of information unavailable at prediction time.

Data lineage: A record of how data moves and changes through a system.

Reproducibility: The ability to obtain the same result using documented inputs and procedures.

Data drift: Change in data distributions or relationships over time.

Review Questions

Short Answer Questions

1. What is meant by the grain of a dataset, and why is it important when joining tables?
2. Distinguish structured, semi-structured and unstructured data.
3. Name four dimensions of data quality.
4. Why should imputation parameters normally be learned from training data only?
5. What is data leakage?

Long Answer Questions

1. Explain a complete workflow for acquiring, integrating and validating data for predictive analytics.
2. Compare alternative approaches to missing values and discuss their assumptions and risks.
3. Explain how duplicates, inconsistent formats and outliers should be investigated and treated.
4. Discuss why sampling design and temporal splitting affect the credibility of model evaluation.
5. Evaluate the governance and ethical issues involved in preparing data for automated decisions.

Analytical / Application Questions

1. A sales table has one row per transaction and a customer table has one row per customer. Explain the checks you would perform before joining them and describe how an incorrect join could distort revenue.
2. A hospital model uses a “discharge outcome” field to predict admission risk. Analyse whether this is leakage and propose a valid preparation design.
3. A dataset has 20,000 records, of which 1,200 lack a delivery-time value. Calculate the missingness rate and recommend investigations before choosing an imputation strategy.

Multiple Choice Questions with Answers

Question 1

Which concept describes the level represented by one row in a dataset?

A. Data grain

B. Data lineage

C. Data drift

D. Data validity

Correct Answer: A. Data grain

Question 2

Which data source is most clearly semi-structured?

A. A relational sales table

B. A JSON event record

C. A scanned photograph

D. A printed invoice

Correct Answer: B. A JSON event record

Question 3

A dataset contains 500 missing values among 10,000 records. What is the missingness rate?

A. 0.5%

B. 5%

C. 10%

D. 50%

Correct Answer: B. 5%

Question 4

Which action best prevents leakage when imputing missing values?

A. Impute the complete dataset before splitting

B. Use test-set values to improve the imputation

C. Fit the imputation rule on training data only

D. Replace all missing values with zero

Correct Answer: C. Fit the imputation rule on training data only

Question 5

What is the safest general response to an apparent outlier?

A. Delete it automatically

B. Replace it with the mean

C. Investigate its business and measurement context

D. Duplicate it to preserve its influence

Correct Answer: C. Investigate its business and measurement context

Question 6

Which key should normally uniquely identify a record within a table?

A. Foreign key

B. Primary key

C. Sampling weight

D. Time zone

Correct Answer: B. Primary key

Question 7

For a time-dependent forecasting problem, which split is generally most realistic?

A. Random split across all dates

B. Chronological split

C. Alphabetical split by customer

D. Split by missingness status

Correct Answer: B. Chronological split

Question 8

A value recorded after the prediction date is used as a model feature. What problem does this create?

A. Stratification

B. Standardisation

C. Data leakage

D. Deduplication

Correct Answer: C. Data leakage

Question 9

If a population group has 8,000 members and a sample contains 400 members from that group, what is the basic group sampling weight wₕ = Nₕ / nₕ?

A. 5

B. 10

C. 20

D. 400

Correct Answer: C. 20

Question 10

Which practice most directly supports reproducibility?

A. Overwriting the raw data after cleaning

B. Keeping undocumented manual corrections

C. Versioning inputs, code and transformation rules

D. Reporting only the final accuracy score

Correct Answer: C. Versioning inputs, code and transformation rules