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

72 practiced

Describe what a Cartesian join is, why it often indicates a bug in an analytics query, and list three practical checks you can perform to detect accidental Cartesian products before running a heavyweight join on a large table.

HardTechnical

70 practiced

Describe an algorithm to compute approximate quantiles (e.g., median, 95th percentile) efficiently for very large distributed datasets without moving all raw data to a single node. Explain trade-offs between accuracy and communication cost and suggest technologies or libraries you would use.

Sample Answer

Approach (high-level)- Use a mergeable summary sketch on each partition/node, send only summaries to a reducer and merge to get approximate quantiles. Two practical sketches: t-digest (good for extremes & non-uniform distributions) and Greenwald‑Khanna (GK; deterministic ε-approx guarantees). Sampling is a cheaper alternative with weaker guarantees.

Example: per-node t-digest sketch, merge centrally.

python

# Requires: pip install tdigest
from tdigest import TDigest

def build_local_digest(values):
    t = TDigest()
    for v in values:
        t.update(v)
    return t

def merge_digests(digest_list):
    master = TDigest()
    for d in digest_list:
        master = master.merge(d)
    return master

# usage: each worker returns pickled TDigest; reducer merges and queries quantiles:
# q = master.percentile(95)  # 95th percentile

Why t-digest/GK- t-digest: very accurate for tails/percentiles, compact, fast, merges linearly; good for skewed data.- GK: provable error ε (rank error), smaller memory for fixed ε, but more complex to implement and less accurate on tails.- Sampling: minimal communication (sample size ~ O(1/ε^2)), but variance; not safe for tail percentiles without large samples.

Trade-offs: accuracy vs communication- Larger sketch size (more centroids in t-digest or smaller ε in GK) → higher accuracy, more bytes per node.- Sampling reduces bytes but increases variance; to get high tail accuracy you need large samples across nodes (high comm).- Latency: sketches merge quickly; avoid repeated full-data shuffles.

Complexity- t-digest: O(n log k) to build (k = centroids), merge O(k) per digest, query O(log k).- GK: O(n log(1/ε)) build, merge cost similar to representation size.

Edge cases- Extremely heavy tails or multi-modal data → increase sketch size.- Very small partitions → sketches may be noisy; consider stratified sampling.- Percentiles near 0/100 need careful centroid resolution.

Technologies- Distributed compute: Apache Spark (approxQuantile uses GK), Flink, Dask.- Storage/ingest: Presto/Trino for ad-hoc, or push sketches into Kafka then aggregate.- Libraries: tdigest (python/java), datasketches (Apache DataSketches offers quantiles sketch with error bounds), Spark's approxQuantile.

Recommendation for a Data Analyst- For dashboards/periodic reports: use t-digest or DataSketches quantiles stored in a metrics store (e.g., persisted per-partition summaries). Tune sketch size for required percentile accuracy (validate on sampled full-data runs).

HardSystem Design

83 practiced

Design an alerting strategy that uses data lineage to prioritize failures. For example, an assertion failed on an upstream table used by 10 downstream dashboards. How would you surface priority, route alerts to the right teams, and avoid alert fatigue? Describe the components and rules you would implement.

Sample Answer

Requirements and constraints:- Surface priority of data-quality failures using lineage so the most business-impacting issues are fixed first.- Route alerts to the right owner(s) and consumers.- Minimize alert fatigue: suppress noise, group related failures, provide clear remediation steps.- Must work with existing tools (SQL jobs, dbt, Airflow, BI tools) and scale as lineage graph grows.

High-level architecture:- Lineage store: canonical directed graph of datasets, tables, views, dashboards, and their owners/SLAs (from dbt, Airflow, BI metadata APIs).- Quality detector: assertion/MTTR checks that emit events (failed assertion, schema drift, row-count anomaly).- Impact calculator: computes downstream impact by traversing lineage and scoring each event.- Alert router & deduper: groups, scores, rate-limits, and routes alerts to channels/people.- Alert UI / Runbooks: human-readable context (affected dashboards, sample failing rows, suggested fixes), feedback loop to adjust ownership/impact.- Observability & metrics: alert counts, mean time to detect/resolve, false-positive rate.

Key components and rules

1) Lineage + ownership metadata- Maintain rich metadata: owners (data engineers, analysts, product), SLAs, dashboard importance (business-critical flag), consumer count.- Example: upstream_table A → used by dashboards D1..D10; owners: eng-team, consumers: finance-team, marketing-team.

2) Impact scoring formula (example)PriorityScore = w1*DownstreamCount + w2*BusinessCriticalCount + w3*FreshnessImpact + w4*AssertionSeverity + w5*DataConsumerSLA- w's tuned with stakeholders (e.g., BusinessCritical weight high).- For the example: DownstreamCount=10, BusinessCriticalCount=3 → score high => urgent.

3) Routing rules- Primary notification to dataset owner + primary consumer owners of top-N affected dashboards.- If PriorityScore > Threshold1: page on-call data-platform or escalation list.- If Threshold1 > score > Threshold2: send targeted Slack/email to owners and dashboard consumers.- Include on-alert payload: lineage subtree, sample rows, failing assertion code, last successful run, rollback points.

4) Grouping & deduplication- Group by root cause: multiple downstream failures from same upstream assertion -> single incident.- Windowing: batch similar alerts in a short time-window (e.g., 5–15 minutes).

5) Avoiding alert fatigue- Dynamic thresholds: require anomalies to be >X% deviation or persist for Y minutes before alerting.- Suppression rules: known maintenance windows, CI runs, transient schema changes auto-muted for short TTL.- Noise filters: only alert on deterministic assertion failures or statistical anomalies that exceed noise floor.- Escalation policy with cooldowns and automatic re-notify if unresolved after increasing intervals.- Provide “soft alerts” (digest emails) for low-priority impacts, not immediate pings.

6) Remediation and feedback- Auto-create incident with runbook links: SQL to reproduce, suggested fixes, rollback procedures.- Allow owners to acknowledge and mark as “investigating” to stop repeated pings.- Post-incident feedback: label false positives and adjust scoring/thresholds.

Observability & KPIs- Track MTTR, alert-to-incident conversion, alerts per owner per week, false-positive rate. Use these metrics to tune weights and suppression rules.

Trade-offs- Tight thresholds reduce missed incidents but increase noise — mitigate with grouping and dynamic thresholds.- Lineage completeness is critical; incomplete lineage reduces routing accuracy — invest in automated ingestion of metadata.

Example flow for the prompt:- Assertion on upstream_table X fails.- Quality detector emits event; Impact calculator finds 10 downstream dashboards, 3 marked business-critical.- PriorityScore exceeds urgent threshold → single grouped alert created, routed to table owner + owners of the 3 critical dashboards, pages on-call if unresolved after 10 min.- Alert contains lineage graph, sample failing rows, query to validate, and suggested rollback query. Owners acknowledge, investigators update incident; system mutes further duplicated alerts until resolved.

HardTechnical

91 practiced

For multi-region data where business date must align to a specific business timezone, design a validation strategy to ensure daily aggregates computed in UTC match those computed when converting event times to the business timezone, especially across DST boundaries. Include SQL checks and test cases.

Sample Answer

Goal: ensure daily aggregates anchored to a business timezone (e.g., America/Los_Angeles) match aggregates computed by converting UTC event_time -> business timezone, including across DST changeovers.

Validation strategy (steps):1. Canonical approach: compute business_date by converting UTC event timestamp to the business timezone and truncating to date; aggregate on that. This is the ground truth.2. Alternative approach: use UTC-based windowing logic (e.g., fixed offsets) or precomputed business_date column — validate it matches canonical approach for all rows, especially around DST transitions.3. Automated SQL checks + targeted test cases around DST boundaries, leap seconds, and ambiguous/skipped local times.

SQL checks (example for Postgres / Redshift with timezone support):

sql

-- 1) Recompute canonical business_date
WITH canonical AS (
  SELECT id, event_time_utc,
    (event_time_utc AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles')::date AS canonical_business_date
  FROM events
),
stated AS (
  SELECT id, business_date AS stored_business_date
  FROM events
)
SELECT s.id, e.event_time_utc, canonical_business_date, stored_business_date
FROM canonical e
JOIN stated s USING (id)
WHERE canonical_business_date <> stored_business_date
LIMIT 100;

sql

-- 2) Aggregate comparison for a date range (including DST days)
WITH agg_canon AS (
  SELECT canonical_business_date AS day, COUNT(*) AS cnt
  FROM (
    SELECT (event_time_utc AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles')::date AS canonical_business_date
    FROM events
    WHERE event_time_utc BETWEEN '2023-03-10' AND '2023-03-15'
  ) t
  GROUP BY 1
),
agg_stored AS (
  SELECT business_date AS day, COUNT(*) AS cnt FROM events
  WHERE event_time_utc BETWEEN '2023-03-10' AND '2023-03-15'
  GROUP BY 1
)
SELECT coalesce(a.day,b.day) AS day, a.cnt AS canon_cnt, b.cnt AS stored_cnt
FROM agg_canon a FULL OUTER JOIN agg_stored b USING(day)
WHERE coalesce(a.cnt,0) <> coalesce(b.cnt,0);

Test cases to include:- DST spring forward day (e.g., second Sunday in March for US): generate events at UTC times that map to the missing local hour (e.g., 02:00 local skipped) — verify mapping and counts.- DST fall back day: generate events in the repeated local hour; ensure conversions include correct offset (use timezone-aware functions) and counts reflect both occurrences.- Boundary seconds: events at 00:00:00 UTC and 23:59:59 UTC around business_date edges.- Historical TZ rule changes: test with older dates if business needs historical accuracy.- Nulls/invalid timestamps: ensure they are flagged.

Additional checks:- Row-level parity: every event has canonical_business_date = stored_business_date.- Aggregate parity across sliding windows (7/30-day) to catch subtle off-by-one shifts.- Monitoring: daily job that runs SQL checks for past N days and alerts on mismatches.

Why this works:- Using DB timezone-aware conversion handles DST rules reliably.- Comparing recomputed canonical values to stored/precomputed values surfaces bugs from naive fixed-offset logic.- Targeted DST test cases ensure edge behaviors (skipped/repeated hours) are validated.

HardTechnical

77 practiced

You are senior data analyst asked to delay a dashboard release because upstream data quality is uncertain. How do you convince product stakeholders that postponing release (to add validations/tests) is the right call? Provide a concise pitch including expected costs, benefits, and short-term mitigations so business can still operate safely.

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.