Every company I’ve worked with has, at some point, tried to solve their data quality problem with a training session. Someone creates a slide deck about “the importance of data hygiene.” Employees sit through it. They nod. They go back to their desks and enter data exactly the way they did before.
This isn’t a people problem. It’s a systems problem. And it requires an engineering solution.
Why “Enter It Correctly” Doesn’t Work
A sales rep has seven minutes between calls. They need to log the last call and prep for the next one. The CRM has a “Company Name” field. They type “Johnsen & Sons” because that’s what it sounded like on the phone. It’s actually “Johnson and Sons LLC.” Next week, a different rep logs the same company as “Johnson Sons.”
Three records. One company. No deduplication. And a pipeline downstream that now thinks you have three separate customers.
You can train the rep to always check the existing database before creating a new record. You can put it in the handbook. You can send reminder emails. But when someone’s moving fast — and every business wants their people moving fast — data entry hygiene is the first thing that slips.
The fix isn’t discipline. It’s design.
Data Lineage: Know Where Your Numbers Come From
Before you can fix data quality, you need to know where your data actually comes from and what happens to it along the way. This is data lineage, and most companies have none.
Data lineage answers three questions:
- Where did this number originate? Which system, which table, which field, which user?
- What transformations happened to it? Was it aggregated? Joined with other data? Filtered? Rounded?
- What downstream systems consume it? If this number is wrong, what breaks?
Without lineage, debugging a bad number in a quarterly report means starting at the dashboard and working backwards through every possible path the data could have taken. I’ve seen this take days. With lineage, you trace the specific value back to its source in minutes.
What Lineage Looks Like in Practice
For a mid-market company, lineage doesn’t have to be a fancy graph database with a visual explorer. It can be as simple as:
- Column-level documentation in your data warehouse: where each column comes from, what transformations are applied, when it was last updated.
- Pipeline logging that records input counts, output counts, and any records that were filtered, merged, or flagged at each stage.
- A source registry — a single document that lists every data source, its update frequency, its owner, and its known quality issues.
The registry is the most underrated piece. When someone asks “why don’t our sales numbers match accounting’s sales numbers?” the answer is almost always in the registry: different sources, different update frequencies, different definitions of “sale.”
Engineering Quality at the Source
The most effective data quality controls happen at the point of entry, not downstream in a cleaning pipeline. Here’s what actually works:
Constrained inputs over free text. Every free-text field is a data quality liability. Dropdowns, typeahead search against existing records, and standardized picklists eliminate entire categories of errors. The sales rep who would have typed “Johnsen & Sons” instead selects “Johnson and Sons LLC” from a search that fuzzy-matches on the first three characters.
Validation at write time, not read time. If a phone number needs to be 10 digits, reject it at the form level. If a date can’t be in the future, enforce that in the UI. If a required field is blank, don’t let the record save. Every invalid record that makes it into your system costs 10x more to fix later than it costs to prevent now.
Canonical identifiers over names. People misspell names. They don’t misspell UUIDs. Every entity in your system — customers, products, locations, employees — should have a system-generated identifier that’s used for joins and lookups. Names are display labels, not keys.
Merge workflows for duplicates. Duplicates will happen regardless of your prevention efforts. The question is whether you have a systematic process for detecting and resolving them, or whether they accumulate forever. A weekly duplicate detection report — even a simple one based on fuzzy name matching — catches problems before they metastasize.
Managing Third-Party Data Inputs
Your own data entry is only half the problem. The other half is data that arrives from external systems: vendor feeds, API integrations, partner data shares, file uploads from customers.
Third-party data has all the problems your internal data has, plus one critical difference: you don’t control it. The vendor can change their schema, their format, their update frequency, or their data definitions without telling you.
Rules for managing external data:
Never trust, always validate. Every inbound data feed should hit a validation layer before entering your system. Check for expected schema, reasonable value ranges, completeness, and freshness. A feed that’s suddenly empty or three days stale should alert someone, not silently produce reports full of zeros.
Version your ingestion contracts. Document exactly what you expect from each external source: which fields, which formats, which frequencies. When the source changes — and it will — you need to know what changed and what needs to be updated on your side.
Quarantine before merging. Inbound data lands in a staging area first. It gets validated, cleaned, and matched to your canonical records before it touches your production tables. This is the bronze layer of a medallion architecture — raw data, exactly as received, with a clear separation between “what they sent” and “what we use.”
Monitor for drift. A source that sent 10,000 records daily for six months and suddenly sends 2,000 isn’t necessarily broken — but it deserves investigation. Build anomaly detection on record counts, null rates, and value distributions for every external feed.
The Data Hygiene Stack
Putting it all together, a practical data hygiene system for a growing company looks like:
- Source registry — What data comes from where, who owns it, known issues
- Input validation — Constraints, dropdowns, required fields, format enforcement at entry point
- Ingestion pipelines — Automated extraction with schema validation and staging
- Deduplication — Regular fuzzy matching and merge workflows
- Lineage documentation — Column-level sourcing and transformation records
- Quality monitoring — Automated checks on completeness, freshness, and anomalies
- Incident process — When a data quality issue is found, how is it traced, fixed, and prevented?
None of these are individually complex. Most can be implemented incrementally — you don’t need to build all seven before any of them provide value. Start with the source registry and input validation (cheap, immediate impact), then build out monitoring and lineage as your data infrastructure matures.
The Compounding Effect
Bad data hygiene compounds. One duplicate customer becomes three. Three become twelve as different systems copy the duplicates. Each system has its own version of the “truth,” and reconciling them becomes a quarterly fire drill that nobody enjoys and everybody dreads.
Good data hygiene also compounds — but in the other direction. Clean data makes every downstream use more reliable. Reports are trusted. Analytics are actionable. AI models trained on clean data produce better predictions. And the time your team spends debugging discrepancies drops to near zero.
The difference between these two paths isn’t talent, budget, or technology. It’s whether someone treats data quality as an engineering discipline — with systems, automation, and monitoring — or as a cultural aspiration enforced through slide decks and good intentions.
Engineering wins every time.
If your team is spending more time reconciling data than analyzing it, that’s a fixable problem. Book a free strategy call and we’ll map out where your data quality is breaking down.