Data Quality and Edge Case Handling Questions

Practical skills and best practices for recognizing, preventing, and resolving real world data quality problems and edge cases in queries, analyses, and production data pipelines. Core areas include handling missing and null values, empty and single row result sets, duplicate records and deduplication strategies, outliers and distributional assumptions, data type mismatches and inconsistent formatting, canonicalization and normalization of identifiers and addresses, time zone and daylight saving time handling, null propagation in joins, and guarding against division by zero and other runtime anomalies. It also covers merging partial or inconsistent records from multiple sources, attribution and aggregation edge cases, group by and window function corner cases, performance and correctness trade offs at scale, designing robust queries and pipeline validations, implementing sanity checks and test datasets, and documenting data limitations and assumptions. At senior levels this expands to proactively designing automated data quality checks, monitoring and alerting for anomalies, defining remediation workflows, communicating trade offs to stakeholders, and balancing engineering effort against business risk.

EasyTechnical

83 practiced

A partner's CSV uses multiple date formats for the same column (e.g., '2024-01-02', '01/02/2024', '2024-01-02T15:04:05Z'). Describe a robust parsing strategy in an ingestion job, including how to fail gracefully or flag bad records, and how to log parsing ambiguities for later investigation.

Sample Answer

Approach: normalize with multi-tier parsing + validation, flag failures, and log ambiguities.

Strategy:1. Pre-define common formats (ISO, MM/DD/YYYY, RFC3339) and a liberal fallback (dateutil.parser).2. Try strict parses in order (fast, deterministic). If none match, apply fallback parser.3. Validate result (e.g., year range, timezone presence) and canonicalize to UTC ISO8601.

Error handling & flagging:- Classify records: OK (parsed + validated), WARN (parsed via fallback or ambiguous), BAD (unparsable or invalid).- Emit BAD to a quarantine queue/table for manual review; continue pipeline for OK/WARN.

Logging & observability:- Log sample raw value, chosen parser, parsed value, record id, and source file/row.- Increment metrics: parse_success_by_format, ambiguous_count, bad_count.- Store ambiguous examples (first N per partner + hashes) in S3/DB for investigation.

Example (Python sketch):

python

from dateutil import parser
FORMATS = ["%Y-%m-%d", "%m/%d/%Y", "%Y-%m-%dT%H:%M:%SZ"]

def parse_date(s):
    for fmt in FORMATS:
        try:
            return datetime.strptime(s, fmt), fmt
        except:
            pass
    try:
        return parser.isoparse(s), "fallback"
    except Exception as e:
        return None, str(e)

Best practices: schema registry to advertise expected formats, contract tests with partners, and automated alerts when bad/ambiguous rates exceed thresholds.

EasyTechnical

74 practiced

Describe simple, commonly used methods to detect outliers in numeric data and when you would apply each: z-score, IQR (boxplot), percentile capping, and robust metrics like median absolute deviation. For each method, state assumptions, failure modes, and how you'd implement detection in SQL over a transactions table with columns (user_id, amount).

Sample Answer

Z‑score- What: Standardize values: z = (x - mean)/std; flag |z| > k (commonly 3).- Assumptions: data roughly Gaussian; mean/std are meaningful.- Failure modes: skewed distributions or heavy tails inflate false positives/negatives; sensitive to existing outliers.- SQL (per-user or global): compute mean/std then filter:

sql

WITH stats AS (
  SELECT avg(amount) AS mu, stddev_samp(amount) AS sigma FROM transactions
)
SELECT t.*
FROM transactions t CROSS JOIN stats
WHERE abs((t.amount - stats.mu)/stats.sigma) > 3;

IQR (boxplot)- What: use Q1, Q3, IQR = Q3-Q1; flag x < Q1 - 1.5*IQR or > Q3 + 1.5*IQR.- Assumptions: nonparametric; good for moderate skew.- Failure modes: extreme skew reduces usefulness; quantile estimation at small sample sizes noisy.- SQL:

sql

WITH q AS (
  SELECT
    percentile_cont(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
    percentile_cont(0.75) WITHIN GROUP (ORDER BY amount) AS q3
  FROM transactions
)
SELECT t.*
FROM transactions t CROSS JOIN q
WHERE t.amount < q.q1 - 1.5*(q.q3 - q.q1)
   OR t.amount > q.q3 + 1.5*(q.q3 - q.q1);

Percentile capping (winsorization)- What: cap values at chosen percentiles (e.g., 1st/99th) or flag beyond them.- Assumptions: you choose cutoffs based on domain knowledge or SLAs.- Failure modes: arbitrary cutoffs may remove true signals; hides distribution issues.- SQL (flagging):

sql

WITH p AS (
  SELECT
    percentile_cont(0.01) WITHIN GROUP (ORDER BY amount) AS p1,
    percentile_cont(0.99) WITHIN GROUP (ORDER BY amount) AS p99
  FROM transactions
)
SELECT t.*, 
  CASE WHEN amount < p.p1 OR amount > p.p99 THEN 1 ELSE 0 END AS is_outlier
FROM transactions t CROSS JOIN p;

Median Absolute Deviation (MAD)- What: robust scale: MAD = median(|x - median|); use modified z = 0.6745*(x - median)/MAD; flag |mod_z|>3.5.- Assumptions: robust for skew/heavy tails; median representative.- Failure modes: if MAD=0 (many identical values) needs fallback; small samples noisy.- SQL:

sql

WITH m AS (
  SELECT median AS med FROM (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) AS median FROM transactions) s
),
absdev AS (
  SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY abs(amount - m.med)) AS mad
  FROM transactions, m
)
SELECT t.*, 0.6745*(t.amount - m.med)/NULLIF(absdev.mad,0) AS mod_z
FROM transactions t CROSS JOIN m CROSS JOIN absdev
WHERE abs(0.6745*(t.amount - m.med)/NULLIF(absdev.mad,0)) > 3.5;

Practical guidance- Use robust methods (IQR, MAD) for skewed/transactional money data. Use z-score only when distribution is near-normal after transform (e.g., log). Always examine sample size, consider per-user vs global thresholds, and log/remove vs flag decisions for downstream pipelines.

MediumTechnical

70 practiced

You have a daily ETL that performs several joins and produces an aggregate KPI. On one day the KPI is 20% lower. Walk through a prioritized troubleshooting checklist: what queries and diffs would you run first (row counts, null rates, join cardinalities, recent code changes), and how would you determine whether to rollback, patch, or accept the change?

Sample Answer

First I’d triage quickly (30–60 minutes) to contain impact, then iterate deeper. Prioritized checklist:

1) Quick signal & scope- Verify KPI drop persists and is not dashboarding bug (refresh, cached tiles).- Check downstream consumers and SLAs; alert stakeholders if critical.

2) Baseline diffs (fast, automated queries)- Total row counts for each upstream table vs yesterday/7-day median:

sql

SELECT source, count(*) AS cnt FROM source_table GROUP BY source;

- KPI raw numerator/denominator counts and the aggregate independently:

sql

SELECT SUM(metric_value) AS numerator, COUNT(*) AS denom FROM metric_table WHERE dt = '2025-11-21';

- Null / sentinel rates on key columns:

sql

SELECT col, COUNT(*) FILTER (WHERE col IS NULL OR col = '') / COUNT(*) FROM table;

3) Join-cardinality & referential integrity- Compare join output row counts and key match rates (left vs inner join deltas):

sql

SELECT COUNT(*) AS left_count,
  SUM(CASE WHEN b.key IS NULL THEN 1 ELSE 0 END) AS unmatched
FROM a LEFT JOIN b ON a.key = b.key WHERE a.dt = ...

- Run DISTINCT key cardinalities for join keys yesterday vs today.

4) Data distribution and schema- Check value distributions for critical dimensions (percentiles, outliers).- Confirm schema/no. of partitions, column types, and recent ALTER TABLEs.

5) Pipeline & code changes- Inspect recent commits/deploys in last 24–48 hours for ETL code, SQL, config, feature flags.- Check job logs for errors, retries, late arrivals, changed timestamps, or new warnings.

6) External dependencies and upstream changes- Verify upstream job completion times; check ingestion volumes and API responses.- Check change logs from sources (e.g., partner schema changes).

Decision matrix: rollback vs patch vs accept- Rollback: if a recent deploy/SQL change clearly introduced a bug (regression test failing, join condition changed) and rollback is low-risk; do immediate rollback, then run backfills or patch fix.- Patch (hotfix): if issue is localized (bad null handling, filter bug) and rollback would remove other valid fixes or is high-risk; deploy targeted fix and validate on replayed partition(s).- Accept (no action): if root cause is legitimate business change (seasonality, real upstream drop) confirmed by stakeholders; document, communicate, and adjust alert thresholds or KPI expectations.

Final steps- Run backfills / reprocess affected date(s) after fix, validate against pre-change baseline.- Postmortem within 48 hours: root cause, corrective actions (tests, monitoring: row-count regressions, null-rate alerts, join-loss alerts), and follow-up (automation to prevent recurrence).

EasyTechnical

72 practiced

Explain how window functions behave when partitions contain zero or one row. For example, what does ROW_NUMBER(), LAG(), and AVG() windowed over a partition return in these small partitions? How would you guard analytical logic that expects at least 2 rows per partition?

Sample Answer

Brief answer: window functions operate per output row. If a partition has zero rows there are simply no output rows for that partition. If a partition has one row, window functions still run for that row but results depend on the function:

- ROW_NUMBER(): returns 1 for the single row.- LAG(col): returns NULL (no prior row); you can supply a default: LAG(col,1,default).- AVG(col) OVER (PARTITION BY ...): returns the value of col (average over that single row).- COUNT(*) OVER (PARTITION BY ...): returns 1.

Example SQL:

sql

SELECT
  id,
  ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY ts) AS rn,
  LAG(value) OVER (PARTITION BY group_id ORDER BY ts) AS prev_value,
  AVG(value) OVER (PARTITION BY group_id) AS avg_in_group,
  COUNT(*) OVER (PARTITION BY group_id) AS cnt_in_group
FROM measurements;

Guarding analytical logic that expects >=2 rows per partition:- Detect small partitions with COUNT(*) OVER and branch: CASE WHEN cnt_in_group >= 2 THEN <calc> ELSE NULL/flag END.- Use LAG(...,1, default) to avoid NULLs when a default makes sense.- Filter out single-row partitions upstream: WHERE group_id IN (SELECT group_id FROM ... HAVING COUNT(*) >= 2).- In pipelines, add data-quality checks/alerts when too many partitions have <2 rows.

Trade-offs: filtering removes rows (may bias results); defaults mask missing data—prefer explicit flags so downstream consumers know which partitions were undersized.

HardSystem Design

89 practiced

Design a deduplication strategy that merges customer records across three databases with conflicting authoritative fields. Explain blocked pairwise matching, scoring, match/no-match/possible thresholds, and how to perform periodic re-evaluation. Include how to scale to tens of millions of records and how to surface uncertain matches for human review.

Sample Answer

Requirements & constraints- Merge customer records from DB-A/B/C where fields conflict and each DB has different authority per field.- Scale: tens of millions of records, ongoing ingest, human-in-loop for uncertain matches.- Goals: high recall for potential duplicates, high precision for auto-merges, auditable provenance.

High-level approach1. Normalize & canonicalize: standardize names, addresses, phones, emails; strip punctuation, normalize Unicode, use libpostal for addresses, extract tokens and phonetic encodings (Soundex/Metaphone).2. Fingerprints & field provenance: create per-record fingerprint + per-field provenance metadata (source, last-updated). Store canonicalized record and metadata in a de-duplicated staging table.

Blocking (blocked pairwise matching)- Use multi-pass blocking to reduce pairwise O(n^2): - Deterministic blocks: exact email, exact phone, national ID, postal code + surname. - Approximate blocks: locality-sensitive hashing (MinHash on name+address tokens) and truncated sorted-ngrams. - Use Bloom filters and inverted indexes (Elasticsearch / Presto for lookups).- For each pass, generate candidate pairs only within blocks; deduplicate candidates across passes.

Scoring (pairwise match model)- Feature engineering: Levenshtein/name similarity, token overlap, email exactness, phone normalized match, address component match, shared provenance, age diff, behavioral signals (same orders).- Model: gradient-boosted trees (XGBoost/LightGBM) trained on labeled pairs; output calibrated probability/confidence.- Combine model score with deterministic rules (e.g., exact SSN → force match).

Match / possible / no-match thresholds- Define thresholds on calibrated probability: - Auto-merge threshold (T_merge): e.g., >= 0.98 → safe auto-merge, update canonical record, merge provenance (store conflicting fields and authoritative resolution rules). - Manual-review threshold (T_review): e.g., 0.6 ≤ score < 0.98 → route to human-in-loop queue. - No-match: score < 0.6 → do nothing.- Allow business-configurable thresholds per cohort (VIP customers stricter).

Conflict resolution / authoritative fields- Maintain field-level authority weights per source; when merging: - If authoritative source exists for a field, use it. - Otherwise, apply winner-by-recentness, confidence-weighted voting, or rule-based fallback.- Record provenance: keep original values, source, timestamp, and resolution decision.

Periodic re-evaluation & incremental updates- Maintain a graph of matched IDs (edges = confirmed pairs) and compute connected components to form clusters/person entities.- Re-evaluate: - Incremental stream: on new/updated records, run blocking for changed records and local scoring; if score > T_merge, update cluster; if in review band, enqueue. - Batch full re-eval: nightly/weekly Spark job recomputes blocks and scores across the dataset to catch transitive merges and drift; re-score existing clusters when authority weights or model changes.- Versioning: store entity version history and maintain immutable audit log for rollbacks.

Scaling to tens of millions- Use Apache Spark for distributed blocking and pairwise scoring; broadcast small lookup tables (e.g., authoritative source weights), partition by block key to localize pair generation.- Use approximate nearest neighbor (FAISS) for vectorized name/address embeddings for large approximate joins.- Avoid materializing all pairs: implement MapReduce-style generation of candidate pairs per block, then filter by cheap heuristics before expensive similarity.- Store indices in Elasticsearch or Redis for fast incremental candidate lookup; keep canonical entities in a columnar store (BigQuery / Redshift / Snowflake) or key-value store for low-latency lookups.- Autoscale worker clusters, use caching, and monitor job skew—choose block keys to balance partitions.

Human-in-loop & surfacing uncertain matches- Build review queue that ingests pairs/clusters with score in [T_review_low, T_review_high]; present side-by-side record view with highlighted differences, provenance, and suggested canonical choice.- Provide quick actions: merge, reject, escalate, mark "needs more data".- Capture reviewer decisions as labels for active learning; use to periodically retrain model.- Use SLA-driven prioritization, routing by business domain (fraud team, customer support), and batch-review UIs for high throughput.- Log decisions for audit and to recalculate dependent analytics.

Monitoring, metrics & governance- Track precision/recall on labeled set, merge rate, review queue latency, drift in score distribution.- Provide rollback capability per entity version and ability to re-run merges after model or rule changes.- Governance: policy-driven source authority matrix, PII controls, and explainability for model decisions.

Trade-offs- Multi-pass blocking + approximate NN trades off more compute for higher recall; thresholds balance automation vs. human cost.- Full nightly re-eval ensures transitive merges but is expensive—use incremental first, periodic full recompute for correctness.

This design yields scalable, auditable deduplication with deterministic rules for high-confidence merges, ML scoring for nuanced cases, and human review for uncertainty, all orchestrated with Spark/FAISS/Elasticsearch and a review UI plus active learning for continuous improvement.

Unlock Full Question Bank

Get access to hundreds of Data Quality and Edge Case Handling interview questions and detailed answers.

Join thousands of developers preparing for their dream job.