Data Validation for Analytics Questions

Covers techniques and practices for ensuring the correctness and reliability of analytical outputs, metrics, and reports. Topics include designing and implementing sanity checks and reconciliations, comparing totals across different calculation methods, validating metrics against known baselines or prior periods, testing edge cases and boundary conditions, and detecting and flagging data quality anomalies such as missing expected data, unexplained spikes or drops, and inconsistent values. Includes methods for designing queries and monitoring checks that surface data quality issues, debugging analytical queries and calculation logic to identify errors and root causes, tracing problems back through data lineage and ingestion pipelines, creating representative test datasets and fixtures, establishing metric definitions and versioning, and automating validation and alerting for metrics in production.

HardTechnical

50 practiced

Explain how you would validate metrics that are computed from both streaming events (near-real-time ingestion) and periodic batch aggregates. Consider eventual consistency, deduplication, watermarking, reconciliation cadence, and how you would present differences to dashboard users while the streaming and batch systems converge.

Sample Answer

Approach: treat streaming as low-latency provisional view and batch as authoritative daily snapshot. Build validation and reconciliation pipelines that compare both and surface drift.

Key components:- Ingestion & deduplication: apply idempotent writes using event IDs and exactly-once sinks where possible; in stream processing (e.g., Flink/Beam) use keyed state + dedup window (event_id + arrival_time) to drop duplicates before metrics are emitted.- Watermarking & lateness: set watermarks based on business SLA (e.g., 2–10 min for near-real-time); allow a late-arrival buffer and hold incremental aggregations for that window. Tag streaming records with event_time, processed_time, and watermark at emission.- Reconciliation cadence: run automated reconciliations at multiple cadences: - Near real-time sanity checks (1–5 min): totals, null rates, ingestion lag alerts. - Hourly reconciles: compare streaming rolling-hour aggregates vs batch partials. - Daily reconciliation: authoritative compare between batch ETL and cumulative streaming totals.- Comparison logic: compare same-key aggregates using tolerance bands and relative diff thresholds (absolute and percent). Track historical drift distributions to set alert thresholds (e.g., >1% for totals or >5% for small-count metrics).- Presentation to users: - Dashboards show two layers: "Provisional (streaming)" with a heartbeat/time-to-stable indicator and "Authoritative (batch)" when available. - Visual cues: color-coded status (green = within tolerance, amber = converging, red = diverged), annotation of last reconciliation time, and percent-delta badges. - Explainability: tooltip with reconciliation details (stream vs batch values, diff, reconciliation run time, potential causes like late arrivals).- Operations & governance: - Maintain an incidents table of reconciliation failures with owner, RCA link, and resolution. - Provide runbooks: how to interpret provisional metrics, when to trust, and escalation path.- Example: conversions metric — stream shows 120 in last 10 min, batch partial shows 115; dashboard shows provisional=120 (amber), delta=+4.3%, watermark age=3min. If after daily batch final =123, reconciliation records final, updates dashboard, and closes incident if within expected drift.

This balances latency and correctness while giving stakeholders clarity about confidence and provenance of numbers.

MediumTechnical

32 practiced

Describe how you'd implement data lineage tracing for a business metric like 'monthly_revenue' that flows from transactional tables through ETL jobs and intermediate views into dashboards. What metadata would you capture (e.g., source tables, transformation SQL, job ids), what tooling would you use, and how would you surface lineage to analysts?

Sample Answer

Requirements and scope:- Track lineage from source transactions → ETL jobs → intermediate views/tables → final metric (monthly_revenue) and dashboards.- Support auditability, impact analysis, and easy discovery for analysts.

Metadata to capture:- Source objects: database, schema, table, column names, last-modified timestamps- Transformations: SQL query text, view definitions, column-level expressions, aggregation windows- Jobs: job id, orchestration DAG id (e.g., Airflow run_id), schedule, start/end times, operator logs, code repo commit hash- Metrics: metric id, canonical definition (formula), business owner, semantic tags (currency, timezone), version history- Lineage links: column-to-column mappings, upstream/downstream dependency graph, confidence/validation checks- Quality checks: row counts, null rates, checksum/hash, test results and timestamps

Tooling and integration:- Metadata store / catalog: use an open-source or managed catalog (e.g., Amundsen/Marquez/DataHub) to store objects and lineage.- Orchestration integration: instrument Airflow/Prefect to emit job, task, and SQL metadata (use hooks/operators or lineage plugins).- ETL/sql parsing: use SQL lineage parsers (OpenLineage, sqlfluff+lineage or custom AST) to infer column-level mappings for views and transformations.- Versioning & provenance: connect to git for SQL/transform code and to CI to capture commit hashes.- Validation & monitoring: Great Expectations or dbt tests to publish data quality results into catalog.

How to surface lineage to analysts:- Integrated catalog UI: searchable lineage graph showing upstream tables/columns, transformation SQL, and last run status; allow click-through to view job run details and schema diffs.- Metric explorer: expose canonical monthly_revenue definition with clickable upstream nodes and "What-if" impact (e.g., if source table changes, highlight affected dashboards).- Embed in BI tool: add links or tooltips in Looker/Tableau dashboards that open the catalog view for the metric or underlying query.- Automated notifications: when a breaking change or test failure occurs, send targeted alerts to metric owners and affected dashboard consumers with suggested mitigations.- Self-service docs: auto-generate README for each metric with examples, business rule explanations, and contact owner.

Operational practices:- Enforce single source of truth metric definitions (dbt/metrics layer or semantic layer).- Require code review and CI checks for metric or transformation changes.- Periodically run lineage audits and reconciliation queries (metric vs. source aggregates).

This combination provides column-level, job-aware lineage that analysts can explore, trust, and act on for troubleshooting and impact analysis.

MediumTechnical

33 practiced

Write a SQL query to detect sudden spikes or drops in daily active users per country using a table daily_events(country, event_date, dau). Define a spike as current_day > 3 * median(previous_14_days) and a drop as current_day < 0.33 * median(previous_14_days). Return country, event_date, prior_14d_median, current_dau, anomaly_type ('spike' or 'drop'). Mention functions you would use on platforms that support percentiles.

Sample Answer

Approach: for each country/day, compute the median DAU over the previous 14 days (excluding the current day), then compare current_dau to 3x and 0.33x of that median to label spikes/drops. Two approaches shown: (A) window function using RANGE by date (concise, DBs that support RANGE with interval), (B) lateral/self-join (accurate across missing dates).

sql

-- A) Window-version (if your DB supports RANGE BETWEEN INTERVAL ... PRECEDING)
SELECT
  country,
  event_date,
  prior_14d_median,
  dau AS current_dau,
  CASE
    WHEN dau > 3 * prior_14d_median THEN 'spike'
    WHEN dau < 0.33 * prior_14d_median THEN 'drop'
  END AS anomaly_type
FROM (
  SELECT
    country,
    event_date,
    dau,
    -- median over previous 14 days excluding current day
    percentile_cont(0.5) WITHIN GROUP (ORDER BY dau) 
      OVER (
        PARTITION BY country
        ORDER BY event_date
        RANGE BETWEEN INTERVAL '14' DAY PRECEDING AND INTERVAL '1' DAY PRECEDING
      ) AS prior_14d_median
  FROM daily_events
) t
WHERE prior_14d_median IS NOT NULL
  AND (dau > 3 * prior_14d_median OR dau < 0.33 * prior_14d_median)
ORDER BY country, event_date;

Alternative (robust when dates may be missing): lateral join per row

sql

SELECT d.country, d.event_date, m.median_dau AS prior_14d_median, d.dau AS current_dau,
  CASE WHEN d.dau > 3*m.median_dau THEN 'spike'
       WHEN d.dau < 0.33*m.median_dau THEN 'drop' END AS anomaly_type
FROM daily_events d
CROSS JOIN LATERAL (
  SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY dau) AS median_dau
  FROM daily_events p
  WHERE p.country = d.country
    AND p.event_date BETWEEN d.event_date - INTERVAL '14' DAY AND d.event_date - INTERVAL '1' DAY
) m
WHERE m.median_dau IS NOT NULL
  AND (d.dau > 3*m.median_dau OR d.dau < 0.33*m.median_dau);

Functions to use by platform:- PostgreSQL / Snowflake: percentile_cont(0.5) or percentile_disc(0.5) (exact median)- BigQuery: APPROX_QUANTILES(dau, 100)[OFFSET(50)] or SAFE/EXACT with percentile_cont in recent versions- Redshift: approx_percentile(dau, 0.5)- ClickHouse: medianExact, medianNotes:- RANGE-based window assumes date continuity; if days are missing, use lateral/join approach.- For large data, compute medians in a rolling-aggregation pre-aggregation (e.g., daily per country) or use approximate percentile functions for performance.

HardSystem Design

29 practiced

Explain strategies to validate metrics across multiple regions where data is ingested to region-specific warehouses and later aggregated globally. Consider issues such as clock skew, timezone normalization, partial replication lag, and producing consistent cross-region metrics. How would you detect missing partitions in a single region and reconcile with global totals?

Sample Answer

Requirements:- Accurate, auditable cross-region KPIs (daily/ hourly)- Tolerate clock skew, timezone differences, and replication lag- Detect missing partitions per region and reconcile to global totals- Provide clear alerts and drill-downs for BI consumers

High-level approach:1. Ingestion-time canonicalization- At source, record two timestamps: event_time (source-local, original) and ingestion_time (UTC at ingestion).- Persist event_time with timezone or normalized to UTC and store source_region_id and source_clock_offset if available.

2. Partitioning and watermarking- Use logical partitions by event_date_utc (not local date) and store event_hour_utc for hourly rollups.- Maintain per-region high-water marks: max(event_time) and max(ingestion_time) processed.

3. Consistency layer & reconciliation pipeline- Produce region-level aggregates with metadata: partition_id (event_date_utc), row_count, sum(metric), min/max event_time, max ingestion_time, processing_version.- Compute global aggregates by summing region-level aggregates but also track provenance (which regions contributed to each partition).

4. Handling clock skew/timezones and partial replication lag- Use event_time-normalized-to-UTC for bucketing. For user-facing local-date KPIs, present conversions at visualization time with clear footnotes.- Allow a lag window (e.g., 48 hours) before marking partitions final; maintain “open/closed” status per partition based on ingestion_time watermarks.- For late-arriving data: run late-arrival backfill jobs that update region aggregates and produce correction deltas.

5. Detecting missing partitions- Scheduler/monitor queries compare expected partitions (based on business calendar) vs. actual region partitions. Missing if no aggregate or row_count=0 and watermark < expected.- Alert on gaps and create incident tickets with drill-downs: show last_ingestion_time, row_count history, and sample source identifiers.

6. Reconciling with global totals- Maintain reconciliation table: per (partition, region) store expected_count (from source logs if available), processed_count, correction_delta, reconciliation_status.- Global total = sum(processed_count) + sum(correction_delta_pending). If discrepancy > threshold, surface as “reconciled=false”.- Provide automated reconciliation jobs: attempt to re-run ingestion/replication for missing partitions, run idempotent backfills, or apply correction rows to global aggregates with audit logs.

7. Monitoring & visualization for BI- Dashboards showing partition completeness heatmap by region/date, latency histograms, reconciliation status, and impact on top KPIs (what % of global metric is missing).- Expose drill-through: region partition → sample rows, ingestion_time distribution, replication lag metrics from CDC pipelines.

Trade-offs:- Delaying finalization (48–72h) improves accuracy but increases freshness latency.- Storing provenance and reconciliation metadata increases storage/complexity but is essential for auditable BI.

Example SQL (reconciliation check):

sql

SELECT p.event_date_utc, r.region_id,
       COALESCE(r.processed_count,0) AS processed,
       s.expected_count,
       COALESCE(s.expected_count,0) - COALESCE(r.processed_count,0) AS missing
FROM expected_partitions s
LEFT JOIN region_aggregates r
  ON s.event_date_utc = r.event_date_utc AND s.region_id = r.region_id
WHERE s.event_date_utc >= CURRENT_DATE - INTERVAL '7 days';

Key practices:- Instrument sources for expected counts when possible- Immutable, idempotent ingestion and backfills- Clear SLA for partition finality and transparent correction workflow for downstream stakeholders.

HardSystem Design

32 practiced

Design a database schema and query strategy to efficiently compute and validate daily distinct user counts (DAU) and weekly active users (WAU) for a system with ~200M users and ~5B events per month. Discuss partitioning, clustering, pre-aggregations/materialized views, approximation techniques (e.g., HLL), and validation checks to ensure counts are accurate within acceptable bounds.

Sample Answer

Requirements & constraints:- 200M users, ~5B events/month (~1.9M events/minute peak). Need daily distinct users (DAU) and 7-day WAU with near-real-time dashboard refresh and bounded error (<1% for executive, exact for audit).

Schema (events lake/table):- events(user_id BIGINT, event_date DATE, event_ts TIMESTAMP, event_type STRING, attrs JSON)- Partition by event_date (daily partitions).- Cluster/partition within date by user_id (hash bucketing, e.g., 256 buckets) to enable fast per-user distinct scans and reduce shuffle.

Pre-aggregation strategy:- Daily user-activity summary table (daily_user_active): (event_date, user_id) — populated by dedup job that writes one row per user per day (use INSERT OVERWRITE or upsert from streaming micro-batch). This table is small: at most 200M rows/day but typical DAU much lower.- Materialized view daily_counts(event_date, dau) built from COUNT(DISTINCT user_id) on daily_user_active; refresh incrementally daily/hourly.

Approximation for scale & latency:- Maintain HLL sketches per partition: - hll_by_date(event_date, hll_sketch) aggregated from events or streaming inserts. - hll_rollup_7d(week_end_date, hll_sketch) computed by unioning 7 daily HLLs for WAU.- Use HLL with calibrated error (e.g., 0.5–1%) and store parameters. Use exact counts from daily_user_active for overnight authoritative numbers.

Query strategy:- Real-time dashboard: query HLL rollups for fast DAU/WAU estimates.- Authoritative reports: query materialized daily_counts and compute exact 7-day WAU by UNION DISTINCT across daily_user_active (or precompute 7-day user table with incremental windows).

Partitioning & storage notes:- Compress and store daily_user_active in columnar format (Parquet/ORC), partitioned by event_date and bucketed by user_id for efficient dedup and joins.- Use compaction to reduce small files.

Validation & accuracy checks:- Periodic reconciliation: compare HLL estimates vs exact counts on sample days (compute relative error). Alert if error > threshold.- Drift detection: compute daily delta between HLL and exact; track moving average and stddev.- Completeness checks: row counts, max event_ts per partition, watermark lateness. Backfill missing partitions automatically.- Anomaly detection: sudden drops/spikes in DAU/WAU trigger alert and link to raw event counts, ingestion lag, and schema changes.- Auditability: retain raw events for X days and nightly exact recompute for regulatory/audit use.

Trade-offs:- HLL for speed and low cost; exact tables for audit. Bucketing improves dedupe performance but increases ingestion complexity. Incremental materialized views balance latency and compute cost.

Unlock Full Question Bank

Get access to hundreds of Data Validation for Analytics interview questions and detailed answers.

Join thousands of developers preparing for their dream job.