Data Quality and Validation Questions

Covers the core concepts and hands on techniques for detecting, diagnosing, and preventing data quality problems. Topics include common data issues such as missing values, duplicates, outliers, incorrect labels, inconsistent formats, schema mismatches, referential integrity violations, and distribution or temporal drift. Candidates should be able to design and implement validation checks and data profiling queries, including schema validation, column level constraints, aggregate checks, distinct counts, null and outlier detection, and business logic tests. This topic also covers the mindset of data validation and exploration: how to approach unfamiliar datasets, validate calculations against sources, document quality rules, decide remediation strategies such as imputation quarantine or alerting, and communicate data limitations to stakeholders.

EasyTechnical

35 practiced

You find 12% duplicate rows in a daily ETL load for orders (duplicates defined by order_id). Outline a remediation plan: quick fixes for the immediate ETL load, and long-term changes to prevent recurrence. Include how you'd detect whether duplicates affect downstream metrics.

EasyTechnical

45 practiced

You need to design a simple dashboard of data-quality KPIs for product analytics stakeholders. Which five KPIs would you include (e.g., null rate, schema mismatch count) and why? For each KPI specify the recommended aggregation cadence (real-time, hourly, daily).

MediumTechnical

44 practiced

Explain how you would use aggregate-level checks (e.g., distinct count of active users per day) to detect partial downstream processing failures that row-level checks miss. Provide an example SQL check and explain how to set a meaningful alert threshold.

Sample Answer

Why aggregate checks: Row-level validation can pass while partial downstream failures (e.g., only some partitions processed) reduce distinct counts. An aggregate distinct-count captures population-level drops.

Example SQL check (daily distinct active users):

WITH current AS (
  SELECT DATE(event_time) AS day, COUNT(DISTINCT user_id) AS distinct_users
  FROM events WHERE DATE(event_time) = '2025-11-22'
), baseline AS (
  SELECT AVG(distinct_users) AS avg_users, STDDEV(distinct_users) AS sd
  FROM (
    SELECT DATE(event_time) AS day, COUNT(DISTINCT user_id) AS distinct_users
    FROM events
    WHERE DATE(event_time) BETWEEN '2025-11-15' AND '2025-11-21'
    GROUP BY DATE(event_time)
  ) x
)
SELECT c.day, c.distinct_users, b.avg_users,
       (b.avg_users - c.distinct_users)/NULLIF(b.avg_users,0) AS pct_drop
FROM current c CROSS JOIN baseline b
WHERE (b.avg_users - c.distinct_users)/NULLIF(b.avg_users,0) > 0.15; -- alert threshold 15%

Setting threshold: use historical variability (mean ± 3*sd) or percent drop (e.g., 10–20%) depending on business sensitivity. For low-variance KPIs use tighter thresholds; for noisy signals use dynamic z-score thresholds.

HardTechnical

41 practiced

Propose a taxonomy for classifying data quality rules (e.g., syntactic, semantic, statistical, lineage). For each category provide two concrete example rules and explain how severity and remediation actions should be defined.

MediumTechnical

31 practiced

Provide a practical checklist of automated checks (at least eight) you would run after each batch ETL job to ensure data quality before marking the job as successful. Include checks for schema, cardinality, nulls, aggregates, referential integrity, and freshness.

Unlock Full Question Bank

Get access to hundreds of Data Quality and Validation interview questions and detailed answers.

Join thousands of developers preparing for their dream job.