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.

MediumTechnical

40 practiced

Describe a canary validation approach for deploying a new ETL transformation or analytics metric. Explain how to select a canary dataset or user segment, the validation checks to run, how to measure impact against the existing pipeline, safety criteria for full rollout, and rollback triggers. Provide examples for revenue and user-count KPIs.

Sample Answer

Framework: treat the change like a controlled experiment—deploy new ETL/metric to a small, representative canary, validate outputs vs baseline, then ramp or rollback based on pre-defined checks and thresholds.

Selecting canary:- Choose a representative but limited slice: e.g., 5–10% of users, one country/region, or a recent-week partition of raw events. For metrics sensitive to user heterogeneity pick stratified sample (new vs returning, device types).- For pipeline-level ETL logic choose a deterministic partition (date range or shard) so comparisons are reproducible.

Validation checks:- Schema & completeness: row counts, null rates, field-level types.- Deterministic parity: for records that should be identical, compare hashes/MD5s.- Aggregation-level checks: compute metric time-series for canary under old and new code.- Statistical checks: mean/median differences, percent difference, CIs; run t-test or bootstrap to detect meaningful shifts.- Logical sanity rules: revenue >= 0, user_count <= DAU upper bound, no negative balances.

Measuring impact:- Compute delta = (new - old) / old for KPIs over same time-window.- Use both point-in-time and rolling-window comparisons; visualize diffs by cohort.- For revenue KPI: compare total revenue, ARPU, and per-transaction distribution. Look for shifts in tail (e.g., big transactions).- For user-count KPI: compare DAU/MAU, new-user attribution, deduplication changes.- Track downstream reports and dashboards for consumer-facing drift.

Safety criteria for full rollout:- All schema and completeness checks pass.- Absolute KPI delta below a tolerance: e.g., revenue delta < 0.5% and user_count delta < 1% (tighten per business sensitivity).- No statistically significant shift in key cohorts (p>0.05) and no increases in error/NULL rates.- No downstream consumer failures or alert spikes for 48–72 hours.

Rollback triggers:- KPI delta exceeds thresholds (e.g., revenue drop >0.5% or user_count drop >1%).- Data quality alerts: missing partitions, schema mismatch, or >X% nulls.- Business signals: order failures, billing anomalies, executive/PM complaint.- If any deterministic parity records differ unexpectedly.

Example — Revenue:- Canary: 5% of transactions by shard.- Run: compare total revenue, ARPU, txn count, and 99th-percentile txn size. Require revenue delta within ±0.3% and no >10% change in high-value txn counts. Roll back if revenue drop >0.5% or increase in refunds.

Example — User-count:- Canary: 10% of user IDs stratified by new/returning.- Run: compare DAU, new-user count, retention curves. Require user_count delta <0.8% and no >2% change in retention at day-7. Roll back if deduplication causes >1% drop in DAU or retention degrades significantly.

Operational notes:- Automate comparisons and alerts; keep an audit trail and data snapshots for forensic.- Communicate thresholds and owners before rollout; run a dry-run (read-only) to preview deltas.This approach balances safety with agility so changes ship confidently.

MediumTechnical

55 practiced

Design a statistical sampling plan for manual auditing of financial transactions when validating every record is too costly. Describe the sampling method (random vs stratified), how to choose strata (e.g., transaction size, region), how to estimate sample size for a target confidence and margin of error, and how to weight sampled findings to estimate total error across the population.

Sample Answer

Situation: You need a practical sampling plan to audit financial transactions when 100% review is infeasible.

Approach (method):- Use stratified random sampling. Stratify because errors are not uniformly distributed; stratification reduces variance and ensures coverage of high-risk groups.- Typical strata: transaction size buckets (e.g., <$1k, $1k–$50k, >$50k), business unit/region, payment type, and risk score from rules/ML.

Choosing strata:- Prioritize strata that drive risk or volume: high-dollar transactions, new vendors, regions with prior findings.- Keep strata few (4–8) to keep logistics manageable; within each stratum, draw simple random samples.

Estimating sample size:- For estimating a proportion of erroneous transactions, use n0 = (Z^2 * p*(1-p)) / ME^2. - Z: z-score for desired confidence (1.96 for 95%) - p: anticipated error rate (use pilot or conservative 0.5) - ME: desired margin of error (absolute, e.g., 0.02 for ±2%)- Apply finite population correction (FPC) if N small: n = n0 / (1 + (n0-1)/N).- Example: N=100,000, target 95% conf, ME=1%, p=0.02 → n0 ≈ (1.96^2*0.02*0.98)/0.0001 ≈ 753; with large N, n≈753.

Allocation across strata:- Neyman allocation (optimal): n_h = n * (N_h * S_h) / Σ(N_j * S_j), where S_h is within-stratum std dev (for binary error S_h = sqrt(p_h*(1-p_h))). If S_h unknown, use proportional to N_h or to sqrt(N_h).

Weighting and estimation of total error:- For each stratum h, compute sample error rate p̂_h = x_h / n_h.- Estimate total number of error transactions: T̂ = Σ_h (N_h * p̂_h).- Variance: Var(T̂) = Σ_h (N_h^2 * (1 - f_h) * p̂_h*(1-p̂_h)/n_h), where f_h = n_h/N_h (FPC).- For dollar impact, compute average error amount within sampled erroneous records and weight similarly: dollar_total = Σ_h (N_h * p̂_h * avg_loss_h).

Practical notes:- Pilot sample to estimate p_h and S_h.- Oversample high-dollar/top-risk strata; use stratified sampling plan documented for defensibility.- Use conservative p=0.5 if no prior data to ensure sufficient sample size.- Report confidence intervals for both count and dollar estimates.

MediumTechnical

37 practiced

Propose preventive data-quality controls that should be implemented at source (frontend forms and APIs) to reduce downstream BI issues. Examples to consider include server-side validation, enumerations for categorical fields, required fields, and contract tests. Explain how you would prioritize which controls to implement first and how to roll them out with minimal disruption to product teams.

Sample Answer

Preventive controls at source should focus on eliminating bad data upstream so downstream BI is reliable. Key controls I’d propose:

- Server-side validation: enforce types, ranges, regex for identifiers (email, phone), numeric bounds, date windows. Client-side validation can help UX but must not be the only gate.- Enumerations & canonicalization: use strict enums (or reference tables) for categorical fields; reject or map unknown values. Normalize inputs (trim, case-fold, timezone normalization).- Required fields & conditional logic: mark essential BI fields (user_id, event_type, timestamp) as required; support conditional requiredness for certain flows.- Strong typing & schemas: publish JSON/Avro/Protobuf schemas in a registry; versioned and backward-compatible changes only.- API contract tests & consumer-driven contracts: add CI tests that validate producer payloads against consumer expectations and sample downstream queries.- Idempotency and deduplication keys: require event IDs to prevent duplicates.- Server-side enrichment & provenance: add server-generated timestamps, source identifiers, and processing metadata.- Validation error handling & observability: return clear error codes, log rejected payloads, and emit metrics for validation failure rates.

Prioritization approach:1. Protect the highest-impact pipelines: prioritize controls for datasets that feed reports used in decision-making or billing.2. Frequency × Severity matrix: rank fields by how often they’re written and how bad the downstream impact is (e.g., wrong currency vs. optional comment).3. Low-effort/high-impact quick wins: required fields, server-side type checks, and schema registration first.4. Align with product roadmaps to avoid blocking releases.

Rollout with minimal disruption:- Start with “soft enforcement” mode: emit warnings/metrics for a period before switching to hard rejects.- Use feature flags and opt-in enforcement per service to test impact.- Run contract tests in CI and provide clear migration guides and automated validators developers can run locally.- Establish SLAs and change windows; communicate breaking-change plans and maintain deprecated fields for a negotiated time.- Provide dashboards showing validation errors per client to accelerate fixes and a small cross-functional “data quality” working group to triage issues.

This balances preventing bad data, minimizing product friction, and creating measurable feedback loops so BI teams can trust source data.

MediumSystem Design

41 practiced

Multiple microservices publish events to a central analytics topic but occasionally change event shapes, causing downstream parsing failures. Propose validation strategies including using a schema registry, consumer-driven contract testing in CI, graceful fallback behavior, and monitoring to prevent broken dashboards. Describe how to handle optional vs required fields and schema compatibility rules.

Sample Answer

Requirements:- Prevent event-shape changes from silently breaking dashboards and downstream consumers- Fast feedback to producers when they change schemas- Allow safe, evolutionary changes (add optional fields) but block breaking changes- Provide graceful fallback so dashboards keep working when new/unknown fields appear- Alert BI and engineers on parsing/quality regressions

High-level architecture:Producers → Kafka topic(s) → Schema Registry (Avro/Protobuf/JSON Schema) → Consumers (stream processors, BI ingestion) Alongside: CI contract tests, runtime validation service, monitoring & alerting.

Core components & responsibilities:1. Schema Registry- Central store of canonical schemas per topic+version.- Enforce compatibility rules (BACKWARD / FORWARD / FULL) configured per topic.- Producers must register schemas (or get approval) before deploying changes.

2. Consumer-driven contract tests in CI- Each consumer publishes a contract describing required fields, types, and expectations (e.g., field X must be present).- CI pipeline runs: new producer schema + existing consumer contracts → validate that consumers’ required fields exist and types are compatible.- Use tools like Pact for sync APIs or custom contract checkers that read registry.

3. Runtime validation & graceful fallback- At ingestion, validate messages against schema; reject or route invalid messages to dead-letter topic (DLQ) with metadata.- Consumers should implement tolerant parsing: - Ignore unknown/extra fields (forward compatibility). - Use default values for optional fields. - If a required field is missing or type-mismatched, fall back to: - Use cached last-known value - Mark record as partial and surface in KPI calculations with flags - Route to DLQ for repair

4. Monitoring & alerting- Track schema registration events, validation errors, DLQ size/rate, parsing errors per producer, dashboard error rates, and missing KPI deltas.- Create alerts (pager/slack) when parsing errors exceed thresholds or dashboards show >X% gaps.

Handling optional vs required fields:- Define in schema and consumer contracts: - Required: critical for correctness (e.g., transaction_id, timestamp). Schema registry must block removal or type change (use FULL/ BACKWARD compatibility). - Optional: analytics-enrichment fields (utm, device). Producers may add optional fields freely; consumers must handle absence.- Prefer adding as optional with defaults; to make a field required, follow a deprecation process: - Announce intention, add checks in CI, run in “soft-enforce” mode (log/alert when missing), then flip to required in schema.

Schema compatibility rules (recommendations):- Use BACKWARD compatibility for topics where consumers require older fields (producers can add optional fields).- Use FORWARD compatibility when new consumers read old data.- Use FULL for strict symmetry when both sides evolve.- For critical analytics topics, enforce strict compatibility (FULL) and require change approval (schema review board).

Operational practices:- Maintain schema change playbook and automated changelog.- Version schemas and roll out with feature flags on producer side.- Provide a sandbox topic for producers to test new schema versions against live consumer contracts.

Trade-offs:- Strict compatibility and gating reduce breakages but slow development; mitigate with clear SLAs and fast contract feedback.- Runtime validation adds latency and storage for DLQs but greatly improves traceability.

Outcome:This combination gives fast CI-level protection via consumer-driven contract tests, centralized management and enforcement with a schema registry, runtime safety with validation and graceful fallbacks, and observability so BI teams quickly detect and respond to data-quality issues before dashboards break.

EasyTechnical

30 practiced

You receive an unfamiliar dataset exported from a new product table. Describe a step-by-step data profiling process you would perform before building dashboards. Include example SQL queries or BI-tool checks you would run, key metrics to compute (e.g., row counts, distinct counts, null rates, histograms), and how you'd document and prioritize findings for stakeholders.

Sample Answer

I follow a structured profiling checklist before any dashboard work: data discovery, quality checks, distribution analysis, and documentation/prioritization.

1) Quick discovery- Row count, column list, sample rows.

-- row count and sample
SELECT COUNT(*) AS row_count FROM product_table;
SELECT * FROM product_table LIMIT 50;

2) Column-level metrics- For each column compute distinct count, null rate, min/max, example values, datatype issues.

SELECT
  COUNT(*) AS total_rows,
  COUNT(DISTINCT product_id) AS distinct_product_id,
  SUM(CASE WHEN product_name IS NULL THEN 1 ELSE 0 END) AS null_product_name,
  MIN(created_at) AS min_created, MAX(created_at) AS max_created
FROM product_table;

3) Distribution & anomalies- Histograms / value frequency (top N), outliers for numeric columns.

SELECT price_bucket, COUNT(*) FROM (
  SELECT width_bucket(price,0,1000,20) AS price_bucket FROM product_table
) t GROUP BY price_bucket ORDER BY price_bucket;

- Use BI-tool: Tableau/Looker quick histograms, bar charts for top categories, null heatmaps, and data freshness tiles.

4) Relationship & integrity checks- FK existence, duplicate keys, referential integrity.

-- duplicate primary keys
SELECT product_id, COUNT(*) FROM product_table GROUP BY product_id HAVING COUNT(*)>1;

5) Time-series validation- Check gaps, future dates, timezone consistency.

6) Document & prioritize findings- Produce a profiling report (spreadsheet + dashboard) with: - Severity (Critical/High/Medium/Low) - Impact (accuracy, completeness, timeliness) - Recommended action (fix source, transform, filter) - Owner and ETAExample: "product_name null rate 12% — Critical — blocks product listing dashboard — ask engineering to backfill or exclude — owner: Product Ops."

I prioritize issues that affect KPI accuracy (counts, revenue) and freshness first, then enrichment and cosmetic issues. I hand off the report and a short BI prototype dashboard highlighting risky metrics for stakeholder review.

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.