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

64 practiced

Write a SQL query that computes daily percentage = successful / total from table daily_counts(date, successful int, total int). Ensure you guard against division by zero and return percentage with two decimal places and a boolean flag 'zero_total' when total is zero.

Sample Answer

Approach: use safe division with NULLIF to avoid division-by-zero, compute percentage as successful/total*100 on numeric types, format/round to two decimals, and add a boolean zero_total flag. Below is a portable Postgres-style solution; I'll also note MySQL variants.

sql

SELECT
  date,
  -- percentage as number with two decimals; if total = 0 return 0.00
  COALESCE(ROUND((successful::numeric / NULLIF(total, 0)) * 100, 2), 0.00) AS percentage,
  -- boolean flag indicating total was zero
  (total = 0) AS zero_total
FROM daily_counts;

Key points:- NULLIF(total, 0) returns NULL when total is zero, preventing division-by-zero.- successful::numeric ensures numeric division (integer/integer would truncate).- ROUND(..., 2) limits to two decimal places; COALESCE converts NULL (when total=0) to 0.00.- zero_total is true when total = 0.

MySQL variant (CAST and IF):

sql

SELECT
  date,
  IF(total = 0, 0.00, ROUND(CAST(successful AS DECIMAL(10,4)) / total * 100, 2)) AS percentage,
  total = 0 AS zero_total
FROM daily_counts;

Edge cases: negative totals (validate upstream), very large values (choose appropriate numeric precision), NULL successful/total (handle with COALESCE if needed).

EasyTechnical

66 practiced

Describe practical strategies to guard against division by zero in production code and SQL. Provide short examples in SQL and Python showing safe division, default values, and when you might prefer NULL vs 0 as the result.

Sample Answer

Approach: Prevent divide-by-zero by validating/divisor normalization before division, using DB functions (NULLIF, CASE, COALESCE) in SQL and explicit checks or exceptions in application code. Choose NULL vs 0 based on semantics: use NULL when “undefined/missing” (preserve information), use 0 when mathematically meaningful or required by downstream consumers.

SQL examples:

sql

-- Return NULL when divisor is 0 (undefined)
SELECT numerator / NULLIF(divisor, 0) AS ratio
FROM payments;

-- Return 0 as default instead of NULL
SELECT COALESCE(numerator / NULLIF(divisor,0), 0) AS ratio_default_zero
FROM payments;

-- Conditional safe division with CASE
SELECT CASE WHEN divisor = 0 THEN NULL ELSE numerator::numeric / divisor END AS ratio
FROM payments;

Python examples:

python

def safe_divide(a, b, default=None):
    """Return a/b, or default (None or 0) if b == 0 or inputs invalid."""
    try:
        if b == 0:
            return default
        return a / b
    except TypeError:
        return default

# usage
x = safe_divide(10, 0, default=None)  # returns None (undefined)
y = safe_divide(10, 2, default=0)     # returns 5.0

When to prefer NULL vs 0:- NULL: preserve "undefined" — useful for analytics (AVG ignores NULL), signals data-quality issues.- 0: use when domain semantics say "zero ratio" or consumers expect numeric output (e.g., UI must show 0).

Other strategies:- Validate inputs early (API/model layer).- Log/metric occurrences of zero-divisor to detect data problems.- Use types that prevent invalid states (non-zero constrained types) where applicable.

HardTechnical

63 practiced

A production aggregation intermittently returns NULL for a computed column, but the same query returns values in staging. Describe a systematic debugging approach: how to sample problematic rows, check schema and engine version differences, instrument failing rows to capture context, and implement a long-term fix to avoid recurrence.

Sample Answer

1) Clarify scope & reproduce: confirm exact query, inputs, and timeframe where production returns NULL but staging does not. Run the same query against production for a small, recent time window to reproduce the NULL.

2) Sample problematic rows:- Identify rows that produce NULL: run SELECT ... WHERE computed_column IS NULL LIMIT 100 and save their primary keys and raw inputs.- Compare to non-NULL neighbors: SELECT pk, inputs, computed_column FROM table WHERE pk IN (<sample>) OR pk IN (<nearby>) to find input differences.- Example SQL:

sql

WITH bad AS (
  SELECT id, col_a, col_b FROM agg_table WHERE computed_col IS NULL LIMIT 100
)
SELECT b.*, t.* FROM bad b JOIN raw_inputs t ON t.id = b.id;

3) Check schema & engine differences:- Dump schemas (SHOW CREATE TABLE / information_schema) and diffs between prod and staging (column types, NULLability, collations, defaults, indexes).- Check DB engine/version, optimizer flags, extensions, and SQL mode. Example: SELECT version(), @@sql_mode;- Look for subtle mismatches: integer vs bigint, timezone settings, functional index differences, or different NULL/strict modes.

4) Instrument failing rows to capture context:- Add targeted logging/audit: wrap the computation to log inputs, intermediate values, and error conditions to a durable table or telemetry:

python

# pseudo
try:
  result = compute(inputs)
except Exception as e:
  INSERT INTO audit_compute(id, inputs, step, error, timestamp) VALUES (...)
  raise
else:
  INSERT INTO audit_compute(id, inputs, result, timestamp) VALUES (...)

- If DB-side, create a debug UDF or a stored-proc that writes intermediate stages for rows where computed_col IS NULL.

5) Root-cause analysis & hypothesis testing:- Use the captured context to check for: NULL propagation from inputs, hidden type conversion/overflow, optimizer rewrite bug, divide-by-zero, timezone/locale differences, or race conditions with upstream writes.- Reproduce locally by forcing identical schema, engine version and running the saved inputs.

6) Long-term fixes and prevention:- Fix root cause (e.g., add NULL-safe guards, explicit casts, change expression, fix upstream data).- Add schema constraints or computed column NOT NULL with default/fallback where appropriate.- Add unit & integration tests with the problematic samples; include them in CI.- Add monitoring/alerting: count of NULL computed_col in production and a weekly diff between prod/staging aggregates.- Deploy with canary and feature flag; roll out DB migrations with backfills and verification.- Document the issue and postmortem with action items.

7) Follow-up:- Verify fix in prod via re-run of sampled rows.- Track recurrence and refine alerts.

This approach combines targeted sampling, environment parity checks, focused instrumentation to capture runtime context, and durable fixes (code, schema, tests, monitoring) to prevent recurrence.

MediumSystem Design

85 practiced

Design a deduplication strategy for a high-throughput Kafka event stream where duplicates arrive due to producer retries within a 5-minute window. Explain how to detect duplicates, ensure idempotency at the consumer level, manage state (TTL, storage), and the trade-offs between memory usage, correctness, and throughput.

Sample Answer

Requirements & constraints:- Duplicates arrive within 5-minute window due to producer retries.- High throughput, low latency, best-effort correctness (no heavy global coordination).- Consumer must be idempotent: no double-processing of logically identical events.

High-level approach:1. Producers assign a unique idempotency key per logical event (UUID or composite: producerId + sequence). Consumers use that key to dedupe within a 5-minute sliding window.2. Keep a short-lived lookup of seen IDs with TTL = 5 minutes. If ID exists → drop; otherwise → process and record ID.

Architecture & components:- Kafka topics partitioned by a key that groups logically-related events (so dedupe state can be local to a partition/consumer).- Consumer processing layer (examples: Kafka Streams, Flink, or custom consumer) with a local state store: - Option A: Embedded RocksDB state store (durable, local) + changelog topic for recovery (Kafka Streams). - Option B: External Redis cluster (in-memory, TTL keys), used if you need cross-partition dedupe.- On read: - Check state store for idempotencyKey. - If present and timestamp within 5 minutes => skip. - Else process and write idempotencyKey with current timestamp and TTL.

Example pseudo (consumer):

java

if (!seenStore.contains(idKey)) {
  process(event);
  seenStore.put(idKey, now); // store has TTL 5m
} else if (now - seenStore.get(idKey) > 5min) {
  process(event); // old entry expired logically; overwrite
  seenStore.put(idKey, now);
} else {
  // duplicate: ignore
}

State management & TTL:- Use TTL on entries to auto-evict after 5 minutes.- For embedded RocksDB: implement background compaction and periodic scans or use RocksDB TTL column family to drop old keys.- For Redis: set EXPIRE on keys; use pipelining for high throughput.- Persist state/changelog: - With Kafka Streams, state backed by changelog topic ensures crash recovery and fault tolerance without external coordination. - If using Redis, ensure snapshotting or persistence scheme matches recovery needs (RDB/AOF trade-offs).

Scaling & partitioning:- Partition by idempotency key (or producerId) so dedupe state is partition-local—avoids cross-node lookups and keeps throughput high.- Add more consumer instances and partitions to scale horizontally.- For cross-partition duplicates (if keys not aligned), must use external shared store (Redis) at throughput and latency cost.

Optimizations & trade-offs:- In-memory hash (fast, low latency) vs persistent local store (durable but higher I/O). RocksDB gives durability + good throughput; Redis gives very low latency but adds network hop.- Bloom filter pre-check: reduces store lookups and memory but introduces false positives (may incorrectly drop rare non-duplicates). Use when you can tolerate occasional false drops.- Correctness vs throughput: - Strong correctness: synchronous write of id -> store before processing (ensures no double processing if crash). But increases latency. - Faster throughput: optimistic processing with asynchronous store write; risk of rare duplicates on crash.- Memory usage: keep keys compact (hash keys, fixed-size), TTL strictly enforced, tune retention to avoid memory blow-up.

Failure modes & handling:- Consumer crash after processing but before recording id: use transactional processing where process + store are atomic (Kafka transactions or write-ahead log).- Rebalance: ensure state restoration (use changelog or replication) before resuming processing.- Clock skew: store monotonic processing time rather than event timestamp for TTL checks, or include producer timestamp with bounded skew assumptions.

Recommended implementation for medium complexity:- Use Kafka Streams with RocksDB state store and changelog, partition by idempotency key, TTL management via periodic cleanup. This gives local low-latency checks, durability, and automatic recovery with minimal external infra.

Metrics to monitor:- dedupe rate, store size, eviction rate, false-positive rate (if Bloom filter used), consumer lag, processing latency.

This design balances correctness (local durable state + changelog), throughput (local lookups, partition affinity), and memory (TTL + compact keys; option for Bloom filter). Choose exact trade-offs based on acceptable duplicate risk, latency SLA, and operational complexity.

HardTechnical

63 practiced

Design an A/B experiment to measure the business impact of loosening a data-quality rule that currently rejects 0.5% of incoming events. Describe how to split traffic, metrics to measure (downstream conversion, error rates), sample size/power calculations, safety rollbacks, and how to ensure no production corruption occurs.

Sample Answer

Requirements & goal:- Measure business impact of loosening a data-quality (DQ) rule that currently rejects 0.5% of events. Primary question: does allowing these events increase downstream conversion/revenue net of any costs (errors, downstream failures)?

Experiment design:- Traffic split: three-arm rollout 1. Control (100% current DQ): 49% 2. Treatment (loosened DQ in-path): 49% 3. Shadow-only / safety canary: 2% — events pass through loosened logic but are not processed downstream; used for high-fidelity validation and rollback signals.

Implementation:- Use feature flag + config-driven DQ check so behavior is toggleable per shard.- Ensure deterministic bucketing by stable user or device id to avoid leakage.- Start with canary on small subset of services/nodes, then ramp to full experiment traffic once checks pass.

Metrics (tracked in near real-time & aggregated):- Primary business metric: downstream conversion rate / revenue per user (RPU) attributed to events that were previously rejected.- Secondary metrics: event acceptance rate, downstream error rate (exceptions, schema violations), failed downstream ingestion attempts, processing latency, queue/backpressure metrics, CPU/memory, customer-visible errors.- Safety metrics (alert thresholds): increase in 5xxs, data corruption indicators (schema mismatch %, drop rate), critical pipeline lag > X, fraud signals, SLA breaches.

Sample size & power:- Focus on detecting a minimum detectable effect (MDE) on conversion rate. Example: - Baseline conversion p0 = 5% (example), desired MDE = 5% relative (delta = 0.25% absolute). - Use two-proportion z-test formula or standard power calculators. Rough formula: n_per_group ≈ (Z_{1-α/2}√(2p̄(1-p̄)) + Z_{1-β}√(p0(1-p0)+p1(1-p1)))^2 / (p1−p0)^2 - For α=0.05, power=0.8, p0=0.05, p1=0.0525 => n ≈ several hundred thousand users per arm. Because the affected population is only 0.5% of events, ensure experiment runs long enough to collect sufficient impacted-event counts; consider stratifying on impacted-event indicator and running power calc on subpopulation.

Safety & rollback:- Automated short-term rollback triggers: if any safety metric exceeds threshold (e.g., >3σ increase in errors, >X% pipeline lag), auto-disable feature flag.- Manual review gates after canary phases before ramp.- Maintain immutable logs of raw incoming events and a replay pipeline so any corrupted processing can be reprocessed.

Preventing production corruption:- Shadow testing first: process events by new logic but write outputs to separate topic/namespace — do not merge into production downstream.- Schema validation and strict type checks before accepting events into critical stores.- Idempotent writes and versioned schemas so replays don't double-count.- Throttling: rate-limit accepted events from loosened path to avoid sudden bursts.- Access controls & audit: only deploy via CI with canary rollout and observability dashboards.

Analysis & interpretation:- Pre-specify primary/secondary metrics and hypothesis tests; correct for multiple comparisons.- Run both intent-to-treat (ITT) and per-protocol analyses; also analyze the subset of events that were previously rejected to estimate marginal value.- If positive lift with acceptable safety signals, gradually promote change to full production; otherwise rollback and investigate.

This plan balances statistical rigor, safety-first rollout, and operational controls to avoid production corruption while measuring real business impact.

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.