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.

MediumTechnical

129 practiced

Given a transactional table transactions(transaction_id, user_id, amount, occurred_at), write an ANSI SQL query that flags transactions that are greater than mean + 3*stddev computed over each user's past 365 days using window functions. Explain assumptions and how to handle users with fewer than 5 prior transactions.

Sample Answer

Approach: for each transaction, compute the mean and sample stddev of that user's transactions in the 365 days preceding the current transaction (exclude the current row), then flag if amount > mean + 3*stddev. If a user has fewer than 5 prior transactions we don't flag (set NULL/false) to avoid unstable statistics.

Assumptions:- occurred_at is a DATE or TIMESTAMP.- ANSI SQL supports RANGE with INTERVAL for window frames (if your engine differs, use a correlated subquery or lateral join filtered by date).- Use STDDEV_SAMP for sample stddev; change to STDDEV_POP if desired.

Query (ANSI SQL):

sql

SELECT
  transaction_id,
  user_id,
  amount,
  occurred_at,
  prior_count,
  prior_mean,
  prior_stddev,
  CASE
    WHEN prior_count >= 5 AND prior_stddev IS NOT NULL AND amount > prior_mean + 3 * prior_stddev THEN TRUE
    ELSE FALSE
  END AS is_outlier
FROM (
  SELECT
    t.*,
    COUNT(amount)   OVER w AS prior_count,
    AVG(amount)     OVER w AS prior_mean,
    STDDEV_SAMP(amount) OVER w AS prior_stddev
  FROM transactions t
  WINDOW w AS (
    PARTITION BY user_id
    ORDER BY occurred_at
    RANGE BETWEEN INTERVAL '365' DAY PRECEDING AND INTERVAL '1' DAY PRECEDING
  )
) q;

Notes:- The window frame excludes the current transaction by using "1 DAY PRECEDING". If multiple transactions share the same timestamp and you want to exclude them, adjust ORDER BY to a strict unique ordering (e.g., occurred_at, transaction_id) and use ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING.- For engines not supporting RANGE+INTERVAL on timestamps (e.g., some BigQuery variants), replace the window with a lateral/correlated subquery: - SELECT aggregates FROM transactions t2 WHERE t2.user_id = t.user_id AND t2.occurred_at BETWEEN t.occurred_at - INTERVAL '365' DAY AND t.occurred_at - INTERVAL '1' DAY.- Choosing >=5 prior transactions is a pragmatic quality rule to reduce false positives; you can tune threshold or use robust stats (median + MAD) if heavy-tailed.

MediumTechnical

70 practiced

Explain what a data contract is between producer and consumer teams in a data platform. Describe enforcement and monitoring mechanisms (schema evolution rules, schema registry, CI tests, runtime validators), how to define backwards-compatible changes, and a rollout plan to handle breaking changes in production.

Sample Answer

A data contract is an explicit agreement between producer and consumer teams that defines the structure, semantics, quality, and SLAs of data exchanged on the platform—think: schema, field types, nullability, required business semantics, update frequency, expected cardinality, and allowed evolution paths. It reduces downstream surprises and enables automated checks.

Enforcement & monitoring:- Schema registry: central store (Avro/Protobuf/JSON Schema) with versioning, compatibility settings. Producers must register new versions; consumers can pin to versions or accept compatible upgrades.- Schema evolution rules: adopt strict compatibility policy (e.g., backward, forward, full) per dataset. Example: for event topics use backward compatibility so old consumers can read new producers’ events.- CI tests: producers run unit/integration tests that validate produced sample messages against registered schemas, plus contract tests that assert required fields, types, and sample downstream queries.- Runtime validators: lightweight schema validation in ingestion (Kafka serializers/deserializers, API gateways) and data quality checks (row counts, null rates, value ranges) that emit metrics and alerts.- Monitoring: lineage + telemetry (schema version, producer ID) surfaced in dashboards; automated alerts on schema changes, QCs failing, or consumer deserialization errors.

Defining backwards-compatible changes:- Add optional nullable fields with defaults — backward-compatible.- Narrowing/renaming fields, removing fields, or changing types are breaking unless handled via aliases or converters.- Use semantic versioning for schemas and document compatibility guarantees in the contract.

Rollout plan for breaking changes:1. Design: propose change in contract doc, list impact matrix (consumers, queries).2. Communicate: notify consumers with timeline, migration guide, sample data, and test harness.3. Dual-write / shadow mode: producers emit both old and new schema (or include adapter) so consumers can test without disruption.4. Canary: enable change for a small subset of consumers or traffic; monitor errors/metrics.5. CI gate: require consumers to run integration tests against new schema before graduation.6. Cutover: after validation, flip default schema and deprecate old version with clear deprecation timeline.7. Rollback plan: keep ability to revert producer changes and coordinate cutback immediately if key metrics degrade.

As a data scientist I’d insist on clear semantic docs, automated contract tests in CI, and dashboards for schema/version adoption—these minimize surprises and let teams safely evolve data.

MediumTechnical

77 practiced

Design an attribution approach for conversions across multiple channels (email, paid, organic). Describe edge cases: multiple events with identical timestamps, missing channel information, partial duplicates across sources, and how to aggregate attribution at campaign and user-level. Explain validation strategies to ensure the attribution pipeline is reliable.

Sample Answer

Approach / framework:- Goal: assign conversion credit across email, paid, organic using a reproducible, auditable multi-touch model (configurable: last-touch, time-decay, linear).- Inputs: event stream (impressions, clicks, opens), user_id, event_timestamp, channel, campaign_id, event_type, conversion_id, attribution_window.

Design:1. Sessionization & attribution window: group touch events per user into sessions and into an attribution window (e.g., 30 days before conversion). Keep ordered event list per conversion.2. Multi-touch engine: implement pluggable strategies: - Last-touch: full credit to last non-direct channel. - Linear: equal credit to all touches in window. - Time-decay: weight touches by recency (e.g., exponential decay).3. Mapping rules: normalize channel and campaign identifiers (source, medium, campaign) with lookup tables and heuristics.

Edge cases:- Identical timestamps: break ties deterministically by event priority (click > impression > open) then by source id. If still identical, split credit evenly among tied events.- Missing channel info: infer from referer/landing page UTM; if impossible, label "unknown" and optionally allocate as null or proportionally distribute to known touches per strategy.- Partial duplicates across sources: deduplicate by event fingerprint (user_id, event_type, timestamp, campaign). If partial overlap (same click recorded in paid and analytics), prefer server-side tracked source; otherwise use confidence score to pick primary or split credit.- Multiple conversions per user: attribute touches to nearest conversion without reuse unless business rule allows shared touches (deduct used credit).

Aggregation:- User-level: store per-conversion attribution vector (channel:credit) and aggregate by user to compute lifetime channel credit, retention-weighted metrics.- Campaign-level: roll up attribution using campaign_id mapping; support both flat and hierarchical aggregation (channel → campaign → creative). Persist both raw event-level attributions and daily aggregates for speed.

Validation & reliability:- Unit tests for tie-breaking, windowing, dedupe logic.- Canary pipeline: run new model in parallel and compare to baseline on sample cohort.- Reconciliation checks: sum attributed conversions equals observed conversions; sum of credits per conversion === 1.0 tolerance check.- Data-quality monitors: alert on spikes in "unknown" channel, negative timestamps, or missing user_id rates.- Backtests: sanity-check expected lift patterns (e.g., paid spikes should reflect in short-term attributions).- Auditable logs: store raw inputs and attribution decisions for sampled conversions to permit root-cause debugging.

Trade-offs:- Deterministic tie-breaks improve reproducibility but may obscure true intent; confidence-based splitting handles ambiguity at cost of complexity.- Time-decay better models recency but requires hyperparameter tuning and business buy-in.

This design balances accuracy, interpretability, and operational reliability; start with last-touch + dedupe and monitoring, then iterate to time-decay with backtested validation.

EasyTechnical

90 practiced

Implement a Python function safe_divide(a, b) that performs element-wise division for NumPy arrays or pandas Series and returns np.nan where division-by-zero would occur. The function should handle scalar inputs, preserve vectorized performance for large arrays, and avoid Python loops. Explain performance trade-offs for very large inputs and chunking strategies.

Sample Answer

Approach: use NumPy’s vectorized operations to avoid Python loops. For arrays use np.divide with the where= parameter and an out buffer prefilled with np.nan so division-by-zero yields np.nan without raising warnings. For pandas Series preserve index and name. Support scalars by treating them as 0-d arrays or using Python division with zero check.

python

import numpy as np
import pandas as pd
from numbers import Number

def safe_divide(a, b):
    """
    Element-wise divide a by b, returning np.nan where b == 0.
    Works for: scalars, numpy arrays, pandas Series.
    Preserves pandas index/name and avoids Python loops.
    """
    # Scalars
    if isinstance(a, Number) and isinstance(b, Number):
        return np.nan if b == 0 else a / b

    # Pandas Series: operate on values and rebuild Series
    if isinstance(a, pd.Series) or isinstance(b, pd.Series):
        # align if mixed Series/scalar/array
        a_s = pd.Series(a) if not isinstance(a, pd.Series) else a
        b_s = pd.Series(b) if not isinstance(b, pd.Series) else b
        # Use underlying numpy arrays for fast vectorized op
        out_vals = np.full(a_s.shape, np.nan, dtype=np.result_type(a_s.values, b_s.values, float))
        np.divide(a_s.values, b_s.values, out=out_vals, where=(b_s.values != 0))
        return pd.Series(out_vals, index=a_s.index, name=a_s.name)

    # Numpy arrays or array-like
    a_arr = np.array(a)
    b_arr = np.array(b)
    out = np.full(a_arr.shape, np.nan, dtype=np.result_type(a_arr, b_arr, float))
    np.divide(a_arr, b_arr, out=out, where=(b_arr != 0))
    return out

Key points:- Uses vectorized np.divide + where to stay fast and avoid elementwise Python loops.- Pre-filling out with np.nan ensures zeros map to NaN; dtype uses np.result_type to avoid unexpected integer truncation.- Preserves pandas Series index and name by working on .values and rebuilding the Series.

Complexity:- Time: O(n) (single pass)- Space: O(n) for output (plus input references); out buffer allocated equal to input size

Edge cases:- b contains np.nan: division yields np.nan (consistent).- Infinite values in a or b behave per IEEE rules (np.divide).- Integer inputs cast to float to allow np.nan.

Performance trade-offs & chunking:- For very large arrays that don’t fit memory, chunking reduces peak memory: process slices (e.g., for i in range(0, n, chunk_size): safe_divide on slices and write to disk or preallocated memory). Chunking adds Python loop overhead and must ensure vectorized work per chunk is large enough to amortize loop cost.- For distributed/parallel processing, use Dask arrays/DataFrame to keep the same semantics but handle out-of-core and parallelism automatically.- Choose chunk_size to balance memory (fits L3/L2 cache for best throughput) and overhead (too small → overhead; too large → memory pressure).

MediumTechnical

84 practiced

You have customer records in CRM and support systems with inconsistent names and addresses. Describe a practical approach to merge partial/inconsistent records into canonical profiles: blocking, candidate scoring, similarity metrics (Levenshtein, token set ratio), supervised match models, and use of third-party reference data. How would you measure precision and recall of the matching?

Sample Answer

Situation/task: Given messy CRM/support customer records, I’d build a practical pipeline to deduplicate and create canonical profiles focusing on scalability, accuracy, and measurable metrics.

Approach:1. Blocking (candidate generation)- Use multiple complementary blocks to avoid missing matches: exact email, normalized phone, token-prefix of last name + zipcode, and locality-based geo-buckets.- Implement concatenated blocking keys and locality-sensitive hashing (minhash) for name/address tokens to scale.

2. Pairwise scoring (features & similarity metrics)- Compute field-level similarities: normalized Levenshtein for full name, token_set_ratio (fuzzy token matching) for addresses, Jaro-Winkler for short strings, exact/email/phone flags, numeric distance for house numbers, and TF-IDF cosine on tokenized addresses.- Aggregate into a feature vector including shared domain, edit distances, token overlap counts, and metadata age/source_priorities.

3. Supervised match model- Label a training set using manual review and high-confidence rules (exact email + phone = match) and generate negatives via distant sampling.- Train a gradient-boosted tree (e.g., LightGBM) or logistic regression to output match probabilities; calibrate (isotonic/Platt).- Threshold selection via ROC/precision-recall curves; consider different thresholds for automated merges vs. human review.

4. Use of third-party reference data- Enrich with authoritative sources (address standardization via USPS/Loqate, phone validation, cardholder/name registries) to improve blocking and disambiguation; treat reference matches as strong features.

5. Canonicalization & merging- For matched clusters, resolve canonical values by source trust score, recency, and majority voting; retain provenance and audit logs.

Measuring precision & recall:- Create a holdout labeled test set representative of rare and ambiguous cases.- Compute pairwise precision/recall and also cluster-level metrics: pairwise F1, B-cubed, and pairwise precision/recall to capture clustering quality.- Report precision at different operating points (e.g., automated merge precision >= 98%), and recall for human-review assisted merges.- Use PR curves and choose operating thresholds balancing business cost of false merges vs. missed merges (use cost-sensitive analysis).- Monitor in production with sampling and drift detection; track downstream KPIs (support deflection, repeat contacts) to validate business impact.

Key trade-offs: blocking aggressiveness vs. recall; conservative thresholds for auto-merge to avoid false positives; prioritize explainability and auditability for customer-impacting merges.

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.