Working with messy customer data
Customer data is never as clean as the documentation says. Duplicate records, free-text fields, scanned PDFs, inconsistent IDs and silent gaps are the normal state of enterprise data. FDEs who can quickly understand and tame a messy dataset unblock projects that others give up on.
Getting access the right way
Access usually takes longer than anything else. Treat it as the first deliverable:
- Ask for the minimum you need — specific tables, fields and a date range — not "the whole database."
- Prefer a read-only replica or extract over production systems.
- Write the request so it is easy to approve: purpose, fields, retention, who will access it, where it will be stored.
- Follow the customer's process exactly. Shortcuts with data access end careers and contracts.
The first-day data audit
Before building anything, profile the data. A few queries answer most of the important questions:
-- How much data, over what period?
SELECT COUNT(*) AS rows, MIN(created_at) AS first_seen, MAX(created_at) AS last_seen
FROM claims;
-- How complete are the fields you depend on?
SELECT
COUNT(*) FILTER (WHERE policy_id IS NULL) AS missing_policy,
COUNT(*) FILTER (WHERE amount IS NULL) AS missing_amount,
COUNT(*) FILTER (WHERE description = '') AS empty_description
FROM claims;
-- Are there duplicates?
SELECT claim_number, COUNT(*) AS copies
FROM claims
GROUP BY claim_number
HAVING COUNT(*) > 1
ORDER BY copies DESC
LIMIT 20;
-- Do keys actually join across systems?
SELECT COUNT(*) AS orphan_claims
FROM claims c
LEFT JOIN policies p ON p.policy_id = c.policy_id
WHERE p.policy_id IS NULL;
Then look at actual rows — twenty random samples — and read them. Many problems are obvious to a human and invisible in summary statistics.
Common mess and what to do about it
| Problem | What it looks like | Typical fix |
|---|---|---|
| Inconsistent identifiers | CUST-0012, cust12, 12 for the same customer |
Normalisation rules; a mapping table agreed with the data owner |
| Free text where structure was expected | Status written as prose in a notes field | Rules first; LLM extraction for the long tail, with sampling checks |
| Documents and scans | PDFs, images, faxes | OCR or document parsing; measure extraction quality separately |
| Time zones and dates | Mixed formats, local vs UTC | Convert to UTC at ingestion; keep the original |
| Silent gaps | A system outage left a week of missing records | Plot volume over time; flag gaps to the owner |
| Duplicates | Same entity entered twice | Deterministic matching first, fuzzy matching with review second |
Write down every assumption and cleaning rule, and get the data owner to confirm the important ones. "We treat claims with no policy ID as invalid" is a business decision, not a technical one.
Handling sensitive data
- Minimise — don't pull fields you don't need, especially personal data.
- Mask or tokenise identifiers where the use case allows it.
- Respect residency — know where data must stay, including which regions your model API calls go to.
- Control copies — no extracts on laptops, no data in tickets or chat threads.
- Log access — be able to say who accessed what, and why.
Sending customer data to a third-party model API is a data transfer. Confirm it is covered by the agreements and the customer's policies before the first call, not after.
Data contracts
When your system depends on a customer feed, agree a lightweight contract: the fields, types, refresh frequency, and who to contact when it breaks. Add automated checks (row counts, null rates, schema changes) that alert you before users notice bad output.
"You get access to the customer's data and it's a mess. What do you do in your first week?" Describe profiling, sampling real rows, documenting assumptions with the data owner, and choosing a narrow, clean-enough slice to prove value first.
Senior Full-Stack Engineer · AI & AWS. I turn ambiguous customer problems into production systems — cloud, data and AI.