A clean-looking spreadsheet can still contain the wrong information. Data preparation needs explicit rules and a trail from the input to the result. AI can propose those rules and help write repeatable transformations, but the rules should be reviewed before they change important data.
Begin with a copy of an approved export, its source, its purpose and a description of what one row represents. Keep the original intact. Use sample or authorized data in the tool you choose. The output is a cleaned copy plus a transformation log and reconciliation, not an unexplained replacement of the source.
What you will leave with
A validated dataset, documented cleaning rules and a reproducible transformation log.
Step 1
Profile before changing
List columns, inferred types, row count, missing values, duplicate candidates and representative values. Identify the expected row grain and candidate keys. A duplicate-looking name does not prove two records are the same entity. Dates and numbers may be formatted differently across sources, so inspect examples before conversion.
Try this instruction
Profile this dataset without changing it. Report columns, types, rows, missing values, duplicate candidates and possible keys. Ask what one row represents. Propose rules for review rather than silently fixing values.
The profile identifies the grain, likely issues and unresolved questions.
Step 2
Agree on cleaning rules
For each issue, specify the rule, rationale and exception behavior. Decide whether a blank means unknown, not applicable or a real zero. Define date interpretation and timezone. Decide how duplicates are identified and which record wins. Ask the data owner about ambiguous business rules rather than letting the model guess.
Every transformation has an explicit rule and an owner for ambiguous cases.
Step 3
Make a reproducible transformation
Ask the agent to write formulas, a query or a script that creates a new output. Preserve stable identifiers and record excluded rows separately. Avoid destructive edits to the only copy. Inspect the code or transformation steps with someone who understands the data before running it against important sources.
The original is preserved and the transformation can be rerun.
Step 4
Reconcile and sample the result
Compare input and output row counts and important totals. Explain every dropped, merged or added record. Inspect samples from ordinary rows and exceptions. Test downstream joins with known expected match counts. A successful script exit only proves that the program ran, not that it preserved business meaning.
Counts, totals and exception samples match the intended rules.
Step 5
Hand off the data with its meaning
Package the cleaned output, rules, source date, unresolved issues and a data dictionary together. Tell the next analyst the grain and which fields may be aggregated. If the work feeds a recurring report, keep the transformation and tests with the workflow so the next export does not require improvisation.
Another analyst can explain and reproduce the cleaned dataset.
When you get stuck
All blanks became zero and the report changed dramatically.
Restore the original and distinguish unknown from zero. Recompute results after agreeing on the treatment.
A join multiplies totals.
Check key uniqueness and join cardinality. Reconcile before aggregating or interpreting the expanded table.
Terms explained
- Grain
- What one row represents, such as one order line or one campaign-day.
- Reconciliation
- Checking that counts and totals agree with the source or have an explained difference.
- Cardinality
- How many records on one side of a relationship can match records on the other.
Sources and further reading
The workflow and templates are teaching guidance. The sources below support the underlying tools and concepts; product setup and availability can change.
Take it into your next task
Your completion checklist
0 / 5 checked
Checks are saved on this browser. Mark them only after you inspect the evidence. These are your checks, not an automated certification.