Dirty data breaks automation, migrations, and reports. Cleanse before you cut over.

# Dirty data breaks automation, migrations, and reports. Cleanse before you cut over.

Most mid-size shops do not fail a migration or an automation project because the tool was wrong. They fail because the source data was never fit to move.

Duplicate customers. The same part number spelled three ways. Blank keys that somehow still get into production. Unit fields that mean gallons in one plant and barrels in another. Finance closes on one set of numbers; ops closes on another. Then someone wires an ETL job, a warehouse load, or a cutover weekend on top of that mess — and the new system inherits every old argument.

We see this pattern constantly in energy, manufacturing, and mid-size operations around Greater Houston and across Texas. The fix is not a bigger platform. It is data cleansing — the unglamorous DBA work that makes automation, migrations, and reporting trustworthy.

What goes wrong when you skip cleansing

Skip the scrub and you pay for it later, usually under a deadline.

Automations amplify the mess. A job that posts invoices, updates inventory, or syncs CRM records does not pause to ask whether “Acme LLC,” “ACME L.L.C.,” and “Acme” are the same customer. It posts three times, or matches the wrong one, and someone spends the next month untangling.

ETL and warehouse loads freeze bad history. Pipelines copy what they are given. If customer IDs collide, dates are null, or product codes drift by plant, the warehouse becomes a durable home for confusion — not a single source of truth. Reporting built on that feed can look polished and still be wrong.

Cutover weekends go long. Migrations fail less often on the script than on the data the script was supposed to carry. Orphaned foreign keys, overlapping primary keys, and “temporary” free-text fields that became permanent all surface when the old system is about to shut off — the worst possible time to discover them.

Reports nobody trusts. When plant and finance cannot agree on revenue, production, or inventory without rebuilding a spreadsheet, the meeting starts with an argument instead of a decision. Dirty dimensions and facts are usually why.

Illustrative pattern (not a client metric): a mid-size manufacturer we have seen in the wild carried three conflicting “active SKU” counts across ERP, spreadsheet, and shop-floor export. Until those lists were reconciled, every forecast and reorder automation was guessing.

What “clean enough” actually means

Perfect data is a myth. Fit for purpose is the bar.

Before ETL, automation, or cutover, we want answers to questions like:

• Does every row that must join have a stable key?
• Can we say what “customer,” “location,” and “product” mean in one sentence each?
• Are duplicates identified and resolved — or at least quarantined — before anything writes downstream?
• Do units, currencies, and date conventions agree across plants and systems?
• Is history complete enough for the reports and models you plan to run, or are we about to invent gaps?

If you cannot answer those, the automation is premature.

Practical DBA steps we take first

This is database work, not a slide deck. A typical cleansing pass looks like this:

1. Inventory the systems of record

List where truth is supposed to live today — ERP, plant systems, CRM, files, spreadsheets that somehow became official. Map which fields feed the automation, warehouse, or cutover. If two systems claim the same fact, pick a winner or document the rule.

2. Profile before you transform

Run profiling on the tables that matter: null rates, distinct counts, min/max dates, orphaned keys, value distributions. SQL Server and other databases make this straightforward with queries against the real tables (or a restored copy). Profiling turns “the data is messy” into a punch list.

3. Define matching and survivorship rules

Duplicates are a policy problem as much as a technical one. Decide what makes two customers the same (tax ID, normalized name + address, account number). Decide which record survives when they merge, and what history you keep. Write those rules down so the next person is not guessing.

4. Fix keys, types, and constraints where it is safe

Tighten what you can in place: trim whitespace, standardize codes, correct obvious type mismatches, add or restore constraints that prevent new orphans. Prefer fixing at the source when the business will own the change. When the source cannot change yet, cleanse in a staging layer — but do not pretend staging is a permanent excuse to leave production dirty.

5. Quarantine what you cannot fix yet

Rows that fail rules go to a reject or exception queue with a reason code, not into the warehouse or the cutover load. Automations should stop or escalate on bad input, not invent a third version of the customer.

6. Prove the feed before cutover

Rehearse the load into a non-production target. Compare row counts, checksums, and critical aggregates (orders, quantities, balances) between source and target. Run the reports the business cares about on the cleaned feed and have ops and finance sign off on the same number. Only then schedule the real cutover.

7. Keep it clean after go-live

Cleansing is not a one-time project. Add checks for new duplicates, null keys, and code drift. Give someone ownership of exceptions. Otherwise the mess rebuilds in a quarter.

With 26 years of professional DBA and database work behind how we approach this, the sequence barely changes: profile, agree on rules, fix or quarantine, prove the feed, then automate. Skipping the middle steps is how “quick” projects become long recoveries.

Why this has to come before ETL, automation, and cutover

ETL without cleansing copies fog into the warehouse. You get dashboards that disagree with payroll and plant.

Automation without cleansing scales bad decisions. The job runs overnight; the cleanup takes weeks.

Cutover without cleansing turns a weekend into a war room. The new system is blamed for problems that lived in the old one for years.

Cleansing first is slower on the calendar up front. It is almost always faster where it counts — when the business has to trust the result.

A short checklist before your next automation or migration

• [ ] Name the systems of record for customer, product, location, and the metrics you report on
• [ ] Profile nulls, duplicates, orphans, and code drift on the tables in scope
• [ ] Write matching and survivorship rules the business will own
• [ ] Decide what fails closed (quarantine) versus what can be auto-corrected
• [ ] Rehearse the load and compare aggregates plant and finance both care about
• [ ] Assign who watches exceptions after go-live

If half of that list is blank, pause the build. The data work is the project.

Talk to us

If you are planning an ETL move, a warehouse, an automation on top of ERP or plant systems, or a cutover — and you are not sure the source data will survive the trip — tell us where it hurts. We do the DBA-side cleansing, profiling, and cutover rehearsal work with mid-size energy, manufacturing, and operations teams. Twenty minutes, no deck tour.

https://pelican-soft.com/contact
Greater Houston, Texas · 281-825-7261
jdelaune@pelican-soft.com