Automation amplifies whatever is already there
A manual process tolerates inconsistent data because a person silently corrects it. They see two spellings of a company name and know it is one company. They see a blank field and ask. An automated process does none of that — it applies the same rule to both spellings and treats the blank as a value.
So the question before automating is not "is our data good enough". It is "what is my process currently doing by hand that the automation will not do".
Decide which system is authoritative for each field
Most businesses end up with the same customer represented in several places: a CRM, a spreadsheet, an invoicing tool, an inbox. That is fine, as long as each field has exactly one system that owns it.
Without that decision, two automations will eventually write conflicting values and the last one to run wins. That failure is particularly unpleasant because it is intermittent and depends on timing, so it looks random.
Write it down as a table: field, owning system, everything else reads it. It takes an afternoon and prevents a category of bug that is very hard to diagnose later.
Give every record a stable identifier
Matching records on a name or an email address works right up until someone changes their name or address, and then it silently creates a duplicate. Every entity that automation touches needs an identifier that never changes and is not derived from anything a person can edit.
If a system does not expose one, generate one and store it on both sides. The cost of adding this early is small. The cost of adding it after two years of records is a data migration.
Make status explicit rather than inferred
A great deal of operational knowledge lives as inference: this row is "active" because the last column has a date in it, or a deal is "closed" because someone moved it to a particular tab. That works when a person reads it and fails when a rule does.
Replace inferred states with an explicit field holding a value from a known set. Then enumerate every value the set contains, including the awkward ones — "on hold", "cancelled by us", "duplicate". Automations will encounter all of them, and the ones you did not enumerate are the ones that will produce the strange behaviour.
- Every state a record can be in, named
- Which transitions are legal, and which are not
- What should happen to work already in flight when a record changes state
Know where the gaps are before the workflow finds them
Run a count of empty values in every field the automation will read. Not a sample — a count. The number is usually higher than anyone expects, and it tells you whether a field can be treated as required or needs a fallback.
The same goes for format: how many phone numbers are in a form your SMS provider will accept, how many dates are strings rather than dates, how many email addresses have trailing spaces. These are all trivial to fix in bulk beforehand and genuinely annoying to handle one-at-a-time inside a workflow.
Fix the intake, not just the backlog
Cleaning existing records is worth doing once. It is worth very little if the thing producing those records keeps producing them in the same shape.
Most data quality problems are validation problems that happened earlier — a free-text field where a list belonged, an optional field that should have been required, a form that accepted anything because rejecting input felt unfriendly. Tighten the intake first, then clean up behind it. Otherwise you will be cleaning the same fields again next quarter.
Agree what a duplicate is before you deduplicate
"Remove duplicates" sounds like a settled instruction and almost never is. Two records with the same email and different names might be one person who changed theirs, or two people sharing an inbox. Two companies with the same name at different addresses might be branches, or might be unrelated.
Write the rule down as a decision someone made: these fields matching means the same entity, these fields matching means a probable match to review, everything else is distinct. Then automate against the rule rather than against an intuition, and keep probable matches in a queue rather than merging them silently.
A wrong merge is much harder to undo than a missed one.
Decide what happens to history
When a record changes, does the old value matter? For a status field the answer is usually yes — knowing something was proposed before it was approved is often the entire question. For a corrected typo, no.
This decision determines whether you need an event log alongside the current state. Retrofitting one is expensive, because the history you wanted is exactly the history you did not keep. Deciding early costs almost nothing.
Test the migration on a copy, with the real data
Cleaning scripts behave differently on real data than on samples, because real data contains the cases nobody thought of: the empty string that is not null, the date in a different century, the name with an unexpected character.
Run the cleanup against a full copy first and count what changed. If the count is much larger or much smaller than expected, the rule is wrong — and finding that out on a copy is free.
Assign an owner per system, not per project
Data quality is not a project that finishes. Fields drift, new integrations write new values, and somebody eventually needs a decision about whether a new status is legitimate.
Name a person per system who owns those decisions. Without one, each integration makes its own choice and the definitions diverge again, usually within a year and usually invisibly until two reports disagree.