Enhancing Dirty Data Detection via Large Language Models: Comparative Insights from ChatGPT, Gemini, and Copilot

Abstract

Background: Dirty data, such as inconsistent categorical encodings, implausible values, duplicated records, and date/format errors, can compromise statistical inference and downstream decision-making. To support scalable data quality screening, LLMs are increasingly considered for data preparation tasks.

Methods: A tabular main dataset containing categorical and numerical variables was generated via stepwise prompting with ChatGPT and subsequently reviewed by domain experts. Seven representative data quality issues were injected (e.g., heterogeneous gender string variants, extreme BMI values due to decimal misplacement, gender–surgery incompatibilities, duplicates, admission date inconsistencies, character substitution [O↔0], and chronological/format errors). Using standardized prompts, the same datasets were uploaded to ChatGPT (v5.0), Gemini, and Copilot; queries were repeated across multiple attempts, and results were compared across models.

Results: Performance varied by error type. ChatGPT and Gemini struggled to consolidate semantically equivalent but differently written gender labels and produced incorrect frequency estimates, whereas Copilot identified more variants but still miscalculated proportions. All three models flagged extreme BMI outliers; Copilot, however, showed calculation errors in descriptive summaries. In detecting clinical plausibility constraints (e.g., gender–surgery mismatches) and structurally empty fields, models commonly misclassified empty cells as missing and, in some scenarios, generated hallucinated outputs. For duplicated records, ChatGPT and Gemini reliably detected duplicates but missed gender–surgery inconsistencies; Copilot’s duplicate detection improved across attempts, and it more consistently captured gender-related mismatches. ChatGPT best identified admission date inconsistencies, while Gemini and Copilot failed to detect these errors. For character substitution, Gemini and Copilot detected the issue while ChatGPT did not. For chronological and date format errors, Copilot outperformed ChatGPT and Gemini.

Conclusions: LLMs can assist with rapid screening for certain data quality issues (notably outlier detection and some format-level anomalies), but they demonstrate inconsistent reliability for semantic normalization, rule-based clinical plausibility checks, and the interpretation of missingness. Human-in-the-loop verification and complementary deterministic validation rules remain essential when LLMs are used in data cleaning workflows.