This is the first stage of the data cleaning process, and it establishes most of the priors about the dataset.

In the data cleaning workflow, the user inputs:

1. An unclean data sample, as .csv or .json.
2. Optionally, descriptive tags for the dataset's domain, e.g. financial, sales or stock market data.

It is worth noting what we truly expect when digesting an "unclean" dataset. In other words, what makes data "unclean"?

"You never know unless you try - even if you are unsure, just pick one and go for it."

— Every maths professor's go-to line (aka my dilemma)

The process usually starts by treating each cleaning stage as a kind of hypothesis test—posing a null case and deciding whether to reject or accept it using the right method. Along the way, a few truths about unclean data show up almost every time:

• Data types are rarely consistent or correct.
• Missing values creep into most columns.
• Duplicated rows (and sometimes even whole columns) are hiding in plain sight.
• Anomalies and outliers are just as common as missing values.

Persistent Data Profiling Examples

Example of uncleaned dataset passed to agent.

Problem 1: inconsistent data type validations and labelling

LLMs generate inconsistent responses, so each data profile must be tested and validated before it reaches the next stage of the E2E pipeline.

LLM Inconsistencies

Problem Example:

Problem Description:

• To understand the severity of this problem, imagine a workspace with a high turnover rate—where workers are constantly leaving and new ones are joining. In such an environment, documentation and work are often tailored to individual users rather than built on a systematic framework.
• Similarly, an LLM cannot consistently produce the same output. For example, the 'integer' value above can randomly become 'int'.

Possible Solutions:

To keep this workflow consistent with my base practices (e.g. only ever output one unique value, 'integer', for integer data fields), I can either:

i.) Improve the input prompt.

ii.) Add class wrappers that run other lightweight LLMs as validators (or use BigQuery's AI.GENERATE_BOOL).

iii.) Find a hack that avoids prompting LLMs altogether.

Option i.) does not remove the stochasticity of the LLM's outputs, i.e. it isn't scalable, and option ii.) requires more experiments and test cases.

Problem 1: solution

An alternative to running a prompt agent over a dataset.

Objective

To validate data types, e.g. binary labels, ordinal labels, etc., for feature transformations.

Problem

• While applying BigQuery AI to a data column is efficient, its output is not consistent. It is the same problem as running language models with equipped tools or function decorators: many catalysts, but not enough time.
• This stage is important because you want to apply the correct data type transformations to progress the other stages without unwanted surprises.

Solution

• Use sentence-transformers with sample data representing each data type:
  • continuous
  • binary labels
  • ordinal labels
  • multi-label categories
  • short text e.g. names, emails
  • long text e.g. chat logs

Assuming there are no dependency or hierarchical relations, the formula takes the canonical cosine similarity form. Let t̂ be the target that maximises cosine similarity over a normalised set {qᵢ}:

t̂ = argmax_t  ⟨t, qᵢ⟩ / (‖t‖ · ‖qᵢ‖)

As pseudocode steps:

1. First, compute q̄ as the normalised set.
2. Then pick t̂ as the target with the highest cosine similarity to q̄.

The data samples representing each data column label are stored in core/data/sample/*.csv, along with the other assumptions used to configure the agent.