Data Quality and Anomaly Detection Questions

Focuses on identifying, diagnosing, and preventing data issues that produce misleading or incorrect metrics. Topics include spotting duplicates, missing values, schema drift, logical inconsistencies, extreme outliers caused by instrumentation bugs, data latency and pipeline failures, and reconciliation differences between sources. Covers validation strategies such as data tests, checksums, row counts, data contracts, invariants, and automated alerting for quality metrics like completeness, accuracy, and timeliness. Also addresses investigation workflows to determine whether anomalies are data problems versus true business signals, documenting remediation steps, and collaborating with engineering and product teams to fix upstream causes.

MediumTechnical

77 practiced

As a data scientist, how would you convince product and engineering teams to prioritize fixing an upstream data quality bug that reduced a model's accuracy by 5%? Describe how you would build a business case, present trade-offs, and propose a remediation roadmap with measurable milestones.

Sample Answer

Situation: A production model's accuracy dropped by 5% after a recent upstream data pipeline change. This reduction materially affects downstream metrics (e.g., conversion prediction, automated decisions) and stakeholder trust.

Task: My goal was to get Product and Engineering to prioritize fixing the upstream data quality bug by building a clear business case, presenting trade-offs, and proposing a concrete remediation roadmap with measurable milestones.

Action:- Quantify impact: I calculated the end-to-end business impact of the 5% accuracy drop (e.g., a 5% lift in false positives increased customer support costs by $X/month and reduced revenue by $Y/month). I ran A/B backtests and simulated model outcomes on recent cohorts to show expected revenue and KPI regressions.- Translate technical risk to product metrics: I mapped prediction errors to user-facing metrics (churn, conversion rate, false-block rate) and to engineering costs (retries, manual review workload).- Build options & trade-offs: I proposed three options—(A) immediate hotfix to upstream schema (estimated 1 sprint, high ROI); (B) short-term model-level mitigation (retrain or add feature guards—2 weeks, medium ROI); (C) longer-term pipeline refactor (2–3 sprints, durable but higher cost). For each I listed cost, time-to-value, residual risk, and rollback plan.- Remediation roadmap with milestones: 1) Day 0–3: Contain — add monitoring alert, input validation, and a feature-flagged model fallback. Success metric: alerts firing correctly and fallback reducing error rate by ≥2%. 2) Week 1–2: Hotfix upstream schema/cleaning logic with Engineering. Success metric: upstream data consistency restored; unit/integration tests added; model re-evaluated to recover ≥3% accuracy. 3) Week 3–6: Full pipeline regression tests, CI data quality checks, and post-deploy shadow testing. Success metric: sustained model accuracy within ±0.5% of baseline over 2 weeks and zero production alerts. 4) Month 2–3: Root-cause postmortem, SLA for data quality, and automation of preventative checks. Success metric: zero recurrence in 90 days.- Communicate cadence: Weekly stakeholder updates with dashboarded KPIs (accuracy, business metrics, incident count) and a decision point at end of each milestone.

Result: Framing the problem in dollars and product KPIs, offering pragmatic short- and long-term options, and providing measurable checkpoints secured priority for the hotfix. The hotfix recovered ~3.5% accuracy in week 2, reducing projected monthly losses by ~70%, while the pipeline tests prevented recurrence.

Learning: Product and Engineering prioritize issues tied to concrete business outcomes and clear remediation plans. Presenting options with trade-offs and measurable milestones accelerates alignment and delivery.

EasyTechnical

111 practiced

You notice a sudden 20% drop in weekly active users. Describe a reproducible investigation workflow to quickly determine whether this is a data quality issue or a true business signal. Include SQL checks, logs to inspect, sampling strategies, and stakeholder checks.

Sample Answer

Start with hypothesis framing: is this a telemetry/data pipeline issue or a real drop in user activity? Work reproducibly (document queries, timestamps, versions).

1) Quick scope checks- Compare WAU by day and cohort (last 14, 7, 3 days) to confirm sudden change and exact start time.

sql

SELECT event_date, COUNT(DISTINCT user_id) as wau
FROM events
WHERE event_date BETWEEN '2025-11-20' AND '2025-12-04'
GROUP BY event_date
ORDER BY event_date;

2) Data quality SQL checks- Row counts on raw vs processed tables for the affected window.

sql

SELECT 'raw' as src, COUNT(*) FROM raw_events WHERE event_date = '2025-12-01'
UNION ALL
SELECT 'proc', COUNT(*) FROM events WHERE event_date = '2025-12-01';

- Null / schema drift: check for missing user_id, event_type, timestamp, or new columns.- Partition/ingest lag: verify latest partition timestamps.

3) Logs to inspect- Ingestion pipeline logs (Airflow/Kafka connectors): failures, retries, schema errors.- Application logs around event emission: exceptions, 4xx/5xx, client SDK updates.- CDN/edge errors and auth service logs (rate-limits, token expiry).

4) Sampling & validation- Random sample 100 raw events and trace full pipeline to processed events to confirm transformations preserved user_id and event_date.- Hash-based re-count: compare distinct user_id hash distribution pre/post to detect truncation.- Client-side sampling: query device/user agents to see if a subset (e.g., iOS vX) stopped sending.

5) Cross-check external signals- Sales, payments, support tickets, uptime/incident dashboards, APM (latency, error rate).- Marketing campaigns or experiments paused/ended.

6) Stakeholder checks & next steps- Notify engineering/infra with reproducible findings (SQL snippets, timestamps, sample IDs).- If data issue: open priority bug, rollback/reprocess partitions.- If business signal: run segmentation (geo, cohort, product) and propose hypotheses for product/marketing owners; set monitoring and alerting.

Key outputs to deliver: reproducible notebook/SQL, sample event traces, pipeline error logs, and recommended immediate action (reprocess vs. investigate product cause).

MediumSystem Design

75 practiced

Describe how you would integrate data quality tests into a CI/CD pipeline using tools like Great Expectations or Deequ. Cover types of assertions (schema, value ranges, invariants), when tests should run (pre-merge, post-deploy, nightly), and how failures should be triaged and reported to developers.

Sample Answer

Requirements & scope:- Catch schema breakage, missing/nullable columns, value ranges, uniqueness/invariants, distribution drift.- Fast checks in CI, full checks in deployment and scheduled monitoring.- Clear owner routing, severity levels (blocker vs warn), observability.

High-level architecture:- Developer PR → CI runner (GitHub Actions/GitLab CI) → lightweight data quality tests (sampled data) using Great Expectations or Deequ.- Merge → CD deploy → post-deploy full-reconciliation tests.- Production scheduler (Airflow) runs nightly/weekly suites for drift, cardinality, invariants; results pushed to monitoring & alerting stack.

Types of assertions & where to run:- Schema (column exists, types, nullability): pre-merge (fast) + post-deploy (full).- Value ranges (min/max, percent within bounds): pre-merge on sample; post-deploy full.- Uniqueness/primary-key invariants, referential integrity: post-deploy and nightly.- Statistical/detect-drift (KL, PSI, distribution comparisons): nightly/weekly.

Example Great Expectations (pre-merge quick checks):

python

from great_expectations.profile.basic_dataset_profiler import BasicDatasetProfiler
import great_expectations as ge

df = ge.from_pandas(sample_df)
suite = ge.dataset.Dataset.expect_column_values_to_not_be_null("user_id")
suite += df.expect_column_values_to_be_between("age", min_value=0, max_value=120)

Test cadence & gating:- Pre-merge: unit/QC tests on synthetic or sampled data; must pass for PR to be mergeable.- Post-deploy: run on full production snapshot; failures can block release or trigger quick rollback depending on severity.- Nightly: full monitoring, drift detection, long-running invariants; non-blocking but create alerts/incidents.

Failure handling & triage:- Classify severity: BLOCKER (schema or PK loss) → automatic rollback or hold; HIGH (integrity range breaches) → on-call data engineer + paging; LOW (drift below threshold) → ticketing.- Automated pipeline actions: annotate PR/commit with test results; create a ticket (JIRA/GitHub issue) automatically with failing expectations, sample rows, metrics, and owner tag.- Observability: push metrics to Prometheus/Grafana and logs to ELK. Store expectation history (Great Expectations Data Docs or Deequ metrics repository) to show trend.- Triage playbook: owner (dataset steward) gets notified, reproducer notebook attached, initial triage within SLA (30–60 mins for high severity), rollback if source/system error.

Best practices:- Keep pre-merge tests lightweight and deterministic using representative samples or contract tests.- Maintain expectation-as-code in the repo; review changes like code changes.- Version expectations with dataset schema versions; use feature flags for gradual rollouts.- Regularly review thresholds and retrain drift detectors to avoid alert fatigue.

This approach balances fast developer feedback, safe deployments, and ongoing production quality monitoring while providing clear routing and actionable context for developers and data engineers.

MediumTechnical

65 practiced

Design an approach to detect duplicate user accounts across systems using fuzzy matching. Explain blocking or indexing strategies, string similarity algorithms (for example n-gram TF-IDF, Levenshtein), feature engineering for name/email/phone, and evaluation metrics for pairwise matching at scale for 50M users.

Sample Answer

Requirements & goal:- Produce high-precision, high-recall pairwise duplicate detection across ~50M users with practical runtime and controllable false-positive rate.

Overall approach:1. Preprocessing & canonicalization - Normalize names (lowercase, strip punctuation, remove prefixes/titles), collapse diacritics, split given/family tokens. - Emails: lowercase, canonicalize Gmail-style aliases (remove dots, plus-tags), extract domain. - Phones: remove non-digits, apply country code normalization and format. - Keep provenance (source system) and last-seen timestamps.

2. Blocking / indexing (reduce O(n^2)) - Multi-stage blocking: coarse then fine. - Stage A (coarse): locality-sensitive hashing (minhash on name n-grams + email domain + phone hash) to create buckets; also deterministic rules (same canonical phone or exact email) for high-confidence buckets. - Stage B (sorted-neighborhood / sliding window) on surname + birth-year when available. - Canopy clustering: use cheap similarity (Jaccard on 2-grams) to form overlapping canopies so high-recall. - Aim: reduction ratio >99.9% so candidate pairs manageable (~tens of millions).

3. Pairwise similarity features / feature engineering - Name features: - Character n-gram TF-IDF cosine (2-4 grams). - Levenshtein distance normalized by length. - Jaro-Winkler score, nickname lookup (Bob↔Robert), token overlap, initial match flags. - Email features: - Exact match flag, local-part similarity (Levenshtein), domain match/typo (edit distance on domain), canonical alias match. - Phone features: - Exact match, last-N-digits match, normalized edit distance. - Other features: - Shared addresses (tokenized), date-of-birth exact or partial match, source-system similarity, temporal proximity. - Aggregate: feed numeric features into a classifier (e.g., gradient-boosted trees) or logistic regression to produce match probability.

4. Algorithms & model - Use n-gram TF-IDF + cosine for robust token/character similarity (fast with sparse vectors). - Use Levenshtein/Jaro-Winkler for short strings where transpositions/typos matter. - Train supervised pairwise model with labeled pairs; use calibration (Platt/Isotonic) for probability thresholds. - Use thresholding: high threshold => automatic merge, mid threshold => human review queue.

5. Scalability & engineering - Compute expensive features offline with Spark or Dask; use broadcasting for small lookup tables (nicknames). - Use LSH/minhash and inverted indices to emit candidate pairs cheaply. - Parallelize pair scoring; store top-K candidate matches per user. - Incremental dedupe: ingest windowed updates and only compare new/changed records vs existing canonical clusters.

6. Evaluation metrics (pairwise, practical for 50M) - Pair Precision, Pair Recall, F1 over labeled test set (sample stratified by blocking buckets). - Reduction Ratio (1 - candidate_pairs / all_possible_pairs) to measure blocker efficiency. - Pair-completeness (recall after blocking) to ensure blocking not dropping true matches. - Precision@K or top-1 precision for canonicalization outputs. - ROC/AUC for classifier; but prefer precision-recall curve because class imbalance. - Business metrics: false merge cost, manual review load, % merged automatically.

Edge cases & trade-offs: - Aggressive blocking reduces compute but risks recall loss; tune canopy thresholds. - Strict merge thresholds reduce false merges but increase manual review. - International names/phones need locale-aware normalization and expanded nickname dictionaries.

Validation & production workflow: - Start with sampled labeling (active learning to prioritize ambiguous pairs). - Monitor online metrics (merges, revert rate), periodic re-training, and human-in-the-loop review for borderline cases.

This pipeline balances recall (via overlapping blocks and rich features) with scalability (LSH/minhash + distributed scoring) and provides actionable thresholds for automated vs manual decisions.

MediumTechnical

79 practiced

You see a 4x spike in signups on a Monday. Lay out a step-by-step investigation plan to determine if this is a true business event or a data collection problem. Include checks across traffic sources, instrumentation, cohort analysis, and sample SQL queries or charts you would run.

Sample Answer

High-level goal: quickly determine whether the 4x Monday signup spike is real (business/marketing event) or an artifact (tracking bug, data pipeline issue, bot/abuse).

Investigation plan (step-by-step)

1) Quick sanity checks (5–15 min)- Compare raw event counts vs. user table inserts: if events >> users, instrumentation issue.- Check upstream logs/ingestion failures for that day.SQL:

sql

-- events vs. users created
SELECT date(created_at) AS d,
       COUNT(*) FILTER (WHERE event_name='signup') AS signup_events,
       COUNT(DISTINCT user_id) FILTER (WHERE event_name='signup') AS signup_event_users,
       COUNT(*) FILTER (WHERE event_type='user_create') AS users_created
FROM events
WHERE date(created_at) BETWEEN '2025-11-01' AND '2025-11-10'
GROUP BY d
ORDER BY d;

2) Traffic source breakdown (15–30 min)- Split by channel/utm and compare growth rates. If one source drives the spike (e.g., paid campaign), it's likely real.SQL:

sql

SELECT source, medium, campaign, COUNT(*) AS signups
FROM events
WHERE event_name='signup' AND date(created_at)='2025-12-01'
GROUP BY 1,2,3
ORDER BY signups DESC LIMIT 50;

Chart: stacked bar of daily signups by source (last 30 days).

3) Instrumentation / deployment checks (10–30 min)- Check recent frontend/backend deployments, analytics SDK versions, config changes, feature flags toggled around Monday.- Search for duplicate event emission (client retries, double-binding). Look for identical event_ids or repeating user_ids within seconds.SQL:

sql

SELECT user_id, COUNT(*) AS cnt, MIN(created_at), MAX(created_at)
FROM events
WHERE event_name='signup' AND date(created_at)='2025-12-01'
GROUP BY user_id
HAVING COUNT(*) > 1
ORDER BY cnt DESC LIMIT 100;

4) Cohort & quality analysis (30–60 min)- Cohort new signups by acquisition source and test activation/engagement in first 24–72 hours. If spike users have low downstream engagement, likely bots/spam.SQL:

sql

-- 24h activation rate for Monday signups
WITH monday AS (
  SELECT user_id FROM users WHERE date(created_at)='2025-12-01'
)
SELECT COUNT(*) FILTER (WHERE a.event_count>0)::float / COUNT(*) AS activation_rate
FROM monday m
LEFT JOIN (
  SELECT user_id, COUNT(*) AS event_count
  FROM events
  WHERE event_name IN ('activate','first_action') AND created_at <= (SELECT MIN(created_at) + interval '24 hours' FROM users WHERE date(created_at)='2025-12-01')
  GROUP BY user_id
) a ON m.user_id=a.user_id;

Charts: retention curve (0,1,7 days) for spike cohort vs. baseline.

5) Bot / fraud signals (30–60 min)- Look at IPs, geolocation, user-agent, signup velocity (many signups from same IP or subnet).SQL:

sql

SELECT ip, COUNT(*) AS signups, ARRAY_AGG(DISTINCT user_agent) AS uas
FROM events
WHERE event_name='signup' AND date(created_at)='2025-12-01'
GROUP BY ip
HAVING COUNT(*) > 10
ORDER BY signups DESC;

- Check CAPTCHA failures, payment verification success rates if applicable.

6) Data pipeline integrity (15–30 min)- Verify ETL jobs timestamps, duplicates introduced by reprocessing, or partitioning issues causing double-counting.- Recompute counts from raw logs (S3/BigQuery raw) and compare.

7) Cross-check external signals (15–30 min)- Marketing calendar: campaigns, partner pushes, PR mentions, organic social virality.- Ad platforms: sudden spend or click surge.- Customer support inbox for correlated activity.

8) Synthesize and act- If real growth: attribute sources, compute LTV/quality, recommend scaling, alert marketing/product.- If fake: roll back instrumentation, patch SDK/debounce events, deduplicate historical data, monitor alerts, and communicate incident + impacted metrics.

Reasoning: stepwise from fast checks (are counts in raw and user table aligned?) to root-cause (traffic vs. instrumentation vs. fraud), using cohort engagement to judge business value and protective queries (IP/UA) to detect malicious traffic. Prioritize reproducible SQL and charts for stakeholders and prepare remediation or attribution depending on outcome.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.