Skip to content

Data Cleaning — Interview Questions

Questions for data analyst technical interviews covering missing values, duplicates, outliers, and data quality.

[Beginner] What are the most common data quality problems you encounter in real datasets?

Show answer

The most common ones, roughly in order of frequency:

  1. Missing values — NULL, NaN, empty strings, "N/A" strings
  2. Inconsistent formatting — dates in multiple formats, mixed currency symbols, inconsistent capitalisation
  3. Duplicates — same record loaded twice from different sources or re-exports
  4. Wrong data types — numbers stored as strings (especially from Excel/CSV exports)
  5. Outliers — extreme values from data entry errors or system glitches
  6. Referential integrity issues — foreign keys pointing to records that don't exist
  7. Domain violations — values outside the expected range (negative quantities, future dates)

[Beginner] What is the difference between dropna() and fillna()?

Show answer
  • dropna() removes rows (or columns) that contain missing values
  • fillna(value) replaces missing values with a specified value
df.dropna()                       # drop rows with ANY NaN
df.dropna(subset=["email"])       # drop rows where email is NaN
df.dropna(thresh=5)               # keep rows with at least 5 non-NaN values

df.fillna(0)                      # replace all NaN with 0
df["discount_pct"].fillna(0)      # replace NaN in one column
df.fillna(df.mean())              # replace with column mean (numeric only)
df.fillna(method="ffill")         # forward fill (time series)

The choice depends on context. dropna() loses data. fillna() introduces assumptions. Always ask: why is the value missing, and what does "unknown" mean in this context?

[Mid-level] What is MCAR, MAR, and MNAR? Give a business example of each.

Show answer

MCAR — Missing Completely At Random The probability of missing is unrelated to any observed or unobserved data. Example: A data pipeline bug randomly failed to capture the phone number for 3% of customers. The failure is independent of any customer characteristic. Treatment: Safe to impute or drop. The remaining sample is still representative.

MAR — Missing At Random The probability of missing depends on observed data, but not on the missing value itself. Example: Older customers are less likely to provide their email address. We can see their age, so the missingness is "explainable" by an observed variable. Treatment: Impute using related variables (e.g., use age to predict whether to impute email).

MNAR — Missing Not At Random The probability of missing depends on the value itself (we can't observe why). Example: High-earning customers are less likely to report their income. The income column is missing precisely because it's high — we can't observe this. Treatment: Dangerous to impute — creates bias. Better to flag as "unknown" and model the missingness explicitly.

[Mid-level] You're asked to calculate average revenue per customer, but 15% of orders have a NULL customer_id. What do you do?

Show answer

Don't just drop them — first investigate:

  1. Why are they NULL? Possible reasons:
  2. Guest checkouts (valid — these customers just didn't create an account)
  3. ETL failure (system issue — some records lost in transit)

  4. Data entry error

  5. What is the revenue impact? If these 15% represent 40% of revenue (e.g., corporate/wholesale orders), dropping them creates a severely biased "average."

  6. Decide based on investigation:

  7. If guest checkouts: assign a synthetic GUEST_XXXX ID using another key (email, session ID) where available
  8. If ETL failure: investigate the root cause, fix the pipeline, re-run

  9. If entry errors: try to match to existing customers using email or name

  10. Report the known limitation: "Avg revenue excludes 15% of orders with no customer_id. These may be guest or wholesale orders."

Never silently drop data without documenting why.

[Mid-level] How do you handle duplicate records in a dataset where each record can appear multiple times because the ETL process re-runs?

Show answer 
# Sort by updated timestamp — most recent first
df_sorted = df.sort_values("updated_at", ascending=False)

# Keep only the first (most recent) occurrence of each key
df_clean = df_sorted.drop_duplicates(subset=["order_id"], keep="first")

# Verify
assert df_clean["order_id"].duplicated().sum() == 0
print(f"Removed {len(df) - len(df_clean)} duplicate rows")

Always verify by checking: (1) the row count after deduplication, (2) that revenue totals haven't changed dramatically, (3) that spot-checking shows the correct version of disputed records.

[Mid-level] What is the IQR method for outlier detection? How does it differ from z-score?

Show answer

IQR method: 1. Calculate Q1 (25th percentile) and Q3 (75th percentile) 2. IQR = Q3 - Q1 3. Flag values below Q1 - 1.5 * IQR or above Q3 + 1.5 * IQR

Z-score method: 1. Standardise each value: z = (x - mean) / std 2. Flag values where |z| > 3 (or 2 for stricter detection)

Key difference: - Z-score assumes approximately normal (symmetric) distribution. It's biased by the presence of outliers (they inflate mean and std, making other outliers look less extreme). - IQR uses median and percentiles — resistant to outliers. Works better for right-skewed data like revenue, order values, and visit counts.

For most business data (revenue, prices, wait times), use IQR. For measurements that follow a normal distribution (heights, test scores), z-score is appropriate.

[Senior] You've been asked to build an automated data quality monitoring system for an e-commerce pipeline. What metrics would you track and how would you alert?

Show answer

Metrics to track per pipeline run:

  1. Volume: total rows, new rows, expected vs actual count (e.g., daily orders should be within ±30% of the 30-day rolling average)
  2. Completeness: missing value percentage per critical column (alert if any key column rises above threshold)
  3. Freshness: max updated_at timestamp — alert if data is more than 2 hours stale
  4. Uniqueness: duplicate key count — alert if > 0 duplicates in primary key column
  5. Referential integrity: order with customer_id not in customers table — alert if > X%
  6. Domain validity: values outside expected range (negative prices, future dates, impossible ages)
  7. Distribution drift: statistical tests (KS test, PSI) to detect if the distribution of key metrics has shifted significantly vs historical baseline

Alerting approach: - Write these checks as SQL queries or Python assertions that run after each ETL load - Post alerts to Slack/Teams with: which check failed, how many rows affected, sample of bad data - Use a tool like Great Expectations, dbt tests, or Soda Core to formalise these as a test suite - Implement tiered severity: WARNING (5% drift), ERROR (15% drift), CRITICAL (pipeline blocked)

← Case Study · Next: EDA →