Data Quality and Anomaly Detection Questions

Focuses on identifying, diagnosing, and preventing data issues that produce misleading or incorrect metrics. Topics include spotting duplicates, missing values, schema drift, logical inconsistencies, extreme outliers caused by instrumentation bugs, data latency and pipeline failures, and reconciliation differences between sources. Covers validation strategies such as data tests, checksums, row counts, data contracts, invariants, and automated alerting for quality metrics like completeness, accuracy, and timeliness. Also addresses investigation workflows to determine whether anomalies are data problems versus true business signals, documenting remediation steps, and collaborating with engineering and product teams to fix upstream causes.

MediumTechnical

91 practiced

Compare batch and streaming architectures for implementing data quality checks in an analytics platform. For each approach, describe which checks are a good fit, latency implications, cost trade-offs, and operational complexity for a BI team. Give concrete examples (row-count checks, dedup, schema validation, late-arrival detection).

Sample Answer

Overview: Batch and streaming are complementary ways to run data-quality (DQ) checks. Batch processes inspect data in time-windowed slices (daily/hourly), while streaming inspects records or micro-batches as they flow (near-real-time). Which to choose depends on the check type, required latency, cost, and BI team operational capacity.

Good-fit checks- Batch (best): row-count reconciliation across source vs warehouse, full-table integrity checks, heavy deduplication that can use global keys, complex aggregations, historical freshness/slippage reports. Example: nightly row-count and null-rate reports for Sales table.- Streaming (best): real-time schema validation, per-event dedup (idempotency), late-arrival detection within SLA windows, anomaly alerts on sudden drops/spikes. Example: streaming detects duplicate order_id within 5 minutes of ingestion and flags downstream dashboards.

Latency implications- Batch: higher latency (minutes to hours) — acceptable for daily reports, SLA-driven KPIs. Lower engineering overhead for coarse freshness.- Streaming: low latency (seconds to minutes) — needed for operational dashboards and immediate alerts, but requires continual pipelines.

Cost trade-offs- Batch: cheaper for large volumes since you run nightly jobs; compute can be scheduled and optimized. Storage for intermediate snapshots may be required.- Streaming: higher ongoing cost (continuous compute, stateful stores) and potential licensing for low-latency frameworks; costs grow with throughput and retention of state for dedupe/late-arrival windows.

Operational complexity for a BI team- Batch: easier to understand and debug (replayable runs, deterministic). BI teams can own frameworks like Airflow + SQL checks; simpler alerting and reconciliation. However, failures can delay reports and require reruns.- Streaming: more complex (state management, windowing, exactly-once semantics). Requires platform/SRE support or managed services (Kafka + ksqlDB, Flink, Confluent). BI team needs monitoring for lag, watermark tuning, and consumer offsets; debugging is harder but enables proactive dashboards.

Concrete check examples and recommendations- Row-count: Batch nightly reconciliation with thresholds and drift alerts; supplement with periodic hourly micro-batch if near-real-time detection desired.- Dedup: Streaming for near-real-time dedupe (use stateful dedupe with TTL); batch dedupe for historical cleanup and heavy joins.- Schema validation: Streaming lightweight validation (field presence/type) to reject or tag bad records; batch-schema drift scans to detect evolving fields and impact on downstream models.- Late-arrival detection: Streaming watermarks and lateness counters for operational SLAs; batch backfill jobs to reconcile late data and update affected aggregates.

Recommendation for BI role- Start with batch DQ for most metrics to keep costs and complexity low. Implement a small set of streaming checks for business-critical operational metrics (payments, inventory) where latency matters. Use managed streaming services to reduce operational burden and define clear ownership: BI owns checks/thresholds and dashboards; platform owns pipeline reliability, stateful scaling, and recovery.

MediumTechnical

84 practiced

Walk through a structured investigation workflow when a core KPI unexpectedly declines 40% overnight. Describe the prioritized data checks, sources to compare, how to use sampling and query logs, and the criteria to escalate to engineering for an upstream fix. Mention documentation and communication steps.

Sample Answer

Situation: Overnight a core KPI (e.g., daily paid conversions) drops 40% with no announced changes.

Workflow (prioritized, step-by-step):

1. Triage & scope- Confirm alert source (dashboard/monitor). Check time window, affected segments (geo, platform, device) and whether downstream dashboards show same drop.- Communicate immediate “investigating” note to stakeholders with current knowns and ETA.

2. Quick data checks (minutes)- Compare KPI across multiple sources: BI dashboard, data warehouse aggregate (e.g., daily summary table), event pipeline (raw events), and third-party systems (ad platform, payment provider).- Check data freshness/ETL status: latest ingestion timestamps, job failures, schema changes.- Look for maintenance/deploy notes in release logs, infra alerts, and data pipeline monitoring (Airflow, DBT).

3. Sampling & deeper validation (30–90 mins)- Run targeted SQL queries on raw events for the drop window and prior baseline; sample user/event records to validate event presence and schema.- Check event volume by event type and upstream identifiers (user_id, order_id). Use GROUP BY to find which sub-categories cause most decline.- Inspect query logs/slow queries and warehouse query history for errors or heavy loads causing partial reads.

4. Cross-checks & hypotheses- Compare cohort-level metrics (new vs returning users), funnel stages, and attribution windows. Validate time zone/UTC shifts or daylight savings issues.- Recompute KPI with alternative logic (e.g., count distinct user_id vs order_id) to rule out deduplication or aggregation bugs.

5. Sampling evidence & escalation criteria- If raw events are missing or malformed for a large fraction (>5–10%) of expected volume, or if ETL jobs failed, escalate to engineering immediately.- If data exists but transformation logic produces wrong aggregates (schema change, column rename, bad join) AND reproducing with raw data shows correct numbers, escalate to data engineering with reproducible SQL snippets and timestamps.- If third-party provider outages cause missing confirmations/payments, escalate to platform/vendor teams with correlation evidence.

6. Documentation & communication- Keep an incident doc (shared doc) with timeline, checks run, queries, sample rows, screenshots, and decisions.- Post regular updates to stakeholders (every 30–60 minutes early, then hourly) with status, impact estimate, next steps, and owner.- After resolution, write a root-cause postmortem: what happened, how detected, corrective actions (alerts, tests, dashboard checks), and prevention (schema change contracts, SLA tests).

Key principles:- Prioritize sources from raw -> processed -> aggregated.- Use sampling to prove presence/absence of events before blaming transforms.- Provide engineers reproducible evidence (timestamps, sample rows, failing SQL) before escalation.

MediumTechnical

78 practiced

Explain the value of row-level lineage versus column-level lineage for a BI organization. Provide use cases where each is necessary, give examples of how lineage helps with troubleshooting, compliance, and impact analysis, and discuss the operational cost and storage trade-offs.

Sample Answer

Row-level lineage tracks where each individual record came from and how it moved/transformed; column-level lineage traces how specific fields are derived across tables, views, and transformations. Both matter for BI but solve different problems.

When to use each (use cases)- Row-level lineage: essential for data corrections, customer support, fraud investigations, and audit trails. Example: a user disputes a charge — row lineage shows the exact source transaction, ingestion batch, and downstream changes.- Column-level lineage: essential for metric governance, report validation, and change impact when business logic changes. Example: if "ARR" suddenly shifts, column lineage reveals which upstream calculation or source column feeds that metric.

How lineage helps- Troubleshooting: Row lineage pinpoints corrupt or duplicate records and the ETL step that introduced them; column lineage identifies a broken transformation or mapping causing many dashboards to show wrong totals.- Compliance: Row lineage provides provable audit trails for regulated data (PII access, consent provenance). Column lineage shows which datasets include derived PII or regulated fields and why.- Impact analysis: Column lineage quickly surfaces which dashboards, alerts, and KPIs depend on a changed field; row lineage shows the subset of users or transactions affected by a data fix.

Operational cost & storage trade-offs- Row-level lineage is high-fidelity but expensive: storing identifiers, change history, and links for billions of rows increases storage, indexing, and query cost and often requires dedicated lineage stores or event logs. It also adds complexity to ingestion pipelines.- Column-level lineage is lighter: storing metadata about transformations, SQL lineage graphs, and mappings is compact and supports fast impact queries, but it can miss issues that only appear at record granularity.Recommendation for BI teams- Use column-level lineage as baseline for governance, impact analysis, and developer productivity.- Enable row-level lineage selectively for high-risk, regulated, or high-value domains (finance, billing, legal) or enable sampling/trace-on-demand to balance cost.- Combine lineage with monitoring, data quality rules, and documented SLAs so analysts can both quickly triage dashboard anomalies and comply with audit requirements.

HardSystem Design

71 practiced

For an organization with many producers and consumers, design a governance model and technical infrastructure that supports schema evolution, deprecation policies, multi-versioning, and contract enforcement without blocking non-critical teams. Describe automation, staging, enforcement points, and rollback strategies.

Sample Answer

Requirements & constraints:- Many producers (data teams, apps) and many BI consumers (dashboards, reports). Need non-blocking evolution, clear deprecation, multi-version support, contract enforcement, automated validation, and safe rollback. SLAs vary: critical pipelines vs exploratory teams.

High-level architecture:- Central Schema Registry + Contract Store (supports Avro/JSON/Parquet schemas and consumer-driven contracts)- CI/CD validation pipeline per data product- Staging environments: sandbox (dev), integration (shared test), and production with multi-version topics/tables- Enforcement & runtime layer: ingest gateways, message brokers (Kafka), and a catalog with lineage & compatibility metadata- Observability: automated compatibility tests, contract tests, SLIs, dashboards, alerts

Core components / responsibilities:1. Schema Registry: stores versions, compatibility rules (backward, forward, full), deprecation metadata, owner contact, deprecation deadline.2. Contract Tests (consumer-driven): consumers publish contract expectations; producers run these in CI against schema changes.3. CI/CD Automation: - Pre-merge hooks: validate new schema against registry compatibility rules and consumer contracts. - Integration tests: synthetic payloads run through staging pipelines, validate downstream BI outputs (sample dashboards / row counts). - Auto-approve rules for non-breaking, low-risk changes; manual review for potentially breaking changes.4. Multi-version support: - Topic/table partitioning by schema-version header or separate topics (topic-v1, topic-v2) with routing rules in ingestion gateway. - Reader libraries (BI connectors) support schema resolution via registry, letting dashboards read older/newer fields gracefully.5. Deprecation policy: - Announce deprecation via registry UI + Slack/email; attach migration guides and example queries. - Minimum deprecation window (e.g., 90 days) for critical datasets; shorter for experimental. - Automated usage tracker: if no consumers for X days, auto-sunset after governance approval.6. Enforcement points: - Dev-time: git hooks, CI tests, contract test failures block merge. - Pre-production: integration pipeline rejects incompatible changes; produces detailed failure reports. - Runtime: ingestion gateway enforces schema tags; can block data that violates required compatibility for critical topics, but for non-critical topics it routes to quarantine stream and notifies owner.7. Rollback strategies: - Schema rollbacks: registry supports revert to previous version; producers can re-register prior schema and republish. - Data rollback: for idempotent writes, replay from topic-specific offsets into new topic; for tables, use time-travel or snapshot restore. - Feature flags & dual-writing: producers can dual-write v1 & v2 for a period; consumers can switch via config toggle; rollback by toggling back. - Automated rollback triggers: if SLIs breach (drop in row counts, failed queries, data quality tests), orchestration triggers rollback playbook and notifies stakeholders.8. Automation & tooling: - CLI and UI for schema registration, test runs, deprecation lifecycle. - Scheduled compatibility sweeps to pre-validate upcoming changes across consumers. - Migration SDKs for common BI tools (Looker/PowerBI) that map deprecated fields and provide autogenerated migration SQL.9. Observability & governance: - Dashboards showing schema versions in use, consumer health, contract test pass rates, upcoming deprecations. - Governance board for exceptions; lightweight review for low-risk changes to avoid blocking small teams.

Trade-offs:- Multi-versioning and dual-writing increase storage and complexity but reduce blocking.- Strict enforcement prevents incidents but can slow innovation; use risk-tiering to balance.- Consumer-driven contracts require consumer investment; provide templates/SDKs to lower friction.

Example scenario:Producer introduces optional field "customer_segment" in schema v2. CI runs: registry marks backward-compatible, consumer contract tests pass, integration test validates BI counts unaffected. Deployed to prod with dual-write for 30 days. Consumers migrate; registry shows progress. After 90 days and zero active consumers on v1, governance auto-deprecates v1 and cleans up.

This model lets BI teams rely on stable contracts, automates validation and notifications, supports graceful evolution, and prevents critical pipelines from breaking while minimizing blockers for non-critical teams.

HardSystem Design

92 practiced

Design a system that correlates pipeline logs, schema-change records, deployment events, and data quality metrics to automatically surface likely root causes for metric breaks. Describe the data model for correlation, indexing strategies, heuristics to rank candidates, and a UI that helps on-call engineers quickly validate suggestions.

Sample Answer

Requirements:- Ingest sources: pipeline logs (task starts/errors), schema-change records (DDL + migration metadata), deployment events (code/config/timestamp, owner), and data quality metrics (DQ checks, thresholds, time-series).- Low-latency correlation (minutes), retention 90d+, explainable suggestions for on-call BI engineers.

Data model for correlation:- Event table (immutable): {event_id, source_type, timestamp, service, env, pipeline_id, job_id, severity, payload (JSON indexed)}.- SchemaChange table: {change_id, object_type, object_name, ddl_diff, applied_at, applied_by, version_tag}.- Deployment table: {deploy_id, service, component, commit_hash, changed_files, deployed_at, deploy_owner}.- DQMetric table (time-series): {metric_id, dataset, table, column, ts, value, expected_range, status}.- CorrelationIndex table linking event_id <-> dq_metric_id with score and reason.

Indexing strategies:- Time-series indices on timestamp for all tables (time-range queries).- Composite indices: (service, pipeline_id, timestamp), (dataset, table, ts).- Full-text / JSONB indices on payload and changed_files for keyword search (column names, migration IDs).- Inverted index mapping schema objects -> recent deployments/DDL for fast join.

Heuristics to rank candidates:1. Temporal proximity: events within configurable window (e.g., ±30min) get base score proportional to inverse time delta.2. Scoped impact: match by dataset/table/column names in payloads/DDL/changed_files -> +weight.3. Severity & frequency: error counts, repeated failing jobs -> +weight.4. Rule-based boosts: schema changes touching columns used in failing DQ checks; deployments touching ETL jobs that write the dataset.5. Historical patterns: if similar metric breaks previously caused by X, boost candidate X.6. Confidence normalization and top-N cutoffs; provide uncertainty band.

UI for on-call validation:- Summary pane: top 3 ranked root-cause candidates with score, reason badges (temporal, schema-change, deploy, log-error).- Timeline view: unified time axis showing DQ metric spike, annotated events (clickable).- Drill-down: raw logs, diff of DDL, deployment diff, and links to commit/PR and pipeline run.- Quick actions: acknowledge, mute, create incident with prefilled evidence, rollback link, or tag as false positive.- Explainability: show contributing signals and numeric weights for each candidate so BI engineers can validate logic quickly.- Filters: time window, environment, dataset, metric, and ability to replay scoring with parameter tweaks.

Operational notes:- Start with conservative windows and amplify with ML after labeled incidents.- Capture feedback loop: engineer validations feed supervised model to improve ranking.- Expose audit trail for compliance and postmortems.

Unlock Full Question Bank

Get access to hundreds of Data Quality and Anomaly Detection interview questions and detailed answers.

Join thousands of developers preparing for their dream job.