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.

MediumSystem Design

73 practiced

Design a pipeline to normalize free-form mailing addresses from multiple sources for analytics and geocoding. Include steps such as parsing, standardization (abbreviations), external validation (e.g., USPS or address verification API), storage of a canonical address plus a confidence score, handling PO Boxes and international formats, and cost/latency considerations for API usage.

Sample Answer

Requirements:- Normalize free-form addresses from multiple sources for analytics and geocoding.- Output: canonical address, parsed components, confidence score, flag (PO Box, international), geocode lat/lon (if available).- Constraints: low-latency for analytics pipelines, bounded API cost, high data quality for dashboards.

High-level architecture:Ingest (stream/batch) → Parse & Tokenize → Standardize (rules + lookup) → External Validation & Geocoding (batched + cache) → Scoring & Enrichment → Store canonical record + lineage → BI/Analytics.

Core components and responsibilities:1. Ingest: ETL jobs (Airflow / dbt) or Kafka for events; normalize source metadata (source_id, raw_address, ts).2. Parser: Use an address parsing library (libpostal) to split into components (street, city, state, postal). Emit parse-confidence.3. Standardizer: Apply USPS abbreviations, casing, directional normalization, and canonicalize unit formats (Apt vs #). Maintain lookup tables for common misspellings.4. External Validation & Geocoding: - First consult internal cache (Redis) keyed by normalized address to avoid API calls. - Batch requests to third-party APIs (USPS, SmartyStreets, Google) during low-cost windows; use rule: only call when parse-confidence < threshold or address used in geocoding/reporting. - For US addresses prefer USPS/approved vendor for delivery-quality validation; for international use region-appropriate providers. - Rate-limit and retry logic; track API cost-per-call and daily budget.5. Scoring & Decisioning: - Combine signals: parse-confidence, match score from external API, geocode accuracy, presence of ZIP+4—produce 0–1 confidence. - Rules: if PO Box detected, set POBox flag and avoid rooftop geocoding; if international format parsed but missing country, flag for manual review.6. Storage: - Canonical table schema: canonical_id, components (street, unit, city, region, postal, country), lat, lon, confidence_score, validation_provider, validation_status, source_ids (array), first_seen, last_seen, lineage JSON. - Use columnar DB (BigQuery / Snowflake) for analytics; maintain normalized lookup table in OLTP (Postgres) for downstream joins.7. BI Integration: - Expose views for dashboards: best_address (highest confidence), address_change_history, geocode_coverage metrics. - Add data quality dashboards (coverage %, avg confidence, API spend).

Handling PO Boxes and international:- PO Box: detect patterns (PO Box, P.O., Box); mark as non-geocodable for rooftop results; optionally geocode to centroid of ZIP+4.- International: Use libpostal for parsing; route to country-specific validation APIs. If provider unavailable, store components and mark validation_status=UNVERIFIED.

Cost & latency considerations:- Cache aggressively; deduplicate identical addresses before API calls.- Batch calls and use asynchronous enrichment to avoid blocking ingestion; enrichment jobs update canonical record later.- Budget policy: only validate addresses above a usage threshold (e.g., top 10% by user activity) or those used in reporting.- Monitor spend with alerts; fallback to heuristic standardization when API budget exhausted.

Monitoring & ops:- Track match rate, confidence distribution, API latency, error rates, and cost per validated address.- Implement alerting for drops in validation quality or budget exhaustion.- Provide a manual review workflow for low-confidence or business-critical addresses.

Trade-offs:- Real-time validation increases cost/latency; prefer asynchronous enrichment for analytics.- Using top-tier geocoders (high accuracy) raises cost—mix providers based on region and required quality.

EasyTechnical

89 practiced

You have an orders table used by dashboards:

orders(order_id INTEGER, user_id INTEGER, amount DECIMAL, discount_amount DECIMAL, order_date DATE)

Some rows have NULL discount_amount. Write an ANSI SQL query that returns total_revenue and avg_discount_per_order for each day in the last 30 days, ensuring that NULL discount_amount is treated as 0 and days with no orders return zero values appropriate for dashboard display. Explain assumptions about NULLs and empty days.

Sample Answer

Approach: generate the last 30 calendar dates with a recursive ANSI-compatible CTE, left join a daily aggregation of orders, treat NULL discount_amount as 0 using COALESCE, and coerce NULL aggregates to 0 for days with no orders.

sql

WITH dates AS (
  -- recursive ANSI SQL CTE to generate last 30 days (including today)
  SELECT CAST(CURRENT_DATE AS DATE) AS dt, 1 AS n
  UNION ALL
  SELECT dt - INTERVAL '1' DAY, n + 1
  FROM dates
  WHERE n < 30
),
orders_daily AS (
  -- aggregate orders by date, treating NULL discount_amount as 0
  SELECT
    order_date,
    SUM(amount - COALESCE(discount_amount, 0)) AS total_revenue,
    AVG(COALESCE(discount_amount, 0)) AS avg_discount_per_order,
    COUNT(*) AS orders_count
  FROM orders
  WHERE order_date BETWEEN CURRENT_DATE - INTERVAL '29' DAY AND CURRENT_DATE
  GROUP BY order_date
)
SELECT
  d.dt AS order_date,
  COALESCE(o.total_revenue, 0) AS total_revenue,
  COALESCE(o.avg_discount_per_order, 0) AS avg_discount_per_order,
  COALESCE(o.orders_count, 0) AS orders_count
FROM dates d
LEFT JOIN orders_daily o ON o.order_date = d.dt
ORDER BY d.dt DESC;

Key points and assumptions:- NULL discount_amount is treated as 0 (meaning "no discount applied"). Hence we use COALESCE(discount_amount, 0).- AVG(COALESCE(discount_amount,0)) computes average discount per order counting nulls as zero; if you prefer average only over orders that had a discount, use AVG(discount_amount) (but that would exclude NULLs).- Days with no orders will have NULL aggregates from the join; COALESCE(..., 0) converts them to zeros for dashboard-friendly display.- The query returns last 30 calendar days (including today). Adjust the interval if you want trailing 30 full days excluding today.- This solution uses ANSI-standard recursive CTEs and interval arithmetic; some databases provide simpler functions (generate_series) — adapt if your engine supports them.

HardTechnical

63 practiced

A legacy warehouse contains 'event_local_time' WITHOUT timezone information, and many rows were written assuming the server timezone. Design a remediation pipeline to detect likely timezone for each record (using user profile timezone, IP geolocation, event patterns), convert to UTC, backfill corrected fields with an audit trail, and update downstream aggregated tables. Describe confidence scoring, rollback strategy, and testing approach.

Sample Answer

High-level approach: build an idempotent ETL remediation that (1) infers the most likely timezone per record using available signals, (2) converts event_local_time → event_utc, (3) writes corrected fields + audit metadata, and (4) rebuilds downstream aggregates. Prioritize safety: run in staging, validate, then backfill production in controlled batches.

Pipeline steps:1. Extract: pull rows with event_local_time and contextual data (user_profile.timezone, request_ip, account_id, created_at patterns).2. Infer timezone heuristics (see below) producing candidate_tz, confidence_score, and reason_tags.3. Convert: event_utc = localize(event_local_time, candidate_tz) → to_utc().4. Write audit: append to remediation_audit table with original_time, candidate_tz, confidence, converted_utc, applied_at, run_id, rule_id.5. Backfill: update main table with event_utc only when confidence >= threshold rules; otherwise flag for manual review.6. Recompute downstream aggregates incrementally using materialized view refreshes or time-partitioned rebuilds.

Inference heuristics & confidence scoring:- If user_profile.timezone present and matches IP geolocation region → high confidence (0.9).- If IP geolocation maps uniquely to a tz and no profile tz → medium-high (0.75).- If neither, derive tz from event patterns: user activity hours cluster -> infer offset (0.6).- Rule combinations add: confidence = 1 - ∏(1 - w_i) where w_i are evidence weights.- Cap and threshold (e.g., apply automatic conversion if confidence >= 0.8; 0.5–0.8 → staged review; <0.5 → manual).

Audit schema (example):CREATE TABLE remediation_audit ( id UUID, source_table TEXT, pk_value TEXT, original_local TIMESTAMP, candidate_tz TEXT, confidence FLOAT, converted_utc TIMESTAMP, reason_tags JSONB, run_id UUID, applied BOOLEAN, applied_at TIMESTAMP, prev_event_utc TIMESTAMP);

Rollback & safety:- All writes are transactional with run_id. Updates are "upsert" preserving prev_event_utc.- To rollback a run: revert by joining remediation_audit where run_id = X and restore prev_event_utc.- Use feature flags to route analytics to corrected or original columns for A/B.- Keep immutable audit log so every change is reversible and auditable.

Downstream update strategy:- For partitioned aggregate tables, recompute only affected partitions (date ranges derived from converted_utc) using dependency DAG.- Use incremental rebuilds and compare row counts and checksums before swap.- Publish both legacy and corrected aggregates for a transition window; switch when validated.

Testing & validation:- Unit test conversion logic across DST transitions, ambiguous local times (fall back) and non-existent times (spring forward).- Staging backfill on a snapshot; run end-to-end data quality checks: - Row-level assertions: no NULL event_utc where confidence>=threshold. - Statistical checks: hourly distribution shifts, user session durations, unique user counts. - Reconciliation: totals and sums vs original aggregated metrics; compute diff and thresholds.- Canary rollout: apply to small percent of users/accounts; monitor key metrics and alert on anomalies.- Manual review queue for medium/low confidence rows via BI tool (Looker/Metabase) to allow product/ops to flag.

Example conversion SQL (simplified):

sql

UPDATE events e
SET event_utc = (rem.converted_utc),
    updated_at = now()
FROM remediation_audit rem
WHERE e.id = rem.pk_value
  AND rem.run_id = '...'
  AND rem.applied = false;

Operational considerations:- Document rules and maintainability: store rule versions and retrain heuristics if behavior shifts.- Monitor drift: periodic re-score older records if user_profile data changes.- Communicate: inform stakeholders about the change window, expected metric deltas, and provide side-by-side dashboards during transition.

This design balances automation with safety via scored inference, immutable audit trails, targeted backfills, and robust testing and rollback so BI reports remain trustworthy throughout remediation.

MediumTechnical

76 practiced

Given tables:

events(user_id INT, event_type VARCHAR, occurred_at TIMESTAMP)

and users(user_id INT), write an ANSI SQL query to compute daily conversion_rate = conversions / exposures for the last 30 days, where exposures = count of users shown a promo and conversions = count of users who clicked. Ensure the query handles nulls, prevents division-by-zero, and emits 0.00% for days with zero exposures rather than NULL. Explain any assumptions.

Sample Answer

Approach: generate the last 30 calendar dates, aggregate distinct users per day for exposures (promo shown) and conversions (promo click), then compute conversion_rate safely using NULLIF and COALESCE so days with zero exposures emit "0.00%".

Assumptions:- events.event_type uses 'promo_shown' for exposures and 'promo_click' for conversions.- occurred_at is UTC; conversion attribution is by occurred_at::date (event date).- We count distinct users (unique users exposed / clicked) per day.- ANSI SQL — use recursive CTE to generate dates for portability.

SQL:

sql

WITH params AS (
  SELECT CURRENT_DATE AS today, 30 AS days_back
),
dates AS (
  -- recursive CTE to generate last 30 dates including today
  SELECT today AS dt, 0 AS offset FROM params
  UNION ALL
  SELECT dt - INTERVAL '1' DAY, offset + 1
  FROM dates
  JOIN params ON offset + 1 < params.days_back
),
events_by_day AS (
  SELECT
    CAST(occurred_at AS DATE) AS dt,
    event_type,
    user_id
  FROM events
  WHERE occurred_at >= (SELECT today - INTERVAL '29' DAY FROM params) -- last 30 days
),
agg AS (
  SELECT
    d.dt,
    COALESCE(e_ex.pos_users, 0) AS exposures,
    COALESCE(e_conv.conv_users, 0) AS conversions
  FROM (
    SELECT dt FROM dates
  ) d
  LEFT JOIN (
    SELECT dt, COUNT(DISTINCT user_id) AS pos_users
    FROM events_by_day
    WHERE event_type = 'promo_shown'
    GROUP BY dt
  ) e_ex USING (dt)
  LEFT JOIN (
    SELECT dt, COUNT(DISTINCT user_id) AS conv_users
    FROM events_by_day
    WHERE event_type = 'promo_click'
    GROUP BY dt
  ) e_conv USING (dt)
)
SELECT
  dt,
  exposures,
  conversions,
  -- numeric rate (0 when exposures=0), and formatted percent string "0.00%" when exposures=0
  ROUND(
    CASE WHEN exposures = 0 THEN 0.0
         ELSE 100.0 * conversions / exposures
    END
  , 2) AS conversion_rate_pct,
  CASE WHEN exposures = 0 THEN '0.00%'
       ELSE CONCAT(ROUND(100.0 * conversions / exposures, 2), '%')
  END AS conversion_rate_formatted
FROM agg
ORDER BY dt DESC;

Key points:- COUNT(DISTINCT user_id) avoids double-counting users who saw/clicked multiple times.- NULLIF is avoided here because we handle exposures=0 explicitly; both approaches (NULLIF or CASE) prevent division-by-zero.- COALESCE ensures missing aggregate rows become zeros.- Recursive CTE ensures ANSI portability for date generation; on DBs with generate_series you can simplify.

HardTechnical

92 practiced

Design a suite of synthetic test datasets to validate ETL handling of time edge cases: DST start and end transitions across multiple timezones, leap seconds, historical timezone rule changes, ambiguous local times, and inconsistent offsets. Provide concrete example rows and explain the assertions you would run against the ETL output.

Sample Answer

Situation: We need a compact suite of synthetic rows that stress ETL timestamp handling so BI reports (counts, funnels, session durations) remain correct across DST, leap seconds, historical rule changes, ambiguous local times and inconsistent offsets.

Dataset design (CSV columns: event_id, raw_timestamp, reported_tz, reported_offset, user_id, event_type, metadata):

1) DST start (spring forward — missing local hour)1001, 2023-03-12 02:30:00, America/New_York, -05:00, U1, page_view, device=A(02:30 local does not exist; reported_offset -05:00)

2) DST end (fall back — ambiguous hour)1002, 2023-11-05 01:30:00, America/New_York, -04:00, U2, click, device=B1003, 2023-11-05 01:30:00, America/New_York, -05:00, U3, click, device=C(same local time twice with different offsets)

3) Leap second1004, 2016-12-31 23:59:60, UTC, +00:00, U4, logout, device=D

4) Historical TZ rule change (pre/post law)1005, 1990-04-01 12:00:00, Europe/Moscow, +03:00, U5, signup, note=pre-change1006, 1991-06-01 12:00:00, Europe/Moscow, +04:00, U6, signup, note=post-change

5) Ambiguous local time in southern hemisphere DST rules1007, 2019-10-06 02:15:00, Australia/Sydney, +11:00, U7, purchase1008, 2019-10-06 02:15:00, Australia/Sydney, +10:00, U8, purchase

6) Inconsistent device offset (device reports wrong offset)1009, 2024-01-15 09:00:00, America/Los_Angeles, -08:00, U9, session_start (but device_offset -07:00 in metadata)(metadata: reported_offset_mismatch=true)

7) Edge of offset (non-hour offset)1010, 2025-07-01 08:00:00, Asia/Kathmandu, +05:45, U10, alert

Assertions to run after ETL normalization/ingestion:

- Canonical UTC conversion: each row has utc_timestamp = convert(raw_timestamp, reported_tz, reported_offset) matching a trusted tz library (IANA). Assert exact match or flagged if ambiguous. - e.g., for 1002 vs 1003 ensure UTC differs by 1 hour.

- Ambiguity detection: for local times that map to multiple UTCs (fall back), ETL must either: - preserve reported_offset and convert accordingly, OR - if offset missing, flag row as ambiguous and not silently pick an epoch. Assert ambiguous_flag true for 1002/1003 if offset absent.

- Missing local times (spring forward): ETL must either normalize to next valid instant or flag invalid. Assert either corrected_utc present and correction_reason='non-existent local time' or invalid_flag true for 1001.

- Leap second handling: ETL must normalize 23:59:60 to 23:59:59.999Z (or next second) consistently. Assert stored_utc in [2016-12-31T23:59:59Z..2017-01-01T00:00:00Z) and leap_second_flag true.

- Historical rules respected: conversions for 1990/1991 use historical tz database. Assert difference between 1005 and 1006 UTC matches one-hour rule change.

- Offset consistency check: if reported_offset ≠ canonical offset for reported_tz at that instant, create offset_mismatch_flag (1009). Assert ETL records both device_offset and canonical_offset and does not silently overwrite without flag.

- Ordering and aggregation invariants: for a user session spanning DST transitions, duration = utc_end - utc_start. Create synthetic pair across 2023-11-05 00:45->02:15 for same user and assert duration arithmetic yields correct minutes despite repeated local hour.

- Non-hour offsets preserved: verify Asia/Kathmandu converted correctly to UTC (1010 -> 02:15:00Z).

- Round-trip: if ETL stores (utc_timestamp, reported_tz, reported_offset), reconstructing local time must equal original raw_timestamp when using stored offset. Assert equality for all non-ambiguous rows.

Implementation notes:- Use authoritative tz library (tzdata/Olson) in tests.- Provide automated test harness that injects these rows, runs ETL, and verifies assertions; failures should produce exact diff showing conversion mismatch, flags, or data loss.

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.