AI Data Cleaning Checklist: 20 Checks Before Analysis

Set the transformation contract before changing values

Define the downstream decision first. A complete daily-volume report, a limited import of validated records, and a training dataset have different release requirements. A partial subset might be acceptable for one task and misleading for another.

Record these six elements with the source snapshot. The original values must remain recoverable even when a normalized copy is easier to analyze.

The 20-check review worksheet provides a place to capture those decisions. The fictional source and decision packet supplies the assumptions behind the example below. Both are teaching aids; they do not constitute approval for a real dataset.

Twenty checks for an AI-assisted data-cleaning workflow

1. Preserve the received extract unchanged

Keep the original file and a stable source identifier before opening a working copy in a tool that may infer types. Record the export time, owner and location. If the file was regenerated, treat it as a different input unless you can demonstrate otherwise.

Preserving a filename alone is insufficient: two exports can have the same name but different contents. Keep the original payload and identify each candidate version separately. Corrections belong in a traceable copy, not over the only source.

2. Record how the extract was produced

Capture the query or export settings, upstream transformations and relevant time zone. A missing row may be an export-filter issue, not a cleaning defect. Ask whether the source is a full snapshot, an incremental update or a correction file.

When the extraction method is unknown, mark that uncertainty. Do not use an apparently plausible count to infer that the file is complete. A transformation cannot recover records that were never supplied without an additional authorized source.

3. Confirm the population and record grain

Write one sentence defining each row: one job, one job event, or one job line, for example. Also define the period and status conditions for inclusion. These definitions determine which keys must be unique and which repeated values are legitimate.

In the teaching case, a repeated delivery of the same export record is excluded under an explicit manifest rule. That is not the same as deleting every later occurrence of a job ID. Revisions and repeated activities need their own rules.

4. Validate columns against the field dictionary

Compare required column names, meanings and formats with the agreed contract. A column renamed from completed_units to quantity needs an explicit mapping, not an assumption that the names are interchangeable.

List new, absent and renamed fields before processing. If an upstream source changes from event quantity to cumulative quantity, the old sum may become meaningless even though every value remains numeric. Stop the affected output until the new definition is confirmed.

5. Preserve identifiers and validate numeric parsing

An identifier such as 0001 is a string when its leading zeros are significant. Parse quantities separately under the declared locale. In the example, the supplied dictionary explicitly makes 1,200 an English-US grouped integer meaning 1200; that interpretation should not be guessed for other sources.

Import configuration matters. pandas read_csv provides controls for types and missing-token interpretation. Our small local probe with pandas 3.0.1 found that the default read converted the illustrative ID 0001 to a number and the token NA to missing. Explicit string and missing-token controls preserved them in that probe. This is library behavior on two teaching rows, not an OpenMax benchmark or a universal import recipe.

6. Classify missing values before filling them

Separate empty, unknown, not applicable, not yet collected and redacted states. A valid source category may also resemble a conventional missing marker: the fixture's dictionary explicitly defines NA as a legitimate category.

Do not change every missing-looking token to a single null state without checking each field. Keep the raw token and a reason code for any normalized missing value. The decision that a value is absent is distinct from the later decision whether estimation is appropriate.

7. Apply conditional completeness requirements

Required fields often depend on workflow state. An open job may have no completion date; a completed job may require both date and quantity. Express that condition directly rather than counting all blanks as defects.

The example's completed record R5 has no quantity. It stays on hold. Filling it with zero would change “we do not know how much” into “we know nothing was completed.” A review queue should ask the source owner for the measurement, not ask AI for a plausible substitute.

8. Resolve key collisions with a documented precedence rule

Check uniqueness at the declared grain and retain all colliding records for comparison. If two versions conflict, define which source, version or effective time is authoritative. An arbitrary first or last row is not a business rule.

When excluding a confirmed repeated delivery, keep the excluded row's source reference and the retained row it duplicates. The raw extract still contains both. This makes the reduction in candidate rows explainable and avoids presenting deleted evidence as if it never arrived.

9. Treat similar entities as candidates, not established matches

Normalized names, addresses or contact fields can help identify possible duplicates, but similarity is not identity. Two people may share a name, and one organization may have several legitimate trading names.

OpenRefine's clustering documentation explicitly distinguishes syntactic clustering from semantically aware reconciliation. Use this boundary when reviewing suggested merges: show the matching evidence and conflicting fields, then obtain an authorized decision. Do not merge identities merely to reduce the number of distinct values.

10. Validate parent references and invalid keys

Check whether each foreign key—a field referencing a record in another table—resolves to an allowed parent, considering timing, inactive records and the relevant snapshot. Keep unmatched records separate from records with invalid or missing keys. An empty key is not an invitation to use a default person or account.

Before enrichment, define how null and empty-string keys are handled. If neither identifies a business entity, exclude them from the join input and retain them in an exception register. Otherwise a technically successful merge can attach unrelated information.

11. Normalize dates under an explicit format

Retain the source date text and specify the accepted formats, time zone where applicable, and cutoff rules. A date such as 03/04/2026 is ambiguous without a format declaration. A parser choosing a valid date does not resolve that business ambiguity.

In the fixture, ISO-formatted dates are accepted, but the slash-formatted R8 date remains on hold because no format is supplied for it. Do not select March 4 or April 3 simply because one interpretation places the row inside the desired reporting window.

12. Normalize measures without losing the original basis

Attach the unit to the value and record any conversion definition. Quantities in boxes, individual items and kilograms cannot be summed as if they were interchangeable. Percentages and decimal fractions also need an explicit scale.

Store the raw amount, normalized amount and conversion rule separately. For currencies or other date-dependent conversions, retain the applicable basis and source. If that basis is absent, mark the normalized amount unavailable rather than choosing a convenient rate or factor.

13. Map controlled categories through an approved crosswalk

A crosswalk is a table linking accepted source labels to their intended standard labels. Give it a version and define its scope. The fixture permits trimming and mapping Done to done; it provides no mapping for BETA.

Send unknown labels to an unmapped queue instead of assigning the nearest-looking category. Also test for collisions: if two source statuses have different business meanings, mapping both to one label may erase a distinction that downstream users need.

14. Inspect text normalization for changed meaning

Encoding repair, whitespace removal and Unicode normalization are different operations. Choose them according to the field definition and compare before/after values. Do not globally remove punctuation or accents from names and identifiers because a matching algorithm prefers simpler strings.

Python's Unicode documentation distinguishes canonical and compatibility normalization forms. Record which form a transformation uses rather than calling all text normalization equivalent. Preserve the original string, and use a separate matching representation if the simplified form is only a search aid.

15. Test ranges and relationships after transformation

Recheck quantities, intervals and relationships after conversion. A parsing rule can introduce an invalid amount even if the source passed a superficial string check. Record the allowed domain, effective rule and permitted exceptions.

For example, “quantity must be a nonnegative integer” is a testable rule when that is the actual field definition. It should not silently be applied to reversal movements or fractional measures. Fix the rule's scope before treating every violation as a bad record.

16. Investigate outliers without smoothing away real events

Compare observations with relevant peers and time windows. An unusual large delivery may be genuine, while an ordinary-looking value may belong to the wrong unit. Retain the reason for the flag and verify the source before changing the observation.

If an analytical method deliberately caps or excludes values, describe that as an analytical treatment, not discovery of the true value. Keep the untreated data and show sensitivity to the decision. Outlier handling should not be chosen solely because it produces a preferred result.

17. Keep imputation separate from observed facts

Imputation estimates missing values; it does not recover the original measurement. Document eligible fields, method, reference population, uncertainty and a flag distinguishing estimated from observed data. Some records should remain incomplete until the owner supplies a fact.

For modeling, fitted preprocessing introduces an additional issue. scikit-learn's guidance on leakage includes imputation among transformations that can leak test information. Fit such preprocessing on training data rather than learning from the combined training and test set. This model-specific step is not a reason to fill the entire operational source before deciding its downstream use.

18. Test join behavior as well as cardinality

Declare the intended relationship—one-to-one, one-to-many or many-to-one—then inspect unmatched keys and output changes. A cardinality check verifies a structural condition, not the business validity of every match.

pandas merge documentation warns that null keys on both sides can match. In our local two-row probe, a many-to-one check still allowed a null-key row to acquire an owner from a null-key reference. Removing invalid keys from both join inputs prevented that match while retaining the original record on hold. Test these semantics in the tool and version you actually use.

19. Reconcile exclusions, holds and usable records

Explain every source row's disposition. In the example, eight received rows become one repeated-delivery exclusion, three eligible candidate rows and four held rows. The partition accounts for all eight; it does not claim all eight are ready.

Reconcile known quantities separately from missing ones. The eligible subset totals 1,230 units. Held records contain 42 known units plus one unknown quantity. Calling 1,272 the full total would implicitly treat the missing quantity as zero, even though the arithmetic sum of available values is correct.

20. Release an identified version for an identified use

Package the candidate, rule versions, change log, exception register and reconciliation. State whether the output is complete or partial and which consumers may use it. Preserve a rollback reference and the link back to source records.

The example supports a three-record validated subset, not a complete dataset report. Release of that subset requires a downstream use that explicitly accepts its limitations. For a complete-volume report, the unresolved quantity, category, key and date remain hold points. Do not convert “these rows pass” into “the dataset is ready.”

Run the checklist as a five-stage review

1. Profile without changing the source. Record schema, row counts, key patterns, missing states and unresolved definitions. The deliverable is a baseline with evidence, not a cleaned file.
2. Write the proposed rules. For each change, specify the field, applicable records, source basis, expected effect and exception handling. Have the owner approve consequential or ambiguous transformations before they run.
3. Build a candidate and a row-level log. Preserve raw values, separate derived fields, and record exclusions and holds. AI explanations belong alongside evidence, not in place of it. Do not silently refresh the source during the run.
4. Reconcile and challenge the output. Repeat relevant checks after changes. Use known-good cases, intentional errors and valid exceptions. Inspect changes in counts, categories, totals and downstream meaning; do not rely only on the number of passing rules.
5. Approve the version and inspect the next refresh. Identify the use, recipient and unresolved dependencies. On the next delivery, compare schema and source definitions before replaying the same transformations. A previously approved rule may no longer fit a changed source.

Track defects introduced by cleaning as well as defects removed. If a new transformation increases apparent completeness by guessing values, the blank count improves while factual reliability may worsen. Define success through the intended decision and the evidence supporting it.

Eight source rows, three eligible records, four holds

This example is fictional. Its source dictionary defines four-character job IDs, a valid NA region token, English-US integer grouping, approved status mapping and date requirements. The export manifest explicitly identifies R3 as a repeated delivery of R2. Those facts are stipulated; they are not inferred by a model.

All unmentioned fields and the complete row payloads are specified in the source packet. The row partition is 8 = 1 + 3 + 4. After removing the repeated delivery, seven observations remain, but one lacks an ID; do not label that count “seven valid unique job IDs.”

The three eligible records contain 10 + 20 + 1200 = 1230 units. The held records contain 5 + 7 + 30 = 42 known units and one unknown amount. Before deduplication, the sum of available quantities is 1,292; excluding the repeated 20 gives 1,272. These are available-value subtotals, not evidence of a complete source total.

If you report an eligibility rate, name the denominator. Three eligible observations out of seven after deduplication is approximately 42.86%; three out of eight received rows is 37.5%. Neither is model accuracy. More importantly, neither percentage authorizes a complete-volume report while unresolved records still affect it.

Choose native rules, scripts or AI for different jobs

Manual or native table checks fit small, stable extracts and clear rules. They let an owner inspect individual changes with little setup. Their weakness is repeatability if the process lives only in someone's memory. Keep the crosswalk and exception decisions outside transient conversations.

Scripted transformations fit recurring structures where rules can be tested. Pin the relevant runtime, preserve input/output versions and include boundary cases. The two pandas probes here illustrate why checking actual parser and merge behavior is more informative than assuming every tool treats missing data the same way.

Interactive clustering tools fit candidate spelling groups that benefit from review. They can surface alternatives, but the reviewer must decide whether values share a meaning. Do not use a similarity threshold as the sole authorization to merge customer identities.

AI assistance is useful for drafting rule explanations, organizing exceptions and asking targeted questions about ambiguous fields. Require references to the supplied dictionary or source rows. When a rule is already explicit, use an executable check for the calculation; when meaning is unknown, obtain evidence rather than asking the model to sound more certain.

Where OpenMax can enter the review process

OpenMax describes its product as a human–agent collaboration platform. A cleaning review is therefore a relevant coordination scenario: an agent may help prepare an exception summary while the responsible owner determines the acceptable treatment. That positioning alone does not verify a native cleaning engine, a particular connector or lossless editing of a dataset.

For a proposed setup, ask for a demonstration of source identification, permitted access, row-level evidence, rule-version recording, owner decisions and separation of candidate from original. These are acceptance requirements, not verified promises about every OpenMax deployment.

As a next step, bring a sanitized sample, its field dictionary and the intended downstream use to the OpenMax team. Start by checking whether the proposed workflow preserves NA, leading-zero IDs and held records in the teaching case. Then test a representative authorized sample. A successful teaching exercise does not grant permission for production writes or establish real-world quality.

If a simple native rule already handles the work, keep it. The product earns a role when recurring handoffs, exceptions and multiple owners create a coordination problem that the existing procedure does not solve well.

Limits and decisions that still require people

Cleaning cannot establish the truth of an unreliable source by itself. It checks and transforms records under declared assumptions. Keep unavailable records, unknown definitions and unsupported formats visible in the release notes. A clean-looking subset can still misrepresent the population if exclusions are hidden.

Confirm the approved environment and handling rules before sharing personal, customer or otherwise restricted information with tools. A small sample is not automatically anonymized. Identity merges, sensitive-attribute inference and consequential decisions require the responsible domain and data-handling review; this article does not supply that approval.

Use the spreadsheet anomaly review guide when the question concerns formula references, hidden rows or workbook state. Use the PDF extraction validation workflow when source values were first obtained from documents. This checklist governs the subsequent transformation and release decisions, not those tools' entire behavior.

The practical finish line is not “all cells are filled.” It is an identified output whose changes, exclusions and remaining uncertainties are suitable for its stated use and can be traced back to the received source.

FAQ: AI data cleaning decisions

Should AI automatically remove duplicate records?

Only an explicit, appropriate rule can justify an exclusion or merge. A repeated delivery, a revised record and a second legitimate event are different situations. Preserve the raw records, identify the retained version and record the authority for the decision. Similarity alone does not establish identity.

Does a missing value become zero after cleaning?

Not unless the source definition explicitly establishes that meaning. Missing can mean unknown, not collected, redacted or not applicable. Preserve the original state and distinguish an observed zero from an estimated or unavailable value. A sum that skips missing values is not automatically the full total.

Can a validated many-to-one merge still attach the wrong information?

Yes. Cardinality is not a complete identity check. For example, the documented pandas null-key behavior can produce a match between missing keys even when the reference side is unique. Validate that keys are usable business identifiers, inspect unmatched cases and test the actual tool semantics.

What belongs in the cleaning log?

Record the source version, source row or field, rule ID/version, raw value, proposed and accepted treatment, evidence, owner decision, candidate version, validation result and rollback reference. Keep excluded and held records traceable rather than describing only the rows that survived.

Can we use the passing records while other rows are held?

Only if the downstream use explicitly accepts a clearly labeled partial subset. State what was excluded and what remains unknown. If the task requires a complete population or total, unresolved records affecting that result remain release blockers even when some rows pass every check.

Sources and editorial verification

Prepared by the OpenMax content team for an OpenMax-owned resource page. The relationship to the product is disclosed. The 20-check sequence and eight-row dataset are editorial teaching materials, not a customer case study or a detection-accuracy benchmark.

Primary references checked September 4, 2026:

• pandas read_csv: import types and missing-token controls.
• pandas merge: null-key behavior and relationship validation.
• OpenRefine clustering methods: syntactic candidate grouping versus semantic reconciliation.
• Python unicodedata: normalization forms and their distinctions.
• scikit-learn common pitfalls: fitted preprocessing and test-data leakage.
• OpenMax public product description: attributed product positioning, not proof of the proposed cleaning setup.

Two in-memory parsing and merge probes were executed with pandas 3.0.1; the live pandas reference pages inspected were labeled 3.0.5. The fixture's arithmetic was checked independently. These narrow checks do not test OpenMax, Excel, arbitrary datasets or all software versions. Real field meanings and release conditions must come from the responsible owner.

For corrections, identify the affected check, source record or application/version through the site's contact route. A changed upstream definition should trigger a review of the rule and its downstream consequences, not just a wording update in the article.