Data Cleaning interview questions
Data Cleaning questions a Data analyst / BI can be asked, from first job to senior. Open one to see the strongest answer and why it works.
Revenue comes back NULL for a day with no sales. What do you put on the chart?
The strongest answer: I say whether that day is zero, missing, or closed — I do not silently COALESCE to 0 if the store was shut or the pipe broke
Why it works: Exactly. Zero, missing, and closed are three stories. Smooth lines hide outages.
- Zero ≠ missing ≠ closed.
- Say which you plotted.
- Do not COALESCE by habit.
- Do not drop days to smooth.
A supplier sends a CSV where the amount column arrives as text: thousands separators, a currency symbol on some rows, and negatives written in brackets.
How do you turn that column into numbers?
The strongest answer: Convert it explicitly — strip the symbol and the separators, turn bracketed values negative, cast — then reconcile the row count and the total, and keep the raw column so I can see what failed
Why it works: Exactly — an explicit conversion plus a reconcile is the difference between clean data and quiet loss.
- Convert, then reconcile.
- Strip symbols and separators first.
- Brackets usually mean negative.
- Never drop what will not parse.
A chart looks exciting and the row count doubled overnight. What do you do before you present?
The strongest answer: Check grain, duplicates, and the pipeline — a doubled table is a finding, not a win, until I know why
Why it works: Exactly. Quality first. Excitement is a hypothesis about a join or a late load.
- Check grain and duplicates.
- Check the pipeline.
- Do not present a mystery spike as a win.
- Do not smooth it away.
A table has no documented key. How do you avoid double-counting?
The strongest answer: I find what is unique in practice, write it down, and I do not assume the first column is the key
Why it works: Exactly. A key is a claim. DISTINCT * hides the smell and changes the grain.
- Find and write the uniqueness.
- Do not assume column one.
- DISTINCT * is not a key.
- Duplicates do not cancel.
SUM(amount) is twice finance’s number. What do you look for first?
The strongest answer: A join that duplicated the fact, or a pipeline that loaded twice — I do not “scale it by 0.5” to match
Why it works: Exactly. Doubles are grain or load bugs. Scaling to match is a red flag.
- Check fan-out and double loads.
- Do not scale to match.
- Compare definitions with finance.
- Rounding is not a 2×.
A country column contains US, U.S., usa with a trailing space, and United States.
How do you clean it?
The strongest answer: Trim and case-fold, then map through a lookup of raw value to canonical value that lives in the pipeline, with anything unmapped landing in a bucket that gets reported rather than silently dropped
Why it works: Right — a mapping table is reviewable, re-runnable, and tells you the day a new variant appears.
- Normalise, then map.
- Trim and case-fold first.
- Keep the mapping as data.
- Report the unmapped, do not hide it.
What is a minimum quality check before you publish a weekly number?
The strongest answer: Row counts versus last week, nulls on keys, and a reconcile to a known source — I do not only “look at the chart”
Why it works: Exactly. Counts, keys, reconcile. Slack pictures are not an archive.
- Counts, key nulls, reconcile.
- Do not eyeball only.
- Likes are not QA.
- Do not archive in Slack only.
Age is missing more often on the highest-value segment. What do you avoid?
The strongest answer: Dropping those rows and then describing “our users” — missingness is a segment, not a nuisance
Why it works: Exactly. Missingness can be the finding. Mean-fill and silent drops bias the story toward whoever filled the form.
- Treat missing as a segment.
- Do not drop and generalise.
- If you impute, say so.
- Do not invent a typical age.
What does a data-quality contract look like for an analyst’s weekly number?
The strongest answer: Expected grain, freshness, and a reconcile key — failing those pages someone, not a vibe that “it looked fine”
Why it works: Exactly. Few, owned, paged. Light-without-owner and rule dumps are furniture.
- Grain, freshness, reconcile.
- Page an owner on fail.
- Few rules, triaged.
- Emoji ≠ contract.
A pipeline started sending NULL for a country code instead of failing. What do you do to the weekly geo report?
The strongest answer: Treat NULL as a new segment, page the pipeline, and do not silently drop those rows from “the world”
Why it works: Exactly. NULL is a break. Pretty maps and US-impute are how you hide an outage.
- NULL is a segment and a page.
- Do not drop for prettiness.
- Do not impute a favourite country.
- Do not average over a break.
How do you treat a certified revenue table like a product?
The strongest answer: With SLOs: freshness, grain integrity, reconcile — owned, paged, the same way an API has latency
Why it works: Exactly. If it has users, it has an SLO. Hope and yearly rewrites are not operations.
- SLO: fresh, grain, reconcile.
- Named owner and pages.
- Hope is not an SLO.
- Do not annual-rewrite as ops.
Two customer records turn out to be the same company, and the business wants them merged before the quarterly numbers go out.
What do you agree before anything is merged?
The strongest answer: The match rule, the survivorship rule field by field — which source wins, or newest non-null — that both original ids are kept as aliases, and that the merge is recorded and reversible
Why it works: Exactly — matching and survivorship are separate decisions, and an unrecorded merge is a number you cannot explain later.
- Match rule and survivorship differ.
- Decide the winner per field.
- Keep both ids as aliases.
- Record it, and keep it reversible.
A Sev-1 decision used a wrong grain. How do you learn?
The strongest answer: Which contract would have failed, and one road change — no hunt for who pasted the SQL
Why it works: Exactly. Missing contract, one change. Hunts and oceans teach fear.
- Fail the contract next time.
- One owned road change.
- No last-paster hunt.
- No Friday ocean.