Data Preparation

This is how much of a typical machine learning project is spent on preparing the data for analysis – not on developing algorithms, not on evaluating them, not even on deploying them. Not the fun stuff. The preparation. This mindset is flawed.

Because the ceiling of accuracy, effectiveness, and value of any model, any study, and any business decision you take lies in data preparation. An algorithm developed using badly prepared data will not only be inefficient but will also learn incorrectly and give you wrong answers. And, more often than not, in the very scenarios you need it to succeed.

This guide will cover everything from the full process of seven steps, a tool comparison that actually helps, and five fatal errors of preparation that silently ruin your projects before you even realize what happened.

What Is Data Preparation?

Data preprocessing refers to the act of gathering, cleansing, transforming, and validating raw data to ensure that it is correct, consistent, and ready for analysis. There are three terms that get confused all too often in this area, causing major headaches.

Data Preparation encompasses all steps from determining which data you need to validate that data as appropriate for your use. Data Preparation is the general category.

Data Preprocessing is a more narrow definition — the process of getting data ready for an ML algorithm: normalizing numeric data, encoding categorical data, handling missing data. You perform preprocessing in a data preparation process, not as a replacement for preparation.

Data Wrangling (also known as data munging) refers to the actual manipulation of data: altering datasets, merging tables, changing formats. Just one step in the data preparation process. Not the whole process.

Finally, ETL stands for Extract, Transform, and Load and is a software engineering process for transferring data from one system to another. This is similar to data preparation but refers to the pipeline infrastructure rather than the analysis taking place within the pipeline.

It is important to understand the differences here because each one refers to a different set of activities performed by different people using different tools. Confusing them will lead to scope issues and incorrect assignment of tasks.

Why Data Preparation Matters More Than Most Teams Realize

In reality, however, according to a survey conducted by CrowdFlower, 76% of data scientists identify data preparation as the worst aspect of their jobs. Not the hardest aspect. The worst. Because mistakes made during data preparation don’t remain localized. They carry over and multiply.

A single improperly formatted date field skews a time series prediction. A single duplicated customer record increases your churn rate and leads your retention team on a wild goose chase to solve an issue that isn’t there. A single misclassified training sample creates a false pattern for your classification algorithm to learn from.

Four things go wrong when data preparation is done haphazardly:

Model accuracy. Whatever pattern exists within the training data, algorithms will find and learn it. If there are mistakes in your training data, then your model will learn those mistakes and will be only as accurate as the data it has been provided with.

Business decisions. A single unaccounted-for duplicate within a financial dataset might cause revenues to be skewed sufficiently to influence budgets. And those decisions will be made based on that mistake before it is discovered.

Downstream tuning. The teams would always spend weeks tuning their hyperparameters before realizing that the problem lay in the training data itself. All the time spent on tuning was wasted time.

Feature engineering opportunities. There’s no way to engineer features out of your data when you don’t have any idea of what your data means. That’s where preparation comes into play.

The 7-Step Data Preparation Process

These steps run roughly in sequence. Some loop — validation failures in Step 7 send you back to Step 4. That’s not a sign the process is broken. It’s the process working correctly.

Step 1 — Define Your Goal and Data Requirements

The majority of errors in data preparation happen before any work starts with the data. They happen here:

Take an example: a group working on a prediction model for customer churn requires totally different variables to be selected, labeled, and prepared than another group who uses the same data set for the purpose of forecasting customer lifetime value. Same raw data set. Different approaches to data preparation right from the start. Without knowing what problem you’re solving, you’ll end up preparing data for a problem that isn’t even known yet — hence, a perfectly clean data set, unfit for analysis.

Before starting to collect any data rows, ask yourself: What decision will be made based on this analysis? What kind of machine learning task is this, classification, regression, clustering? What are the logical relationships between the target variable and other features? What are your criteria for data quality: how many missing values can you tolerate, what time span does it cover, and how much data do you need?

Step 2 — Data Collection

Data is ubiquitous. Databases, cloud, APIs, flat files, IoT devices, third party feeds. The problem with collecting data isn’t the availability of it but rather stitching together sources without losing consistency.

There are two aspects that will surprise your team. The first is that most of the enterprise data is unstructured – documents, images, videos, audio, sensor readings. It does not fit well into a relational database structure and is treated differently from structured data during almost all subsequent processing steps. Secondly, data volume is misleading. Larger volumes of data are not necessarily better quality data. Smaller, well-labeled datasets often beat large noisy datasets in supervised ML tasks. Gather only the data that you need per your Step 1 specifications.

If you require 18 months of historical data, don’t gather five years just because it is more. That is obvious. But teams do it all the time.

Step 3 — Data Profiling

Data profiling is the diagnostic process that is often skipped by most teams. Don’t.

You’re not repairing things at this point; you’re diagnosing what you’ve got — how complete each field is, how many duplicates there are, whether value falls within reasonable bounds, whether the format is consistent between records. Think of this process as the medical examination before surgery.

Why does this process save you time? Imagine a team that imports customer addresses from three different regional CRMs, who through data profiling realize that one of the systems is storing ZIP codes as integers (stripping out leading zeroes for ZIP codes like 07030) while the other two CRM systems are storing ZIP codes as string fields. Without data profiling, this error will make it to the data cleansing process and potentially into your model. Identifying it in the data profiling process will take twenty minutes. Finding it in your deployed model will take days of debugging and a model rollback.

Measure four aspects: completeness (percentage of values present), uniqueness (number of duplicates), consistency (formatting between records), and validity (values that are present but logically invalid — a customer aged 412, for example).

Step 4 — Data Cleaning

This is where most of the effort gets spent, and this is also where most of the mistakes occur because of people rushing through things.

There are five problem areas which require systematic attention:

Missing Values. Never assume that deletion is the way to go. Deleting rows will lose good data and will cause any downstream modeling to be biased if the missing data was not missing at random. If there is something systematic about those individuals who did not fill out a particular form field, then deleting them will not only decrease the number of records but also delete an important part of the population, and any future modeling will become biased toward not predicting outcomes in that segment of the population.

Duplicates. Easy to identify, sometimes difficult to reconcile. Two records of the same customer with differing email addresses could both be correct if the customer has changed contact information. Blind de-duplication could result in valid data being deleted.

Outliers. Do not immediately dismiss them. An outlier in the customer purchase amount could be an error. Alternatively, it could be your best customer. Analyze the profile before deciding what to do with the outlier.

Inconsistent formatting. Dates recorded in three different formats within the same field (“01/15/2024,” “2024-01-15,” “January 15, 2024”). Currency figures recorded with and without dollar signs. State names and state abbreviations. Choose one format and use it consistently across all records.

Invalid values. Values that logically cannot exist: negative ages, dates of birth in the future, purchase amounts higher than the price of a product. They require domain knowledge to recognize. Software will highlight them; a human needs to make the judgment call.

Read More: Data Cleaning Guide

Step 5 — Data Transformation and Enrichment

The former is accurate; the latter is usable. They are two different things.

Transformation refers to changing your data to the format needed by your algorithm or model. Normalization makes sure that a variable that goes from thousands does not swamp a variable that goes from units in your distance calculation — without normalization, most algorithms based on distance would yield a result purely based on scale, not relationship. One-hot encoding refers to turning categories like “red,” “blue,” and “green” into binary variables that can be processed by algorithms. Aggregation involves summing transaction-level data into daily or monthly summaries, depending on the level of analysis.

Enrichment refers to adding information to your data that was not present in your source 

system. For example, enriching transaction records with weather data to see whether weather conditions influence purchasing behavior, or enriching customer addresses with latitude and longitude to measure proximity. Enriching customer data with third-party demographics to extend their profiles. When done right, enrichment is one of the most powerful things you can do during preparation. When done wrong, it injects bias from your external data directly into your analysis.

Step 6 — Data Labeling

Two out of the three highest-ranked articles on this subject completely omit this stage, which poses an issue since labels represent your supervised model’s training ground — the potential for your model will be determined not by the amount of labels, but their quality.

A model trained to distinguish urgent and non-urgent customer support tickets cannot learn to make such a differentiation without thousands of tickets being properly labeled beforehand. In other words, poor-quality labels don’t result in a model performing poorly — they result in a model that was trained to perform a task incorrectly.

There are three types of labeling in use. Expert labeling, performed manually by specialists with domain knowledge, results in the highest quality labels, although it’s slow and expensive. Crowdsourcing labeling via platforms like Scale AI scales more easily but requires measures to ensure quality — inter-annotator agreement scoring, gold-standard annotations, random audits. Programmatic labeling involves labeling through programming and classifiers — frameworks like Snorkel were created specifically for this.

One of the problems that silently spreads and should be explicitly pointed out: label leakage. Label leakage occurs when the information contained in the label is unintentionally included in the feature set — the timestamp that hints at the label, the data that were filled in after the fact. The model learns how to identify the label by its proxy rather than its essence. Test performance is great. Performance on unseen data is poor.

Step 7 — Data Validation and Storage

Validation is the quality gate between preparation and utilization. Most teams think of validation as a checklist. It is not.

Validation means validating your dataset against your requirements from Step 1. A format check ensures that all fields contain the right data types. A range validation picks up any data that falls outside of the expected boundaries. A cross-field validation identifies any logical contradictions between fields, such as recording the checkout date before the check-in date. With tools like Great Expectations, teams can codify their expectations and have them version-controlled and automatically validated against every incoming batch of data.

If validation fails, which will happen, teams will have to go back to Step 4. That is how the process works and should be expected, not perceived as a problem.

Storage determines everything that comes after it. Cleaned and validated data for analytics is best stored in a data warehouse. Data for machine learning feature engineering is typically stored in a feature store to handle versioning and avoid recomputing the same feature across different models. Placing training data for machine learning into an analytics-friendly storage makes life harder down the road and is an easy mistake to regret six months later.

Data Preparation Tools — How to Choose the Right One

Weirdly enough, most tool comparison articles list everything available and help you choose nothing. Here’s an actual decision framework.

Tool	Best For	Skill Required	Scale
Pandas	Exploratory prep, small-to-medium datasets	Python proficiency	Single machine (memory-limited)
dbt	SQL-based analytics pipelines, repeatable transforms	SQL + some Python	Medium-to-large
Apache Spark	Distributed processing on very large datasets	Python/Scala + infrastructure knowledge	Very large
Databricks	Unified analytics and ML pipelines	Moderate-to-high	Enterprise scale
Trifacta / Alteryx	Visual, low-code preparation for analysts	Low — no coding required	Medium
Great Expectations	Automated data validation and testing	Python proficiency	Any scale
Amazon SageMaker Data Wrangler	ML-integrated prep on AWS infrastructure	Low-to-moderate	Medium-to-large

Three factors will help you make your decision:

What kind of person are you working with? A data scientist fluent in Python will start with Pandas. An analytics engineer building robust pipelines will use dbt. A business analyst who hasn’t learned to code yet will be much more comfortable with Trifacta or Alteryx. Selecting a tool beyond someone’s technical ability introduces training costs that don’t usually get funded.

How big is your dataset? Pandas imports everything into memory; after a certain size, it just won’t work anymore. This point of no return marks when you need Spark or Databricks. It will depend on how much RAM your machine has, but for most single machines, a few gigs should be the tipping point.

Where is your data stored and where is it going? If you’re using all AWS services, then SageMaker Data Wrangler can save you some time on integration. Otherwise, consider a cloud-agnostic solution.

The 5 Most Common Data Preparation Mistakes

These I have seen firsthand, and most of them were in projects I worked on when I started out.

Mistake 1: Cleaning before profiling. Rushing to clean without profiling first is like solving problems without diagnosing them. You could waste a lot of time making sure dates are in a consistent format in a column that is only 60% filled with garbage data and should be deleted altogether.

Mistake 2: Deleting missing values automatically. This one is always easy to do and usually incorrect. When missing values coincide with a certain segment of customers, such as those who did not fill out some field or left the transaction incomplete, deleting those rows means deleting the entire segment. The model will never see that segment. Not that it would not perform well on that segment. Never.

Mistake 3: Data leakage while preparing data. Already mentioned in Step 6, which covered labeling. But it is also relevant to transformations. Using statistics calculated from the whole dataset to normalize data means feeding the model data from the test set via the normalization parameters. It appears to perform better than it does. In production, it fails. Always split your data before running any preparation steps that use aggregate statistics.

Mistake 4: Thinking of validation as a one-time process. You do the validation, the data checks out, and you continue. But six months later, a new data source was added to the upstream pipeline. Nobody reruns the validation pipeline. This is where scheduled, automated validation comes in. Run the validation suite each time a new batch of data is processed.

Mistake 5: Assuming that data preparation is a one-time effort. Production changes. Your customers evolve. New types of products emerge. Sensors are calibrated differently. The pipeline that works great for your data in 2024 might be completely flawed when applied to your data in 2026 without generating any kind of error message.

How Long Does Data Preparation Actually Take?

The “80% of project time” statistic is true but hides an important point – 80% is an average across projects with very different requirements. What determines how much preparation needs to be done?

Quality of data at the source. Data coming out of a well-managed production database with consistent schemas can be prepared in days. Data coming from multiple legacy databases with undocumented formats takes weeks. The difference between these two cases is greater than the duration of most projects.

Number of sources. Every additional source means more work in terms of integration, formatting, and validation. Adding three sources is not double the amount of work needed for one source – it is three or even four times as much. Every join operation brings a whole new class of possible inconsistencies.

Familiarity with the domain. Data scientists who are not familiar with the business context will make preparation mistakes that will need to be fixed later. Someone who understands the business will catch abnormalities that otherwise would have been smoothed away. Domain knowledge is a preparation resource; the lack of domain knowledge is a preparation 

cost.

Tools. Preparation by hand in spreadsheets on a medium-sized dataset takes weeks. The same task in a properly configured dbt pipeline with automated testing takes days. The tooling gap is real and worth planning for.

Rough Benchmarks:

• Small analytics project (one clean source, defined goal): 1–3 days
• Mid-size ML model (2–4 sources, mixed quality, some labeling): 2–6 weeks
• Enterprise data pipeline (multiple systems, compliance requirements, ongoing ingestion): months of initial build, with continuous maintenance

Frequently Asked Questions

Conclusion

All guides on this subject consider data preparation as something that comes before doing the actual job.

However, the quality of your preparation determines the ceiling of anything done on top of this preparation — be it your models, reports, or business decisions made based on those datasets. There is no point at which you could make up for poor preparation with good modeling. Your data was contaminated before the algorithm even laid eyes on it.

Teams that spend time and effort into building reusable, validated data preparation pipelines do not only build better models. They create models that work in production, analytics that withstand any scrutiny, and business decisions that do not have to be retracted after six months because someone discovered an anomaly and traced it back to the formatting issue in a date field of 2024.

Choose a dataset you currently work with. Go through Step 3 — profiling only.