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.

HardSystem Design

83 practiced

Design an end-to-end test harness that can validate pipeline changes against production-like data without writing to production datasets. Include strategies for data masking, selecting representative subsets that preserve distribution, running parallel shadow/canary pipelines, diffing outputs while tolerating nondeterminism, and infrastructure and safety measures to prevent accidental write-through.

Sample Answer

Requirements & constraints:- Validate pipeline changes end-to-end on production-like data without modifying prod datasets.- Preserve statistical properties (distributions, cardinalities).- Support parallel shadow/canary runs, tolerant diffing for nondeterminism, and strict no-write guarantees.- Low latency for validation feedback and automated rollbacks.

High-level architecture:Prod Data Source (read-only) → Data Extractor → Data Masker & Sampler → Test Data Store (isolated) → Test Orchestration → Shadow Pipeline (candidate) & Baseline Pipeline (gold) → Diff Engine & Validator → Reporting & Safe Rollout Controller

Key components and responsibilities:1. Data Extractor- Connects via read-only credentials and audited queries.- Exports samples via change-data-capture or snapshot; rate-limited to avoid load spikes.

2. Data Masker- Deterministic tokenization/pseudonymization (HMAC with rotation) for IDs.- Field-level policies: redact PII, preserve formats (length, char classes), maintain referential integrity via consistent mapping tables.- Differential privacy/noise for aggregates where necessary.

3. Representative Sampler- Stratified sampling to preserve distribution across key dimensions (user cohorts, geography, time windows).- Reservoir sampling for streaming; ensure rare-event oversampling with weighted sampling and reweighting metadata for metrics.

4. Test Data Store & Isolation- Dedicated encrypted namespace (separate cloud/project/account), IAM policies denying write-through to prod, network egress controls.- Immutable snapshots; lifecycle automated and audited.

5. Shadow / Canary Runs- Orchestrate parallel runs with identical inputs to baseline and candidate versions.- Resource isolation via namespaces/containers; traffic steering disabled for writes.- Option for "live shadow" where candidate runs on live read traffic but all write operations are sandboxed or stubbed.

6. Diff Engine tolerant to nondeterminism- Deterministic key-based pairing of records.- Multi-tier comparison: - Exact-match for deterministic fields. - Tolerance thresholds (numeric deltas, relative %). - Semantic equivalence (order-insensitive sets, top-K comparisons). - Probabilistic tests (KS test, JS divergence) for distribution-level checks.- Record-level scoring and aggregated SLO checks; configurable tolerances per field/metric.- Replayable logs and hashed checkpoints for audit.

7. Safety & Prevent accidental write-through- Infrastructure controls: separate service accounts, deny-list of prod endpoints, egress firewall, network policies.- CI/CD gating: tests must pass in harness before rollout; automated rollback triggers.- Fail-safe sandboxing: override or mock all sinks (databases, queues) with non-persistent stubs; use immutable credentials that only permit read.- Chaos checks: penetration test on harness to ensure no secrets allow prod writes.- Audit & alerting: alert on any attempt to access prod write endpoints; immutable logs stored separately.

Operational considerations:- Monitoring: metrics for sample representativeness, run-time errors, divergence scores; dashboards & alerts.- Performance: scale test cluster with autoscaling; cap sample size to budget; prioritize critical cohorts.- Governance: policy-driven masking configs, data retention limits, periodic privacy reviews.- Automation: scheduled smoke runs, pull-request triggered validations, and nightly full-regression runs.

Example flow:- A PR triggers harness: extractor takes a stratified snapshot (10k records), masker pseudonymizes, sampler ensures 5% heavy-hitter oversample. Baseline and candidate run in parallel. Diff engine computes per-field deltas; numeric fields allowed ±2% tolerance; distribution divergence must be <0.01 JS divergence. If violations exceed thresholds, CI fails and deployment blocked; detailed artifact (inputs, outputs, diffs) stored for debugging.

Trade-offs:- Full-fidelity vs. privacy: stronger masking reduces debugging fidelity—mitigate with targeted synthetic enrichment.- Cost vs. coverage: smaller representative sets reduce cost but might miss rare regressions—use periodic large-scale runs.- Complexity: maintaining mapping determinism and sampling correctness adds engineering overhead; automate and test harness itself.

This design gives SREs a production-like, safe validation path that preserves data distributions, prevents write-through, tolerates nondeterminism, and integrates with CI/CD and incident controls.

EasyTechnical

89 practiced

Provide a checklist and example defensive parsing strategies an SRE should implement at ingestion to catch and handle data type mismatches and inconsistent formatting. Include concrete examples for date parsing with multiple formats, numeric strings (commas, currency signs), and how to route and log bad records to a dead-letter queue with useful metadata for remediation.

Sample Answer

Checklist — defensive parsing at ingestion- Validate schema early: required vs optional, types, length, enums- Defensive normalization: trim, unicode normalize, remove invisible chars- Multi-format parsers with prioritized formats + strict fallback- Clean numeric input (commas, currency, percent) then cast- Validate ranges and business rules (dates not in future, amounts >= 0)- Idempotency keys and record IDs for tracing- Instrumentation: counters, histograms, sampling for bad-record rates- Dead-letter queue (DLQ): include original payload, error code, parser version, timestamp, source metadata, ingestion offset- Alerting on DLQ rate thresholds and growth

Concrete examples

Date parsing (Python):

python

from datetime import datetime

PREFERRED = ["%Y-%m-%d", "%d/%m/%Y", "%m-%d-%Y", "%Y%m%d", "%b %d %Y"]

def parse_date(s):
    s = s.strip()
    for fmt in PREFERRED:
        try:
            dt = datetime.strptime(s, fmt)
            # business validation
            if dt.year < 1900 or dt > datetime.utcnow():
                raise ValueError("date out of range")
            return dt.isoformat()
        except ValueError:
            continue
    raise ValueError(f"unparseable_date:{s}")

Numeric strings:

python

import re
def parse_amount(s):
    s = s.strip()
    s = re.sub(r"[,\s]", "", s)           # remove thousands separators
    s = re.sub(r"^\$", "", s)             # remove leading $
    s = s.replace("%", "")                # handle percent separately
    if s == "" or s in ("-", "N/A"):
        raise ValueError("empty_numeric")
    return float(s)

Routing bad records to DLQ (design):- On parser exception, wrap into DLQ message: { "orig": <raw>, "error": "unparseable_date", "parser": "v1.2", "source": "kafka-topic-1", "partition": 3, "offset": 1245, "received_at": "...", "sample_hash": "..." }- Push to dedicated DLQ topic or object store (Kafka DLQ, S3 + manifest)- Tag with remediation hints (e.g., expected formats, sample counts)- Emit metric: dlq_count{error="unparseable_date"} and alert when rate > threshold

Observability & remediation- Expose top-N errors, example raw samples, and counts in dashboard- Automate a daily job to sample DLQ and attempt fixes with improved heuristics; create tickets for recurring patterns- Keep parser versions in metadata to reproduce behavior

Why this works- Early rejection + rich metadata makes debugging fast and non-blaming- Prioritized formats reduce false negatives; normalization handles noisy inputs- Metrics + DLQ enable alerting and operational workflow for remediation without blocking pipeline.

MediumTechnical

75 practiced

As an SRE designing a streaming pipeline with Apache Flink or Beam, explain how watermarks and allowed lateness work in event-time processing. Provide recommended configuration choices and trade-offs for two scenarios: (1) supporting late events up to 24 hours with high correctness, and (2) ultra-low-latency analytics where late events should be discarded. Discuss resource and state implications.

Sample Answer

Watermarks and allowed lateness (event-time processing)- Watermarks: stream-wide timestamps that indicate the system believes no events with event-time ≤ watermark will arrive; Flink/Beam use them to trigger event-time window completion.- Allowed lateness: a grace period after a window is initially closed during which late events are still accepted and can update window results (often via retractions or updates).

Recommended configurations & trade-offs

Scenario 1 — support late events up to 24 hours, high correctness- Watermark strategy: use adaptive/periodic watermarks based on observed event-time skew (e.g., BoundedOutOfOrderness with maxOutOfOrderness ≈ expected skew like minutes) but don’t push watermark to end-of-day; instead rely on allowed lateness.- Allowed lateness: set to 24h (or slightly > observed max delay). Enable state retention beyond that (state TTL ≥ 24h + safety margin).- Processing semantics: enable exactly-once sinks where possible; use accumulative windows with retraction or compacted changelog sinks.- Trade-offs: high state size (per-window state kept 24h), heavier RocksDB usage, longer checkpoint sizes and slower restores, more I/O. Increased cost for storage and retention; need retention GC tuning and monitoring.- Operational actions: increase checkpoint interval prudently, scale task managers, monitor watermark lag, and set alerts for state growth and checkpoint duration.

Scenario 2 — ultra-low-latency analytics, discard late events- Watermark strategy: aggressive watermarks (e.g., watermark = current_ingest_time - small epsilon) or ingestion-time watermarks to close windows quickly.- Allowed lateness: set to zero (or very small like seconds). Emit early results and do not accept late arrivals.- Trade-offs: lower correctness for late arrivals but minimal state retention, smaller checkpoints, faster recovery, lower memory/disk. Simpler semantics (no retractions).- Operational actions: prioritize low latency: short checkpoint intervals, small RocksDB caches, high parallelism; monitor dropped-late-event metrics and downstream implications for SLAs.

Resource & state implications (summary)- Long allowed-lateness → large, long-lived state, larger checkpoints and backup storage, slower failover; need scaled I/O, tuned RocksDB and retention policies.- Zero allowed-lateness → minimal state, faster throughput and recovery, but need to accept correctness loss; monitor watermark emission and event-time vs ingestion-time divergence.

As SRE responsibilities: codify SLOs (latency vs correctness), run capacity tests with worst-case skew, add metrics/alerts for watermark lag, state size, checkpoint timeouts, and automate scaling/retention changes based on observed patterns.

HardTechnical

86 practiced

Describe a time when you had to convince stakeholders to accept a trade-off between data correctness and delivery speed (if you have no direct example, outline how you would handle it). Explain how you would quantify business risk, present mitigation options, define acceptance criteria, and ensure transparency and rollback options during release.

Sample Answer

Situation: At my previous company we had a high-priority feature ready to ship before peak shopping season, but the new data ingestion pipeline introduced a small window where data deduplication could miss ~0.5–1% of records under backpressure. Product wanted fast delivery; reliability team worried about customer-facing billing discrepancies.

Task: As the SRE lead, I needed to convince stakeholders to accept a measured trade-off (faster delivery with controlled correctness risk) or delay deployment.

Action:- Quantified business risk: estimated impacted users = 0.5% of daily active users ≈ 2,500 users; median revenue per user $12 → expected revenue exposure ≈ $30k/day peak. Also modeled downstream cost (support load ≈ +15 tickets/day, SLA penalty risk < $5k).- Presented mitigation options with metrics: 1. Delay and fix (full correctness): 3-week delay, eliminates risk. 2. Phased rollout with feature flag + partial correctness: immediate rollout to 5% of traffic, monitor mismatch rate and support signals; hold/backout if mismatch >1% or tickets >X/hour. 3. Hotfix telemetry-first: deploy fast but add compensating reconciliation job within 48 hours to correct missed records.- Defined acceptance criteria (SLO-aligned): observed data-mismatch rate ≤0.5% over 24h window, customer-visible errors <0.05% and support tickets <5/day; rollback thresholds and time-to-rollback ≤15 minutes.- Ensured transparency: published a short risk report and runbook to the stakeholder channel, scheduled daily checkpoints during rollout, and gave ops a pre-authorized rollback plan.- Implemented automation: feature flagging, circuit-breaker that auto-rolls back on threshold breach, and a reconciliation job that runs with progress dashboards.

Result: Stakeholders approved option 2 with the reconciliation job. We rolled out to 5%, monitored closely; mismatch stayed at 0.3% and support impact was minimal, so we progressively increased traffic. The reconciler fixed remaining issues within 24 hours. We delivered on schedule, avoided major customer impact, and the postmortem led to a permanent test harness to prevent regressions.

Learnings: Quantify impact in business terms, pair risks with concrete mitigations and measurable acceptance criteria, and make rollback and monitoring operationally trivial so stakeholders feel safe choosing faster delivery.

EasyTechnical

81 practiced

You're onboarding a new analytics feature that reads a 'user_events' topic. As the SRE, list the checks, documentation, and runtime validations you would require before enabling the feature in production to ensure data contract compatibility and to avoid runtime failures and silent data corruption.

Sample Answer

Checklist I would require before enabling the new analytics feature reading the user_events topic:

Pre-launch checks (static / infra)- Contract validation: confirm topic schema in Schema Registry (Avro/JSON Schema/Protobuf), check backward/forward compatibility with producers and existing consumers.- Serialization/codec test: verify consumer can deserialize messages (local unit tests against representative samples).- Topic config: retention, cleanup.policy, partitions, replication.factor, min.insync.replicas, message.max.bytes match expected throughput and message size.- ACLs and quotas: consumer principal has read permission and adequate quota; producers can’t be throttled unexpectedly.- Offsets and start position: decide earliest/latest/timestamp; ensure consumers won’t reprocess massive backlog.- Capacity planning: estimate throughput/lag and ensure consumer pool, CPU, memory, and network allocated.

Documentation required- Data contract doc: schema fields, types, nullable rules, semantic meanings, required fields, allowed values/enums, version history.- Ingestion/runbook: startup steps, config flags, environment variables, canary rollout plan, rollback procedure.- SLAs & SLOs: acceptable consumer lag, processing latency, data completeness metrics and ownership.- Test vectors: examples of valid/invalid messages used in CI.

Runtime validations & safeguards- Schema enforcement at read time: fail-fast on schema mismatch; log and increment metric for schema-version mismatches.- Input validation: reject or route malformed messages to DLQ with tagging (reason, raw payload).- Sanity checks/guardrails: required-field presence, type checks, bounds checks (timestamps within plausible window), deduplication keys.- Data quality monitoring: daily/real-time checks for volume, unique user counts, rate per event type, null-rate per field, schema drift detection; alert on >X% deviation.- Health metrics & alerts: consumer lag, processing errors, DLQ rate, deserialization errors, throughput; set paging alerts and runbook links.- Canary rollout & shadowing: run consumer in shadow mode in prod to validate without affecting downstream; do small-percentage canary consumer first.- Observability: structured logs, request IDs, trace correlation to producers when possible, metrics tagged by schema version.- Automated CI gates: schema compatibility checks, integration tests that consume/validate messages from a staging topic.

Rollback & post-launch- Immediate kill switch (feature flag) to stop reads, and documented rollback to prior consumer version.- Post-deploy smoke tests: verify event counts, key metrics, and absence of new errors for at least one business cycle.

These checks prevent silent corruption by enforcing contract compatibility, catching malformed data early, and ensuring operational visibility and fast remediation paths.

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.