Access full report
Oops! Something went wrong while submitting the form.
🤍
Facilitated by The Modern Data Company in collaboration with the Modern Data 101 Community
Latest reads...
.png)
.png)
.png)
.png)
TABLE OF CONTENT
TL;DR
TL;DR
- “100% clean” is not a realistic end state for data cleaning; it’s a moving target that shifts as new data enters the system.
- Manual review should be reserved for semantic and business-logic judgment calls, not deduplication or format standardisation; those belong in deterministic rules.
- Data cleaning with Python (or any scripting layer) handles the structural layer at scale; AI helps with the messy semantic layer, but only when directed with schema, logic, and examples.
- The hardest data quality issues usually trace back to source systems, not the cleaning pipeline, which means part of the fix is organisational, not technical.
Most people searching for how to clean data already know the basics of removing duplicates and filling in blanks. The harder question is the one that actually shows up in data engineering forums and team retros: what to do about the data that survives every rule you write.
This is where data cleaning stops being a checklist and starts being a design decision about where automation ends, and human judgment begins.
What “100% Clean” Actually Means in Data Cleaning
Even after deduplication, null-handling, and format standardisation, some of the data still looks wrong in ways no rule anticipated. That’s just how real-world data behaves. Data quality is seldom a one-time destination; even certified-clean data can drift the moment it’s touched again, so treating “100% clean” as a fixed target yields diminishing returns, not a finish line.
That’s why data quality frameworks now split into two questions: is the data structurally correct, and is it driving better decisions? A dataset can pass every completeness and freshness check and still feed a report nobody reads, proof that “clean” and “useful” aren’t the same goal, and optimising only for the former misses the point.
Why Manual Data Cleansing Fails to Scale
Manual review is indispensable for catching what rules can’t anticipate, but it doesn’t scale linearly with data volume, and treating it as the default backstop for every edge case creates a quiet tax on the data team. Fixing data quality issues in most organisations still happens reactively; an analyst spots a discrepancy, traces it through undocumented pipelines, and applies a local, often unrecorded fix, which means the same category of error tends to resurface every time new data lands, because the fix lived in someone’s memory rather than in a rule.
The scale of this problem is well documented outside of anecdote, too. McKinsey has found that data teams without robust data controls in place can spend 20 to 30% of their time on data cleansing alone, time that compounds every time a manual fix isn’t captured as a reusable rule. The practical implication is straightforward: manual review should be a deliberate, bounded layer of your data cleaning process, not the default answer to every anomaly a stakeholder flags.
The Deterministic Layer: What Rules-Based Data Cleansing Can Handle
Before reaching for AI or another round of manual review, it’s worth being precise about how much of “dirty data” is actually deterministic, meaning it can be caught and fixed with a fixed, testable rule every single time.
1. Structural checks: duplicates, nulls, types, and formats
Structural issues are the most tractable category in data cleansing, precisely because “correct” has an objective definition. A phone number either matches a valid format or it doesn’t; a date field is either a parseable date or it isn’t. This is the layer where academic data cleaning research has concentrated for two decades, with systematic reviews of data cleaning methods consistently aiming to reduce the human overhead involved in catching feature- and label-level errors at scale. Deduplication, null detection, type coercion, and format standardisation belong here: write the rule once, run it on every batch, and don’t route these to a human reviewer.
2. Business-logic checks: encoding what “valid” means for your business
A second deterministic layer sits one level up: rules that aren’t about formatting but about business logic. An inactive customer cannot have an active subscription. An opportunity can’t close before it’s created. These rules require domain input up front, but once defined, they’re just as automatable as a null check; the trick is capturing them as versioned logic instead of a Slack thread that only the person who wrote it remembers. This is precisely the kind of expectation that data contracts are designed to encode as enforceable, machine-readable agreements between the teams producing data and the teams consuming it, so a business rule survives beyond the one dashboard where someone happened to notice it was being violated.
Data Cleaning vs. Data Wrangling: Understanding the Core Difference
“Data cleaning and wrangling” often gets used as a single phrase, but the two solve different problems, and conflating them is part of why cleaning projects run over scope.
- Data cleaning corrects errors within a dataset: wrong values, duplicates, missing fields. Data wrangling (or munging) reshapes, joins, and restructures data into a new form altogether, regardless of whether the underlying values were clean to begin with.
- A dataset can be perfectly wrangled; joined, pivoted, and shaped exactly for the report, while still containing the same duplicate customer records it started with. Keeping this distinction explicit matters operationally, too: when multiple teams each write their own cleaning logic for the same source instead of sharing a single cleaned, contract-backed layer, the result is duplicated effort and metrics that quietly disagree with each other across the organisation.
Data Cleaning with Python: Automating the Deterministic Layer
Once a rule is defined, deduplication logic, a null-handling policy, and a business-logic constraint are in place, data cleaning with Python is how it gets applied consistently instead of by hand, every time, on every batch. A simple example of the structural layer in practice:
import pandas as pd
df = pd.read_csv("customers.csv")
# Structural: standardize types and formats
df["email"] = df["email"].str.lower().str.strip()
df["signup_date"] = pd.to_datetime(df["signup_date"], errors="coerce")
# Structural: deduplicate on a stable business key, not on name
df = df.drop_duplicates(subset=["customer_id"], keep="last")
# Business logic: flag (don't silently drop) rows that break a known rule
df["invalid_subscription_flag"] = (
(df["customer_status"] == "inactive") & (df["subscription_status"] == "active")
)
Notice what this script does not do. It doesn’t try to guess whether “Jhn Smtih” should become “John Smith,” and it flags the business-logic violation rather than silently correcting it.
Scripting the deterministic layer is what frees up manual review and, increasingly, AI to focus only on the genuinely ambiguous cases, rather than re-litigating a null check every week.
How to Clean Data in the Semantic Gray Zone Using Schema-Aware AI
Semantic errors, not structural ones, are what make teams ask “is this where AI comes in?”: a full name duplicated across fields, a company recorded as “zzzz,” a name with “Mr.” still attached. No rule catches every variant, but telling an LLM to “clean this dataset” often makes it worse: silent deletions, invented standardisations, unreviewed assumptions.
- Treat AI as a code generator you audit.
- Feed it your real schema and sample rows instead of letting it guess structure. Separate the business rule (”merge redundant name fields, strip honorifics into a title field”) from the engineering standard (”vectorised operations, not row-by-row loops”).
- Show input-output examples for ambiguous cases instead of prose descriptions.
The output should be a script, readable, testable, versionable, never a silently altered CSV. AI scales a rule; it doesn’t decide what the rule should be, which is the same discipline behind treating data quality as ongoing, not a one-time state. Skip that discipline, and you get clean data feeding decisions nobody trusts.
Upstream Source-System Issues
Many data quality issues originate in source systems. Duplicate customer IDs, invalid timestamps, or unexplained negative order values often result from poor system configuration, broken processes, or weak governance. Cleaning them downstream only recreates the same issues with every ingestion.
This is also where the cultural dimension of data quality becomes unavoidable. Data quality is frequently reduced to a tooling problem when it’s actually a question of accountability: who gets looped in when an issue surfaces, who owns the definition of “correct,” and how quickly that person can act on it.
Resolving these problems requires clear ownership. Teams should document source-system issues in data contracts, schema documentation, or lineage records so they can be traced, assigned, and fixed at the source instead of repeatedly patched downstream.
Operationalising Data Cleansing Across Enterprise Data Platforms
Ad hoc scripts and one-off manual reviews don’t compound; they get rewritten by the next analyst who touches the dataset. The organisations that stop re-solving the same data cleaning problems tend to have moved the deterministic layer into their data platforms rather than leaving it in individual notebooks. Modern data platforms are increasingly evaluated specifically on whether they turn fragmented, messy inputs into governed, quality-checked data products by default, rather than treating cleaning as a manual pre-step every team repeats for itself.
Concretely, this looks like defining service-level objectives for each dataset, completeness, accuracy, freshness, uniqueness, and letting automated quality checks run continuously against those thresholds rather than waiting for someone to notice a dashboard looks wrong. When a check fails, the alert should route to an owner with the context needed to act, not into a shared inbox nobody monitors.
This is the difference between data cleaning as a recurring, manual chore and data cleaning as an enforced property of how data enters the system in the first place.
A Practical Governance Loop: Automate, Escalate, Document
The answer to “how do you ensure data is 100% clean apart from manual review” is a loop that requires three layers:
- Automate the deterministic layer.
- Structural checks and known business rules run as code: Python scripts, dbt tests, or data product monitoring that tracks freshness, volume, and error rates continuously with zero manual touch once defined.
- Direct AI at the semantic gray zone, under audit.
- Use schema-aware, example-driven prompting to generate scripts for messy free-text fields, and review the generated logic before it runs against production data.
- Escalate and document what neither layer can fix.
- When an issue traces back to a source system, don’t quietly absorb it as a recurring manual fix, tie it back to the specific decision it’s actually distorting, and use that framing to make the business case for fixing it upstream instead of arguing about the data in the abstract.
Manual review should focus on decisions that require business judgment, such as resolving duplicates or choosing canonical values.
So, Can You Ever Reach 100% Clean Data?
Practically, no, and treating that as the goal tends to misdirect effort toward polishing dimensions that are easy to measure (completeness, freshness) rather than the harder question of whether the data is actually driving better decisions.
The dimensions of data quality that are simple to track are, like the ones measured before the data is ever used, while the value a dataset creates is tangled up in human judgment and resists a single clean number. A more durable goal is a system where the deterministic layer is fully automated, the semantic layer has a clear, audited process rather than an ad hoc one, and everything that’s genuinely unresolved is documented and escalated instead of silently absorbed as manual work.
FAQs
Q1. How to ensure data is clean?
Run structural checks (nulls, duplicates, format consistency) and semantic checks (values that are technically valid but meaningless “xyz” as a company name, honorifics stuck to a name field). Automate the first, review the second. Then verify the “clean” data actually improves a decision or report, not just passes a scorecard.
Q2. How do you usually check if the data you are working with is clean and accurate?
Start with a profiling pass: null rates, duplicate rows, type mismatches, outliers. Then spot-check a sample against source-of-truth records for accuracy, not just format validity. Finally, trace a few records through to their downstream use, a report or model, to confirm the data is actually informing something correctly.
Q3. How can you verify that your data is clean and ready to analyse?
Three checks: completeness (no missing critical fields), consistency (same entity represented the same way everywhere), and correctness (values match reality, verified against a trusted source or sample audit). If all three hold and the data still doesn’t change any decision downstream, it’s clean but not yet useful, a different problem.
Q4. What is data cleaning?
The process of finding and fixing errors, inconsistencies, and inaccuracies in a dataset, deduplication, null-handling, format standardisation, and correcting semantically wrong values, so the data reliably reflects reality before it’s used for analysis or decisions.
Your Copy of the Modern Data Survey Report
See what sets high-performing data teams apart.
Better decisions start with shared insight.
Pass it along to your team →
https://www.moderndata101.com/survey
Your Copy of the Modern Data Survey Report
See what sets high-performing data teams apart.
Better decisions start with shared insight.
Pass it along to your team →
https://www.moderndata101.com/survey
No items found.
Author Connect 🖋️
Find more community resources
About Modern Data 101
Modern Data 101 is a movement redefining how the world thinks about data. A community built by the same team behind the world’s first data operating system, Modern Data 101 sits at the intersection of data, product thinking, and AI. Spread across 150+ countries, the community brings together a global network of practitioners, architects, and leaders who are actively building the next generation of data systems.
At its core, Modern Data 101 exists to simplify the journey from raw data to tangible and observable impact. It advocates high-potential data systems and next-gen architectures to unify and activate insights and automation across analytics, applications, and operational workflows at the edge.
In a world shifting from data stacks to AI ecosystems, Modern Data 101 helps teams not just navigate the change but lead it.
Access full report
Oops! Something went wrong while submitting the form.
