You can test the feature described in this article on your own dataset using the CleanMyExcel.io<\/a> service, which is free and requires no registration.<\/p>\n Data validity refers to data conformity to expected formats, types, and value ranges. This standardisation within a single column ensures the uniformity of data according to implicit or explicit requirements.<\/p>\n Common issues related to data validity include:<\/p>\n And the list goes on.<\/p>\n Error types such as duplicated records or entities<\/strong> and missing values<\/strong> do not fall into this category.<\/p>\n But what is the typical strategy to identifying such data validity issues?\u00a0<\/p>\n Data cleaning, while it can be very complex, can generally be broken down into two key phases:<\/p>\n 1. Detecting data errors\u00a0\u00a0<\/p>\n 2. Correcting these errors.<\/p>\n At its core, data cleaning revolves around identifying and resolving discrepancies in datasets\u2014specifically, values that violate predefined constraints, which are from expectations about the data..<\/p>\n It\u2019s important to acknowledge a fundamental fact: it\u2019s almost impossible, in real-world scenarios, to be exhaustive in identifying all potential data errors\u2014the sources of data issues are virtually infinite, ranging from human input mistakes to system failures\u2014and thus impossible to predict entirely. However, what we can<\/em> do is define what we consider reasonably regular patterns in our data, known as data expectations\u2014reasonable assumptions about what \u201ccorrect\u201d data should look like. For example:<\/p>\n By establishing these expectations, we create a structured framework for detecting anomalies, making the data cleaning process both manageable and scalable.<\/p>\n These expectations are derived from both semantic and statistical analysis. We understand that the column name \u201cage\u201d refers to the well-known concept of time spent living. Other column names may be drawn from the lexical field of high school, and column statistics (e.g. minimum, maximum, mean, etc.) offer insights into the distribution and range of values. Taken together, this information helps determine our expectations for that column:<\/p>\n Expectations tend to be as accurate as the time spent analysing the dataset. Naturally, if a dataset is used regularly by a team daily, the likelihood of discovering subtle data issues \u2014 and therefore refining expectations \u2014 increases significantly. That said, even simple expectations are rarely checked systematically in most environments, often due to time constraints or simply because it\u2019s not the most enjoyable or high-priority task on the to-do list.<\/p>\n Once we\u2019ve defined our expectations, the next step is to check whether the data actually meets them. This means applying data constraints and looking for violations. For each expectation, one or more constraints can be defined. These Data Quality<\/a> rules can be translated into programmatic functions that return a binary decision \u2014 a Boolean value indicating whether a given value violates the tested constraint.<\/p>\n This strategy is commonly implemented in many data quality management tools, which offer ways to detect all data errors in a dataset based on the defined constraints. An iterative process then begins to address each issue until all expectations are satisfied \u2014 i.e. no violations remain.<\/p>\n This strategy may seem straightforward and easy to implement in theory. However, that\u2019s often not what we see in practice \u2014 data quality remains a major challenge and a time-consuming task in many organisations.<\/p>\n This validation workflow is split into two main components: the validation of column data types and the compliance with expectations.<\/p>\n One might handle both simultaneously, but in our experiments, properly converting each column\u2019s values in a data frame beforehand is a crucial preliminary step. It facilitates data cleaning by breaking down the entire process into a series of sequential actions, which improves performance, comprehension, and maintainability. This strategy is, of course, somewhat subjective, but it tends to avoid dealing with all data quality issues at once wherever possible.<\/p>\n To illustrate and understand each step of the whole process, we\u2019ll consider this generated example:<\/p>\n Examples of data validity issues are spread across the table. Each row intentionally embeds one or more issues:<\/p>\n The task here is to determine the most appropriate data type for each column in a data frame, based on the column\u2019s semantic meaning and statistical properties. The classification is limited to the following options: string, int, float, datetime, and boolean. These categories are generic enough to cover most data types commonly encountered.<\/p>\n There are multiple ways to perform this classification, including deterministic approaches. The method chosen here leverages a large language model (Llm<\/a>), prompted with information about each column and the overall data frame context to guide its decision:<\/p>\n Example<\/em>:<\/p>\n\n
What is Data Validity?<\/h2>\n
\n
When data meets expectations<\/h2>\n
\n
\n
An LLM-based workflow to generate data expectations, detect violations, and resolve them<\/h2>\n
![\"\"](\"https:\/\/lh7-rt.googleusercontent.com\/docsz\/AD_4nXcbmlZVTjnXMlPtEaASw5SoQvLywnpCiBExtnE9pN45Ak-6gmsGzdjRo7Q9xdJdV2aOtK_4IzKZEI3cXEc8SwNuGawU96vSigGikFD2fu_B-apSShpe12hON0niWRiolLpSjqeJ?key=lABtwTjQ29DDn4nC3kBCGRmV\")
![\"\"](\"https:\/\/lh7-rt.googleusercontent.com\/docsz\/AD_4nXeYpouorauXeJOAvIfWyvphJT3znXGnE3phvilOjpOo0be2Q0EVw0dUbelPo32h_vmiBCvPNFwI8uGkw5ESwre9Lnyyl3zrkZsZ5tBEiQgEsXH3Q221K3behCFNaMBUkQ6L2QPI?key=lABtwTjQ29DDn4nC3kBCGRmV\")
\n
Validate Column Data Types<\/h3>\n
Estimate column data types<\/h4>\n
\n
![\"\"](\"https:\/\/lh7-rt.googleusercontent.com\/docsz\/AD_4nXeimHu7_ZOMOLJy2Plhtqo3W6rQcy8m4WgLEK8FqLGVdtVA77VSeVK_p1hR8CzdyQe81hi2vORUZDSU42vEmZD4KPfnWcCWRbTAJx47tK6ZK_9_z5WvYVpCzcsIHY3DIrk07tHUqg?key=lABtwTjQ29DDn4nC3kBCGRmV\")
<\/figure>\n