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.

HardTechnical

30 practiced

You must deduplicate a dataset with billions of customer records. Propose a scalable architecture using blocking and locality-sensitive hashing (LSH/MinHash). Describe data structures, parameter tuning, candidate generation, scoring, and how to incorporate manual review for uncertain matches.

Sample Answer

Situation & goal (brief): We need to deduplicate billions of customer records reliably and scalably, producing high-precision merged identities while surfacing uncertain cases for manual review.

High-level architecture:- Ingest/normalize raw records into a data lake (Parquet) via ETL (Spark).- Use two-stage dedupe: (1) blocking to produce candidate pairs, (2) LSH/MinHash to find similar soft-matches, then (3) pairwise scoring and adjudication.- Orchestrate with Airflow; store results and linkage graph in a graph DB or HBase for queryability.

Data structures:- Normalized canonical record per input with attributes: canonical_name, tokens (set), address_tokens, email_norm, phone_norm, dob.- MinHash signatures (e.g., 128-bit / 128-permutations) stored as compact binary or int arrays in Parquet.- LSH buckets: mapping from band-hash -> list of record IDs (stored in distributed key-value / Spark RDD/hash partition).

Blocking + candidate generation:- Deterministic blocks: exact phone, exact email, postal 5-digit + last name token. These capture high-precision pairs cheaply.- Token-based blocking + LSH: create shingled tokens from name+address, compute MinHash signature, split into B bands, hash each band to bucket -> join records that land in same bucket. Combine block results (union) per record, limit candidate count per record with top-K per bucket or sampling.

Parameter tuning:- MinHash permutations P (e.g., 128–256). Choose P larger for higher recall; cost increases linearly.- Bands B and rows R where P = B*R: tune B to set collision probability curve target: want high collision for Jaccard >= threshold (0.8) and low for < 0.5.- Empirically tune on labeled sample: sweep (B,R) to get desired precision/recall tradeoff.- Cap candidate pool per record to avoid quadratic explosion (e.g., max 500 candidates).

Scoring & merging:- Use feature vector per candidate: Jaccard(name_tokens), Jaccard(address_tokens), exact email match (binary), phone match, DOB distance, string edit distance on normalized name, domain-based email similarity.- Train a lightweight classifier (logistic regression / XGBoost) on labeled pairs to produce match probability and calibration.- Thresholds: auto-merge if p >= 0.98; auto-reject if p <= 0.02; else send to manual review.

Manual review workflow:- Batch uncertain pairs into human-review queues with context (record snapshots, provenance, reason codes).- Use a web UI showing side-by-side records and recommended merge actions; capture reviewer decision for retraining.- Prioritize reviews by business impact (high LTV customers first) and ambiguous clusters via clustering algorithms.

Scalability & ops:- Run MinHash + LSH in Spark using map-side hashing and partitioned writes; persist signatures to avoid recompute.- Monitor candidate counts, precision/recall on holdout; maintain drift detection (data distribution changes).- Periodically retrain scoring model with reviewer labels; maintain audit trail for merges.

Key trade-offs:- More permutations -> better recall but higher storage/compute.- Aggressive blocking reduces compute but risks missed duplicates.- Balance with human-in-the-loop thresholds tuned to business cost of false merges vs manual review cost.

This pipeline gives scalable, tunable deduplication with measurable precision/recall and a human review loop to handle edge cases and continuously improve the model.

HardTechnical

31 practiced

You're asked to recommend a governance policy: enforce strict data constraints upstream (source) or validate/fix downstream in ETL. As lead analyst, present the trade-offs (latency, ownership, cost, product velocity), propose a hybrid policy and criteria for escalating schema changes to engineering.

Sample Answer

Situation: Our analytics pipelines consume events and transactional feeds from multiple product teams. We’ve repeatedly seen schema drift and bad upstream data that either breaks dashboards or requires ad-hoc fixes in ETL, slowing delivery.

Recommendation — trade-offs:- Enforce strict upstream constraints (source): - Latency: Lowest downstream processing time because bad data is prevented. - Ownership: Clear product-team responsibility for quality. - Cost: Higher initial engineering effort per producer to add validation and contract tests. - Product velocity: Short-term slowdown for producers; long-term faster, more reliable releases.- Validate/fix downstream (ETL): - Latency: Faster for product teams to release (no blocking), but ETL becomes slower and more complex due to fixes. - Ownership: Blurs responsibility; analytics owns fixes and private transforms. - Cost: Ongoing operational and maintenance cost in ETL; duplicated fixes. - Product velocity: Short-term faster for producers; analytics/back-end becomes a bottleneck over time.

Hybrid policy I propose (balanced, pragmatic):1. Classify data domains by criticality (Critical = billing, legal, core user metrics; Important = product metrics; Optional = experimental).2. For Critical fields: enforce upstream schema contracts and runtime validation. Producers must provide contract tests in CI and schema in a central registry.3. For Important fields: require producer schema registration and best-effort validation; ETL includes strict alerts and automated, reversible fixes (e.g., type coercion, defaulting) with metadata tagging.4. For Optional fields: allow downstream transformations with documented mappings and consumer-owned fixes.

Operational rules & tooling:- Central schema registry (with versions), lightweight contract tests, and automated CI checks.- Real-time schema-compatibility checks in ingestion (reject or quarantine noncompliant messages).- ETL layer implements idempotent, auditable remediation steps and quarantine pipelines for malformed records.- Monitoring: data-quality dashboards, SLA alerts, and consumer impact reports.

Escalation criteria to involve engineering (when producers must change source):1. Frequency: >1% of records failing or >daily recurring failures from same producer.2. Business impact: Any failure affecting Critical metrics, billing, compliance, or executive dashboards.3. Downstream cost: Fixes costing >X engineer hours per month (set threshold) or repeated manual interventions.4. Consumer count: If >N downstream consumers rely on the field (N set by org, e.g., 3 teams).5. Schema incompatibility: Breaking changes (type changes, field removal) that cannot be safely handled by automatic transforms.6. Data sensitivity/regulatory requirement: PII or regulated fields must be corrected at source.

Governance process:- Triage: Data team classifies incidents and applies automated fixes when safe.- Notify: Producer team notified via standardized incident with reproducible examples and impact summary.- Escalate: If any escalation criterion met, open engineering ticket with priority SLA and include suggested schema patch, contract test, and migration plan.- Review cadence: Weekly cross-team data governance sync and quarterly contract compliance audit.

This hybrid minimizes product friction while protecting critical analytics reliability — pushes ownership to producers where it matters, keeps analytics resilient with automated fixes and clear escalation rules to avoid long-term technical debt.

HardTechnical

31 practiced

Design an efficient anomaly detection approach for daily revenue time series across 100k stores. Discuss algorithms (rolling z-score, seasonal decomposition, STL, FB Prophet, hierarchical models), compute resource trade-offs, batching vs streaming, and strategies to reduce false positives across noisy small stores.

Sample Answer

Requirements & constraints:- Detect daily revenue anomalies for 100k stores with varying volume and seasonality, low-latency alerts for critical drops, and periodic reporting. Budgeted compute (e.g., Spark cluster + modest online service).

Algorithmic strategy (multi-tiered, pragmatic):1. Tier 1 — Lightweight streaming checks (high recall, low compute) - Rolling z-score on log(revenue+1) with robust stats: median and MAD over window (e.g., 28 days) per store. Fast, easy in SQL/stream processors. - Use per-store minimum data threshold (e.g., ≥14 days) otherwise treat as "insufficient data".

2. Tier 2 — Seasonal-aware modeling for candidate cases - Apply STL decomposition (weekly + yearly seasonality) or ETS on stores with sufficient history. STL is robust to trend/seasonal shifts and cheap when batched. - For high-value stores (top X% by revenue) and persistent anomalies, run Prophet/ARIMA to forecast and produce prediction intervals. Prophet handles holidays and changepoints but is heavier.

3. Tier 3 — Hierarchical / pooled models - For small/noisy stores, use hierarchical (Bayesian) or pooled Prophet models: learn shared seasonality across store clusters (region/format) then borrow strength to reduce variance and false positives.

Compute trade-offs:- Rolling z-score: O(N) per day, trivial at scale; ideal for streaming.- STL/ETS per store: moderate CPU, best batched in Spark; parallelizable across executors.- Prophet/hierarchical: expensive per model; limit to top stores or weekly batch; consider autoscaling or sampling.

Batching vs streaming:- Streaming for real-time alerts using rolling z-scores + simple rules.- Daily batched jobs (Spark) to run STL/Prophet, recompute baselines, and update hierarchical models. Hybrid approach balances latency and cost.

Reducing false positives for noisy small stores:- Minimum data and revenue thresholds.- Use aggregated baselines (cluster-level seasonality) with per-store adjustments.- Increase tolerance (wider prediction intervals) for low-volume stores, flag as “watch” rather than action.- Require persistence: mark anomaly only if deviation persists >2 days or combined with business signals (refund spikes, POS downtime).- Post-filter with rules: holiday calendars, promotions, store closures.- Use ensemble voting: anomaly confirmed if ≥2 methods agree.

Monitoring & validation:- Track precision/recall via labeled incidents, A/B test thresholds, review false positives monthly, and surface results in dashboards (Power BI/Tableau) for operations teams.

This design gives a fast, scalable surface for anomalies while using stronger models selectively to reduce noise and operational load.

MediumTechnical

39 practiced

When datasets are too large to scan fully, what sampling strategies (random, stratified, reservoir, hash-based) would you use to validate quality? Explain trade-offs, reproducibility, and how you'd detect rare but important issues that sampling might miss.

Sample Answer

When full scans are infeasible, choose sampling to balance cost, bias, and the risk of missing rare but important issues.

Overview of strategies and when to use them:- Random (simple random sampling): easiest; unbiased for overall metrics (means, rates). Good for quick health checks. Trade-off: may miss rare events; reproducibility requires a fixed RNG seed.- Stratified: partition by known important fields (date, region, product, customer segment) and sample within each stratum. Reduces variance and ensures coverage of small but important groups. Use when you know which subpopulations matter.- Reservoir sampling: stream-friendly algorithm to get a uniform sample when you can’t store data or don’t know total size in advance. Useful for single-pass ETL validation on logs.- Hash-based deterministic sampling: compute hash(key) mod N to select rows (e.g., 1%). Reproducible, easy to join samples across tables, and ideal for deterministic monitoring pipelines.

Trade-offs:- Bias vs variance: stratified reduces variance for strata-specific estimates but requires correct strata choice. Random is unbiased but higher variance for subgroups.- Operational cost: reservoir and hash-based are single-pass; stratified may require pre-aggregation or indices.- Reproducibility: deterministic hash or seeded RNG gives exact repeatability; reservoir is reproducible only if stream order and seed are fixed.

Detecting rare but important issues:- Oversample rare strata (stratified with higher sampling weight) or use focused sampling: sample all records for flagged entities (e.g., high-value customers) or sample by anomaly score.- Use targeted rule-based filters to pull all records matching suspicious patterns (null-heavy columns, outliers, schema violations) in addition to random sampling.- Run periodic full-pass checks on incremental slices (e.g., daily partitions) or use bloom filters/count-min sketches to quickly surface rare keys deserving full inspection.- Monitor derived metrics over time (error rates, missingness); sudden deviations trigger a full scan of the affected partition.

Practical workflow for a data analyst:1. Start with deterministic hash-based 1% sample (reproducible).2. Add stratified oversamples for small but critical segments.3. Run rule-based checks and anomaly scoring; materialize candidates for full inspection.4. Log sampling provenance (method, seed, hash function, timestamp) so stakeholders can reproduce findings.

This combination balances cost, reproducibility, and the ability to detect and escalate rare but impactful data quality problems.

EasyTechnical

42 practiced

You have a Postgres table `users(user_id INT PRIMARY KEY, email TEXT, signup_date TIMESTAMP, last_login TIMESTAMP)` containing 5M rows. Write a SQL query that returns a row per column with the number of NULLs and the number of empty-string values (for text columns). Explain any assumptions and discuss how you'd run this check across dozens of tables efficiently.

Sample Answer

Approach: For each column return counts of NULLs and (for text columns) empty-string values. I'll assume standard Postgres, empty string only applies to text-like columns, and table is large (5M rows) so queries should be I/O-conscious.

SQL (per-table, explicit columns):

sql

SELECT
  'user_id' AS column_name,
  COUNT(*) FILTER (WHERE user_id IS NULL) AS null_count,
  NULL::bigint AS empty_string_count
FROM users
UNION ALL
SELECT
  'email',
  COUNT(*) FILTER (WHERE email IS NULL),
  COUNT(*) FILTER (WHERE email = '')
FROM users
UNION ALL
SELECT
  'signup_date',
  COUNT(*) FILTER (WHERE signup_date IS NULL),
  NULL::bigint
FROM users
UNION ALL
SELECT
  'last_login',
  COUNT(*) FILTER (WHERE last_login IS NULL),
  NULL::bigint
FROM users;

Notes:- FILTER with COUNT avoids scanning multiple times per row; Postgres will still scan table once if planner consolidates — use a single SELECT with multiple aggregates instead:

sql

SELECT
  COUNT(*) FILTER (WHERE user_id IS NULL) AS user_id_null,
  COUNT(*) FILTER (WHERE email IS NULL) AS email_null,
  COUNT(*) FILTER (WHERE email = '') AS email_empty,
  COUNT(*) FILTER (WHERE signup_date IS NULL) AS signup_date_null,
  COUNT(*) FILTER (WHERE last_login IS NULL) AS last_login_null
FROM users;

- For dozens of tables: generate queries from information_schema (columns) and produce dynamic SQL (psql, Python, or a stored procedure) that loops tables, only checks text-like columns for empty strings. Run during off-peak, use ANALYZE/appropriate indexes if selective, or sample (TABLESAMPLE SYSTEM) for approximate metrics on very large datasets. Store results in a monitoring table for trend tracking.

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.