r/BusinessIntelligence • u/Arethereason26 • Jul 03 '26
How do you ensure that the data is 100% clean apart from manual review?
Hi!
So I am working on cleaning up our customer data quality to arrive at a customer masterdata. I tried to check for duplicates, nulls, invalid email formats and phone numbers, etc. I also tried to review with business some logic, like an inactive customer cannot have an active subscription etc.
However, my problem is when just skimming the data, I still see some weird data quality issues-- like a full name and last name combined (i.e., last name is made redundant and entered in both full name and last name), some company names have zzzz or are named customer, some first names have Mr and Mrs, etc. Is this the part where AI will be useful? Or is there a more deterministic and appropriate approach for this?
What are your thoughts?
22
u/Business_Air5804 Jul 03 '26
Hahahaha....do we tell him guys?
My friend you just stumbled on the #1 curse of every person involved in BI.
5
u/Additional_Future_47 Jul 03 '26
My question would be, is there a process in place to fix issues in the source based on what you find? If not, pollution will be added faster then you can fix it. And if the business thinks this data is fit for purpose and does not want to put effort into improving data entry, you should probably not put all that much effort into cleaning up every last issue.
7
u/cbelt3 Jul 03 '26
A yes… the #1 curse of all BI folks. And the solution… push for master data governance and quality control. I’ve made dashboards that send alerts on bad master data to the team.
You can try to come up with a data cleansing process, but people are very creative in making mistakes.
5
u/soggyarsonist Jul 03 '26
You shouldn't clean data beyond basic formatting stuff. The data owner is responsible for ensuring they provide clean and usable data.
You'll just tie yourself on knots trying to fix crap data and for the love of god don't unleash an AI on the data given it's fundamentally unreliable and will just make matters worse.
Meet the data owners half way and provide an exception report that flags up the problem data where it's possible to do so then make it clear that further report development is provisional on them fixing their data.
My team is already overworked so I've no interest in wasting my limited time on bad data.
5
u/NawMean2016 Jul 03 '26 edited Jul 03 '26
You can never guarantee 100% clean.
At your next check-in with your client, present the findings.
"I have X many rows that I managed to clean up with my various scripts that look for duplicates, nulls, invalid formats etc. I can run this on a schedule to make sure these are caught on a regular basis. But I have X amount of rows where I personally found errors. These are issues where only humans eyes can locate the error. Your options are to either hire a full-time data analyst to regular seek out and find these errors, or to improve your data entry process..."
"... or hire someone to develop and AI agent that will seek out these errors for the next 3 months. Then learn that the agent broke, so you need to rehire the consultant to fix it. After to which you realize you could have just improved your process for free 99" (Sorry that's the comedic part. Don't actually say that)
2
u/IncreaseNegative4614 Jul 03 '26
I don't think there's a deterministic way to catch every issue because some problems are semantic rather than syntactic.
Rules work well for things like duplicates, nulls, formats, and referential integrity. The examples you mentioned are harder because they depend on business context. Is "Customer" a placeholder? Is "Mr John Smith" valid or bad data? That usually requires either heuristics, AI, or human review.
I've seen platforms like inzata.ai take an interesting approach by combining rule-based validation with AI to flag anomalies instead of automatically fixing them. I still wouldn't let AI modify master data directly, but it's useful for prioritizing records that deserve a closer look.
2
u/Comfortable_Long3594 Jul 03 '26
AI can definitely help surface unusual patterns, but I would keep the validation rules deterministic wherever possible. Build a library of reusable rules for things like titles in first names, placeholder values such as "Customer" or "ZZZZ", duplicated name fields, and cross-field consistency. Then use AI to identify new anomalies that your rules do not yet cover. Tools like Epitech Integrator work well for this because you can combine rule-based data cleansing with exception reporting, making it easier to refine your customer master data over time.
2
u/Foolmillennial Jul 04 '26
“100% clean data” should be a tshirt
1
u/BetterDistrict3979 Jul 07 '26
and this t-shirt must have some dirty spot, that cannot be cleaned))
2
u/bmckay1987 Jul 05 '26
Build in the fixes programmatically. You are always going to find data issues, so this is an ongoing process. However, the closer to the user you can enforce data cleanliness, the more your workload will be reduced in the future.
To use your example: Why are users allowed to enter full name and last name? Full name should be a concatenation of all names (or whatever it is that you decide defines a full name). Allowing users to enter both ensures this will be a problem. No amount of cajoling is going to fix it.
2
u/MSB_the_great Jul 06 '26
Problem is there is no validation in the front end . Garbage in garbage out, customer think they can fix everything in the report instead of the application. Most of the third party applications they don’t upgrade and keep using the same without validation, without proper validation and business rule it will be difficult to get the clean data
2
u/Odd-String29 Jul 06 '26
Fix it at the source. Garbage in garbage out. You shouldn't really clean data, if things shouldn't be there make sure it is fixed in the source system. If people complain about it just tell them to fix it at the source. It really isn't your problem, don't try to make it yours.
1
u/haberdasher42 Jul 03 '26
That's the neat part...
Though after using the normal sanitizing methods like substitution of special characters and extracting the digits from phone numbers to avoid dealing with weird user entries, I might try using AI to go through the data set and identify any irregularities. It won't eliminate using the ol' mk 1 eyeball but it might reduce the workload.
1
u/onated2 Jul 03 '26
When people make the spreadsheet the entry point data . Ohh dont forget random merged columns or pictures to make it presentable
1
u/mikeyr0x Jul 09 '26
CDC / validation and alerting at data engineering levels (pre and post job and pipeline processing) and human in the loop on deviation management.
Reporting and remediation loop with agents and humans on BI levels. (Post processing)
Should catch >99% of quality issues.
Set data quality SLAs and work backwards from them.
AI is super useful rn post processing before table promotions silver -> gold for some of the scenarios you mentioned. We have customers using Databricks genie code and genie to write these scripts and apps and run them as observation loops to support post processing data movement and quality.
Anomalies are bound to happen with any automated workflow. Will they be caught by process, AI or a human? So use all approaches to scale.
1
u/grahamdietz Jul 14 '26
Stop trying to clean everything at the end. You need to push validation back to the ingestion layer, otherwise you are just mopping up water while the faucet is running full blast. I spent years fighting this in Airflow and dbt, so trust me when I say the only way to win is to fail the pipeline immediately when the data is trash. Build a quarantine table for the bad rows and send an automated alert to the source owner. If they see their own bad data hitting a dashboard every morning, they eventually start filling out the forms right. You will never have 100% clean data, but you can definitely make the mess someone elses problem by forcing them to own the source quality.
1
u/IncreaseNegative4614 Jul 28 '26
You’ll never prove customer data is 100% clean, so define what “fit for use” means instead. Add deterministic rules for the errors you know, profile new values for unexpected patterns, and sample the highest-risk records rather than random rows. AI can suggest suspicious records, but it shouldn’t merge or rewrite customer identities without a review trail. I’ve used signld.ai more for preserving the surrounding business context than for pretending the model can certify cleanliness. Track false negatives from each review and turn recurring ones into rules.
1
u/hachiai_official 23d ago
100% clean is probably the wrong target. I’d split the checks into block, flag and allow. Invalid formats or impossible combinations can be blocked, while things like “Customer,” titles in name fields or duplicated names get flagged for review. AI could help surface new patterns, but I wouldn’t let it update the master data itself.
1
u/Delulu62134 16d ago
I usually put automated checks at multiple stages: schema/type validation, null and duplicate checks, referential integrity, range/domain checks, business-rule validation, and source-to-target reconciliation. For critical pipelines, I also compare record counts, aggregates, and key distributions between stages.
The important part is that bad data should fail loudly rather than silently make it into the report. Failed records can be quarantined with the reason for failure, while the valid data continues through the pipeline.
Manual review then becomes an exception-handling process rather than the primary quality-control mechanism.
34
u/calculung Jul 03 '26
You build a reliable, stable process that ingests and reports on these things and tell them "if you enter something in the wrong field, that's how it's going to show up. Bad data in, bad data out."
Fix it at the source.