Data Quality, Preparation and Feature Engineering

Introduction and Context

Big data analytics rarely fails because an algorithm cannot be executed. It more often fails because the data are incomplete, inconsistent, duplicated, poorly defined, incorrectly joined, or shaped by historical bias. Data preparation is therefore not a preliminary clerical task; it is a central analytical activity that determines whether results are credible and useful.

Data quality concerns the fitness of data for a stated purpose. A dataset can be adequate for monitoring website traffic but inadequate for regulatory reporting. Quality is consequently contextual: it must be judged against business requirements, analytical objectives, legal obligations, and operational constraints.

Data preparation converts raw observations into an analysis-ready representation. It commonly includes profiling, cleaning, transformation, integration, validation, and documentation. Feature engineering then creates informative variables from raw or prepared data so that statistical and machine-learning models can detect relevant patterns.

A reliable process also requires reproducibility. Another analyst should be able to identify where each field originated, which rules changed it, which exclusions were made, and which version of the pipeline produced a result.

Learning Objectives

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

1. Explain the major dimensions of data quality and design a profiling exercise.
2. Detect and treat missing, noisy, duplicated, inconsistent, and biased data.
3. Transform and integrate large datasets while preserving meaning and lineage.
4. Engineer, select, and validate features without causing data leakage.
5. Design reproducible preparation pipelines with appropriate quality controls.
6. Evaluate ethical, governance, and operational implications of preparation decisions.

1. Data Quality as Fitness for Use

The principal dimensions of data quality are shown below.

These dimensions can conflict. A real-time sensor stream may be timely but noisy. A manually verified survey may be accurate but not timely. Improving one dimension does not automatically improve all others.

A useful quality measure is a rate or proportion. If n required records are examined and c pass a specified rule, the conformance rate is:

Here, Q is the proportion conforming to the rule, c is the number of conforming records, and n is the number inspected. The measure is meaningful only when the sampling method, rule definition, and denominator are documented. A value of 0.98 does not indicate high quality if the test omitted the most problematic source system.

Quality thresholds should be tied to consequences. A 2% error rate may be tolerable in exploratory analysis but unacceptable in a payment or safety application. Data owners should define critical fields, acceptable thresholds, escalation rules, and service-level expectations.

2. Profiling Large Datasets

Profiling is the systematic examination of structure, content, and relationships before transformation. It should occur at several levels:

• Structural profiling: schemas, data types, field names, file sizes, partitioning, and refresh frequency.
• Descriptive profiling: counts, distinct values, minimums, maximums, means, medians, quantiles, and frequency distributions.
• Rule profiling: violations of range, format, domain, and business rules.
• Relationship profiling: duplicate keys, orphan records, referential integrity, and cross-field contradictions.
• Temporal profiling: gaps, unexpected spikes, late-arriving records, and changes in distributions over time.

For a numeric field, the arithmetic mean is:

where xᵢ is observation i, and n is the number of non-missing observations. The mean is sensitive to extreme values, so median and quantiles should also be inspected. For a sample, the sample standard deviation is:

The denominator n − 1 is used when estimating population variability from a sample under standard independent-sampling assumptions. These summaries describe data; they do not prove that observations are accurate.

A practical profiling report should identify field meaning, data type, null percentage, distinct-count estimate, common values, suspected outliers, and quality rules. At large scale, approximate algorithms, sampling, sketches, and distributed aggregation may be used. Approximation should be disclosed, especially when results drive compliance decisions.

3. Missing Data

Missingness may arise from system failure, optional questions, delayed arrival, privacy restrictions, or the fact that a measurement was not applicable. The reason matters more than the blank itself.

Three traditional mechanisms are useful:

• MCAR (missing completely at random): missingness is unrelated to observed and unobserved values.
• MAR (missing at random): missingness depends on observed variables after those variables are considered.
• MNAR (missing not at random): missingness depends on the unobserved value or another unobserved process.

These are modeling assumptions, not labels that can always be proved from the dataset. For example, income may be missing more often for high-income respondents, suggesting a non-random process.

Treatment options include deletion, imputation, explicit missing categories, and model-based methods. Deleting rows is defensible only when the loss is acceptable and the missingness mechanism supports it. Mean imputation is simple but reduces variation and can distort relationships. Median imputation is more robust to skew. A missingness indicator can preserve information about the fact of nonresponse, but it should be used thoughtfully rather than automatically.

For time series, forward filling may be reasonable for a slowly changing status but inappropriate for a rapidly changing measurement. Interpolation assumes an evolution between known points and should not be applied across long gaps without justification. In all cases, imputation parameters must be calculated using the training data only and then applied to validation and test data.

4. Noisy, Erroneous, and Outlying Data

Noise is unwanted variation; an error is an incorrect observation; an outlier is an observation unusually distant from others. They are not identical. A genuine large transaction is an outlier but not necessarily an error.

Detection methods include range rules, robust summaries, visual inspection, domain constraints, and temporal comparisons. The interquartile range is:

where Q₁ and Q₃ are the first and third quartiles. A common exploratory rule flags values below Q₁ − 1.5IQR or above Q₃ + 1.5IQR. This is a screening convention, not a universal error rule. It is less affected by extreme values than a standard-deviation rule, but it may flag valid observations in a skewed distribution.

Treatment should follow investigation:

1. Correct values using an authoritative source when the error is known.
2. Remove records only when they are demonstrably invalid or outside the population of interest.
3. Cap or winsorize values only when the analytical purpose justifies limiting influence and the rule is documented.
4. Use transformations such as logarithms for highly skewed positive variables, remembering that interpretation changes.
5. Retain valid extremes and consider robust models when they contain meaningful signal.

A validation rule should distinguish impossible values, such as a negative quantity sold, from merely unusual values, such as an unusually large order.

5. Duplicates, Inconsistency, and Standardization

Duplicates can be exact, near, or semantic. Two rows with identical fields are exact duplicates. Two customer records with variant spellings may represent the same entity. A repeated transaction identifier may indicate ingestion duplication, a legitimate correction, or multiple line items.

Deduplication requires a defined unit of observation and a survivorship rule. A rule might retain the latest record by event time, but only if later records represent authoritative updates. Fuzzy matching based on names or addresses can generate false merges, so high-impact matches should be reviewed or supported by stable identifiers.

Standardization includes consistent date formats, time zones, units, currency, capitalization, categorical labels, and character encoding. Dates should be stored with an explicit interpretation of calendar, time zone, and event versus processing time. Currency conversion requires an exchange-rate date and currency source. Converting pounds to kilograms requires a stated conversion convention and consistent units.

Categorical harmonization should use a controlled vocabulary. For example, “United Kingdom,” “UK,” and “GB” should not be treated as separate countries if the analytical definition is country of residence. However, apparently similar labels must not be merged without understanding their meanings.

6. Data Integration and Entity Resolution

Integration combines data from databases, files, APIs, streams, and external providers. The major risks are incompatible schemas, different granularities, conflicting definitions, and incorrect joins.

Before joining, analysts should establish:

• the grain of each table;
• candidate keys and their uniqueness;
• whether the join is one-to-one, one-to-many, or many-to-many;
• the treatment of unmatched records;
• event-time and effective-date logic; and
• the authoritative source for conflicting attributes.

A many-to-many join can multiply rows and inflate totals. For example, joining a customer table with multiple addresses to a transaction table with multiple purchases may produce combinations rather than meaningful records. Aggregation to a common grain before joining is often safer.

Entity resolution links records that refer to the same real-world entity. Deterministic matching uses stable identifiers. Probabilistic matching combines agreement on fields such as name, address, and date of birth. Matching thresholds should be evaluated using false-match and missed-match costs, with special safeguards for sensitive domains.

Schema integration also requires semantic alignment. “Revenue” may mean invoiced revenue in one system and cash received in another. A technically successful join can therefore be analytically invalid.

7. Transformation and Representation

Transformation changes the representation of data while attempting to preserve relevant meaning. Common methods include scaling, encoding, aggregation, date decomposition, and text processing.

Min–max scaling maps a value to a selected interval, commonly [0, 1]:

where x is the original value and x_min and x_max are the training-data minimum and maximum. It is applicable when x_max ≠ x_min. New values outside the training range may produce values below 0 or above 1 unless clipped, and clipping itself should be documented.

Standardization uses:

where μ and σ are the training-data mean and standard deviation, with σ > 0. Standardization is useful for many distance-based, linear, and gradient-based methods, but it does not make a distribution normal.

Categorical variables may be represented using one-hot encoding. For k categories, one may create k binary columns, although a linear model with an intercept commonly uses k − 1 columns to avoid perfect multicollinearity. The omitted category becomes the reference category.

Log transformation, often log(1 + x) for nonnegative data, can reduce right skew and compress large values. It is not appropriate without considering zeros, negative values, interpretation, and the model’s assumptions.

8. Feature Engineering

A feature is an input variable used by an analytical model. Feature engineering creates variables that express domain knowledge and predictive structure more directly than raw fields.

Useful feature families include:

• Ratios and rates: revenue per customer, defect rate, utilization.
• Aggregations: transaction count, mean basket value, maximum delay.
• Recency and frequency: days since last purchase and number of purchases in a period.
• Temporal features: day of week, season, elapsed time, rolling averages, and lag values.
• Interactions: product of or conditional combination between variables when their joint effect matters.
• Text features: term frequencies, embeddings, sentiment measures, or document length.
• Geospatial features: distance, region, travel time, or neighborhood characteristics.
• Domain indicators: whether a threshold, event, or service-level condition was met.

A feature should have a clear definition, a valid time reference, and a known availability point. In a prediction made at time t, a feature must be computable using information available no later than t.

Data Leakage

Leakage occurs when training data contain information that would not be available when the model is used. Examples include using a customer’s eventual cancellation status to predict cancellation, calculating a rolling average with future observations, or imputing the full dataset before splitting it.

Leakage creates inflated validation performance and poor deployment results. The remedy is to define the prediction timestamp, split data according to the real decision process, fit transformations within training partitions, and test pipeline behavior with time-aware and adversarial checks.

Feature Selection

Feature selection may remove irrelevant, redundant, unstable, or high-risk variables. Filter methods use statistics such as correlation or mutual information; wrapper methods evaluate subsets through model performance; embedded methods select during model fitting. Selection must occur inside the resampling process. Selecting features once using the full dataset leaks information from validation data.

Correlation does not establish usefulness or causality. A variable can have low marginal correlation but strong nonlinear or interaction effects. Conversely, a highly predictive variable may be unethical, unstable, or a proxy for protected status.

9. Validation and Reproducible Pipelines

A production-quality preparation pipeline separates raw, standardized, curated, and feature-ready layers. A typical flow is:

Figure: A preparation pipeline with validation and monitoring as a continuing control.

Validation should include:

• Schema tests: required columns and compatible data types.
• Domain tests: valid ranges, categories, and formats.
• Uniqueness tests: keys and event identifiers.
• Referential tests: accepted relationships between tables.
• Statistical tests: distribution shifts, missingness changes, and unusual volumes.
• Reconciliation tests: totals compared with source systems.
• Pipeline tests: repeatability, idempotence, and failure handling.

Idempotence means that rerunning a process on the same input produces the same result, subject to documented nondeterminism. Version control should cover code, configuration, schemas, feature definitions, and, where feasible, input snapshots or immutable references.

Lineage records the origin and transformations of data. It can be represented as a directed graph: source fields flow through operations into output fields. Good lineage supports audit, debugging, impact analysis, and responsible deletion requests.

Worked Example: A Training-Safe Customer Feature

Suppose a retailer predicts whether a customer will purchase in the next 30 days. The prediction date is 1 July. The analyst creates a feature called “orders in the previous 90 days.” For each customer, the eligible interval is 2 April through 30 June, assuming dates are measured at day level and the prediction is made after the 30 June close.

A customer has orders on 10 April, 20 May, and 15 July. Only the first two are available at prediction time, so the feature value is 2. Including the 15 July order would be leakage because it occurs after the prediction date. If the customer table contains two records for the same order because of an ingestion retry, the count must be based on a deduplicated order identifier rather than raw rows.

The feature definition should specify customer key, event time, inclusive boundaries, duplicate rule, late-arrival policy, and output type. The same transformation must be applied during training and deployment, with the relevant historical cutoff changing by prediction date.

Applied Case: Illustrative Retail Data Quality Improvement

Background

A fictional online retailer, Northstar Market, uses orders, customer profiles, web events, and delivery records to forecast repeat purchases. Its initial model showed excellent validation accuracy but poor performance after deployment.

Problem

Investigation found that 8% of order events were duplicated during a retry process, customer country codes used three incompatible vocabularies, delivery status was updated after the prediction date, and missing delivery dates were more frequent for remote regions. The model also used a “lifetime completed orders” field that had been calculated after the customer’s entire history was loaded.

Analysis

The team first defined the grain of each source: one row per order, one row per customer, one row per web event, and one row per delivery attempt. It profiled null rates, key uniqueness, time zones, and daily volumes. A reconciliation showed that raw order rows exceeded the financial order ledger because retries had been counted as new orders.

The team then established a point-in-time rule: only records available by the prediction timestamp could contribute to features. It created a canonical country vocabulary, deduplicated orders by order identifier using the authoritative event version, and separated event time from ingestion time. Missing delivery dates were retained as missing rather than converted to an arbitrary date; a separate indicator identified whether delivery information was available.

Approach and Solution

The pipeline was divided into raw, standardized, curated, and feature layers. Automated tests checked key uniqueness, accepted categories, order-total reconciliation, and maximum feature timestamps. Time-based validation was used: earlier months trained the model and later months tested it. Feature definitions and data contracts were stored in version control, and failed quality tests stopped publication rather than silently producing a dataset.

Outcome

Deployment performance became lower than the original inflated estimate but substantially more stable. Business users gained an explanation for excluded records and could trace model inputs to source systems. The retailer also discovered that regional missingness reflected logistics conditions rather than random technical failure, prompting a separate operational intervention.

Learning Points

The case demonstrates that data quality is connected to modeling validity, governance, and business process improvement. Deduplication without a grain definition is unsafe; missingness can be informative; temporal availability is essential; and a lower but honest validation score is more valuable than an inflated score caused by leakage.

Ethical, Governance, and Regulatory Considerations

Preparation decisions can distribute benefits and harms. Removing records from a minority group may improve apparent consistency while reducing representation. Imputation can conceal unequal service access. Location, purchase history, health information, and inferred attributes may be personal data subject to privacy law and organizational policy.

Important controls include data minimization, purpose limitation, access control, retention schedules, consent or other lawful bases where required, and secure handling of identifiers. Analysts should assess whether features act as proxies for protected characteristics. Fairness evaluation should be performed across relevant groups where legally and ethically appropriate, while recognizing that fairness metrics can conflict and that group definitions may be sensitive.

Documentation should record who approved a rule, why it was selected, its expected effect, and how it will be reviewed. Automated decisions may require explanation, human oversight, appeal mechanisms, and impact assessments. Synthetic data can reduce exposure in development, but it does not automatically eliminate privacy risk or guarantee preservation of analytical properties.

Contemporary Developments and Best Practices

Modern data platforms increasingly use data contracts, schema registries, observability tools, feature stores, and automated lineage. Data contracts specify expected fields, types, semantics, freshness, and quality obligations between producers and consumers. Feature stores can centralize reusable definitions and help align offline training features with online serving features, but they introduce governance and operational complexity.

Machine-learning systems also require monitoring for data drift, concept drift, missingness changes, and pipeline failures. Large language models and automated coding tools can assist with profiling and transformation generation, but their outputs must be tested, reviewed, and prevented from exposing confidential data. AI-generated transformations should never be accepted merely because they execute successfully.

Best practice is to:

1. Begin with the analytical decision and define the unit, time, and purpose of observation.
2. Profile before cleaning and preserve raw data immutably.
3. Prefer correction based on evidence over indiscriminate deletion.
4. Make assumptions, thresholds, and imputation rules explicit.
5. Fit transformations only on appropriate training data.
6. Test joins, keys, totals, timestamps, and distributions.
7. Treat lineage, versioning, and documentation as technical controls.
8. Monitor quality after deployment, not only during model development.
9. Review privacy, fairness, and proxy risks before releasing features.
10. Ensure that business owners understand the limitations of the prepared data.

Challenges and Limitations

No preparation method can recover information that was never collected. Profiling can identify anomalies but cannot always determine the correct value. Imputation may reduce bias in one estimate while increasing it in another. Entity resolution can create false links, especially when identifiers are shared or names are common. Distributed processing improves scale but can make ordering, reproducibility, and debugging more difficult. Quality thresholds can also create incentives to manipulate metrics rather than improve underlying processes.

The appropriate response is not to seek perfectly clean data. It is to create transparent, purpose-specific, monitored data products whose limitations are understood and managed.

Chapter Summary

Data quality is fitness for a defined use and includes accuracy, completeness, consistency, validity, uniqueness, timeliness, integrity, and accessibility. Profiling examines structure, distributions, rules, relationships, and time behavior before transformation. Missing values must be analyzed in relation to their causes; noisy and extreme observations require investigation rather than automatic removal. Deduplication and integration depend on grain, keys, semantics, and authoritative sources.

Transformation includes scaling, encoding, aggregation, and temporal representation. Feature engineering expresses domain knowledge through rates, counts, recency, lags, interactions, text, and geographic variables. Features must respect the prediction timestamp to prevent leakage. Reproducible pipelines require versioning, lineage, validation, idempotence, data contracts, and monitoring. Finally, preparation decisions have ethical, privacy, fairness, and regulatory consequences and should be documented and reviewed.

Key Terms

Data quality: Fitness of data for a specified analytical or operational purpose.

Data profiling: Systematic examination of data structure, content, distributions, and relationships.

Completeness: The extent to which required values are present.

Validity: Conformity with permitted formats, domains, or rules.

Data lineage: A record of data origins and transformations.

Missing completely at random: Missingness unrelated to observed or unobserved values.

Missing at random: Missingness explainable by observed variables under a stated model.

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

Outlier: An observation unusually distant from the rest of the data.

Deduplication: Identification and treatment of multiple representations of the same record or entity.

Entity resolution: Linking records that refer to the same real-world entity.

Data grain: The unit represented by one row or observation.

Standardization: Conversion to consistent formats, units, or controlled vocabularies.

Feature engineering: Creation of model inputs from raw or prepared data.

Data leakage: Use of information unavailable at the time a prediction would be made.

One-hot encoding: Representation of categorical values using binary indicator columns.

Data contract: An agreed specification of schema, meaning, quality, and delivery expectations.

Data drift: Change in the distribution or characteristics of data over time.

Idempotence: Property that repeated execution on the same input produces the same result.

Review Questions

Short Answer Questions

1. Why is data quality described as fitness for use rather than an absolute property?
2. Distinguish an outlier from an erroneous observation.
3. What is the purpose of defining the grain of a dataset before joining tables?
4. Define data leakage and give one example.
5. Why should imputation parameters be fitted using training data only?

Long Answer Questions

1. Explain the major dimensions of data quality and describe an appropriate diagnostic for each.
2. Compare deletion, imputation, and explicit missingness indicators as responses to missing data.
3. Describe the risks of integrating datasets with different grains, time conventions, and business definitions.
4. Explain how a reproducible data preparation pipeline supports auditability and operational reliability.
5. Discuss the ethical and governance risks associated with feature engineering for automated decisions.

Analytical / Application Questions

1. A transaction table contains 1,200,000 rows but only 1,150,000 distinct transaction identifiers. Propose an investigation that distinguishes ingestion duplicates from legitimate multiple-line transactions, and explain which totals should be reconciled.
2. A model predicts customer churn on 1 October. Design three features that are valid at prediction time and identify one apparently useful feature that would create leakage.
3. A dataset has missing delivery dates concentrated in rural areas. Explain how you would investigate the missingness mechanism, prepare the data, evaluate possible regional bias, and communicate limitations to decision makers.

Multiple Choice Questions with Answers

Question 1

Which dimension of data quality concerns conformity to permitted formats and business rules?

A. Timeliness

B. Validity

C. Accessibility

D. Uniqueness

Correct Answer: B. Validity

Question 2

Why can a many-to-many join produce incorrect aggregate totals?

A. It always removes unmatched records

B. It converts numeric fields to text

C. It can multiply combinations of rows

D. It prevents data profiling

Correct Answer: C. It can multiply combinations of rows

Question 3

A value lies above Q₃ + 1.5IQR. What is the most appropriate interpretation?

A. It is certainly a data-entry error

B. It is an exploratory outlier flag

C. It must be deleted before modeling

D. It proves the distribution is normal

Correct Answer: B. It is an exploratory outlier flag

Question 4

Which action best prevents leakage during imputation?

A. Calculate imputation values using the complete dataset before splitting

B. Use the test-set mean for test records

C. Fit imputation values on training data and apply them to later partitions

D. Replace every missing value with zero

Correct Answer: C. Fit imputation values on training data and apply them to later partitions

Question 5

A feature uses orders made after the prediction timestamp. What problem does this create?

A. Underfitting

B. Data leakage

C. One-hot encoding

D. Referential integrity

Correct Answer: B. Data leakage

Question 6

For min–max scaling, which condition is required for the usual formula to be defined?

A. The variable must be categorical

B. The training maximum must equal the training minimum

C. The training maximum must differ from the training minimum

D. The variable must have no outliers

Correct Answer: C. The training maximum must differ from the training minimum

Question 7

What is the main purpose of a data contract?

A. To guarantee that every value is accurate

B. To specify expected schema, meaning, delivery, and quality obligations

C. To replace all data governance processes

D. To remove the need for monitoring

Correct Answer: B. To specify expected schema, meaning, delivery, and quality obligations

Question 8

Which statement about a valid extreme observation is correct?

A. It should always be deleted

B. It may contain important signal and can be retained after investigation

C. It is necessarily a duplicate

D. It proves the measurement instrument failed

Correct Answer: B. It may contain important signal and can be retained after investigation

Question 9

What does idempotence mean in a preparation pipeline?

A. The pipeline requires no validation

B. Repeated execution on the same input produces the same result

C. The pipeline accepts only numerical data

D. Every source must have the same schema

Correct Answer: B. Repeated execution on the same input produces the same result

Question 10

A retailer has 2,000 inspected records, of which 1,900 pass a quality rule. What is the conformance rate?

A. 5%

B. 10%

C. 90%

D. 95%

Correct Answer: D. 95%