AI solution design · Databases and data
Cleaning up customer data
Find likely duplicates, standardise messy free-text fields and flag bad records, with a person approving every merge.
Talk about something like thisThe situation
A customer database has grown over fifteen years. The same company appears as "Smith & Sons Ltd", "Smith and Sons Limited" and "Smiths Ltd", addresses are typed three different ways, and a free-text "industry" column holds four hundred spellings of about thirty things. Mailings go out twice, and reports count the same customer more than once.
How it works
Step by step
- 1
Look before touching
Profile the data first: how many duplicates are there likely to be, which fields are messy, and what a correct record looks like.
- 2
Find likely duplicates
Combine ordinary matching rules (postcode, phone, company number) with text embeddings that recognise "Limited" and "Ltd" as the same, and score every pair.
- 3
Standardise free text
A model maps the four hundred spellings onto your agreed list of categories, flagging the ones it is not sure about.
- 4
A person decides
Pairs above a threshold are shown side by side with a suggested merge. Nothing is merged without approval.
- 5
Write back safely
Approved changes go through a stored procedure, with the old values kept and an audit trail, so any merge can be undone.
What it is built from
- SQL Server
- The customer database, plus staging tables for matches and decisions
- Azure OpenAI embeddings
- Measures how similar two records are
- Azure Functions
- Runs the matching on a schedule
- .NET and Angular review screen
- Side-by-side comparison and approval
- Power BI
- A data quality report that tracks improvement over time
Safeguards
- No automatic merges below a confidence threshold you choose
- Accuracy measured on a hand-checked sample before anything runs at scale
- Original values retained and every change logged, so merges can be reversed
- The model never gets direct write access to the database
What you end up with
- A cleaner customer database
- A review queue for the doubtful cases
- A standard category list applied across the data
- A recurring data quality report so it stays clean
Where to start
Run it read-only first and report what it finds. Most organisations are surprised by the number of duplicates, and it is a useful result before anything is changed.
More in this group
Ask your database
Managers ask a question in plain English and get a table from the SQL Server database, with the query and an explanation shown.
Read the design →Forecasting from your own history
Learn from years of orders or jobs to predict next month's workload, and publish the forecast back where your team already looks.
Read the design →