Why 80% of Data Engineering is Cleaning (and How to Do It Right)

Data Cleaning & Denoising: The "Battlefield" of Data Engineering 🧹

It is an industry consensus that data engineers spend 60% to 80% of their time on data cleaning. Why? Because raw data is messy, and "garbage in, garbage out" is the absolute truth in data science.

In this post, based on the data_engineering_book, we’ll deconstruct the logic of industrial-grade data cleaning—moving from "just fixing bugs" to "building robust cleaning pipelines."

1. Where Does the "Noise" Hide?

According to the Data Engineering Book, data quality is the prerequisite for data value. Noise typically falls into 5 categories:

Noise Type	Symptoms	Business Impact
Missing Values	Null addresses, missing age fields	Failed deliveries, incomplete user segments
Outliers	$1M orders (avg is $100), 1000°C sensors	Flawed sales forecasts, cost miscalculations
Duplicates	Double-submitted forms, sync errors	Inflated user counts, duplicate revenue
Inconsistency	"2024-05-01" vs "05/01/24"	Aggregation failures, broken time-series
Logic Conflicts	Registration date after purchase date	Distorted behavior analysis

---

2. The Methodology: Diagnosis-Treatment-Validation

The handbook proposes a three-step closed-loop system for industrial data cleaning:

Step 1: Data Profiling (Diagnosis)

Never start cleaning without measuring. Use Pandas for a quick health check:

import pandas as pd
import matplotlib.pyplot as plt

df = pd.read_csv("raw_data.csv")

# 1. Missing Value Ratio
print(df.isnull().sum() / len(df))

# 2. Outlier Detection using Boxplot
df["order_amount"].plot(kind="box")
plt.show()

# 3. Logic Check: Reg_date should be before Order_date
conflict_count = len(df[pd.to_datetime(df["reg_time"]) > pd.to_datetime(df["order_time"])])
print(f"Logic conflicts found: {conflict_count}")

Step 2: Targeted Cleaning (Treatment)

Cleaning should be context-aware. Don't just delete everything.

• Missing Values: Use Median for skewed numerical data, Mode for categorical, or Model-based imputation for high-priority missingness.
• Outliers: Use Winsorization (clipping at 99th percentile) or business-rule-based correction.
• Duplicates: Keep the "First" or "Last" based on a timestamp.

Step 3: Validation

Repeat the profiling step. Are the null ratios acceptable? Are the logic conflicts cleared? Does the cleaned data still represent the business reality?

---

3. The 4 Principles of Engineering Excellence

Data cleaning isn't a one-time script; it's an automated process. Follow these rules:

1. Traceability: Log every step. Know exactly how many records were dropped and why.
2. Reusability: Wrap your logic into functions.
3. Non-intrusive: Never modify the source file. Always output to a new "Cleaned" layer (Silver layer).
4. Automation: Orchestrate your cleaning jobs using Airflow or Prefect.

Example: A Reusable Cleaning Module

def clean_missing_values(df, fill_rules):
    """
    A reusable function for missing value imputation.
    :param fill_rules: e.g., {"age": "median", "status": "Unknown"}
    """
    df_clean = df.copy()
    for col, rule in fill_rules.items():
        if rule == "median":
            df_clean[col] = df_clean[col].fillna(df_clean[col].median())
        elif rule == "mode":
            df_clean[col] = df_clean[col].fillna(df_clean[col].mode()[0])
        else:
            df_clean[col] = df_clean[col].fillna(rule)
    return df_clean

---

Conclusion

Data cleaning is the foundation of your data "building." If the foundation is weak, the building will fall.

The data_engineering_book covers the entire pipeline—from ingestion to deployment—with industrial-grade insights. If you want to move from "writing scripts" to "designing systems," this repo is a goldmine.

👉 Repo Link: datascale-ai/data_engineering_book

What's the weirdest "dirty data" you've ever encountered in production? Let's share some horror stories in the comments! 👻👇

---