5. Data Preparation, Quality and Feature Engineering

Introduction and Context

Data science models rarely fail because an algorithm cannot be executed. More often, they fail because the data used to train, validate, and operate the model is incomplete, inconsistent, biased, poorly documented, or transformed incorrectly. Data preparation is therefore not a preliminary clerical task; it is a central analytical activity that determines whether statistical conclusions and machine-learning predictions are trustworthy.

Data preparation includes profiling, cleaning, transforming, integrating, validating, documenting, and monitoring data. Feature engineering extends this work by converting raw observations into variables that represent useful patterns for analysis. These activities connect source systems with analytical models and create a controlled path from raw data to business or scientific insight.

A useful distinction is between data quality and data usefulness. A dataset may be technically complete but unsuitable for a particular task. For example, a customer table may contain no missing values but still lack a reliable indicator of customer tenure. Conversely, a dataset may contain missing values yet remain useful if the missingness is understood and handled appropriately. Quality is therefore assessed in relation to intended use, analytical context, and risk.

This chapter examines the tools, processes, metrics, governance practices, and professional decisions involved in preparing data for data-science work. It also considers leakage, bias, lineage, automation, and the use of modern platforms such as SQL transformation frameworks, distributed processing systems, data-quality services, feature stores, and machine-learning pipelines.

Learning Objectives

After studying this chapter, the learner should be able to:

1. Explain the role of profiling, cleaning, transformation, integration, and validation in a data-science workflow.

2. Diagnose common data-quality problems using appropriate metrics and tools.

3. Select defensible strategies for missing data, outliers, inconsistent values, and duplicate records.

4. Design transformations and features while avoiding data leakage and unnecessary complexity.

5. Apply principles of data lineage, bias management, governance, and reproducibility.

6. Evaluate practical data-preparation pipelines in organizational and industry settings.

From Raw Data to Analytical Data Products

A data-preparation workflow begins with a question rather than with a tool. The intended prediction, decision, or analysis determines which records, variables, time periods, and quality thresholds matter. A fraud-detection model, for example, requires different preparation choices from a workforce-retention study. The former may prioritize event ordering and low-latency features; the latter may prioritize stable definitions of employment status and fairness across demographic groups.

The preparation lifecycle can be understood as an iterative sequence:

source discovery → profiling → quality assessment → cleaning → integration → transformation → feature construction → validation → publication and monitoring.

These stages are not strictly linear. Profiling may reveal that a source must be re-integrated. Validation may expose an incorrect transformation. Monitoring may identify drift that requires a new feature or revised rule. Mature teams treat preparation as a managed data product with owners, documentation, tests, version control, and service expectations.

Important Concepts and Terminology

A dataset is a structured or semi-structured collection of observations. A record or row represents an entity, event, or observation, while a field, attribute, or column represents a measured characteristic. A schema defines fields, data types, relationships, and constraints. A data dictionary explains the semantic meaning, units, permitted values, and ownership of fields.

Data profiling is the systematic examination of structure, content, distributions, relationships, and anomalies. Data quality refers to the fitness of data for an intended purpose. Data lineage records the origin, movement, transformation, and use of data. Metadata is information describing data, including technical, business, operational, and governance properties.

A feature is an input variable used by an analytical model. Feature engineering is the construction, selection, encoding, or transformation of features from raw data. Target leakage occurs when information unavailable at prediction time is used to construct a predictor. Data drift describes changes in input distributions, while concept drift refers to changes in the relationship between inputs and the target.

Profiling Before Cleaning

Profiling establishes an evidence-based understanding of a dataset. At the structural level, the analyst inspects row counts, column names, data types, primary keys, relationships, file formats, and partitioning. At the statistical level, the analyst examines minimum and maximum values, means, medians, percentiles, variance, frequency distributions, and cardinality. At the semantic level, values are compared with business definitions: a field labelled “revenue” may contain gross sales in one source and net sales in another.

Useful profiling questions include:

• What proportion of values is missing in each field?
• Are supposed identifiers unique and stable?
• Do categorical values contain spelling, case, or formatting variations?
• Are numerical values within plausible domain limits?
• Are dates valid, correctly formatted, and ordered logically?
• Do relationships between tables satisfy expected cardinalities?
• Have distributions changed across time, locations, or source systems?

Profiling tools may range from SQL queries and spreadsheet summaries to Python libraries such as pandas and automated profiling packages. In larger environments, data catalogs and observability platforms calculate statistics across warehouses, lakes, and streaming systems. Automation is valuable, but profiling output requires domain interpretation. A rare value is not necessarily an error, and an apparently normal distribution can conceal systematic bias.

Data-Quality Dimensions and Measurement

Quality should be measured through explicit dimensions rather than vague judgments. The most commonly used dimensions are:

• Completeness: the extent to which required values are present.
• Validity: conformity to permitted formats, types, ranges, and rules.
• Accuracy: agreement with the real-world object or event being represented.
• Consistency: absence of contradictions within or across datasets.
• Uniqueness: absence of unintended duplicate records.
• Timeliness: availability and currency of data when required.
• Integrity: preservation of relationships and constraints among records.

Metrics make quality visible. If 2,000 of 100,000 required customer phone numbers are missing, completeness is 98 percent. A validity rule might require that transaction amounts be non-negative, and a referential-integrity rule might require every order to reference an existing customer. A uniqueness test might compare total rows with distinct values of a declared business key.

Quality thresholds should be connected to decisions. A 95 percent completeness rate may be adequate for exploratory segmentation but unacceptable for regulatory reporting. Teams should define critical data elements, quality rules, tolerances, owners, escalation procedures, and the action taken when a threshold is breached. Quality dashboards should distinguish between a failed test, a warning, and an accepted exception.

Cleaning: Correcting Problems Without Destroying Evidence

Cleaning involves correcting, standardizing, excluding, or annotating problematic observations. The first principle is to preserve the raw source and perform transformations in a reproducible layer. Overwriting original data makes errors difficult to investigate and weakens lineage.

Missing Data

Missingness has different meanings. A field may be missing because a value was not collected, because it was not applicable, because a system failed, or because a respondent deliberately withheld it. Statistical treatment should reflect this distinction. Three useful categories are:

• Missing completely at random: missingness is unrelated to observed or unobserved values.
• Missing at random: missingness is related to observed variables but not, after conditioning, to the missing value itself.
• Missing not at random: missingness is related to the unobserved value or an underlying process.

Common responses include deletion, single-value imputation, group-wise imputation, model-based imputation, and explicit missing-category treatment. Mean imputation is simple but reduces variability and can distort relationships. Median imputation is more robust to skewed distributions. Missing-indicator variables can preserve information when the fact that a value is missing has predictive meaning. For serious analytical work, multiple imputation or carefully specified predictive imputation may be more appropriate.

The imputation model must be fitted using training data only when the goal is prediction. Applying information from the full dataset before splitting creates leakage. Imputation rules should also be recorded so that identical logic can be applied during deployment.

Duplicates and Entity Resolution

Duplicates may be exact copies, repeated events, or multiple representations of the same entity. Removing duplicates requires understanding the unit of analysis. Two transactions with the same customer and amount may be legitimate repeated purchases. Entity resolution may use deterministic keys, standardized names and addresses, or probabilistic matching. False matches can be more damaging than unresolved duplicates, particularly in credit, healthcare, and identity-related applications.

Inconsistent Categories and Formats

Text categories should be standardized through controlled vocabularies, mapping tables, or reference data. “M,” “Male,” and “male” may represent one category, but such an assumption must be confirmed. Dates require attention to time zones, locale conventions, daylight-saving changes, and invalid calendar values. Units must be converted explicitly; mixing kilograms and pounds can create apparently plausible but incorrect measurements.

Outliers

An outlier is an observation that is unusually distant from others, but unusual does not mean erroneous. Outliers may represent data-entry mistakes, fraud, rare but important events, or genuine heavy-tailed behaviour. Detection methods include interquartile-range rules, robust z-scores, isolation forests, clustering, and domain thresholds. Remedies include correction, capping or winsorization, transformation, separate modelling, or retention with an outlier indicator. Removing an observation solely because it affects a model’s performance is poor practice unless a defensible domain reason exists.

Integration and Transformation Across Sources

Organizations commonly combine relational databases, application logs, sensor streams, spreadsheets, APIs, and external reference data. Integration requires decisions about keys, grain, time, versioning, and conflict resolution. Joining a customer table to monthly transactions may create one row per transaction rather than one row per customer. If the intended model is customer-level, transactions must be aggregated deliberately.

A sound integration process specifies the grain of every intermediate table. It also documents whether a join is one-to-one, one-to-many, or many-to-many. Many-to-many joins can silently multiply records and produce inflated totals. Reconciliation controls—such as comparing pre- and post-join counts and financial totals—help expose such errors.

Transformations may include scaling, logarithmic transformation, date decomposition, aggregation, encoding, and reshaping. Numerical scaling is important for distance-based and gradient-based algorithms, although tree-based models are generally less sensitive to scale. Log transformations can reduce right skew in variables such as income or transaction value. Categorical variables may be represented through one-hot encoding, ordinal encoding, frequency encoding, target encoding, or learned embeddings. Each technique has assumptions: ordinal encoding can impose a false order, while target encoding can leak target information if not implemented within training folds.

Feature Engineering as Analytical Design

Feature engineering converts domain understanding into measurable signals. Good features often represent recency, frequency, intensity, change, duration, interactions, or context. For a subscription service, useful examples might include days since last activity, number of active weeks, percentage change in usage, and support contacts per month.

Feature construction should begin with the prediction point: the exact time at which a decision is made. Only information available before that point may be used. A feature such as “number of complaints in the next 30 days” may be highly predictive of churn, but it is unavailable at the time the retention decision is made and therefore constitutes leakage.

Feature selection reduces noise, computation, and overfitting. Filter methods use statistical association or information criteria; wrapper methods evaluate subsets through model performance; embedded methods use regularization or model-specific importance. Selection must be conducted within the training process. Selecting variables using the full dataset before cross-validation can produce optimistic estimates.

Features should be evaluated for predictive value, stability, interpretability, operational availability, fairness, and cost. A feature that improves accuracy slightly but requires a delayed manual process may not be suitable for real-time use. A highly predictive postal code may reproduce socioeconomic or demographic inequalities and therefore requires careful review.

Validation, Reproducibility, and Lineage

Validation operates at several levels. Schema tests verify names, types, and required fields. Row-level tests verify ranges and formats. Cross-record tests verify uniqueness and relationships. Aggregate tests compare totals and distributions with trusted benchmarks. Statistical tests examine drift, unexpected shifts, and changes in missingness.

Data pipelines should separate development, training, validation, and production data. Transformations must be fitted on the training partition and then applied unchanged to validation and test partitions. A pipeline object in a machine-learning library can package imputation, encoding, scaling, and modelling steps, reducing the risk of inconsistent application.

Lineage answers questions such as: Which source produced this feature? Which code version transformed it? Which reference data and parameters were used? Which model consumed it? Lineage supports debugging, audit, reproducibility, impact analysis, and regulatory explanation. It may be captured through metadata catalogs, transformation manifests, version-control systems, experiment trackers, and feature-store metadata.

A feature store can provide a governed repository for reusable features, with definitions, ownership, freshness information, and separate offline and online access. Its principal value is consistency between model training and serving. It does not eliminate the need for sound feature definitions or leakage controls.

An Illustrative Case: Predicting Late Loan Repayment

Background

Consider a fictional regional lender developing a model to identify personal-loan accounts likely to become seriously overdue. The lender combines application data, repayment transactions, customer-service records, and account-status histories.

Situation and Problem

An initial model appears highly accurate in historical testing. However, the risk team notices that performance falls sharply after deployment. Investigation reveals that the development table contains duplicate accounts, inconsistent employment categories, missing income values, and a feature called “days past due in the next billing cycle.” The last feature is unavailable when the prediction is supposed to be generated.

Analysis

Profiling shows that the customer identifier is not unique because one customer may hold several accounts. The correct modelling grain is therefore account-month, not customer. A many-to-many join between service calls and repayments has multiplied transaction amounts. Income is missing more often for self-employed applicants, creating potential subgroup effects. The apparently strong future-delinquency feature is target leakage.

Approach

The team first preserves raw extracts and creates versioned transformation models. It defines account-month as the unit of analysis and validates key uniqueness at that grain. Employment categories are mapped through a reference table. Income is imputed using training-period information, accompanied by a missingness indicator. Repayment records are aggregated using only events available before each monthly scoring date. Features include recent repayment ratio, number of missed payments in the previous six months, account age, utilization relative to approved amount, and recent change in payment behaviour.

The team uses time-based validation rather than a random split, because future performance is the operational objective. Quality tests check non-negative amounts, valid dates, account-status consistency, join-cardinality changes, and feature freshness. Performance and error rates are compared across relevant customer groups. The model documentation records every source, transformation, parameter, and exclusion rule.

Outcome

The revised evaluation is less impressive than the original result but is more credible. Deployment monitoring shows that the model’s input distributions remain within defined tolerance levels for several months. When missing income rates increase in one application channel, the data owner is alerted and the model risk team assesses whether recalibration is necessary.

Learning Points

The case demonstrates that data preparation is inseparable from model validity. Correct grain prevents aggregation errors; temporal discipline prevents leakage; subgroup analysis exposes quality and fairness concerns; and lineage allows the organization to explain how a prediction was produced.

Professional and Organizational Considerations

Data preparation is both technical and organizational. Data owners define business meaning and acceptable quality. Data engineers implement reliable ingestion and transformation. Data scientists construct analytical features and evaluate model consequences. Governance, compliance, and domain specialists review access, retention, fairness, and regulatory requirements. Clear responsibility prevents the common assumption that data quality is solely the data scientist’s problem.

Sensitive attributes and personal data require data minimization, purpose limitation, access control, retention policies, and appropriate de-identification. Removing a protected attribute does not necessarily remove bias because proxy variables may remain. Fairness assessment should consider the application, decision consequences, group representation, and relevant performance measures. Imputation, exclusion, and outlier handling can affect groups unequally and should be evaluated accordingly.

Common mistakes include cleaning without a documented purpose, changing raw data, relying on averages without investigating missingness, deleting all outliers, joining tables without checking grain, selecting features before data splitting, using future information, and treating automated quality scores as proof of accuracy. Another frequent mistake is optimizing for model performance while ignoring latency, cost, interpretability, maintainability, or legal constraints.

Current Developments and Best Practices

Modern data platforms increasingly support declarative transformation, automated testing, observability, data contracts, and metadata-driven governance. Data contracts specify expected schemas, semantics, quality obligations, and change-management responsibilities between producers and consumers. Data observability tools monitor freshness, volume, schema, distribution, and lineage. Generative and machine-learning assistants can suggest mappings, detect anomalies, or generate transformation code, but their output requires human review, testing, and security controls.

A robust preparation practice follows these principles:

1. Define the analytical objective, prediction point, grain, and acceptable quality before transforming data.

2. Preserve raw data and version every important transformation.

3. Profile structure, distributions, missingness, relationships, and subgroup patterns.

4. Use explicit quality rules with owners, thresholds, alerts, and documented exceptions.

5. Treat missingness, duplicates, outliers, and categories according to their meaning rather than through automatic deletion.

6. Fit data-dependent transformations only on training data and apply them consistently.

7. Test joins, temporal availability, leakage, and fairness as part of the pipeline.

8. Record lineage, feature definitions, data versions, and operational freshness.

9. Monitor data and model behaviour after deployment.

10. Prefer features that are stable, available, interpretable, and useful for the decision being supported.

Chapter Summary

Data preparation establishes the reliability of analytical work. Profiling reveals structural, statistical, and semantic characteristics; quality management evaluates completeness, validity, accuracy, consistency, uniqueness, timeliness, and integrity. Cleaning requires contextual treatment of missing values, duplicates, inconsistent categories, and outliers. Integration must respect keys, grain, cardinality, time, and reconciliation controls.

Feature engineering transforms domain knowledge and raw events into useful, operationally available predictors. Its central safeguards are temporal discipline, leakage prevention, reproducibility, and evaluation within the training process. Validation combines schema, row-level, relational, aggregate, and statistical tests. Lineage and governance make transformations explainable and maintainable, while fairness and privacy considerations ensure that preparation does not create or conceal harm. Effective data preparation is therefore a socio-technical capability linking data platforms, analytical methods, organizational responsibility, and trustworthy decision-making.

Key Terms

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

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

Completeness: Degree to which required data values are present.

Validity: Conformity of data to defined formats, ranges, types, or rules.

Data lineage: Record of data origins, transformations, movement, and use.

Metadata: Information describing data’s structure, meaning, ownership, and operation.

Schema: Definition of fields, types, constraints, and relationships in a dataset.

Missingness: The condition in which an expected data value is absent or unavailable.

Imputation: Estimation and insertion of a replacement value for missing data.

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

Outlier: An observation unusually distant from the prevailing pattern of data.

Data integration: Combining data from multiple sources into a coherent analytical structure.

Feature: An input variable used by an analytical or machine-learning model.

Feature engineering: Creating, transforming, encoding, or selecting useful model inputs.

Target leakage: Use of information unavailable at prediction time in a model feature.

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

Concept drift: Change in the relationship between predictors and the target over time.

Data contract: An explicit agreement about a data product’s schema, meaning, quality, and change obligations.

Feature store: A governed system for managing and serving reusable analytical features.

Data observability: Monitoring of data freshness, volume, schema, distributions, and quality behaviour.

Review Questions

Short Answer Questions

1. Why should data profiling precede most cleaning activities?

2. Distinguish completeness from validity in data quality assessment.

3. What is the difference between an outlier and a data error?

4. Define target leakage and explain why it produces misleading evaluation results.

5. What information does data lineage provide?

Long Answer Questions

1. Explain the major stages of a data-preparation lifecycle and show how they interact iteratively.

2. Discuss alternative strategies for handling missing data and the assumptions behind them.

3. Explain how data integration can create duplication or aggregation errors through incorrect join grain.

4. Describe the principles of feature engineering, including scaling, encoding, feature selection, and temporal availability.

5. Discuss how quality metrics, validation tests, lineage, and governance contribute to trustworthy analytical systems.

Analytical / Application Questions

1. A customer dataset contains 8 percent missing income values, three spellings of the same occupation, and duplicate customer identifiers. Design a profiling and preparation plan, including tests and decisions you would make.

2. A churn model uses “support tickets opened during the 60 days after the scoring date” and performs exceptionally well in testing. Diagnose the problem and propose a valid redesign.

3. A hospital model has lower accuracy for a minority patient group after missing values are imputed. Analyze possible causes and recommend technical and governance actions.

22. Multiple Choice Questions with Answers

Question 1

A data scientist joins customer records to transaction records and observes that total revenue has doubled. What is the most likely explanation?

A. The numerical values were standardized incorrectly

B. A many-to-many join multiplied records

C. Missing values were replaced with medians

D. The target variable was one-hot encoded

Correct Answer: B. A many-to-many join multiplied records

Question 2

Which action best prevents leakage when constructing an imputed feature for a predictive model?

A. Compute imputation values using the complete dataset before splitting

B. Delete every row containing a missing value

C. Fit the imputation procedure on training data only

D. Use the target value to replace every missing predictor

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

Question 3

A field contains values such as “Male”, “male”, and “M”. What preparation issue does this primarily illustrate?

A. Category inconsistency

B. Concept drift

C. Feature scaling

D. Referential integrity

Correct Answer: A. Category inconsistency

Question 4

Which quality dimension asks whether a value conforms to an allowed range or format?

A. Accuracy

B. Timeliness

C. Validity

D. Uniqueness

Correct Answer: C. Validity

Question 5

Why is the prediction point important in feature engineering?

A. It determines the algorithm’s learning rate

B. It identifies which information was available when the decision was made

C. It guarantees that all features are normally distributed

D. It removes the need for validation data

Correct Answer: B. It identifies which information was available when the decision was made

Question 6

A rare transaction is flagged as an outlier but is confirmed by the business as a legitimate high-value purchase. What is the most defensible action?

A. Automatically delete it

B. Replace it with the median

C. Retain it, possibly with an outlier indicator

D. Convert it to a categorical label without review

Correct Answer: C. Retain it, possibly with an outlier indicator

Question 7

Which practice is most appropriate when evaluating a model intended to predict future monthly account delinquency?

A. Use only a random split of all historical records

B. Use time-based validation reflecting future prediction

C. Select features using the test set

D. Include future account status as a predictor

Correct Answer: B. Use time-based validation reflecting future prediction

Question 8

What is the primary value of data lineage in a feature pipeline?

A. It guarantees perfect model accuracy

B. It records the origin and transformations of analytical data

C. It automatically removes demographic bias

D. It eliminates the need for data profiling

Correct Answer: B. It records the origin and transformations of analytical data

Question 9

Which statement best describes a feature store?

A. A repository for raw backups only

B. A system for managing and serving governed reusable features

C. A tool used exclusively to visualize distributions

D. A database that stores only target variables

Correct Answer: B. A system for managing and serving governed reusable features

Question 10

A model’s input distribution changes substantially after deployment, although the prediction relationship has not yet been shown to change. What phenomenon is being observed?

A. Data drift

B. Entity resolution

C. Target leakage

D. One-hot encoding

Correct Answer: A. Data drift