82 Data Cleaning Interview Questions and Answers (2026)

Data cleaning is the process of detecting and correcting (or removing) errors, inconsistencies, and inaccuracies in a dataset so it faithfully represents reality. It is critical because models and analyses inherit every flaw in their input: garbage in, garbage out.

• What it covers:

  • Handling missing values, duplicates, outliers, wrong types, inconsistent formats, and structural errors.

  • Standardizing units, categories, and encodings so records are comparable.

• Why it is critical:

  • Model quality: dirty features degrade accuracy and can create silent bias.

  • Trust: decisions made on bad data are wrong even if the analysis is correct.

  • Cost: errors caught late (in production) are far more expensive than at ingestion.

• Effort reality: it typically consumes the majority of a data project's time, and skipping it rarely saves time overall.

They overlap but differ in scope: cleaning fixes correctness, transformation reshapes/re-encodes for use, and wrangling is the umbrella process that includes both plus gathering and structuring data.

• Data cleaning: Goal is correctness: remove duplicates, fix types, handle nulls and outliers, standardize values.

• Data transformation:

  • Goal is fitness for use: scaling, encoding, aggregating, deriving features, pivoting, joining.

  • Data can already be clean and still need transformation.

• Data wrangling (a.k.a. munging): The broad, iterative end-to-end process of getting raw data into usable shape: it encompasses acquisition, cleaning, and transformation.

• Rule of thumb: cleaning makes it correct, transformation makes it usable, wrangling is the whole journey.

Data wrangling (also called munging) is the iterative process of transforming raw, messy data into a clean, structured format ready for analysis. It is critical because most real-world data arrives incomplete, inconsistent, and in the wrong shape for the questions being asked.

• What it involves:

  • Discovering, structuring, cleaning, enriching, and validating data from one or more sources.

  • Joining and reshaping datasets into a single coherent analysis table.

• Why it is critical:

  • Analysis and models can't run directly on raw source formats; wrangling makes data consumable.

  • It surfaces data quality problems early, before they corrupt conclusions.

  • It is where much of a project's time and interpretive judgment actually goes.

• Iterative by nature: insights during analysis often send you back to re-wrangle.

Data preprocessing is the full set of steps that convert raw data into a form suitable for modeling; data cleaning is the foundational subset within it that fixes data quality issues before any feature work begins.

• Preprocessing scope: Cleaning, plus transformation (scaling, encoding), feature engineering, and data reduction/splitting.

• Cleaning's role:

  • Comes first: handle missing values, duplicates, outliers, and inconsistent types so later steps operate on trustworthy data.

  • A bad clean propagates: e.g. scaling with unremoved outliers distorts every feature.

• Relationship: All cleaning is preprocessing, but not all preprocessing is cleaning (encoding a clean categorical is preprocessing, not cleaning).

Data transformation converts data from one format, structure, or representation into another to make it usable for analysis or modeling; cleaning instead fixes correctness problems. Transformation assumes the data is right and reshapes it; cleaning makes it right in the first place.

• What transformation includes: Scaling/normalization, encoding categoricals, aggregating, pivoting/reshaping, deriving new features, joining sources.

• How it differs from cleaning:

  • Cleaning targets errors (nulls, duplicates, wrong types); transformation targets representation and structure.

  • Data can be perfectly clean and still require heavy transformation for a given model.

• Ordering: Usually clean first, then transform: transforming dirty data (e.g. one-hot encoding a misspelled category) just propagates the error.

• Overlap: both are part of preprocessing/wrangling and the line can blur (e.g. standardizing date formats is arguably both).

Cleaning dominates the timeline because real-world data is messy, heterogeneous, and undocumented, and every issue must be discovered, understood, and corrected without breaking valid records. It is iterative and largely manual investigation, not a one-time script.

• Data arrives dirty and varied:

  • Multiple sources with different schemas, encodings, and conventions must be reconciled.

  • Missing values, duplicates, typos, and outliers each need a tailored decision.

• Diagnosis is exploratory:

  • You must first find problems (profiling, spot-checks) before fixing them.

  • Root causes are often ambiguous and require domain expertise to interpret.

• Decisions have consequences:

  • Imputing or dropping records can bias results, so choices need validation.

  • Edge cases multiply: a rule that fixes one segment may corrupt another.

• It is iterative and repeated:

  • New data batches reintroduce issues, so pipelines need maintenance.

  • Poor upstream documentation forces slow reverse-engineering of meaning.

Data quality is measured across several dimensions that together tell you whether data can be trusted for a decision. Each targets a different failure mode, so a dataset can pass one and fail another.

• Accuracy: Does the value match reality? Wrong values silently mislead analysis even when everything else looks fine.

• Completeness: Are all required values/records present? Missing data biases aggregates and breaks models.

• Consistency: Do values agree across systems and fields (no contradictions)? Ensures the same entity means the same thing everywhere.

• Validity: Does the value conform to its rules (type, range, format, allowed set)? Guards structural correctness.

• Uniqueness: No unintended duplicates. Duplicates inflate counts and double-weight records.

• Timeliness: Is the data current enough for its use? Stale data drives decisions on a world that no longer exists.

• Why they matter together: they are orthogonal, and downstream trust requires all the relevant ones, not just one.

Real-world datasets rarely arrive clean; common issues map closely to the quality dimensions they violate and usually stem from human entry, integration of multiple sources, or system changes over time.

• Missing values: Nulls, blanks, or placeholders like -1, 999, or "N/A" masquerading as data.

• Duplicates: Exact copies or near-duplicates (same customer under slightly different names).

• Inconsistent formats: Mixed date formats, unit mismatches (kg vs lb), casing, and encoding issues.

• Invalid or out-of-range values: Negative ages, future birthdates, malformed emails, categories outside the allowed set.

• Outliers and errors: Data-entry typos, sensor spikes, or genuine extremes that need investigation.

• Structural problems: Wrong data types, multiple values in one column, inconsistent schemas across sources.

• Semantic/consistency conflicts: Contradictory records (order date after ship date), or same code meaning different things across systems.

Poor data quality propagates: flawed inputs produce flawed metrics, which drive wrong decisions and erode trust. The damage is often invisible until a decision based on the bad data fails, making it both costly and hard to trace.

• Distorted KPIs: Duplicates inflate counts; missing data understates totals; both misstate revenue, churn, or conversion.

• Misleading insights: Biased or inaccurate data yields wrong correlations and models that fail in production (garbage in, garbage out).

• Bad decisions: Budget, inventory, or strategy choices made on wrong numbers cause real financial loss.

• Operational and compliance cost: Wasted time reconciling reports, failed regulatory reporting, and rework.

• Loss of trust: Once stakeholders distrust a dashboard, they ignore data entirely, undermining a data-driven culture.

Missing data arises whenever a value is never collected, lost, or invalidated along the pipeline, and it can be represented in many forms beyond an obvious null.

• Common causes:

  • Human entry: skipped survey questions, optional form fields, refusal to answer.

  • System/technical: sensor failures, timeouts, dropped API responses, ETL join mismatches.

  • Structural: a field that legitimately doesn't apply (e.g. spouse name for a single person).

  • Merging data: records present in one source but not another produce nulls after a join.

• Common representations:

  • True nulls: NULL, NaN, None, NA.

  • Sentinel/placeholder values: -999, 0, 9999, far-future dates.

  • Text stand-ins: "N/A", "unknown", "-", "none", or an empty string "".

• Implication: always distinguish "not collected" from "collected as zero/empty" before analysis.

In SQL, missing values are represented by NULL, which behaves as "unknown" under three-valued logic, so you handle it by detecting it explicitly, substituting defaults, or filtering it out.

• Detecting NULLs: Use IS NULL / IS NOT NULL; comparisons like = NULL always return unknown, never true.

• Substituting values: COALESCE(col, fallback) returns the first non-null; NULLIF does the reverse (turn a sentinel into NULL).

• Aggregation semantics: Aggregates like AVG and SUM ignore NULLs, and COUNT(col) skips them while COUNT(*) counts rows.

• Filtering and joins: WHERE drops rows where the predicate isn't true, silently excluding NULLs; NULLs don't match in joins, which can drop rows unexpectedly.

• Imputing across rows: Window functions like LAG/LEAD can carry values forward for time-ordered fills.

Inconsistent values are the same real-world entity written many ways; you find them by profiling distinct values and frequencies, then standardize with normalization rules and fuzzy matching so each concept maps to one canonical form.

• Identify:

  • List distinct values and counts (GROUP BY or value_counts()) to spot near-duplicates and rare oddities.

  • Sort alphabetically so "USA", "U.S.A.", "usa" cluster together.

• Normalize the easy cases:

  • Trim whitespace (strip()), collapse internal spaces, and unify case (lower()).

  • Standardize punctuation, accents, and encodings.

• Reconcile categorical variants:

  • Map synonyms to a canonical label with a lookup/dictionary ("NY", "N.Y." -> "New York").

  • Use fuzzy matching (Levenshtein distance) to catch typos, then confirm before auto-merging.

• Guardrails:

  • Validate against a controlled vocabulary or reference table going forward.

  • Don't over-merge distinct things ("St." as Saint vs Street depends on context).

A true duplicate is more than one record representing the same real-world entity or event that should exist only once; legitimately repeated data is multiple valid records that happen to share values but represent distinct real occurrences. The distinction is about what the row means, not whether the values look identical.

• True duplicate:

  • Same entity accidentally recorded twice (double form submission, re-import).

  • Should be merged or removed.

• Legitimately repeated data:

  • Distinct real events that coincidentally share attribute values.

  • Removing them would lose real information.

• Example:

  • A customer buys the same $3 coffee twice on the same day: two identical-looking transaction rows, but they are two real sales (legitimate repetition), not a duplicate.

  • Contrast: the same order written to the database twice due to a retry is a true duplicate.

• How to tell them apart: Look for a distinguishing field (a unique transaction ID or precise timestamp) and use domain knowledge about whether the event can legitimately recur.

Parsing converts raw strings into typed values by matching them against an expected format and rejecting or flagging what doesn't fit: the challenge is that real-world strings carry inconsistent formats, locale differences, and hidden noise.

• Numbers:

  • Strip thousands separators, currency symbols, and whitespace; handle locale decimal commas (1.234,56).

  • Watch for percentages, parentheses for negatives, and stray text.

• Dates:

  • Specify an explicit format rather than guessing; day/month order and 2-digit years are ambiguous.

  • Account for time zones, and Excel serial-number dates.

• Booleans: Map a controlled set of tokens ("Y"/"N", "true"/"1", "t") to real booleans; decide how to treat blanks and unknowns.

• Cross-cutting challenges:

  • Distinguish NULL, empty string, and placeholder values like "N/A".

  • Decide policy for unparseable values: coerce to NULL, quarantine, or fail loudly.

Treat the raw source as read-only: always write cleaned results to a new object, layer, or file, and never overwrite the original.

• Read-only source layer: Ingest raw data into an immutable landing zone (bronze layer) with restricted write permissions.

• Copy, don't edit in place: In pandas, use df.copy() and assign transformations to new variables rather than mutating; be wary of chained-indexing that edits silently.

• Functional style: Write pure functions that take a frame and return a new frame, avoiding inplace=True.

• Separate output paths: Persist cleaned data to a distinct path/table (silver/gold) so the pipeline can always re-derive from raw.

"Garbage in, garbage out" (GIGO) means the quality of any analysis or model is capped by the quality of its input data: flawed inputs produce flawed outputs no matter how sophisticated the method.

• The core idea: Algorithms don't fix bad data; they faithfully learn and amplify whatever patterns (and errors) exist in it.

• Downstream impact:

  • Biased or mislabeled data leads to biased predictions; noisy features reduce accuracy and stability.

  • Errors compound: a wrong value early in a pipeline propagates into aggregates, joins, and reports.

• Why it's deceptive: A model still outputs confident numbers on garbage input, so failures are silent unless you validate the data itself.

• The takeaway: Invest in profiling, validation, and cleaning upfront; it usually yields more improvement than model tuning.

Structured data fits a predefined schema of rows and columns (databases, spreadsheets); unstructured data has no fixed model (text, images, audio). This distinction fundamentally changes what "cleaning" even means.

• Structured data:

  • Organized in tables with typed fields; queryable with SQL.

  • Cleaning is field-level: fix types, ranges, duplicates, missing values, and format consistency.

• Unstructured data:

  • Free-form content with no columns; meaning must be extracted.

  • Cleaning is preprocessing: tokenization, lowercasing, removing noise/HTML, deduplication, and often converting to features/embeddings.

• Semi-structured note: JSON/XML/logs have tags but flexible shape, so cleaning includes parsing and flattening nested fields.

• Impact on process: Structured cleaning is rule-driven and easily validated against a schema; unstructured cleaning is heuristic, harder to verify, and more dependent on the target task.

A typical workflow moves from understanding the raw data, through profiling and issue detection, to systematic fixes and validation, ending in a documented, analysis-ready dataset.

1. Ingest and inspect: Load the raw data and look at shape, types, and a sample of rows.

2. Profile / audit: Compute summary stats, null counts, unique values, and distributions to surface anomalies.

3. Resolve structural issues: Fix column names, types, parsing errors, and reshape (wide/long) as needed.

4. Handle missing values, duplicates, and outliers: Impute, drop, or flag based on context and downstream needs.

5. Standardize and validate: Normalize formats/units/categories, then assert rules (ranges, uniqueness, referential integrity).

6. Document and version: Record decisions and save the clean dataset plus the cleaning script so it is reproducible.

A scripted pipeline is reproducible, auditable, and rerunnable: manual edits in a spreadsheet are invisible, error-prone, and impossible to reliably repeat on new or updated data.

• Reproducibility: The same input always yields the same output, and anyone can rerun it when raw data refreshes.

• Auditability: Code (in version control) documents exactly what changed and why; manual edits leave no trail.

• Scale and consistency: A script applies identical logic to millions of rows and to future batches; hand edits don't scale.

• Error reduction: Manual copy/paste and ad hoc fixes silently introduce new errors that are hard to trace.

• Testability: Scripted steps can be unit-tested and validated with assertions; one-off edits cannot.

Data cleaning sits after extraction and typically before or during the transform stage, once raw data has landed but before it feeds analytics or models. Its exact position differs between ETL and ELT.

• In ETL: Cleaning happens in the transform step, before data is loaded into the warehouse: only clean data lands.

• In ELT: Raw data is loaded first, then cleaned/transformed inside the warehouse (e.g. staging to curated layers).

• In the broader lifecycle:

  • Sits between collection/ingestion and analysis/modeling; some validation also runs at ingestion.

  • Not strictly one-time: monitoring and re-cleaning recur as new data flows in.

• Common pattern: raw (bronze) to cleaned (silver) to business-ready (gold) layers.

Data transformation reshapes data into a consistent, usable form (converting types, scaling, encoding, standardizing units) so it is clean and analysis-ready; feature engineering goes further by creating new predictive signals for a model. The line is intent: transformation makes data correct and uniform, feature engineering makes it more informative.

• Data transformation (part of cleaning):

  • Normalizing formats: dates to ISO, currencies to one unit, casing consistency.

  • Type conversion and structural reshaping: strings to numbers, pivoting, aggregation.

  • Goal: represent the same information correctly and consistently, without adding new meaning.

• Feature engineering (part of modeling prep):

  • Derives new variables: ratios, interaction terms, extracting day-of-week from a timestamp, text embeddings.

  • Goal: increase predictive/analytical value, guided by domain knowledge and the target.

• Overlap and distinction:

  • Some steps (encoding categoricals, scaling) sit in both: cleaning cares that they are valid, engineering cares that they help the model.

  • Rule of thumb: transformation fixes representation; feature engineering adds information.

You define data quality rules by combining domain knowledge, downstream requirements, and profiling of the actual data, then expressing each expectation as a testable rule with an acceptable threshold rather than demanding perfection. The goal is fitness for purpose, not absolute cleanliness.

• Start from purpose and stakeholders:

  • Ask what decisions the data feeds and what error the business can tolerate.

  • Rules for a billing table are stricter than for an exploratory dashboard.

• Profile the data first: Measure current null rates, ranges, distributions, and duplicate levels to set realistic baselines.

• Express rules per dimension:

  • Validity: allowed types, ranges, regex, enumerations.

  • Completeness: max % nulls per critical column.

  • Uniqueness: key columns must be distinct.

  • Timeliness: data no older than X hours.

• Set thresholds, not absolutes: e.g. warn at 2% nulls, fail the pipeline at 5%; tune to cost of false alarms vs missed errors.

• Automate and monitor: Encode rules as tests (tools like Great Expectations) that run on each load, and revisit thresholds as data evolves.

Data quality management (DQM) is the ongoing discipline of defining, measuring, monitoring, and improving data quality so data stays trustworthy over its lifecycle. It is a continuous process and governance practice, not a one-off cleaning task.

• Core activities:

  • Profiling and measuring against quality dimensions.

  • Defining rules, standards, and ownership (data stewards).

  • Cleansing, monitoring, and alerting on regressions.

  • Root-cause fixes at the source, not just downstream patches.

• Why it matters:

  • Trustworthy data underpins reliable analytics, ML, and reporting.

  • Prevents costly errors, compliance failures, and rework.

  • Cheaper to prevent bad data upstream than to repeatedly clean it downstream.

• Continuous, not one-time: new data and changing systems constantly reintroduce issues, so DQM is a feedback loop.

Validity means a value conforms to its defined rules (type, format, range, allowed set); accuracy means it correctly reflects the real-world truth. Validity is checkable against a schema alone, while accuracy requires comparison to reality, which is why a value can be perfectly valid yet completely wrong.

• Validity: conforms to rules:

  • A date in YYYY-MM-DD, an email matching a regex, an age between 0 and 120.

  • Verifiable without knowing the true value.

• Accuracy: matches reality: Requires a trusted reference or ground truth to confirm.

• Why valid but inaccurate happens:

  • A customer's birthdate 1990-01-01 is valid format and range, but if the person was born in 1985 it is inaccurate.

  • A typo'd but well-formed value passes validation yet misrepresents the truth.

• Practical takeaway: validation catches structural errors cheaply, but accuracy needs cross-checks, source verification, or reconciliation.

The three mechanisms describe why data is missing, which determines whether a handling strategy is safe or biased. Understanding them tells you whether the missingness is ignorable (MCAR/MAR) or must be modeled (MNAR).

• MCAR (Missing Completely At Random):

  • Missingness is independent of both observed and unobserved values (e.g. a sensor drops a reading randomly).

  • Safest case: deletion or simple imputation won't bias results, only reduce power.

• MAR (Missing At Random):

  • Missingness depends on other observed variables, not the missing value itself (e.g. income missing more often for younger people, where age is recorded).

  • Correctable: model-based imputation using the observed predictors recovers unbiased estimates.

• MNAR (Missing Not At Random):

  • Missingness depends on the unobserved value itself (e.g. high earners refuse to report income).

  • Hardest: cannot be fixed from the data alone; needs domain assumptions or explicit modeling of the missingness mechanism.

• Why it matters before choosing a strategy:

  • The same method (e.g. mean imputation) is harmless under MCAR but biases MNAR badly.

  • The mechanism is often untestable directly; you infer it from domain knowledge and patterns, and choose the most defensible assumption.

NULL traps are the subtle ways null values break logic, aggregations, and joins because NULL means "unknown," not a value, and follows three-valued logic.

• Three-valued logic:

  • Comparisons with NULL return UNKNOWN, not true/false: NULL = NULL is not true; use IS NULL.

  • A WHERE clause silently drops rows that evaluate to UNKNOWN.

• Aggregation surprises:

  • Most aggregates ignore nulls: AVG divides by non-null count, so nulls skew the mean differently than zeros would.

  • COUNT(col) excludes nulls while COUNT(*) includes them.

• Joins and set operations: Nulls in join keys don't match, dropping rows; but NOT IN with a null in the list returns no rows unexpectedly.

• Propagation and coercion:

  • Arithmetic/concatenation with NULL yields NULL, poisoning derived columns.

  • Loading into pandas can upcast integer columns to float to hold NaN.

Disguised missing values look like real data, so you detect them by profiling each column's distribution and value set, then testing suspicious sentinels against domain rules.

• Frequency and value inspection:

  • Run value_counts() per column to spot unnatural spikes (e.g. thousands of exactly -999 or 0).

  • List distinct strings to catch "N/A", "na", "unknown", "", varying case and whitespace.

• Distribution/range checks:

  • Sentinels appear as outliers far outside plausible ranges (a min of -999 in a positive-only field).

  • A histogram with an isolated bar at a round number signals a placeholder.

• Domain plausibility: Distinguish real zero from placeholder zero: 0 income may be missing, but 0 purchases is genuine, so context decides.

• Fix: Convert confirmed disguises to true nulls before handling.

Strategies range from deletion, to simple statistical imputation, to model-based imputation; the choice depends on the missingness mechanism, how much is missing, and the data type/distribution.

• Deletion:

  • Listwise (drop rows) or column drop: fine when data is MCAR and the missing fraction is small.

  • Risks discarding information and biasing results if not MCAR.

• Simple imputation:

  • Mean: only for roughly symmetric numeric data; sensitive to outliers.

  • Median: preferred for skewed data or with outliers (robust).

  • Mode / constant: for categorical values, or a "Missing" category to preserve the signal.

• Model-based / advanced:

  • KNN or regression imputation uses other columns: good under MAR.

  • Multiple imputation (MICE) creates several imputed datasets to reflect uncertainty, giving valid standard errors.

• Add a missingness indicator: A flag column marking "was missing" lets a model learn from the fact of missingness (useful for MNAR).

• Choosing:

  • Small % + MCAR: deletion is acceptable and simplest.

  • MAR with predictive columns: model-based imputation.

  • Inference where uncertainty matters: multiple imputation over single-value fills.

Delete when the missing data is small, random (MCAR), or so pervasive in a column that it carries little signal; impute when the data is valuable, the missing share is moderate, or deletion would bias or shrink the sample too much.

• When deletion makes sense:

  • Row deletion: few affected rows and MCAR, so the remaining sample stays representative.

  • Column deletion: a feature is mostly missing (e.g. >60-70%) or irrelevant, so imputing would be mostly guesswork.

• When imputation makes sense:

  • Data is scarce and every row matters, or the column is predictive.

  • Missingness is MAR and recoverable from other variables.

• Trade-offs and biases:

  • Deletion loses information and reduces statistical power; under MAR/MNAR it introduces selection bias by removing a non-random subgroup.

  • Imputation retains sample size but can inject bias and understate variance if the method or mechanism assumption is wrong.

  • Deleting rows can also silently alter the population (e.g. dropping all low-income responders skews the sample).

• Practical guidance:

  • Quantify the missing % and mechanism first, then pick the least biasing option and document it.

  • When unsure, compare model results with deletion vs imputation as a sensitivity check.

A missing indicator flag is an extra binary column that records whether a value was originally missing (1) or present (0) before imputation. It preserves the information that the value was absent, which the imputed value itself hides.

• What it captures:

  • Imputation replaces the gap with a plausible value, but the fact that data was missing can itself be predictive.

  • The flag lets a downstream model learn from "was this reported or not?" separately from the filled value.

• When it helps most:

  • When data is MNAR/MAR (missingness carries signal): e.g. income left blank correlates with risk.

  • With tree-based or linear models that can exploit the extra feature.

• Trade-offs:

  • Adds dimensionality: one flag per column, which can bloat wide sparse datasets.

  • Little value if missingness is purely random (MCAR) and unrelated to the target.

Numerical imputation replaces missing values with a statistic or model-based estimate; the right choice depends on the distribution's shape and the presence of outliers. Mean suits clean symmetric data, median is safer for skew/outliers, and model-based methods preserve relationships.

• Mean imputation:

  • Fast and unbiased for symmetric, roughly normal data.

  • Sensitive to outliers and skew: the mean gets dragged toward the tail, giving unrealistic fills.

• Median imputation: Robust to outliers and skewed distributions; preferred default when in doubt.

• Mode / constant imputation: Useful for discrete counts or a domain sentinel (e.g. 0 when missing means "none").

• Model-based imputation:

  • KNN imputation fills from similar rows; preserves local structure but scales poorly and needs feature scaling.

  • Iterative/regression (IterativeImputer, MICE) models each feature from the others: best accuracy, more compute and risk of leakage.

• Key discipline:

  • Fit imputation statistics on the training set only, then apply to validation/test to avoid leakage.

  • All single-value imputation shrinks variance and weakens correlations; consider adding a missing flag.

For categorical data you cannot average, so you either fill with the most frequent category, treat missingness as its own category, or predict it from other features. The best option depends on whether the absence is meaningful.

• Mode imputation: Fill with the most frequent level; simple but can over-inflate the majority class and distort proportions.

• "Missing" as an explicit category:

  • Add a level like "Unknown"; preserves the missingness signal and avoids fabricating a real category.

  • Often the safest choice for tree models and when missingness is informative.

• Predictive imputation: Train a classifier on complete rows to predict the missing label; more accurate but costlier and can leak.

• Practical considerations:

  • Impute before encoding, or encoding will treat NaN inconsistently.

  • For high-cardinality fields, an explicit "Unknown" avoids polluting rare categories.

Both are deletion strategies for missing data. Listwise deletion removes any row with a missing value in the variables used; pairwise deletion drops rows only for the specific calculations where a value is missing, keeping them elsewhere.

• Listwise (complete-case):

  • Keeps only fully observed rows; every analysis runs on the same consistent sample.

  • Cost: can discard large amounts of data and bias results unless data is MCAR.

• Pairwise:

  • Uses all available values for each statistic (e.g. each correlation uses rows where both variables are present).

  • Retains more data but different statistics rest on different subsets/sample sizes.

• Trade-offs:

  • Listwise: simpler, comparable estimates, but wasteful and biased if missingness isn't random.

  • Pairwise: more efficient use of data, but can yield inconsistent covariance matrices (even non-positive-definite) and ambiguous N.

  • Both assume MCAR to stay unbiased; when that fails, prefer imputation or model-based methods.

Group-wise (conditional) imputation fills a missing value using the statistic computed within a relevant subgroup rather than the whole column. It beats global mean/median whenever the variable's distribution differs meaningfully across groups.

• How it works:

  • Group by a related feature (region, product category, gender) and impute with that group's mean/median.

  • A generalization of this is regression/model-based imputation conditioning on multiple predictors.

• When it wins:

  • Strong group structure: e.g. missing salary imputed by job title is far more accurate than one global median.

  • It preserves between-group differences that global imputation flattens.

• Cautions:

  • Small or empty groups give unstable estimates: fall back to the global statistic.

  • Fit group statistics on training data only to prevent leakage.

There is no universal cutoff; you choose a threshold by balancing the missing rate against the feature's importance and whether the missingness is informative. A common rule of thumb is to consider dropping columns above roughly 50-70% missing, but always validate against the alternative.

• Factors that push the threshold:

  • Feature importance: a strong predictor is worth keeping and imputing even at high missingness.

  • Informative missingness: if absence itself predicts the target, keep it (a missing flag) rather than drop.

  • Imputation reliability: the more you'd fabricate, the weaker the retained signal.

• Practical approach:

  • Set a project-wide baseline (e.g. drop >60% missing) then override case by case.

  • Confirm empirically: compare model performance with vs. without the column.

  • Apply the threshold decision from training data and freeze it, so train and test keep the same columns.

These are gap-filling methods mainly for ordered/time-series data. Forward-fill carries the last known value forward, backward-fill pulls the next known value backward, and interpolation estimates intermediate values along a trend between known points.

• Forward-fill (ffill):

  • Propagates the last observation forward; natural for "value holds until it changes" signals (e.g. a sensor reading, account status).

  • Risk: staleness during long gaps and it can leak future-looking assumptions if misapplied.

• Backward-fill (bfill):

  • Fills with the next known value; useful at the start of a series or when the later value is representative.

  • Risk: uses future information, causing lookahead leakage in forecasting.

• Interpolation:

  • Linear or spline interpolation estimates values along the trend; smoother and often more realistic for continuous, gradually changing quantities.

  • Risk: assumes smooth behavior between points, so it fabricates trends and hides abrupt changes or noise.

• Shared cautions:

  • Data should be sorted by the time/order index first, or results are meaningless.

  • Long gaps amplify all these errors; cap fill distance (e.g. limit) and prefer ffill for causal time-series to avoid leakage.

Structural errors are problems in how data is recorded or shaped rather than the values themselves: wrong types, wrong layout, or mislabeled categories that make the dataset internally inconsistent.

• Wrong data types: Numbers or dates stored as text; fix by parsing/casting to the correct type.

• Inconsistent categories: Same label spelled/cased differently; consolidate to canonical values.

• Mixed units or formats: kg vs lbs, or MM/DD vs DD/MM dates; convert everything to one standard.

• Untidy layout: Values buried in column headers or several variables in one cell; reshape to tidy form (one variable per column, one observation per row) and split combined fields.

• Duplicate or misaligned records: Repeated rows or shifted columns from bad parsing; deduplicate and re-parse with correct delimiters.

• General approach: Profile the schema and value ranges, fix at the source when possible, and codify checks so the error can't recur.

Noise is random error or meaningless variation layered on top of the true signal; you reduce it by smoothing or aggregating so the underlying pattern shows through, while being careful not to erase real structure.

• Binning: Sort values into bins and replace each by the bin mean, median, or boundary to dampen small fluctuations.

• Smoothing: Moving averages or rolling medians for time series; the median resists spikes better than the mean.

• Regression/model fitting: Fit a function to the data and use the fitted values as the denoised signal.

• Clustering: Group similar points; values far from any cluster are candidate noise/outliers.

• Aggregation: Roll up to a coarser granularity so random per-record error averages out.

• Caution: Over-smoothing removes genuine signal (real peaks, rare events); distinguish noise from meaningful outliers before discarding.

Regular expressions match text against patterns, so they're used in cleaning to find, validate, extract, and replace substrings, standardizing formats like phone numbers, dates, or whitespace. Their power is also their risk: patterns are easy to get subtly wrong and can silently mangle valid data.

• Common cleaning uses:

  • Validation: check a field matches an expected shape (e.g. ^\d{5}(-\d{4})?$ for a US ZIP).

  • Extraction: pull structured pieces from free text with capture groups.

  • Substitution: collapse whitespace, strip punctuation, or reformat with re.sub().

• Risks of over-relying on regex:

  • Edge cases: real-world data (international phone/date formats, names) rarely fits one clean pattern.

  • Over-matching: a greedy or loose pattern can silently corrupt valid values.

  • Readability: complex patterns are hard to maintain and review.

  • Wrong tool: structured formats (HTML, CSV, JSON) should use real parsers, not regex.

  • Catastrophic backtracking: pathological patterns can hang on certain inputs (ReDoS).

• Mitigations:

  • Anchor patterns (^, $) and test against both valid and invalid samples.

  • Prefer specialized libraries where they exist (phonenumbers, dateutil).

Standardizing units and number/currency formats means converting every value to one agreed canonical representation: a single unit, a single numeric type, and a single currency where possible, while preserving the original for traceability. The core work is parsing messy strings into structured (value, unit) pairs and then converting.

• Pick a canonical form up front:

  • Decide the target unit (e.g. store all weights in grams, all distances in meters) and target numeric type/precision.

  • For currency, store amount plus an explicit currency code (USD) rather than a bare number.

• Parse before converting:

  • Separate the numeric value from its unit/symbol, handling thousands separators and decimal marks that differ by locale (1,000.50 vs 1.000,50).

  • Strip currency symbols ($, €) and map them to codes.

• Convert with a reliable factor or library:

  • Use maintained tools for units (pint) instead of hand-coded factors to avoid mistakes.

  • Currency conversion needs a dated exchange rate: don't merge currencies without recording the rate and date.

• Guard against precision and ambiguity:

  • Use Decimal for money to avoid float rounding errors.

  • Flag ambiguous inputs (bare "1.5" with no unit) rather than guessing.

• Keep the raw value: Store original alongside canonical so conversions are auditable and reversible.

You map a messy free-text field with many variants ("NY", "N.Y.", "new york") onto a controlled vocabulary: a fixed, approved set of canonical values. This combines normalization, fuzzy matching, and a lookup/mapping table, ideally with human review for uncertain cases.

• Normalize first: Lowercase, trim whitespace, strip punctuation, and normalize Unicode so trivial variants collapse.

• Define the controlled vocabulary: Enumerate the allowed canonical values, often from an authoritative reference or domain standard.

• Match variants to canonical values:

  • Exact/synonym lookup: a hand-built mapping dictionary catches known aliases and abbreviations.

  • Fuzzy matching: string-similarity (Levenshtein, token-set ratio via rapidfuzz) catches typos and reorderings.

  • Set a similarity threshold; above it auto-map, below it flag for review.

• Cluster to discover variants: Tools like OpenRefine's key-collision/clustering group likely-equivalent strings so you can assign one canonical label per cluster.

• Keep a human in the loop: Auto-matching guesses wrong on ambiguous cases; review borderline matches to avoid merging distinct categories.

• Prevent recurrence: Enforce the vocabulary at entry (dropdowns, validation) so cleaning doesn't have to repeat.

Choosing the master (or "golden") record is a survivorship decision: you apply consistent rules to keep the most complete, accurate, and trustworthy version, and often merge the best fields across duplicates rather than blindly picking one row.

• Common survivorship criteria:

  1. Completeness: prefer the record with the fewest null/missing fields.

  2. Recency: prefer the most recently updated record (via a timestamp).

  3. Source trust: prefer records from a more authoritative system.

  4. Validity: prefer values that pass validation (a real email over a malformed one).

• Field-level merging: Instead of one whole winner, build a composite: take the best value per field (newest phone, most complete address).

• Preserve lineage: Keep the source record IDs and merge history so the decision is auditable and reversible.

• Make rules deterministic: Encode tie-breakers explicitly so the same inputs always yield the same master, avoiding arbitrary picks.

Record linkage (also called entity resolution or fuzzy matching) is the process of identifying records that refer to the same real-world entity across or within datasets when there is no shared reliable key, by comparing them on similarity rather than exact equality.

• Core steps:

  1. Preprocess/normalize fields (case, whitespace, formats) so comparisons are fair.

  2. Blocking: group candidates on a cheap key to avoid comparing every pair.

  3. Comparison: score field similarity (edit distance for names, phonetic codes, numeric closeness).

  4. Classification: combine scores into a match/no-match/review decision via thresholds or a model.

• Deterministic vs probabilistic:

  • Deterministic: rule-based exact/near rules on chosen fields.

  • Probabilistic (Fellegi-Sunter): weight each field's agreement by how discriminating it is and compute a match probability.

• Why it matters: It catches near-duplicates that exact matching misses, enabling deduplication and merging of messy real-world data (customers, patients, businesses).

A primary key enforces row-level identity while uniqueness constraints enforce non-duplication on chosen columns: together they let the database reject duplicates at write time and let you find them with queries.

• Primary key: identity + prevention:

  • Guarantees every row is uniquely addressable and cannot be NULL; two identical PK values are rejected on insert.

  • Prevents duplicates only on the key itself, not on business-meaningful attributes.

• Unique constraints: business-level dedup:

  • Apply to natural keys (email, SSN) or composite columns so the DB blocks logical duplicates even with different surrogate IDs.

  • A composite UNIQUE (col_a, col_b) catches duplicates defined by a combination.

• Detection: Even without constraints, group and count to surface duplicates before enforcing.

• Caveats:

  • Constraints only catch exact matches; near-duplicates (typos, casing) slip through and need fuzzy matching.

  • NULLs are treated as distinct in most engines, so NULLable unique columns still allow multiple NULLs.

Detect wrong types by profiling each column's actual values against its intended semantic type, then cast deliberately with explicit formats and error handling: automatic inference is convenient but guesses from samples and can misread the whole column.

• Detection:

  • Profile value patterns: a numeric column stored as text, dates as strings, or booleans as "Y"/"N".

  • Look for values that fail to cast, or numeric columns with unexpectedly high cardinality of non-numeric characters.

• Fixing: Cast explicitly with a known format and coerce failures to NULL for inspection rather than silently dropping rows.

• Pitfalls of auto-inference:

  • Sampling: inferring from the first N rows misreads columns whose odd values appear later.

  • Leading zeros in ZIP codes or IDs get stripped when read as integers.

  • A single stray string forces a whole numeric column to text/object.

  • Ambiguous dates (01/02/2024) get parsed under the wrong locale.

Automatic type inference guesses a column's type from a sample of values, which is fast but can silently corrupt data when the sample isn't representative: mitigate it by declaring schemas explicitly and validating after load.

• Pitfalls:

  • Sample-based guessing: types inferred from early rows break when later rows differ.

  • Lost leading zeros: IDs and ZIP codes read as integers drop them permanently.

  • Precision loss: large integers or high-precision decimals read as floats.

  • Type collapse: one bad value coerces an entire numeric column to string.

  • Ambiguous dates parsed under the wrong locale without warning.

• Mitigations:

  • Declare an explicit schema/dtypes on load (dtype=) instead of trusting inference.

  • Read identifier-like columns as strings by default.

  • Increase or disable sampling limits so inference sees the full column.

  • Validate post-load with a schema tool (row counts, null rates, type checks) to catch silent coercions.

A mixed-type column (numbers, strings, dates all in one field) usually signals ingestion or entry problems; you handle it by diagnosing the intended type, coercing or separating values, and isolating the ones that can't be salvaged.

• Diagnose first:

  • Profile the column to see the distribution of types (e.g. df['col'].map(type).value_counts()) and inspect the odd values.

  • Decide the single intended type from business meaning, not from what's most common.

• Coerce and quarantine:

  • Cast with a safe converter (pd.to_numeric(..., errors='coerce')) so bad values become NaN instead of crashing.

  • Keep the rejected rows separately so you can review rather than silently drop them.

• Common root causes: Sentinel values in a numeric field ("N/A", "unknown"), thousands separators, or numbers stored as text from Excel/CSV.

• Downstream problems it causes:

  • Pandas stores it as object dtype: arithmetic, sorting, and aggregation break or give wrong results.

  • Database loads fail on type mismatch; models silently misinterpret encoded categories as numbers.

  • Joins fail when a key is int on one side and string on the other.

Data quality checks are automated rules that assert expectations about data (completeness, uniqueness, validity, consistency); you embed them at every boundary of the pipeline so bad data is caught early rather than corrupting downstream tables.

• Common categories of rules:

  • Completeness: required fields are non-null.

  • Uniqueness: primary keys have no duplicates.

  • Validity: values fit type, format, range, or allowed set.

  • Consistency/integrity: cross-field logic and referential links hold.

  • Freshness/volume: data arrived on time and in expected row counts.

• Where to integrate them:

  1. At ingestion: reject or quarantine malformed source data before it enters staging.

  2. After transformation: verify business logic and joins didn't drop or duplicate rows.

  3. Before publish/load: a final gate so consumers never see bad data.

• Handling failures:

  • Distinguish hard fails (block the pipeline) from soft warnings (log and alert).

  • Tools like Great Expectations, dbt tests, or Soda formalize and version these rules.

You validate cleaning by comparing before/after states against explicit expectations: confirm the intended fixes happened, confirm nothing else changed unexpectedly, and reconcile totals so you didn't silently lose or alter data.

• Verify intended changes: Re-run the checks that originally failed (nulls filled, duplicates gone, formats normalized) and confirm they now pass.

• Guard against new errors:

  • Compare row counts before and after: know exactly why any rows dropped.

  • Check aggregate reconciliations (sums, means, category counts) stayed sensible.

  • Re-run the full validation suite so a fix in one column didn't break another.

• Structural and statistical sanity:

  • Confirm dtypes, ranges, and distributions match expectations; watch for shifts that signal over-cleaning.

  • Spot-check a sample of transformed records by hand against the raw source.

• Make it repeatable: Keep the raw data immutable and codify validation as automated assertions so you can re-verify on every run.

You validate each value by testing it against a defined set of legal possibilities: domain checks confirm membership in an allowed set or reference table, and range checks confirm numeric/date values fall within sensible bounds. Values that fail are flagged, corrected, or quarantined.

• Controlled vocabulary / domain checks:

  • Test membership in a fixed set (e.g. status in {'active','closed','pending'}).

  • Normalize first (trim, case-fold) so "Active" and "active " aren't false failures.

• Reference data checks: Validate codes against an authoritative lookup table (country codes, currency codes, product IDs) via a join; unmatched rows are violations.

• Range checks: Numeric bounds (age 0–120), date bounds (not in the future), and length limits.

• Business rules: Encode domain logic explicitly (a discount can't exceed the list price) and version them alongside the pipeline.

• Handling failures: Prefer flagging and routing to a review/quarantine queue over silent auto-correction, especially for reference-data misses.

Cross-field checks validate the relationship between multiple columns in the same record, rather than each column in isolation: a row can have every field individually valid yet still be logically inconsistent.

• Why they're needed: Single-column checks can't catch contradictions between fields; the invalidity lives in the combination.

• Common examples:

  • Temporal ordering: start_date <= end_date, or birth date before hire date.

  • Arithmetic consistency: quantity * unit_price == line_total.

  • Conditional requiredness: if country == 'US', then state must be present.

  • Category coherence: a "shipped" order must have a ship date.

• How to implement:

  • Express as row-level boolean predicates and report the failing rows.

  • Handle nulls deliberately: decide whether a missing field passes or fails the rule.

Good documentation makes cleaning reproducible and auditable: record what you changed, why, and what assumption justified it, so anyone (including future you) can trace a value back to its raw source and challenge the decision.

• Capture decisions and rationale:

  • For each transformation record the rule, the reason, and the assumption behind it (e.g. "blank region set to 'Unknown' because it's not required downstream").

  • Note the impact: how many rows were affected.

• Make it live with the code:

  • Prefer versioned scripts/notebooks over manual edits so the logic is self-documenting and reproducible.

  • Use version control and commit messages to track how rules evolved.

• Preserve traceability:

  • Keep the raw data immutable and never overwrite it; clean into a new layer.

  • Maintain a data dictionary and a changelog of cleaning rules.

• Flag uncertainty:

  • Add columns or logs marking imputed/derived values so consumers know what was inferred vs. observed.

  • Record open questions and edge cases you didn't resolve.

An immutable, versioned copy of raw data is the ground truth: it lets you reprocess when cleaning logic changes, reproduce any past result, and prove exactly how a cleaned value was derived.

• Reproducibility: If a bug is found in cleaning code, you rerun against the original raw data instead of losing information already discarded.

• Auditability: Auditors can compare raw versus cleaned to verify every transformation was legitimate and no data was silently fabricated or dropped.

• Point-in-time recovery: Versioning (snapshots, Delta Lake time travel, DVC) lets you reconstruct the dataset as it looked on any date.

• Lineage and trust: Linking outputs back to raw versions supports debugging, regulatory compliance, and reproducing model training sets.

By building validation and monitoring into every stage of the pipeline (not just the end), enforcing contracts at boundaries, and making the whole flow reproducible and observable.

• Validate at boundaries: Enforce schema and data contracts at ingestion and between stages so bad data is stopped early.

• Constraints and tests: Apply uniqueness, not-null, referential, and range checks; automate them with dbt tests or Great Expectations.

• Idempotent, atomic writes: Ensure reruns don't create duplicates or partial state; use transactions or staging-then-swap patterns.

• Reconciliation: Compare row counts and key aggregates against source to confirm nothing was lost or duplicated.

• Lineage and monitoring: Track lineage and emit quality metrics with alerting so issues are detected and traced quickly.

• Quarantine over silent drop: Isolate failing records for review rather than discarding them silently.

Data profiling is the systematic examination of a dataset's structure, content, and relationships before cleaning, so you understand what you're dealing with and can plan targeted fixes rather than blindly transforming.

• Structure and types: Confirm each column's actual type vs expected (numbers stored as text, dates as strings), row/column counts, and granularity (what one row represents).

• Completeness: Missing-value rates per column, and whether missingness is random or patterned (e.g. always missing for one segment).

• Distributions and ranges: Min/max, mean/median, cardinality, and frequency counts to spot outliers, impossible values (negative age), and skew.

• Uniqueness and consistency: Duplicate keys, inconsistent categorical labels ("NY" vs "New York"), and format variance in dates/phones/currency.

• Relationships and validity: Referential integrity across tables and business-rule violations (order date after ship date).

• Goal of the exercise: Produce a prioritized list of quality issues weighted by their impact on the intended use, not to fix everything found.

A genuine extreme value is a real but rare observation; a data entry error is an artifact of recording. You distinguish them by checking plausibility against domain knowledge and corroborating evidence, not by statistics alone.

• Signals it's an error: Physically/logically impossible (age of 300, negative weight), obvious unit or decimal shifts (10x, 100x), or contradicts other fields in the same row.

• Signals it's genuine: Plausible in the domain (a billionaire's net worth, a viral spike), consistent with related fields, and reproducible in the source system.

• How to investigate: Trace back to source, compare against known valid ranges, and look for clustering (many identical odd values suggests a systematic pipeline bug).

• Conceptual treatments:

  1. Confirmed error: correct if the true value is recoverable, otherwise treat as missing.

  2. Genuine extreme: keep it, but consider robust methods or transformation so it doesn't distort models.

Once identified, the treatment depends on why the value is extreme and how the data will be used: the choice ranges from removing it to leaving it untouched, and there's no default that fits every case.

• Remove: Delete the row when it's a confirmed error and unrecoverable, and losing the record won't bias the data.

• Cap (clip): Set values beyond a threshold to that threshold (e.g. anything above the 99th percentile becomes the 99th percentile value).

• Winsorize: Similar to capping but symmetric at both tails by percentile; keeps sample size while limiting influence.

• Transform: Apply log/sqrt/Box-Cox to compress a skewed scale so extremes stop dominating.

• Flag: Keep the value but add an indicator column, letting a model learn from the anomaly rather than hiding it.

• Leave: Do nothing when it's genuine and meaningful, especially if extremes are the point (fraud, defaults).

• How to decide: Error vs genuine, the task (robust models tolerate outliers; linear/distance-based ones don't), and whether the extreme itself carries signal.

Ensuring data quality for ML is a continuous discipline spanning validation before training, correct handling during preparation, and monitoring in production, all oriented around whether the data faithfully represents the problem.

• Validate inputs: Profile for missingness, outliers, and invalid values; enforce schema and range checks with tools (e.g. Great Expectations).

• Check labels and representativeness: Verify label accuracy and that training data reflects production distribution to avoid bias and sampling skew.

• Prevent leakage: Fit all cleaning/imputation/scaling on the training split only, inside a pipeline, and apply to validation/test.

• Ensure consistency train-to-serve: Use the same transformation code offline and online so features match at inference time.

• Monitor over time: Watch for data drift, schema changes, and rising null rates; alert and retrain when quality degrades.

• Make it reproducible: Version data and cleaning steps so results and fixes can be audited and repeated.

Tidy data is a standard structuring rule from Hadley Wickham: each variable forms a column, each observation forms a row, and each type of observational unit forms a table. It is a useful cleaning target because it gives every dataset a predictable shape that downstream tools, joins, and aggregations expect.

• The three principles:

  1. Each variable is its own column (e.g. date, temperature are separate columns, not merged).

  2. Each observation is its own row (one measured unit per row).

  3. Each observational unit gets its own table (e.g. orders separate from customers, linked by keys).

• Common untidy shapes it fixes:

  • Column headers that are values (e.g. year columns 2019, 2020) collapsed into a single variable via reshaping.

  • Multiple variables packed into one column (e.g. "male_25-30") split out.

  • Variables stored in both rows and columns.

• Why it is a good target:

  • Tools are built for it: vectorized operations, group_by/aggregation, and plotting libraries assume column-per-variable.

  • It separates structure from analysis, so cleaning decisions are consistent and reusable.

  • It makes joins, filtering, and missing-value detection straightforward.

• Caveat: tidy is the analysis-friendly "long" shape; storage or presentation may favor a "wide" pivot, so reshape as needed rather than treating tidy as the only valid form.

You quantify missingness by computing per-column null rates and then examining how missing cells co-occur across variables, because the pattern hints at the underlying mechanism (MCAR vs MAR vs MNAR).

• Univariate summary: Compute per-column missing counts and percentages with df.isna().mean() to prioritize columns.

• Multivariate/co-occurrence:

  • Build a boolean missingness matrix (df.isna()) and correlate columns to see which go missing together.

  • Visual tools: missingno matrix/heatmap/dendrogram reveal blocks, stripes, and clusters of joint missingness.

• Test the mechanism:

  • Compare distributions of observed variables split by whether another column is missing: if they differ, it suggests MAR (missingness relates to observed data).

  • No relationship anywhere is consistent with MCAR; a self-related pattern you can't explain hints at MNAR.

• What patterns tell you:

  • Monotone patterns (once missing, stays missing across columns) suggest dropout in surveys/time series.

  • Whole rows/columns missing may indicate an ETL or join failure rather than data-collection gaps.