SQL-Based Data Validation and Anomaly Detection Questions

Techniques for validating data quality and detecting anomalies using SQL: identifying nulls and missing values, finding duplicates and orphan records, range checks, sanity checks across aggregates, distribution checks, outlier detection heuristics, reconciliation queries across systems, and building SQL based alerts and integrity checks. Includes strategies for writing repeatable validation queries, comparing row counts and sums across pipelines, and documenting assumptions for investigative analysis.

EasyTechnical

25 practiced

Given a table orders(order_id INTEGER PRIMARY KEY, customer_id INTEGER, order_date DATE, amount NUMERIC), write a PostgreSQL query to identify duplicate business records defined by the combination (customer_id, order_date, amount). Return groups with count > 1 and show the list of order_ids per duplicate group. Then provide a safe SQL statement to mark or remove duplicates while keeping the lowest order_id.

Sample Answer

To find duplicate business records by (customer_id, order_date, amount) and list order_ids for groups with count > 1:

sql

-- Find duplicate groups and list order_ids
SELECT
  customer_id,
  order_date,
  amount,
  COUNT(*) AS cnt,
  array_agg(order_id ORDER BY order_id) AS order_ids
FROM orders
GROUP BY customer_id, order_date, amount
HAVING COUNT(*) > 1;

Safe approach to mark duplicates (recommended before deleting): add a flag column and set it for rows that are not the lowest order_id in their group.

sql

BEGIN;

ALTER TABLE orders ADD COLUMN is_duplicate boolean DEFAULT FALSE;

WITH ranked AS (
  SELECT order_id,
         ROW_NUMBER() OVER (PARTITION BY customer_id, order_date, amount ORDER BY order_id) AS rn
  FROM orders
)
UPDATE orders
SET is_duplicate = TRUE
FROM ranked
WHERE orders.order_id = ranked.order_id
  AND ranked.rn > 1;

COMMIT;

Safe deletion keeping the lowest order_id (wrap in transaction and verify rows first):

sql

BEGIN;

-- Preview rows that would be deleted
WITH to_delete AS (
  SELECT order_id
  FROM (
    SELECT order_id,
           ROW_NUMBER() OVER (PARTITION BY customer_id, order_date, amount ORDER BY order_id) AS rn
    FROM orders
  ) t
  WHERE rn > 1
)
SELECT * FROM orders WHERE order_id IN (SELECT order_id FROM to_delete);

-- Delete duplicates
WITH to_delete AS (
  SELECT order_id
  FROM (
    SELECT order_id,
           ROW_NUMBER() OVER (PARTITION BY customer_id, order_date, amount ORDER BY order_id) AS rn
    FROM orders
  ) t
  WHERE rn > 1
)
DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM to_delete);

COMMIT;

Notes:- Always run the preview SELECT first and take a backup or run in a transaction you can ROLLBACK.- ROW_NUMBER() picks the lowest order_id to keep; adjust ORDER BY if a different keep rule is desired.

MediumBehavioral

44 practiced

Tell me about a time you investigated an unexpected anomaly in a production dashboard. Structure your answer using the STAR method: explain the situation, your task, the actions you took to validate and investigate (including SQL checks), and the result. Emphasize how you documented assumptions and communicated with stakeholders.

Sample Answer

Situation: Last year our weekly executive dashboard showed a sudden 18% drop in paid-conversion rate for one region overnight. The dashboard is used by marketing and revenue leadership, so this anomaly caused urgent concern.

Task: As the data analyst on call, I needed to validate whether the drop was real, find the root cause, and communicate recommendations and status to stakeholders quickly and clearly.

Action:- Immediate validation: I ran quick aggregation checks to confirm the metric change and scope.

sql

-- check conversions by day and region
SELECT event_date, region, COUNT(*) AS conversions
FROM events
WHERE event_name = 'purchase'
  AND event_date BETWEEN '2025-10-01' AND '2025-10-14'
GROUP BY 1,2
ORDER BY 1;

- Cross-checked denominator and upstream events (clicks, sessions) to see whether drop was numerator or denominator driven.

sql

-- check sessions by tracking source
SELECT event_date, traffic_source, COUNT(*) AS sessions
FROM sessions
WHERE region = 'EMEA'
GROUP BY 1,2;

- Investigated recent deploys and ETL runs: checked pipeline logs and ingestion timestamps; found a schema change in the payment processor feed introduced the prior evening that caused payment_status to be null for some records.- Validated by joining raw payment feed to analytics table and counting mismatches.

sql

SELECT COUNT(*) FROM payments_raw r
LEFT JOIN payments_analytics a ON r.txn_id = a.txn_id
WHERE r.payment_status IS NOT NULL AND a.payment_status IS NULL;

- Documented assumptions and checks in a living incident doc: what I assumed (dashboard formula uses payments_analytics.payment_status='completed'), queries I ran, timestamps, and suspected root cause.- Communicated: sent an initial Slack alert to marketing + engineering, provided interim numbers and ETA, opened a ticket with engineering and tagged the ETL owner, and scheduled a 30-min sync to align on fixes.

Result: Engineering confirmed the schema change and rolled back the feed mapping within 2 hours; metrics returned to expected levels the next hourly refresh. My documentation (queries, evidence, timeline) was attached to the incident ticket; stakeholders received a concise postmortem explaining root cause, corrective action, and two preventive recommendations (schema-contract tests in CI and an alert on sudden metric deltas). This reduced stakeholder follow-ups and led to a new monitoring check that prevented a similar regression two months later.

This episode reinforced the value of quick validation queries, clear assumption logging, and frequent stakeholder updates to maintain trust during incidents.

HardSystem Design

28 practiced

Design a low-latency streaming anomaly detection system using Kafka and Flink (or a similar stack) that can detect metric spikes within 1 minute of occurrence. Describe windowing strategy, state management, handling of late or out-of-order events, mitigation of false positives, backpressure and scaling considerations, and how to persist detected anomalies for downstream reporting.

Sample Answer

Requirements:- Detect metric spikes within 1 minute of occurrence, <60s end-to-end latency, tolerate out-of-order events (~30s), produce alerts reliably for downstream reporting.

High-level architecture:- Sources → Kafka topics (partitioned by entity/metric) → Flink (event-time processing, watermarks) → Anomaly score/alert sink → Kafka alerts topic + OLAP store (ClickHouse/Snowflake) + Monitoring/BI.

Windowing strategy:- Use event-time incremental aggregations with sliding windows (window length 1m, slide 15s) or tumbling 1m with per-event incremental stats to meet latency. Compute running aggregates (count, sum, mean, variance) using keyed streams per metric/entity so partial results emit continuously (every 15s).

State management:- Keyed state in Flink with RocksDB backend, checkpointing enabled (periodic, durable). Keep compact running summaries (Welford’s online variance, EWMA) and recent sample buffers (bounded, TTL ~1 hour). Use state TTL to evict inactive keys.

Late / out-of-order events:- Use event-time with watermark strategy (bounded out-of-orderness ~30s). Allow small allowedLateness to update aggregates; for very late events, route to a corrections topic and optionally recompute historical aggregates offline. Emit provisional alerts immediately and mark as tentative until watermark passes.

Anomaly detection & false-positive mitigation:- Combine statistical methods: z-score on online mean/variance, EWMA for short-term trend, and seasonality-aware baseline (compare to same minute-of-day window). Require multiple signals or threshold crossing for n consecutive emits (e.g., 2 windows) to reduce noise. Add rate-limit / dedup window (suppress repeated alerts for same entity within X minutes). Maintain dynamic thresholds: percentile-based baselines updated continuously.

Backpressure & scaling:- Partition Kafka by entity hash to allow horizontal scaling. Tune Flink parallelism per operator; use asynchronous checkpoints and RocksDB local storage to avoid stalls. Monitor consumer lag and Flink backpressure metrics; autoscale Kafka brokers and Flink task managers based on lag/CPU/heap. Use batching at sink and circuit-breaker logic to avoid downstream overload.

Persistence for reporting:- Publish anomalies to a durable Kafka alerts topic with schema (entity, metric, timestamp, score, context, tentative/final). Sink concurrently to OLAP (ClickHouse/Snowflake) for ad-hoc analysis and dashboards; use exactly-once sinks or at-least-once with idempotent writes. Store raw feature snapshots for postmortem and model improvement.

Trade-offs:- Lower watermark latency reduces detection lag but increases false negatives from late events. Ensemble of lightweight online stats gives low-latency detection; complex ML models can run asynchronously for enrichment and reduced false positives.

Operational notes:- Add alert auditing, feedback loop (analyst/labeling) to refine thresholds, and run periodic backfills to validate model drift.

MediumSystem Design

25 practiced

Design an alerting strategy for a daily file-based ingestion pipeline that expects partner files by 02:00 UTC. Requirements: SLA is 99% of files should arrive within 4 hours of expected time; missing or late files should trigger an incident. Describe what checks you would run, alert thresholds, on-call escalation, retry/backfill procedures, and how to handle false positives.

Sample Answer

Requirements & scope:- Files expected daily by 02:00 UTC per partner. SLA: 99% of files arrive within 4 hours (by 06:00 UTC). Missing/late files -> incident.

Checks to run (automated, scheduled):1. Ingestion heartbeat (02:00): confirm file arrival timestamp, filename pattern, size > 0, checksum if provided.2. Arrival window check: if file not seen by 03:00 (1h late) raise warning; by 04:00 raise high-priority alert; by 06:00 (SLA boundary) create incident for missing file.3. Content validation: schema/row-count sanity and last-modified time within expected window (reject stale files).4. Upstream consistency: compare file count against expected partner schedule and metric drift (e.g., sudden 90% drop in rows).5. End-to-end downstream check: confirm downstream table updated within expected window.

Alert thresholds & severity:- Warning (pager/email): missing by 03:00 (gives ops time to react).- High alert (page to on-call): missing by 04:00 or malformed content that blocks downstream.- Incident (page + conference bridge + notify stakeholders): missing by 06:00 or SLA breach predicted (probabilistic when multiple partners fail).

On-call & escalation:- Primary on-call (Data-ops/ETL engineer) paged for high alerts. 15 min ack, 30 min resolve escalation to secondary.- After 60 min unresolved, notify Data Analyst (you) and product owner with business impact summary (reports affected, customers impacted).- Maintain runbook with common fixes and contact list for partner ops.

Retry / backfill procedures:- Automatic retries for transient transfer errors (3 attempts with exponential backoff).- If file arrives late: mark ingestion as backfill run, tag data with arrival timestamp, reprocess in isolated window, notify downstream owners of data retroactive changes.- For missing files: attempt partner API/SFTP pull, request re-send; if unavailable, use last-known-good fallback (documented) and flag reports that used fallback.

Handling false positives:- Implement debounce: require condition to persist for X minutes (e.g., 10–15 min) before paging, and use warning channel for transient issues.- Correlate alerts with system health (network, SFTP server metrics) to suppress alerts caused by infra outages.- Provide an easy “acknowledge and silence” for planned maintenance, and a feedback loop to tune thresholds (weekly review of alert history to reduce noise).

Metrics & reporting:- Dashboard showing file arrival latency histogram, SLA compliance %, recent incidents and root causes.- Weekly SLA report and postmortem for incidents > threshold.

Why this works:- Early warnings + escalating thresholds give time to remediate without waking people unnecessarily.- Backfill and tagging preserve data correctness and traceability for analysts.- Feedback loops minimize false positives and keep alerts actionable for the data team.

HardTechnical

28 practiced

Outline an end-to-end pipeline to detect anomalies in a daily revenue time series using STL decomposition and robust z-score in Python. Specify how you would aggregate raw event data in SQL, export to a notebook or job for STL decomposition, compute anomaly scores, store anomaly metadata back to the warehouse, and expose results to dashboards. Include considerations for retraining frequency, handling seasonality, and scaling.

Sample Answer

Requirements:- Daily revenue time series (per product/region), detect point anomalies (spikes/drops) with explainable scores.- Near-daily job, low latency for dashboards, store anomaly metadata in warehouse.

1) Aggregate raw events in SQL (daily):- Example SQL to produce daily revenue per key dimension and load to table daily_revenue:

sql

-- aggregate events into daily revenue per product/region
INSERT INTO analytics.daily_revenue (date, product_id, region, revenue)
SELECT
  DATE(event_timestamp) AS date,
  product_id,
  region,
  SUM(amount) AS revenue
FROM raw.events
WHERE event_timestamp >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1,2,3;

2) Export to notebook / job:- Scheduler (Airflow/Prefect) triggers a Python job that reads the aggregated table for required window (e.g., last 365 days).

3) STL decomposition + robust z-score implementation (Python):- Use statsmodels' STL; compute residual = observed - (trend+seasonal), then robust z-score using median/MAD.

python

import pandas as pd
from statsmodels.tsa.seasonal import STL
import numpy as np
from sqlalchemy import create_engine

# load data for one ts (example)
df = pd.read_sql("SELECT date, revenue FROM analytics.daily_revenue WHERE product_id=42 AND region='US' ORDER BY date", engine, parse_dates=['date'])
df = df.set_index('date').asfreq('D').fillna(0)

stl = STL(df['revenue'], period=7, robust=True)  # weekly seasonality default
res = stl.fit()
residual = res.resid

# robust z-score
med = residual.median()
mad = np.median(np.abs(residual - med))
robust_z = 0.6745 * (residual - med) / (mad + 1e-9)

anomalies = df.copy()
anomalies['z'] = robust_z
anomalies['is_anomaly'] = anomalies['z'].abs() > 3  # threshold tunable
anomalies['trend'] = res.trend
anomalies['seasonal'] = res.seasonal

4) Anomaly metadata storage:- Write back to warehouse table analytics.daily_anomalies with fields: date, product_id, region, revenue, z_score, is_anomaly, trend, seasonal, detection_ts, model_version.

sql

-- example upsert (pseudo)
MERGE INTO analytics.daily_anomalies AS tgt
USING staged.anomalies AS src
ON tgt.date=src.date AND tgt.product_id=src.product_id AND tgt.region=src.region
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHED THEN INSERT ...

5) Expose to dashboards:- Materialize a view joining daily_revenue + daily_anomalies; create dashboard filters (prod/region), color-code anomalies, include z-score, explanation (trend/seasonal) and link to alerting.

Operational considerations:- Seasonality: detect period automatically (autocorrelation/periodogram) or try candidate periods [7,30,365]; use robust STL (robust=True) and enough history (>= 2*periods).- Retraining/frequency: run daily; retrain model per time-series/window each run. Recompute decomposition on rolling window (e.g., 365d). Store model_version (code commit + period used).- Threshold tuning: calibrate false positive rate on holdout; consider dynamic thresholds based on business impact.- Scaling: vectorize processing by grouping and parallelizing per (product, region) using Dask or Spark; batch pulls from warehouse and push anomaly batches. Use job queue and autoscaling workers. For millions of series, use approximate/streaming methods or sample heavy hitters.- Monitoring: track detection rates, recent false positives, data drift (mean, variance), and runtime metrics. Add alerts for data gaps.- Explainability: store trend and seasonal components and short textual reason (e.g., "residual -3.4 MAD -> negative anomaly during downward trend").

This pipeline gives robust, interpretable daily anomaly detection and can scale by batching and parallel processing while preserving explainability for dashboards and stakeholders.

Unlock Full Question Bank

Get access to hundreds of SQL-Based Data Validation and Anomaly Detection interview questions and detailed answers.

Join thousands of developers preparing for their dream job.