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

51 practiced

Given the following table schema for an OLAP table:

orders(order_id BIGINT PK, customer_id BIGINT, order_date DATE, amount DECIMAL(10,2), status VARCHAR)

Write a single SQL query (ANSI SQL) that identifies rows where any of the critical columns (order_id, customer_id, order_date, amount) are NULL and returns the column(s) that are NULL per row along with a row_count.

Sample Answer

To find rows where any critical column is NULL and report which columns are NULL per row plus a count, build a null-list per row with CASE expressions, filter for any NULL, then group to get row_count. Here's an ANSI SQL single-query solution:

sql

SELECT
  -- Preserve identifying key (nullable); cast to string for grouping/display
  CAST(order_id AS VARCHAR(50))        AS order_id,
  TRIM(BOTH ', ' FROM
    (CASE WHEN order_id IS NULL     THEN 'order_id, '     ELSE '' END ||
     CASE WHEN customer_id IS NULL  THEN 'customer_id, '  ELSE '' END ||
     CASE WHEN order_date IS NULL   THEN 'order_date, '   ELSE '' END ||
     CASE WHEN amount IS NULL       THEN 'amount, '       ELSE '' END)
  )                                   AS null_columns,
  COUNT(*)                            AS row_count
FROM orders
WHERE order_id IS NULL
   OR customer_id IS NULL
   OR order_date IS NULL
   OR amount IS NULL
GROUP BY CAST(order_id AS VARCHAR(50)),
         TRIM(BOTH ', ' FROM
           (CASE WHEN order_id IS NULL     THEN 'order_id, '     ELSE '' END ||
            CASE WHEN customer_id IS NULL  THEN 'customer_id, '  ELSE '' END ||
            CASE WHEN order_date IS NULL   THEN 'order_date, '   ELSE '' END ||
            CASE WHEN amount IS NULL       THEN 'amount, '       ELSE '' END)
         )
ORDER BY row_count DESC;

Key points:- WHERE filters only rows with any NULL in critical fields.- CASE + concatenation (||) builds a comma-separated list of NULL columns per row.- TRIM removes trailing commas/spaces.- GROUP BY the identifier and null_columns produces counts of identical null patterns per order_id. Edge cases: some DBs use + or CONCAT() instead of || and different TRIM syntax; cast types as needed.

HardSystem Design

51 practiced

Design a reconciliation pipeline that automatically compares row counts and numeric aggregates across three ETL stages (source → staging → warehouse), captures discrepancies, and attempts automated root-cause identification using lineage metadata. Outline the components, data model, and algorithm for automated root-cause hints.

Sample Answer

Requirements & goals:- Automatically compare row counts and numeric aggregates (sum, avg) across three ETL stages: source → staging → warehouse on every run.- Capture discrepancies, store history, surface alerts and suggested root-cause hints using lineage metadata.- Easy-to-read BI dashboard and integrations (Slack/email).

High-level architecture:- Orchestrator: Airflow or Dagster schedules reconciliation jobs post-ETL.- Metrics extractor: lightweight jobs that compute per-table & per-partition metrics at each stage.- Reconciliation store: central metadata DB (Postgres) holding metrics, results, lineage, and alerts.- Lineage service: graph DB or metadata catalog (e.g., Amundsen/Marquez or Neo4j) exposing lineage edges down to column-level.- Analyzer/Reasoner: service that compares metrics, runs root-cause algorithm, scores hypotheses.- Alerting/UI: BI dashboard (Looker/PowerBI) + alerting channel.

Data model (core tables):- metrics(table, stage, partition_key, metric_type, metric_name, value, collected_at, run_id)- reconciliation(run_id, table, partition_key, metric_name, stage_left, stage_right, value_left, value_right, delta, status)- lineage_nodes(node_id, type(table/column/view), name)- lineage_edges(parent_node_id, child_node_id, transformation, filter_predicate)- rc_hints(reconciliation_id, hint_type, confidence_score, evidence)

Reconciliation flow:1. On ETL completion, orchestrator triggers Metrics extractor for source, staging, warehouse for same partition(s).2. Store metrics; compute deltas and relative/absolute thresholds.3. If delta exceeds threshold → reconciliation record created with status=failed.4. Analyzer queries lineage graph for affected table/columns and traverses upstream nodes.

Automated root-cause hint algorithm (summary):- Input: failed reconciliation R for table T, metric M, partition P.- Step A — quick checks (low-cost): - Compare staging vs source: if staging << source → suspect ingest failure/drop/trim. - Compare warehouse vs staging: if warehouse << staging → suspect load job/merge/delete/late-arrival. - Check run timestamps: late upstream run → propagation delay. - Check row-level uniqueness/duplicates by comparing counts vs distinct key counts.- Step B — lineage-driven candidate generation: - Traverse lineage upstream from T up to N hops (configurable). - For each upstream transformation node, evaluate evidence: - If transform has a filter_predicate and count reduction ratio ≈ delta → hint: “filter applied” - If aggregate node combines partitions and sum mismatch localized to specific partition → hint: “partitioned load mismatch” - If a join node causes value reductions correlated with join-key cardinality change → hint: “inner join causing drops” - If staging shows nulls or zeros for numeric columns while source non-null → hint: “type cast/null handling”- Step C — scoring and ranking: - For each candidate C compute score = weighted sum of signals: - magnitude_match = 1 - |expected_reduction - observed_reduction| - temporal_match = 1 if upstream run time aligns with delta window - metadata_match = 1 if transformation metadata indicates filtering/aggregation - historical_pattern = 1 if similar reconciliations previously traced to same node - Normalize to [0,1], return top-K hints with confidence and evidence links (metrics, run_ids, diff samples).- Step D — optional automated remediation suggestions: - Re-run upstream job, reingest partition, enable null-handling, change join type, or raise human review.

Example hint types:- “Missing ingest for partition P” (high confidence if source has rows, staging empty, no ingest run)- “Inner join dropped rows at transform X” (if join-key cardinality fell by X% and matches delta)- “Aggregation bug: sum on casted column” (if warehouse sum differs but staging distinct values unchanged)- “Late data arrival” (if source timestamp > warehouse load time)

Operational considerations:- Thresholds: relative (e.g., >1%) and absolute; configurable per table.- Sampling: store small row-level samples (hashes) to validate duplicates without full row compare.- Performance: compute metrics incrementally; store per-partition stats to localize issues.- Explainability: include links to lineage node, transform SQL, recent commits, and run logs.- Feedback loop: users confirm hints → feed back to historical_pattern model to improve scoring.

Trade-offs:- Deep lineage traversal increases compute cost; limit hops or prioritize nodes by impact.- Row-level reconciliation is expensive; rely on aggregates + smart sampling and hashes.- Automated remediation risk: prefer human-in-the-loop for destructive actions.

Output to BI team:- Dashboard tiles: reconciliation health over time, failed tables, top root-cause hints, per-table confidence.- Drill-down: per-run evidence, sampled row diffs, transformation SQL, suggested next steps.

This design provides fast detection, explainable root-cause hints leveraging lineage, and a clear path for BI analysts to triage and resolve data quality issues.

HardSystem Design

25 practiced

You need to detect anomalies in streaming metrics with tight latency constraints (sub-10s detection). Architect a streaming solution using Flink/Beam/ksqlDB that performs SQL-like checks, describing state management, checkpointing, fault-tolerance, and how alerts are emitted without duplicating or losing events.

Sample Answer

Requirements & approach:- Real-time anomaly detection on streaming metrics with sub-10s detection, SQL-like checks (thresholds, rate-of-change, percentile), no duplicates/loss, fault-tolerant, BI-friendly alerts.

High-level architecture:- Kafka as durable ingestion + alert topic.- Stream processing with Flink SQL (or Beam with SQL runner / ksqlDB if you prefer SQL-first). Flink provides low-latency, stateful processing and strong checkpointing semantics.- Metrics/events → Kafka topic → Flink SQL job → anomaly detector (ProcessFunction/SQL UDFs) → alert topic / BI store (e.g., ClickHouse/BigQuery) → dashboards.

Detection logic (SQL-like):- Use Flink SQL for expressions: tumbling/sliding windows for aggregates (e.g., 1s/5s windows), SQL UDFs for percentiles or EWMA for trend checks.- Example checks: value > threshold, delta over last N seconds > x%, z-score > y, p95 > threshold.

State management:- Use RocksDB keyed state for per-metric state (last value, rolling aggregates, histograms/TDigest for percentiles).- Keep minimal per-key state: last timestamp, last value, count, TDigest. Configure state TTL to remove stale keys.- For joins/enrichment (lookup tables for thresholds), use asynchronous lookups with cached keyed state to avoid blocking.

Checkpointing & fault-tolerance:- Enable Flink checkpointing interval = 2–5s (tunable) to meet sub-10s recovery / exactly-once semantics.- Use durable checkpoint backend (e.g., S3/GCS/HDFS) and set externalized checkpoints for safer restarts.- Enable checkpointing + Kafka 0.11+ transactional sink (exactly-once) or Flink’s two-phase commit sink for alerts to avoid duplicates on retries.- Configure restart strategy (fixed-delay/exponential) to meet SLAs.

Watermarks & event-time:- Use event-time processing with watermarks tuned for expected event skew (small out-of-order bound, e.g., 1–2s). For strict sub-10s latency prefer small allowed lateness; drop or dead-letter very late events.- For rate checks use processing-time windows when out-of-orderness is negligible.

Alert emission without duplicates or loss:- Emit alerts into Kafka using Flink’s exactly-once sink (two-phase commit) or Kafka transactions. This couples stream state checkpointing with the sink commit so on failure you neither double-emit nor lose alerts.- Deduplicate alerts at consumer/UI: include deterministic alert_id composed of metric_id + window_start + rule_id. Downstream BI can upsert by alert_id (idempotent write) into analytical store.- For transient suppression (avoid alert storms), maintain short-lived dedupe state per alert_id and apply backoff rules.

Latency tuning:- Use small checkpoint interval (2–5s), reduce operator chaining & task slot contention, give adequate parallelism per key cardinality.- Keep state updates cheap (aggregate incrementally), avoid full-window scans—use incremental aggregates & sketches (TDigest, Count-Min).- Monitor taskmanager CPU/memory; prefer RocksDB local SSDs for large state, tune RocksDB write buffers and memory.

Observability & BI integration:- Push alerts to alert topic + upsert to BI store for dashboards. Store raw events and derived features for auditability.- Emit metrics (latency, watermark lag, checkpoint durations, state sizes) to Prometheus/Grafana.- Provide ability for analysts to author SQL rules which are compiled to Flink SQL jobs or dynamically loaded rule table (saved in Kafka/DB and read by job for eval).

Edge cases:- High-cardinality keys: use pre-aggregation or approximate algorithms.- Late events: decide SLA—drop/compensate with backfill.- Cluster failover: ensure externalized checkpoints and committed Kafka offsets are in sync.

Why this fits BI:- SQL-like ruleability for analysts, sub-10s detection via tuned Flink, durable exactly-once delivery to alerts and BI stores, and clear audit trail for investigations.

MediumTechnical

31 practiced

Design a set of SQL checks to verify referential integrity for denormalized analytics tables (e.g., fact tables referencing dimension keys). Include checks for missing keys, stale foreign keys, and surrogate key drift. Explain how you'd schedule and surface failures.

Sample Answer

Situation: As a BI analyst maintaining denormalized fact tables, I’d implement automated SQL checks that run daily to detect broken referential integrity, stale foreign keys, and surrogate-key drift before dashboards consume bad data.

Checks and example SQL:

1) Missing keys (fact FK not present in current dimension)- Purpose: detect facts referencing nonexistent dim rows.

sql

SELECT f.dim_id, COUNT(*) AS cnt
FROM analytics.fact_sales f
LEFT JOIN dim.customers d ON f.dim_customer_id = d.customer_id
WHERE d.customer_id IS NULL
GROUP BY f.dim_id
ORDER BY cnt DESC;

2) Stale foreign keys (fact references dim rows that were soft-deleted or out-of-date)- Purpose: catch FK pointing to rows marked inactive or with validity ranges.

sql

SELECT f.id, f.dim_customer_id
FROM analytics.fact_sales f
JOIN dim.customers d ON f.dim_customer_id = d.customer_id
WHERE d.active = FALSE OR f.event_date NOT BETWEEN d.valid_from AND d.valid_to;

3) Surrogate key drift (natural key in fact matches different surrogate or changed mapping)- Purpose: identify when source natural key → surrogate mapping changed (common with SCD issues).

sql

-- assume fact stores natural_customer_key and dim has surrogate customer_id
SELECT f.natural_customer_key, f.dim_customer_id AS fact_surrogate,
       d.customer_id AS current_surrogate
FROM analytics.fact_sales f
LEFT JOIN dim.customers d ON f.natural_customer_key = d.natural_key
WHERE f.dim_customer_id IS DISTINCT FROM d.customer_id
LIMIT 100;

4) High-level row-level ratio checks- Percentage of facts with valid FK:

sql

SELECT
  1 - (SUM(CASE WHEN d.customer_id IS NULL THEN 1 ELSE 0 END)::float / COUNT(*)) AS valid_fk_ratio
FROM analytics.fact_sales f
LEFT JOIN dim.customers d ON f.dim_customer_id = d.customer_id;

Scheduling and alerting- Schedule: run checks in Airflow or Prefect daily after ETL completes (or hourly for critical tables).- Failure handling: set thresholds (e.g., valid_fk_ratio < 0.995 or >100 missing keys) to mark as failed.- Surface failures: - Send Slack + email alerts with query results and sample rows. - Create a Data Quality dashboard (Looker/Tableau/Power BI) that shows trends, failing tables, and severity. - Auto-create a ticket in Jira for critical failures with samples and suggested owner. - Include retention: keep historical check results to detect worsening trends.

Operational notes and best practices- Classify severity: missing keys for key metrics = high priority; minor drift = medium.- Store checks and results in a data_quality schema for auditing.- Where possible, enrich alerts with remediation steps (e.g., re-run dimension sync, backfill SCD).- Maintain idempotent checks and limit returned sample rows to avoid huge payloads.

This approach provides fast detection, clear ownership, and measurable SLAs so dashboards remain reliable.

HardTechnical

27 practiced

Explain how to implement a z-score or exponentially weighted moving average (EWMA) anomaly detector using only SQL primitives for a time-series metric like daily_revenue. Provide example queries or window-function sketches and explain limitations.

Sample Answer

Approach summary: compute an online anomaly score per day either via z-score (using rolling mean & stddev) or EWMA (recursive exponential smoothing). Both can be expressed with SQL window functions (or recursive CTE for EWMA if your SQL lacks ORDERED aggregates). Below are compact Postgres-style patterns and practical limitations.

Z‑score (rolling window):- Use a trailing window (e.g., last 28 days) to compute mean and stddev, then z = (value - mean)/stddev.

sql

SELECT
  day,
  daily_revenue,
  AVG(daily_revenue) OVER (ORDER BY day ROWS BETWEEN 27 PRECEDING AND 1 PRECEDING) AS mu_28,
  STDDEV_SAMP(daily_revenue) OVER (ORDER BY day ROWS BETWEEN 27 PRECEDING AND 1 PRECEDING) AS sigma_28,
  (daily_revenue - AVG(daily_revenue) OVER (ORDER BY day ROWS BETWEEN 27 PRECEDING AND 1 PRECEDING))
    / NULLIF(STDDEV_SAMP(daily_revenue) OVER (ORDER BY day ROWS BETWEEN 27 PRECEDING AND 1 PRECEDING),0) AS z_score
FROM daily_revenue_table
ORDER BY day;

Flag anomalies when ABS(z_score) > threshold (e.g., 3), optionally require min window population (COUNT OVER).

EWMA (recursive form via recursive CTE or analytic if supported):- EWMA_t = alpha * x_t + (1 - alpha) * EWMA_{t-1}

Recursive CTE example (alpha = 0.3):

sql

WITH ordered AS (
  SELECT day, daily_revenue,
         ROW_NUMBER() OVER (ORDER BY day) AS rn
  FROM daily_revenue_table
),
rec AS (
  SELECT day, daily_revenue, rn,
         daily_revenue AS ewma -- seed with first value
  FROM ordered WHERE rn = 1
  UNION ALL
  SELECT o.day, o.daily_revenue, o.rn,
         0.3 * o.daily_revenue + 0.7 * r.ewma
  FROM ordered o
  JOIN rec r ON o.rn = r.rn + 1
)
SELECT day, daily_revenue, ewma,
       daily_revenue - ewma AS residual
FROM rec
ORDER BY day;

You can compute a running deviation (e.g., EWMA of squared residuals) to normalize residuals into an EWMA z-like score:- var_ewma_t = beta * (residual_t)^2 + (1-beta) * var_ewma_{t-1}- sigma_ewma = sqrt(var_ewma)

Limitations and practical notes:- Seasonality/day-of-week effects: raw z/EWMA will flag expected weekly patterns as anomalies. Mitigate by de-seasonalizing (compare to same weekday rolling stats) or model separate baselines per weekday.- Cold start / warm-up: rolling stats / recursive seeds need warm-up; avoid scoring until enough history.- Autocorrelation: independence assumption violated; adjust thresholds or use robust methods.- Missing dates: fill gaps (generate calendar) to keep consistent windows.- Performance: window functions scale but large tables may need partitioning, indexing, or pre-aggregation. Recursive CTEs can be slow—consider batch materialization.- Sensitivity tuning: choose window size, alpha, thresholds by looking at historical precision/recall and business impact.- SQL limitations: more advanced anomaly models (seasonal decomposition, ARIMA) require external tooling (Python/R) or UDFs.

This SQL-only approach is practical for BI dashboards: compute scores nightly, store in an anomalies table, surface in reporting with context (previous values, baseline, threshold).

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.