How to Clean Up Messy or Inconsistent Company Data

Signs Your Company Data Needs a Cleanup

Your data needs cleaning up the moment the same fact starts meaning two different things, depending on which system you happen to check that day.

• Duplicate records for the same customer, product, or transaction, usually under slightly different spellings or abbreviations.
• Blank or missing fields in places that should always be filled in.
• The same fact showing different values depending on which system or report you pull it from.
• Formats that vary from record to record: dates written three different ways, phone numbers with and without area codes, inconsistent casing on company names.
• Teams spending real meeting time manually double-checking numbers before anyone will trust them out loud.
• Integrations that fail, or need a manual fix, every time two systems try to sync.

Set Your Data Quality Standards First

Before altering a single record, establish what “clean” means for every field in your schema. Skipping this step turns cleanup into a moving target, causing errors to re-emerge as soon as team members forget why they made a fix.

• Define Accepted Criteria: Set explicit standards per field—including valid formats, required completeness percentages, and acceptable numerical ranges.
• Maintain a Data Dictionary: Publish a simple reference naming each field, its allowed values, and its designated owner.
• Establish a Legacy Policy: Determine how to handle historical data that predates new standards. Retroactively patch business-critical fields and grandfather non-essential legacy entries.

Profile and Assess What You’re Working With

Profiling comes before fixing, not after. Run a pass across every field before you touch anything, so you know exactly how bad the problem actually is instead of guessing off a bad meeting.

• Run a profiling pass before fixing anything: count nulls, duplicates, and out-of-range values per field.
• Trace each recurring issue back to its actual source, a specific form, a broken integration, or one legacy system, rather than just cataloging symptoms.
• Flag which fields are business-critical versus safe to leave imperfect for now.

If you find yourself fixing the same duplicate or the same bad date format more than once, that’s a sign you patched a symptom instead of closing off the source.

Standardize Formats and Field Conventions

Standardizing means every system agrees on what a date, a name, or a number should look like.

• Convert every date field to one format across every system it touches.
• Normalize company and person names to one casing and suffix convention.
• Store numbers as numbers, not text, so downstream calculations don’t silently fail.
• Align units of measurement and currency before merging datasets.

A date stored as text in one system and as a timestamp in another can pass every validation check and still break the report that joins them.

Remove Duplicate Records

• Define what counts as a duplicate per entity before running any matching tool: a shared email address doesn’t always mean the same company.
• Use fuzzy matching, not exact-match rules alone, to catch near-duplicates like “Smith, John” versus “John Smith.”
• Auto-merge the obvious matches, but route low-confidence matches to a person instead of merging blind.
• Document which record wins in a conflict, so the rule applies consistently next time.

Handle Missing Values With a Clear Policy

Missing data falls into two distinct categories: critical fields that break downstream processes, and non-critical fields that can remain blank.

1. Critical Fields: Account IDs, tax numbers, and primary keys must be populated or flagged before records move through your pipeline.
2. Non-Critical Fields: Secondary contact info or survey responses can remain empty without corrupting downstream analysis.
3. Imputation Methods: When filling gaps, use explicit statistical methods rather than arbitrary defaults:
   • Numerical Data: Apply mean or median imputation for normally distributed values, or use k-Nearest Neighbors (k-NN) / regression models for complex datasets.
   • Categorical Data: Use mode imputation or explicitly flag entries as “UNKNOWN”.
   • Auditability: Always tag imputed values in your metadata so statistical estimates are never confused with original, customer-reported data.
4. Deletion vs. Retention: Remove rows only when missing values render the entire record unserviceable. Defaulting to row deletion reduces sample size while silently discarding valid, valuable signals.

Validate and Correct Remaining Errors

Validation catches what earlier steps miss: a record that passes every field-level check but breaks the moment it’s evaluated against a rule that spans multiple fields or systems.

Start with range and logic checks, enforced as CHECK constraints at the database layer, or as pipeline assertions if you’re validating before load. A percentage can’t exceed 100. An end date can’t precede a start date. A foreign key has to resolve to a record that actually exists. These catch errors no formatting rule would ever flag, because the individual values are all technically valid on their own.

Cross-check high-stakes fields, addresses, emails, tax IDs, against an external source when one’s available: an address validation API, an email verification service, a government registry lookup. Save structural cleanup, casing, stray whitespace, trailing characters, for last. A TRIM() and a regex pass are trivial to run, but running them before the logic and referential checks just means redoing the work once a deeper problem turns up.

Automate the Recurring Parts and Keep Monitoring

A validation rule that only runs once is worth exactly one cleanup. Wire the same logic into the pipeline so it fires on every new record, not just the batch you happened to be looking at.

• Turn each rule above into an automated check: a validation library or a scheduled job in your orchestrator, an Airflow DAG, a dbt test suite, not a spreadsheet macro someone has to remember to run.
• Set alert thresholds so a small anomaly, a null rate ticking up, a duplicate rate creeping past normal, gets flagged before it becomes a reporting problem.
• Re-run a full profiling pass on a set schedule. Data drifts even when nothing visibly breaks.

Manual vs. Automated: When Each Makes Sense

Deciding What to Clean First

Treating all messy datasets equally wastes resources on standalone tables while critical, multi-system feeds remain broken. Prioritize datasets by upstream dependencies and overall business impact.

• Dependency Mapping: Target core tables feeding multiple downstream analytics pipelines or customer-facing AI models first.
• Scoring Matrix: Resolve ties using a direct calculation:

Priority Score = (Business Impact x Urgency) + Effort

What to Do When Sources Disagree

Two systems reporting different numbers for the same customer isn’t a data entry error. It’s a definition mismatch, and no amount of reformatting or deduplication will fix it.

• When one system is clearly the system of record, use it, and document that decision so the same question doesn’t come up again.
• When neither system is clearly authoritative, you need a single governed definition for shared concepts: customer, revenue, active account. That definition gives both systems something to check against instead of each side defending its own number.
• Log the discrepancy and alert whoever depends on that data. Don’t let it surface downstream as a mystery someone has to reverse-engineer during a report.

Back Up Before You Touch Anything

Every cleaning rule you write is a chance to make things worse, not just better. Treat the data you’re about to touch as read-only until you’ve proven the rule actually works.

• Always work from a copy, a staging table, or a snapshot, never the production original, so a bad rule can’t destroy something unrecoverable.
• Test cleaning rules on a small sample before applying them to the full dataset.
• Keep a version history of the cleaning steps themselves, not just the final result, so a bad change can be traced back and undone.

How to Know the Cleanup Actually Worked

You can’t tell whether a cleanup worked from how the data looks. You can only tell from what you measured before you started and what you measure after.

• Track a small set of metrics before and after: percent complete, percent duplicate, percent passing validation.
• Set a specific target, 98% passing validation, say, rather than a vague goal like “cleaner data.”
• Revisit the same metrics on a schedule so quality decay gets caught early, not just once when the project wraps.

Making This Repeatable With Actian

A successful data quality strategy turns manual cleanup into an automated, continuous background process.

Actian Data Intelligence Platform embeds real-time accuracy, completeness, and consistency metrics directly into your search results and lineage graphs, giving your team instant visibility into data health without manual audits. Paired with knowledge-graph-driven governance and Actian DataConnect for hybrid integration, you can resolve source conflicts and maintain clean, trusted data across your entire organization.

Frequently Asked Questions (FAQ)

1. What’s a realistic timeline for a first data cleanup project?

A focused initial cleanup targeting 1–2 high-priority datasets typically takes 2 to 4 weeks. This includes setting field standards, running initial profiling passes, applying transformations, and verifying automated pipeline rules.

2. Can two source systems both be “right” at the same time?

Yes. For example, a CRM might record “Revenue” upon contract signing, while an ERP records it upon invoice payment. Both figures are accurate within their respective contexts, which is why establishing clear, governed definitions across systems is essential.

3. How much data loss risk is acceptable when automating cleanup rules?

Zero unrecoverable data loss is acceptable. Always run automated cleaning steps on staging copies, maintain immutable backups of raw inputs, and flag or quarantine failing rows rather than permanently deleting them.

4. Does prioritizing the “worst” dataset ever mean ignoring a smaller but more critical one?

No. Prioritization should always be driven by business impact and dependencies rather than raw error volume. A small reference table with a 5% error rate that feeds regulatory reporting takes precedence over a massive log table with a 30% error rate that nobody queries.

5. Can a small team maintain data quality without a dedicated data steward?

Yes, provided validation rules and quality monitoring are automated within your data pipelines. Modern platforms surface automated alerts and quality scores directly into developer workflows, enabling engineering teams to maintain clean data without manual overhead.

About Author

About Actian Corporation

Actian empowers enterprises to confidently manage and govern data at scale. Organizations trust Actian data management and data intelligence solutions to streamline complex data environments and accelerate the delivery of AI-ready data. Designed to be flexible, Actian solutions integrate seamlessly and perform reliably across on-premises, cloud, and hybrid environments. Learn more about Actian, the data and AI division of HCLSoftware, at actian.com.