Data Quality and Governance Questions

Covers the principles, frameworks, practices, and tooling used to ensure data is accurate, complete, timely, and trustworthy across systems and pipelines. Key areas include data quality checks and monitoring: nullness and type checks, freshness and timeliness validation, referential integrity, deduplication, outlier detection, reconciliation, and automated alerting. Includes designing service level agreements for data freshness and accuracy, data lineage and impact analysis, metadata and catalog management, data classification, access controls, and compliance policies. Encompasses operational reliability of data systems: failure handling, recovery time objectives, backup and disaster recovery strategies, data observability, and incident response for data anomalies. Candidates may be evaluated on designing end to end data quality programs, selecting metrics and tooling, defining roles and stewardship (data owner, steward, custodian), building golden-record and master-data-management strategies for record linkage and deduplication across source systems (illustrative domains include CRM and sales data, IoT telemetry, financial transactions, and event or log data, among others), and implementing automated pipelines and governance controls.

EasyTechnical

50 practiced

You need to detect simple outliers in transaction amounts before feeding data to reporting. Explain two quick statistical methods (z-score and IQR) and show how you'd compute them in SQL or Python/pandas for a transactions(amount) column. When might each method fail?

Sample Answer

Brief approach: two common quick detectors are z-score (assumes approximate normality) and IQR (non-parametric, robust to skew). Both flag points far from central tendency before reporting.

Z-score (pandas):

python

import pandas as pd
df = pd.read_csv('transactions.csv')  # has 'amount'
mu = df['amount'].mean()
sigma = df['amount'].std(ddof=0)     # population std
df['z'] = (df['amount'] - mu) / sigma
df['z_outlier'] = df['z'].abs() > 3  # common threshold

Z-score (SQL, Postgres):

sql

WITH stats AS (
  SELECT AVG(amount) AS mu, STDDEV_POP(amount) AS sigma FROM transactions
)
SELECT t.*,
       (t.amount - s.mu) / NULLIF(s.sigma,0) AS z,
       CASE WHEN ABS((t.amount - s.mu)/NULLIF(s.sigma,0)) > 3 THEN TRUE ELSE FALSE END AS z_outlier
FROM transactions t CROSS JOIN stats s;

IQR (pandas):

python

q1 = df['amount'].quantile(0.25)
q3 = df['amount'].quantile(0.75)
iqr = q3 - q1
lower = q1 - 1.5 * iqr
upper = q3 + 1.5 * iqr
df['iqr_outlier'] = (df['amount'] < lower) | (df['amount'] > upper)

IQR (SQL):

sql

WITH q AS (
  SELECT
    percentile_cont(0.25) WITHIN GROUP (ORDER BY amount) AS q1,
    percentile_cont(0.75) WITHIN GROUP (ORDER BY amount) AS q3
  FROM transactions
), bounds AS (
  SELECT q1, q3, (q3 - q1) AS iqr, q1 - 1.5*(q3-q1) AS lower, q3 + 1.5*(q3-q1) AS upper FROM q
)
SELECT t.*,
       CASE WHEN t.amount < b.lower OR t.amount > b.upper THEN TRUE ELSE FALSE END AS iqr_outlier
FROM transactions t CROSS JOIN bounds b;

When each fails:- Z-score: fails for skewed distributions or heavy tails (mean/std distorted), and small samples (unstable sigma). Not robust to extreme values.- IQR: more robust but can miss outliers in light-tailed distributions or when legitimate multimodal behavior exists (e.g., separate customer segments). Choice of multiplier (1.5) is heuristic.

Practical tips:- Visualize (histogram/boxplot), consider log-transform for right-skewed amounts before z-score.- Combine methods or use domain rules (business limits, velocity/frequency checks) for production reporting.

MediumSystem Design

48 practiced

Design a strategy to manage and version data quality rules and tests (e.g., in dbt or a tests-as-code framework) so analysts can propose and review rule changes. Include branching or approval workflows, testing environments, and how to handle rule deprecation.

Sample Answer

Requirements:- Analysts must propose, review, and approve data-quality (DQ) rule changes.- Rules/tests stored as code (dbt/tests-as-code), with CI that runs tests in isolated envs.- Traceability, rollback, and safe deprecation.

High-level design:- Repo: dbt project + tests in SQL/YAML under /models and /tests. Each DQ rule is a file with metadata (id, owner, severity, created_at, deprecated=false).- Feature branches + PR workflow for changes. PR must include: description, sample failing/passing dataset or failing test snapshot, risk assessment, rollout plan.

Core components:1. Version control & branching: - Main branch = production-ready rules. - Develop/staging branch for integration. - Feature branches per rule change; PR template enforces metadata and owner review.2. CI/CD: - On PR: run dbt compile + unit tests against lightweight ephemeral test DB (dockerized or cloud dev DB with anonymized subset). Run full test suite on staging branch against snapshot of production data (masked). - Merge to main triggers run in production-like environment and deployment of rule artifacts.3. Approval workflow: - Require at least one analyst peer review + one data gov approver (CODEOWNERS). Use PR checks to block merges until approvals and passing CI.4. Testing environments: - Local developer sandbox for quick iteration. - CI ephemeral test DB for PRs. - Staging (daily refreshed, masked snapshot) for integration/regression. - Production runs only after staging passes.5. Deprecation & rollout: - To deprecate: create a deprecation PR that marks deprecated=true, sets sunset_date, and adds migration notes. Keep rule flagged but disable enforcement level (e.g., severity -> advisory) for one release cycle while monitoring. - After sunset, remove rule file with another PR and archive metadata in an audit folder. Maintain changelog.6. Observability & rollback: - Store test results, failures, and historical rule versions in CI artifacts and a small metadata table (dq_rules_audit) in warehouse tracking rule_id, version, status, who changed it, and change reason. - Use feature flags (rule enabled/disabled) in a config table to quickly toggle problematic rules without code rollback.Trade-offs:- Full production-like staging is safest but more costly; ephemeral masked snapshots balance cost and safety.- Requiring multiple approvals increases governance but slows iteration; tune by severity.This approach gives analysts a clear, code-first, reviewable process with safe testing, traceability, and controlled deprecation.

HardTechnical

77 practiced

You must design an incident response playbook for data quality anomalies impacting executive dashboards: include alert routing, initial triage steps, roles responsible, communication templates, rollback options, and post-mortem requirements.

Sample Answer

Situation: Executive dashboards surfaced incorrect KPIs (revenue spike and churn drop) from a recent ETL change, risking wrong business decisions.

Playbook (high-level runbook):

1) Alert routing- Automated alerts: threshold and anomaly detectors (data quality checks, row counts, null rates, schema drift) push to PagerDuty -> on-call Data Analyst -> Slack #data-ops-exec (primary).- If within 15 min unresolved or severity high (executive-facing metric changed >10%), escalate to Data Lead and BI Engineer; notify Product/Finance stakeholders.

2) Initial triage (first 30 min)- Confirm alert: reproduce anomaly on dashboard and raw source (SQL).- Check recent deploys/ETL jobs in last 24 hrs, table row counts, schema changes, and pipeline logs.- Determine scope: single widget, dashboard, or system-wide.- Classify: Data error (source), Transformation error (ETL/SQL), Visualization bug (dashboard layer), or External (late upstream file).

3) Roles & responsibilities- On-call Data Analyst: triage, initial mitigation, owner of incident until handoff.- BI Engineer: ETL / data pipeline fixes, schema/ingestion recovery.- Data Lead: escalation, executive comms, decision authority for rollback.- Stakeholder Liaison (Product/Finance): validate business impact, approve actions.

4) Communication templates- Short alert (to execs):Subject: [INCIDENT] Exec Dashboard — {metric} anomalous (Impact: {high/med/low})Body: Summary, Time detected, Scope, Mitigation in progress, ETA next update, Contact: {Data Lead}- Post-update (15/60 min): status, actions taken, measured impact, next steps.

5) Mitigation & rollback options- Hotfix options: a) Temporary dashboard toggle to “last-known-good data” (point-in-time snapshot) or disable affected widget. b) Re-run last successful ETL job / backfill using verified snapshot. c) Apply SQL patch to transformation to correct calculation; deploy to staging then prod if low-risk.- If uncertain risk >20% exec impact, rollback to snapshot and pause downstream jobs until root cause fixed.

6) Post-mortem requirements (within 48–72 hrs)- Owner: Data Lead + incident reporter.- Deliverables: timeline, root cause, corrective actions, permanent preventative controls (tests, monitors, runbook updates), SLA breach analysis, stakeholder sign-off.- Metrics: time-to-detect, time-to-mitigate, time-to-resolve, business impact estimate.- Share findings in blameless meeting; assign owners and deadlines for remediation.

Key preventative controls: automated unit tests for transformations, data contracts with producers, daily reconciliation jobs, alert runbooks linked in tickets, and mandatory pre-deploy data validation for executive dashboards.

HardSystem Design

42 practiced

Design an end-to-end data quality program for an enterprise analytics organization: include governance structure, tooling, automated checks, monitoring, incident response, success metrics, and a 6-month rollout plan that balances quick wins and foundational work.

Sample Answer

Requirements & constraints:- Improve trust in analytics, reduce time spent cleaning data, support self-service BI, across 100+ data sources, SLA for critical metrics: 99% accuracy, 48h incident resolution.

High-level program:- Governance: Data Quality Council (monthly) — stakeholders: Head of Analytics (sponsor), Data Engineers, Data Stewards (by domain), 2 senior Analysts, Product/Business reps. Roles: policy owner, steward (owns fixes), monitor (owns alerts), remediation lead.- Policy & standards: data catalogs, schema/versioning, metric definitions (single source of truth), SLAs per critical dataset.

Tooling:- Catalog & lineage: OpenMetadata / Alation- Checks & validation: Great Expectations for tests; dbt for transformations and tests- Orchestration & monitoring: Airflow + Prometheus + Grafana- Alerting & incident mgmt: PagerDuty + Jira for ticketing- Self-service: Confluence + shared metric layer (dbt + semantic layer for BI tools)

Automated checks:- Ingest-level: schema, null-rate, row-count delta, freshness- Transform-level: uniqueness, referential integrity, value ranges, distribution drift, cardinality checks- Downstream: metric-level reconciliation (daily totals vs source), SLA checks- Tests implemented as dbt/GX suites, run in CI and on schedule; failures create Jira tickets and notify stewards.

Monitoring & incident response:- Dashboards (Grafana) for DQ KPIs: pass rate, failing tests, mean time to detect/resolve.- Triage runbook: severity levels (P0: metric blocker), auto-notify steward + on-call, create ticket with context & lineage link, rollback/re-run option, postmortem in 72h.

Success metrics:- % of critical datasets with automated tests (target 90% in 6 months)- Test pass rate (target >=98%)- Mean time to detect/resolve (MTTD < 4h, MTTR < 48h)- Reduction in analyst time spent on cleaning (target -30%)- Stakeholder trust score via survey (+20 pts)

6-month rollout (balanced quick wins + foundations)

Month 0 (prep)- Kickoff, establish Council, inventory critical datasets, define SLAs and owners.

Month 1 (quick wins)- Deploy catalog prototype, onboard 5 high-impact datasets, implement basic ingest checks (row-count, freshness), create DQ dashboard.

Month 2 (foundations)- Standardize metric definitions, set up dbt project for core models, instrument lineage and automated test harness (Great Expectations).

Month 3 (scale)- Expand tests to 30% critical datasets, integrate checks into CI and Airflow, implement alerting and Jira automation, run training for analysts on interpreting alerts and using catalog.

Month 4 (automation)- Add distribution drift and reconciliation tests, enable automatic remediation patterns (re-run pipelines, revert to snapshot), begin measuring MTTR/MTTD.

Month 5 (governance & adoption)- Formalize SLAs, publish runbooks, hold Council review of incidents, survey stakeholders, iterate on rules.

Month 6 (maturity)- 90% coverage of critical datasets, dashboards show KPIs, reduced analyst cleanup time, continuous onboarding process documented.

Trade-offs & risks:- Start with high-impact datasets to show value quickly; deeper lineage and full coverage is resource-intensive. Mitigate via phased scope and automation templates.

Example quick dbt + Great Expectations check (conceptual):- dbt model builds daily_sales; GX checks assert non-null order_id, row_count > 0, revenue within expected range; failure triggers Airflow alert + Jira ticket with failing test and lineage link.

This program empowers analysts to trust data, spend less time cleaning, and deliver faster insights while creating operational processes for long-term data quality.

EasyTechnical

46 practiced

In Excel or Google Sheets, what are three practical checks a non-technical product manager could run to surface data quality problems in a small CSV export (<=50k rows)? Give the exact functions or steps for each check.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.