Data Preparation:
Transforming Raw Data into Model-Ready Foundations

Missing values: delete, impute or encode as a feature

A missing value is rarely a coincidence and often a finding in itself: in the dataset of a delivery platform, 38.78 percent of orders were unrated, the most important statement about the platform's data quality. So before you replace anything, it is worth asking why the value is missing.

Three routes are open. Delete: remove rows or whole columns, defensible for a few affected rows with no relation to the target variable. Impute, that is, replace: with the median, the mean, the last known value or an auxiliary model. In a customer segmentation we replaced missing income values with the median and removed one extreme value; of 2,240 customer records, 2,232 remained. Encode as a feature: the absence itself becomes information, for instance as a column "income unknown". That is the right route when the absence is systematic, because then it tells the model something about the customer.

A special case is placeholders such as "?", "-1" or "9999" that pass as valid values. In the UCI Adult dataset the column "Workclass" appears 100 percent filled; only after the question mark is declared a missing value do 5.6 percent gaps appear.

Decision rule (our rule of thumb, not a standard): if the missing share of a column is below about five percent and no pattern is recognisable, we delete or simply impute. Above that, or as soon as the absence is related to the target variable, it is encoded as a feature and every imputation is documented. With a very high missing share the column usually only works as a yes/no feature. In the Titanic data the cabin is missing for 77.1 percent of passengers, the age for 19.9 percent.

Duplicates and keys

Duplicates rarely arise from typing errors and mostly from technology: an export ran twice, two systems hold the same customer, a join multiplied rows. In the UCI Adult dataset with 32,561 rows we measured 24 duplicate rows, that is 0.074 percent: low, but not zero, and invisible without a check.

The check runs on two levels: identical complete rows are technical duplicates and are deleted. Rows that match only on the declared key columns, say order number plus date, are business duplicates, and the question is which one is right. The second level presupposes that the key is known at all. In process data, duplicate order numbers are among the defects that decide whether a robust model emerges or a pretty, wrong analysis.

Decision rule: technical duplicates are deleted and their share is logged. Business duplicates are never deleted silently but resolved by a rule agreed with the business department: the most recent state wins, or the state from the leading system. The rule goes into the pipeline as code.

Outliers: measurement error or rare event

Statistically an outlier is a value far outside the rest of the distribution; the classic rule after Tukey draws the line at 1.5 times the interquartile range. In our data quality audit across three public datasets, value plausibility was the dimension that most often lit up red, because heavy tails are the rule in real data. The next step is nonetheless always a human one. Is the value a sensor error, a confused unit or a genuine rare event? In the UCI Adult dataset the column fnlwgt shows a heavy tail and is entirely legitimate as a sampling weight.

Decision rule: an outlier is deleted only if the error can be named: unit, sensor or typing error. Otherwise it stays in the dataset, gets a flag as its own feature, and the modelling chooses methods that are less sensitive to extreme values, such as the median instead of the mean or tree-based models instead of linear ones.

Formats and units: date, decimal separator, time zones

The least spectacular part of data cleaning causes the latest errors. A date stored as text hardly stands out at first and becomes a drama as soon as a time series model sorts by it; in our audit of 25 years of Apple daily prices such a text date was the first finding. Decimal separators work just as quietly: "1.250" is, depending on origin, one thousand two hundred and fifty or one point two five. A chart in one of our own articles showed English decimal points for a while, while the text beside it used the comma.

Units and time zones belong in the same check: changing units in machine data, timestamps in local time next to timestamps in universal time, daylight-saving changes that make an hour appear twice or not at all. Each of these deviations is harmless on its own; in sum they produce a wrong model.

Decision rule: every column gets a declared type, a unit and, for timestamps, a time zone, as code in the pipeline, not in the head of the person handling it. Gaps in time are counted but not filled automatically: in the Apple prices there were 168 gaps, 2.7 percent of the intervals, and all of them were weekends and exchange holidays.

Anyone who wants to know before the project how much cleaning lies ahead gets a finding with a risk traffic light from the Data Quality Check. The cleaning itself is expressly not included there; it is the subject of this phase.

A data pipeline is the processing route that turns raw data into features in the same order and with the same rules on every run. The term ETL (extract, transform, load) describes the same route from the viewpoint of data storage. Which tool executes it is secondary; what matters are reproducibility, tests and versioning, the three properties from the motto of the chart further down.

Why notebook scripts are not enough: a notebook is the right tool to develop a cleaning routine and the wrong one to operate it; after three weeks nobody knows which export was the basis.

Three things are versioned: the code of the transformations; a frozen data state (snapshot) of the raw data against which every result can be recalculated; and the transformation logic as documentation, that is, which rule applies for which reason. The "documentation of all transformations" among the deliverables of this phase is therefore not an afterthought but a by-product of the pipeline.

Every run checks the schema and the value ranges beforehand: it tests whether the input still matches the expected schema (columns, types, units) and whether the value ranges are plausible, that is, no negative quantities, no timestamps from the future, missing shares below the agreed threshold. Such contract tests against a known schema are a different layer from the diagnosis in phase 2; a complete test suite costs between one and four weeks depending on the data model and in return catches every future export that silently changes its format. If a test fires, the run aborts instead of delivering a quietly wrong result.

Phase 6 takes over the same pipeline: the pipeline that produced the training data later produces the inputs for the model in operation. Anyone who builds two separate routes in phase 3, one for the experiment and one for production, gets deviations in phase 6 that nobody can explain.