The records your automation cannot use, and the ones it half-uses

Every automation project meets the same obstacle a week in: the data is not as tidy as everyone assumed. Missing phone numbers, dates typed three ways, addresses in a single free-text field, a status column with values nobody recognises.

The usual advice is to clean everything first, which is both correct and unhelpful — full cleanup is a project of its own, and businesses that wait for it never automate anything. The useful question is narrower: which defects stop the work, which pass through silently, and which of your fields actually matter.

Loud failures and quiet ones

HubSpot's documentation on import errors describes two outcomes that are worth holding side by side.

If your import file is missing a value for a required property, the row errors. You are told, the data does not go in, and you fix the file and import again. Annoying, visible, safe.

If a value fails the property's validation rules — special characters, too long, too short — then, in the documentation's words, the affected records are imported but the property does not contain a value.

The record arrives. The field is empty. Nothing in the import summary calls this out as the problem it will become.

That second case is the one that hurts, because it produces a database that looks populated and is not. Three months later an automation skips those contacts, or worse, does something with a blank where a value should have been, and the trail back to an import nobody remembers is cold.

Validation is a property of the field, not of the import

The same vendor's documentation on validation rules makes a point that changes how to think about the problem. Validation applies when a value is set or edited — during creation, on a record, on the index page, through import, and through form submissions built in the current form editor.

In other words, the rule belongs to the field and defends every route into it. Set it once and it covers the web form, the manual entry and the integration alike.

That is the leverage. Cleaning existing records is work proportional to how many you have. Constraining a field is work proportional to how many fields you care about — a much smaller number — and it stops the mess being remade tomorrow.

The same documentation notes that when properties have automatic formatting enabled, the import tool cleans values as they come in, which handles a share of format problems without anybody's intervention.

Which fields actually matter

Not all of them, and this is where cleanup projects go wrong.

Work backwards from what the automation must do. A reminder workflow needs a contact method and an appointment time; it does not care whether the industry field is filled in. An invoicing workflow needs a legal name, an address and terms. A follow-up sequence needs a date of last contact that is actually maintained.

For each automation you are planning, list the fields it reads. Usually four to six. Those are the fields to fix and to constrain. Everything else can stay untidy indefinitely without costing you anything, and pretending otherwise is how a two-week project becomes a two-month one.

Then, for each of those fields, ask three questions of your existing records: how many are empty, how many hold something that is not what the field is for, and how many disagree with another system that also holds it.

The four defects, in the order they cost you

Empty. The cheapest to detect and the easiest to plan around — an automation can be told to skip or to flag. The risk is silent skipping: a workflow that processes 890 of 1,000 records and reports success.

Wrong shape. A phone number as +44 20 7... in some rows and 020 7... in others; a date as 03/04 with nobody sure which is the month. This is what formatting rules and validation are for, and it is the defect most improved by constraining the field.

Wrong meaning. The field is populated, correctly formatted, and holds something other than what the name implies — a notes field carrying delivery instructions for some customers and complaint history for others. No validation catches this. Only reading a sample does.

Stale. Accurate when written, wrong now. The address of a customer who moved, the contact for a company where that person left. The only defence is knowing how old the value is, which is why a "last verified" date on your important fields is worth more than it looks.

A practical order of work

Sample fifty records by eye. Not a report — actually look at fifty. Twenty minutes, and you will learn more about your data's real defects than from any summary count.

Fix the fields your first automation reads, and only those.

Constrain them so they stay fixed. Validation, required flags, formatting rules, dropdowns instead of free text. Free text is where meaning goes to diverge.

Decide what the automation does with a bad record — skip and flag, or stop and ask. Both are fine; having no answer is not, because the default is usually to proceed with a blank.

Then leave the rest alone until an automation needs it.

When the data is the actual project

Sometimes the honest finding is that the records cannot support the automation anyone wants, and the sequencing has to change.

That is worth discovering deliberately rather than three weeks into a build. The signals are recognisable: the same entity exists in two systems with no shared identifier, key facts live only in free-text notes, or nobody can say which system is right when two disagree.

Establishing which of your fields are dependable, and which automations your current records could actually support, is one of the things a process audit covers: $299, three business days, and a straight answer on whether you are ready to build or need a week of data work first.

Sources

I read these pages in August 2026. Vendor documentation moves; check the vendor's own page. This describes one platform's behaviour rather than a general rule.

What happens to a row missing a required value, versus one that fails validation: HubSpot on troubleshooting import errors.

Where validation rules apply, and automatic formatting on import: HubSpot on setting validation rules for a property.

Related: one customer, two records covers records multiplying, rather than the contents of the fields inside one.