Validation and Edge Case Handling Questions

Focuses on validating the correctness and robustness of software systems and the data that flows through them, and on identifying and handling boundary conditions before they cause silent failures. Covers input validation and sanitization on both client and server side, schema and type checks, and null or missing value handling. Includes duplicate detection and off-by-one or boundary testing such as pagination limits, date range filters, and value range checks. Also covers validation in data-processing contexts: guarding aggregations and joins against duplicate rows or cartesian-product results, and time zone or DST-aware date range checks. Emphasizes designing code, APIs, and queries that fail safely, produce meaningful errors instead of silent corruption, and are covered by targeted tests for edge cases (malformed input, empty collections, concurrent access, unexpected data shapes).

EasyTechnical

90 practiced

Write a Python function using pandas that takes a DataFrame and a schema dict mapping column names to expected dtypes (e.g., {'id':'int64','amount':'float64','ts':'datetime64[ns]'}). The function should return rows where types don't match and attempt safe coercions (parse datetimes, convert numeric strings). Describe behavior on coercion failures and nulls.

Sample Answer

Approach: iterate schema, for each column attempt a safe coercion to the expected dtype (datetime parsing, numeric conversion). Record rows where values are non-null and (a) original value already has wrong dtype or (b) coercion fails (produces NaN/NaT) or (c) value remains not of expected type. Return a DataFrame of offending rows with columns: original value, coerced value, and error reason.

python

import pandas as pd
import numpy as np

def validate_and_coerce(df: pd.DataFrame, schema: dict) -> pd.DataFrame:
    """
    Validate DataFrame columns against schema and attempt safe coercions.
    Returns rows (subset of original df) that still don't match after coercion,
    with extra columns: <col>__coerced and <col>__issue for each problematic col.
    Nulls (None/np.nan/pd.NaT) are NOT considered mismatches.
    """
    df = df.copy()  # avoid mutating caller's df
    issues = pd.DataFrame(index=df.index)

    for col, expected in schema.items():
        if col not in df.columns:
            issues[f"{col}__issue"] = f"missing_column"
            continue

        orig = df[col]
        coerced = orig.copy()

        # attempt coercion based on expected dtype string
        if "datetime" in expected:
            coerced = pd.to_datetime(orig, errors="coerce", utc=False)
        elif expected.startswith("int") or expected.startswith("float") or expected in ("int64","float64"):
            coerced = pd.to_numeric(orig, errors="coerce")
            # if expecting integer, and coercion produced floats without NaN, try astype
            if expected.startswith("int"):
                # keep NaN as is; convert non-NaN floats that are integral
                coerced = coerced.where(coerced.isna(), coerced.astype("Int64"))  # nullable int
        else:
            # for object/string or other types, cast where safe
            try:
                coerced = orig.astype(expected)
            except Exception:
                # fallback: leave as-is; we'll flag non-matching non-nulls
                coerced = orig

        df[f"{col}__coerced"] = coerced

        # Determine issues: non-null original that failed coercion (isna after coercion)
        bad_mask = orig.notna() & coerced.isna()
        # Also if coerced dtype doesn't match expected for non-null coerced values
        # (e.g., strings that remained strings)
        # For simplicity treat values not convertible as bad; already captured by isna
        issue_series = pd.Series(index=df.index, dtype=object)
        issue_series[bad_mask] = "coercion_failed"
        issues[f"{col}__issue"] = issue_series

    # Combine issues: keep rows where any issue is non-null or any missing column issue
    any_issue = issues.notna().any(axis=1)
    # Prepare output: original columns + coerced & issue columns for rows with problems
    result = pd.concat([df.loc[any_issue, df.columns], issues.loc[any_issue]], axis=1)
    return result

Key behavior:- Nulls (None/np.nan/pd.NaT) are not flagged as mismatches (treated as acceptable missing data).- Coercion failures produce NaN/NaT; those rows are flagged as "coercion_failed".- For integer expected types we use pandas nullable Int64 to avoid losing NaNs.- Original data is preserved; coerced columns are appended for inspection.

Complexity: O(n * c) where n = rows, c = number of columns in schema (dominated by vectorized conversions). Edge cases: missing columns, mixed-type containers, locale-specific numeric formats, timezone-aware datetimes — handle with pre-cleaning if needed.

MediumTechnical

77 practiced

Define a small set of operational data quality KPIs for production datasets: completeness, accuracy, timeliness (freshness), and consistency. For each KPI propose a measurement method (SQL or metric), a suggested threshold for alerting, and a practical remediation action when thresholds are breached.

Sample Answer

Completeness- Measurement (SQL): fraction of non-null required fields per row.

sql

SELECT 1.0 - (COUNT(*) FILTER (WHERE col IS NULL)::float / COUNT(*)) AS completeness
FROM production_table;

- Threshold: alert if < 98% for critical tables, < 95% for others.- Remediation: backfill from source or ETL raw logs; if source missing, mark affected records and notify owners; add input validation and monitor upstream failures.

Accuracy- Measurement (metric): sample-based validation against trusted reference or business rules (rate of rule violations).

sql

SELECT COUNT(*) FILTER (WHERE value NOT BETWEEN min_ref AND max_ref)::float / COUNT(*) AS error_rate
FROM production_table;

- Threshold: alert if error_rate > 1% for numeric KPIs, > 0.5% for financial/PII.- Remediation: quarantine bad records, audit transformation logic, create unit tests for mappings, and retrain upstream data entry/process owners.

Timeliness (Freshness)- Measurement (SQL): lag between data timestamp and ingestion_time percentile.

sql

SELECT percentile_cont(0.95) WITHIN GROUP (ORDER BY now() - ingestion_time) AS p95_lag
FROM production_table;

- Threshold: alert if p95_lag > SLA (e.g., 1 hour for near-real-time, 24 hours for daily).- Remediation: restart/scale data pipelines, inspect job failures, implement retry/backpressure, fallback to last-known-good snapshot.

Consistency- Measurement (SQL): schema and cross-table referential checks (foreign key violation rate).

sql

SELECT COUNT(*) FILTER (WHERE fk_id IS NOT NULL AND fk_id NOT IN (SELECT id FROM dim_table))::float / COUNT(*) AS fk_violation_rate
FROM production_table;

- Threshold: alert if violation_rate > 0.1% or any new schema mismatch.- Remediation: rebuild affected joins, fix ETL ordering, enforce schema contracts (contracts, tests), and coordinate schema change window with producers.

Notes: instrument these as automated metrics in monitoring (Prometheus/Datadog), create alerting with runbooks, and include business-owner escalation.

HardSystem Design

71 practiced

Design a safe backfill system for batch feature computation that supports partial reruns, is idempotent, and protects downstream models from duplicate updates. Include job checkpoints, transactional writes, validation checks before commit (row counts, checksums), and an approach to test backfills in staging before production promotion.

Sample Answer

Requirements:- Support partial reruns (date/partition range)- Idempotent computation and writes- Prevent duplicate updates to downstream models- Job checkpoints, transactional commits, validation (row counts, checksums)- Staging backfill tests before promotion

High-level architecture:- Orchestrator (Airflow/Luigi) → Compute workers (Spark/Pandas) → Temp storage (parquet on S3) → Feature store staging area (versioned tables) → Validation service → Atomic publisher → Feature store production

Flow:1. Partitioned backfill job created with metadata (feature set, start/end partition, job_id, run_id).2. Checkpointing: orchestrator writes progress to a durable job state table (DB) per partition: queued, running, success, failed, checksum, row_count, attempt_count. Workers update per-partition checkpoints after compute.3. Compute: worker reads source partitions, computes features, writes to partitioned temp location path=/backfills/{job_id}/{partition}/v{run_id}. Use deterministic computation (stable sorting, fixed seeds) to ensure idempotency.4. Validation: after each partition compute, validation service calculates row_count and checksum (e.g., CRC64 over sorted key+feature payload). Compare against expected invariants (no null keys, schema match).5. Transactional commit: once partition passes validation, the publisher moves partition from staging to feature-store using an atomic operation: either (a) transactional table insert with upsert and optimistic concurrency control (bigquery/DeltaLake/Hudi/Iceberg with ACID) or (b) a rename of partition directory and update of partition metadata in a single DB transaction.6. Duplicate protection: writes use upsert with source run_id and a conflict policy: accept only if run_id >= current_run_id for that partition (or use compare-and-swap on version). Include tombstone/soft-delete markers for rollbacks.7. Partial reruns: orchestrator consults job state to run only partitions with status != success or with checksum mismatch. Attempts incremented to avoid infinite retries; manual review flag on repeated failures.8. Safety for downstream models: feature-store exposes a "publish window" flag per partition. Downstream consumers read only partitions with publish_state=COMMITTED and verified=true. Optionally, support feature-store shadowing: write new versions to shadow namespace and switch alias only after full job-level validation.

Validations before commit:- Row count matches expectation and not zero (unless expected)- Checksum equals computed baseline or previous successful run- Schema and data-type checks- Referential integrity (all keys present in entity table)- Statistical checks (distribution shift thresholds), optional alerts

Testing in staging:- Full-scale dry-run in staging with production-like data sampling and production compute cluster size- End-to-end smoke test: run backfill for a small partition range, validate check counts/checksums, publish to staging feature-store, run downstream model eval on staging data; compare metrics vs baseline (AUC, calibration).- Chaos tests: simulate partial failures, network errors, duplicate job submissions to verify idempotency and restart logic.- Canary promotion: promote a subset of partitions to production (blue/green) and run shadow scoring for comparison before full cutover.

Operational notes:- Maintain immutable artifacts: store staging parquet + checksums for audit and rollback.- Monitoring/alerts on validation failures, checksum mismatches, excessive retries.- Clear run metadata for reproducibility (git commit hash, config, secrets masked).- Consider using Delta/Iceberg/Hudi to simplify ACID upserts and time-travel for audits and rollbacks.

This design ensures deterministic, checkpointed backfills, transactional commits, strong validation, and safe promotion path from staging to production while protecting downstream models from duplicate or partial updates.

HardTechnical

96 practiced

Design an experiment to quantify how often labels are late (label delay) and the impact on model training/monitoring. Describe how to instrument label arrival timestamps, measure label completeness over time windows, and incorporate label delay awareness into training and alerting.

Sample Answer

Requirements & goals:- Quantify distribution of label arrival latency (time between event and label ingestion).- Measure label completeness as function of time since event.- Evaluate impact on model training/validation and monitoring (e.g., biased metrics, delayed drift detection).- Produce actionable alerts and training strategies that are delay-aware.

Instrumentation (what to record):- For every labeled instance record: - event_id, event_timestamp (when outcome could first be observed), label_value, label_arrival_timestamp (when label was written to DB), label_source, label_status.- If labels are batched or backfilled, record batch_id and backfill_reason.

Example ingestion snippet (Python):

python

from datetime import datetime
def record_label(event_id, event_ts, label, source, db):
    rec = {
      "event_id": event_id,
      "event_timestamp": event_ts.isoformat(),
      "label_value": label,
      "label_arrival_timestamp": datetime.utcnow().isoformat(),
      "label_source": source
    }
    db.insert(rec)

Measuring label delay & completeness:- Define label_delay = label_arrival_timestamp - event_timestamp (seconds/days).- Compute empirical CDF and percentiles (P50, P90, P99).- Completeness curve C(t): fraction of events from day D that have labels by t days after event.- For rolling windows: compute C_t(w) for events in each week/month to detect trend.

Example completeness computation (Python/pandas):

python

# df columns: event_timestamp, label_arrival_timestamp
df["delay_days"] = (df.label_arrival_timestamp - df.event_timestamp).dt.total_seconds()/86400
def completeness(df, max_days=30):
    out=[]
    for d in range(0, max_days+1):
        out.append((d, (df.delay_days <= d).mean()))
    return out

Experiment design to quantify model impact:1. Offline simulation using historical data: - For a set of reference days, simulate training/validation using only labels that would have arrived within a cutoff t (e.g., 1,3,7,14,30 days). - Retrain or evaluate same model architecture on progressively more complete labels and compare metrics (AUC, calibration, precision@k). - Track metric bias: metric_t - metric_full where metric_full uses final labels.2. Controlled A/B if possible: - Train Model A using labels available within short window (fast labels), Model B using labels with longer window or backfilled labels. Compare downstream business metrics.3. Time-to-detection simulation for monitoring: - Inject synthetic drift or use historical periods with known drift. For each label cutoff t, simulate how long it takes for alerting rules (metric thresholds, population shifts) to detect drift.

Incorporating delay-awareness into training & monitoring:- Training: - Use sample weighting where recent samples with incomplete labels get lower weight or are excluded until sufficient completeness. - Use progressive training: start with older fully-labeled data; gradually incorporate newer data as completeness passes threshold. - Use semi-supervised or weak-label models to leverage unlabeled but feature-rich recent data (e.g., pseudo-labeling, survival analysis for time-to-label).- Validation: - Always report metrics as functions of label completeness (metric@t). Provide expected bias at common operating cutoffs. - Use bootstrapped confidence intervals considering missingness.- Monitoring & Alerts: - Monitor label arrival rate and completeness curve; compute change points in the delay distribution. - Alert conditions: sudden drop in C(7) or increase in median delay beyond historical baseline (e.g., >2σ). - Add “label-staleness” gating to metric alerts: suppress model-performance alerts if completeness < threshold to avoid false positives. - Automated backfill reconciliation jobs and alerts when backfills would materially change metrics (e.g., predicted metric shift > δ).

Key metrics to report:- Delay percentiles (P50/P90/P99), mean delay.- Completeness at business-relevant windows C(1), C(3), C(7), C(30).- Metric bias curves metric_diff(t).- Time-to-detect-drift vs t.

Trade-offs & caveats:- Excluding recent data reduces recency and may miss concept drift; weighting or semi-supervised methods mitigate this.- Backfills can change historical metrics—track and record backfill events.- Ensure timestamps use consistent clocks and timezone handling.

Practical next steps:- Instrument label timestamps immediately.- Run the offline simulation for past 6–12 months, produce completeness dashboards and metric-bias curves.- Define operational thresholds and implement alerting in monitoring stack (Prometheus/Grafana or DataDog).

HardTechnical

92 practiced

Discuss tradeoffs between strict schema enforcement (e.g., Avro/Protobuf with enforced types and compatibility) versus schema-on-read flexibility in a large organization. Consider developer velocity, data quality, backward compatibility, and testing burden. Recommend an approach with guardrails for a company ingesting many third-party feeds.

Sample Answer

Start by framing the two poles briefly: strict schema enforcement (Avro/Protobuf + schema registry, strong typing, declared compatibility rules) guarantees typed, versioned contracts; schema‑on‑read (JSON/Parquet with loose or no enforced schema) maximizes flexibility and rapid exploration.

Tradeoffs by dimension:- Developer velocity: Schema‑on‑read lets analysts and data scientists iterate quickly (ad hoc joins, new features). Strong schemas slow initial change (must evolve schemas, bump versions) but speed downstream consumer integration because contracts are explicit.- Data quality: Strong schemas catch type/field errors at ingest; enforce nullability, enums and defaulting → fewer silent downstream surprises. Schema‑on‑read shifts error detection to consumers and testing, increasing risk of silent corrupt or malformed data.- Backward compatibility: Avro/Protobuf + compatibility settings gives predictable evolution (back/forward compatibility guarantees). Schema‑on‑read requires custom compatibility checks and ad hoc migrations, often brittle.- Testing burden: Strong schemas centralize many checks (reducing consumer test surface) but require producer integration and contract tests when changing schemas. Schema‑on‑read forces each consumer to add parsing/validation, multiplying test effort.

Recommendation for a large org ingesting many third‑party feeds:Adopt a hybrid governed‑flexibility approach:1. Ingest boundary: Validate raw payloads against lightweight open schemas (JSON Schema) and run schema detection; reject or quarantine grossly invalid feeds. Store raw payloads immutably for replay.2. Canonical internal contracts: Define Avro/Protobuf canonical schemas for curated internal topics with a schema registry and compatibility rules (backward/forward as appropriate). Transform validated raw feeds into canonical messages in the ingestion layer.3. Sandbox for analytics: Allow schema‑on‑read access to raw data in a controlled exploratory environment (sandbox datasets or notebooks) so data scientists iterate fast without affecting production contracts.4. Guardrails and automation: - Automated contract tests: CI that runs producer/consumer contract tests on schema changes (like Pact but for data schemas). - Data quality gates: Lightweight expectations (Great Expectations) and streaming alerts to quarantine anomalies. - Versioning policy and migration playbook: clear semantic rules (add non‑nullable only via migration), deprecation windows, and migration tooling. - Metadata, lineage, and cataloging: enforce schema registration, sample data, and owners in a data catalog so consumers discover schemas and expectations. - Monitoring: schema registry metrics, schema drift detection, and alerting for compatibility-breaking writes.

Why this balances well: it preserves downstream reliability and predictable model inputs (critical for reproducible ML) while not killing exploratory velocity. The ingestion validation + canonical contract pattern concentrates testing burden at the ingestion layer and CI, simplifying consumers, improving data quality, and giving predictable backward/forward compatibility for production models.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.