Data Observability and Governance Questions

Encompasses designing monitoring, alerting, governance, and metadata practices to maintain long term data reliability. Topics include building observability for data pipelines with logging metrics and traces, setting service level agreements and data quality service level indicators, anomaly detection for data and metrics, automated validation and alerting, lineage and provenance tracking, metadata and cataloging, data contracts, access controls for sensitive data, and processes for governance and compliance. Candidates should be able to design end to end frameworks that combine validation checks, anomaly detection, monitoring dashboards, incident workflows, and documentation to ensure trust in data products.

HardTechnical

69 practiced

Given a time series per-partition of row counts, design an algorithm (pseudocode acceptable) to detect gradual silent failures where counts slowly decay over weeks. Explain sensitivity tuning, how to distinguish legitimate seasonal decay from failure, and recommended automated remediation actions.

Sample Answer

Situation: We need to detect slow, “silent” degradations in per-partition row counts (weeks-long decay) across many partitions reliably and at scale.

Approach (overview):- Model expected per-partition count as a combination of recent trend + seasonal baseline.- Compute rolling-median baseline and exponential-weighted moving average (EWMA) trend.- Detect sustained negative drift by testing whether recent counts fall below threshold fraction of expected for a sustained window (multiple weeks) with statistical significance.

Pseudocode:

python

# inputs: time series counts[t] for t=1..T (daily), seasonality_period=7 (weekly)
alpha = 0.2  # EWMA smoothing
window_sustain = 21  # days to declare sustained drift
sensitivity_z = 2.0  # alert threshold in std devs

# 1. deseasonalize
seasonal = rolling_median(counts, 7)  # per-day-of-week median preferred
deseason = counts / (seasonal + eps)

# 2. trend via EWMA
ewma = EWMA(deseason, alpha)

# 3. residuals and volatility
resid = deseason - ewma
vol = rolling_std(resid, 28) + min_vol  # avoid tiny vol

# 4. detect sustained negative deviations
flag_days = [r < -sensitivity_z * v for r, v in zip(resid, vol)]
if sum(flag_days[-window_sustain:]) >= window_sustain * 0.8:
    alert_partition()

Sensitivity tuning:- sensitivity_z controls false positives: higher z => fewer alerts. Start with 2.0, validate with historical labelled incidents using ROC/PR curves.- window_sustain controls duration required — set to multiple of business cycles (e.g., 21–28 days) to avoid transient noise.- min_vol and smoothing alpha prevent overreaction on low-volume partitions; for low-volume partitions, switch to relative-drop thresholds (e.g., >50% drop).

Distinguish seasonal/legitimate decay:- Use deseasonalization by day-of-week/month patterns.- Fit piecewise linear trend or seasonal ARIMA to capture known business cycles.- Cross-check upstream signals: ingestion lag, schema changes, connector logs, and source-side metrics.- Use cohort comparison: compare partition to peer partitions with similar attributes; if entire cohort decays synchronously, likely business-driven.

Automated remediation recommendations:1. Triaging: - Enrich alert with: recent % drop, time window, upstream ingestion success rates, schema-change events, recent deploys, and peer-partition status.2. Automatic quick fixes (safe, reversible): - Re-run recent ingestion jobs for the partition if transient failure suspected. - Restart connector / streaming job if upstream offset lag detected.3. Escalation: - Create incident with runbook if automated retries fail or if drop > configurable critical threshold.4. Suppression/maintenance mode: - Allow temporary suppression for planned seasonal changes via a metadata flag or calendar override.5. Feedback loop: - Log true/false positives and use labels to retrain thresholding or a lightweight ML detector (e.g., isolation forest on features: slope, zscore, volume).

Operational notes:- Implement in streaming monitoring (e.g., Spark Structured Streaming or Flink metrics) and store recent windows in a time-series DB (Prometheus/Influx + Kafka).- Run backtests on historical data to tune parameters per partition class (high-volume vs low-volume).- Provide guardrails to avoid alert fatigue: group alerts, rate-limit, and require enriched context before paging on-call.

MediumTechnical

74 practiced

Given a table 'ingests' with columns (table_name TEXT, last_ingest_at TIMESTAMP, expected_interval_seconds INT), write a SQL query that computes an on-call-friendly data freshness metric: percentage of tables that had a successful ingest within their expected interval over the last 24 hours. Assume a Postgres-like dialect.

Sample Answer

Approach:Compute for each table whether it had a successful ingest within its expected interval at the time of that ingest, and then restrict to ingests in the last 24 hours (or evaluate freshness as of "now" for last-ingest timestamps). Finally report percentage of distinct tables that are "fresh" in that 24h window.

SQL (Postgres-like):

sql

WITH recent AS (
  -- consider each table's latest ingest in the last 24 hours
  SELECT
    table_name,
    MAX(last_ingest_at) AS last_ingest_at,
    MAX(expected_interval_seconds) AS expected_interval_seconds
  FROM ingests
  WHERE last_ingest_at >= now() - interval '24 hours'
  GROUP BY table_name
),
evaluated AS (
  SELECT
    table_name,
    last_ingest_at,
    expected_interval_seconds,
    -- is the table fresh at the time of its latest ingest?
    -- treat as fresh if latest ingest occurred within expected_interval_seconds before now
    (last_ingest_at >= now() - (expected_interval_seconds || ' seconds')::interval) AS is_fresh
  FROM recent
)
SELECT
  COUNT(*) FILTER (WHERE is_fresh) * 100.0 / NULLIF(COUNT(*),0) AS pct_fresh_last_24h,
  COUNT(*) AS tables_considered
FROM evaluated;

Key points:- We pick latest ingest per table within last 24h to avoid double-counting.- Use expected_interval_seconds per table to compute freshness relative to now.- NULLIF avoids division-by-zero when no tables present.

Edge cases:- Tables with no ingests in last 24h are excluded; if you want to count them as stale, LEFT JOIN against distinct table list (from a catalog) and mark missing as stale.- Null expected_interval_seconds should be handled (e.g., COALESCE to a default).- Timezone-awareness: ensure last_ingest_at and now() share same timezone.

Alternatives:- Evaluate freshness per table using its absolute last_ingest_at even if older than 24h, then filter for tables owned by the system; or compute rolling windows (e.g., sliding hourly).

EasyTechnical

84 practiced

A BI dashboard shows stale results (last refreshed 3 hours ago) even though the nightly ETL job did not report errors. Describe a step-by-step triage plan to identify the root cause: include checks at ingestion, transformation, storage, and serving layers and quick commands/queries you would run.

Sample Answer

Start by scoping and confirming impact: which dashboard, which dataset/table, when it last showed fresh data, and whether multiple dashboards are affected.

1) Check ingestion layer- Confirm source availability and arrival timestamps: - Cloud storage: ls and head recent files

bash

aws s3 ls s3://bucket/path/ --recursive | sort -r | head
    aws s3 cp s3://bucket/path/file.csv - | head

- DB sources: check latest rows

sql

SELECT MAX(created_at) FROM source_table;

- Check scheduler/logs that run ingestion (Airflow, cron):

bash

airflow tasks list DAG_ID && airflow tasks state DAG_ID TASK_ID YYYY-MM-DD
  tail -n 200 /var/log/ingest_job.log

2) Check transformation layer- Verify ETL execution status and runtime errors (Airflow/Dataproc/Spark):

bash

airflow dag-state DAG_ID YYYY-MM-DD
  gcloud dataproc jobs list --region=... --filter="status.state=ERROR"

- Inspect job logs for late failures or skipped downstream tasks.- Validate transformed table timestamps / row counts:

sql

SELECT MAX(updated_at), COUNT(*) FROM transformed.table;

- Run a small downstream transform locally or re-run a failing task to reproduce.

3) Check storage/warehouse- Confirm target tables partitions and recent loads:

sql

-- BigQuery example
  SELECT partition_id, row_count FROM `project.dataset.__PARTITIONS_SUMMARY__` ORDER BY partition_id DESC LIMIT 5;
  SELECT MAX(event_date) FROM dataset.table;

- Check for permissions/quota issues, table truncation, or accidental overwrites.- Check materialized views or incremental load markers (watermarks).

4) Check serving/dashboard layer- Verify the dashboard’s data source config (table name, schema, credentials) hasn’t changed.- Check cache/refresh settings and last refresh logs (Looker, Tableau, Metabase): - Clear cache or trigger a manual refresh.- Example: query the live data used by dashboard to confirm staleness:

sql

SELECT COUNT(*), MAX(updated_at) FROM analytic.dataset.table WHERE <dashboard_filter>;

- Check API/BI connector errors in logs.

5) Rapid isolation and remediation- If ingestion stalled: re-run ingestion or backfill missing files.- If transformation failed: re-run failed tasks, run idempotent jobs for affected partitions.- If warehouse load OK but dashboard stale: clear BI cache, reconfigure connector, or update scheduled refresh.

6) Post-mortem checks- Add alerts: missing partition, late pipeline run, row-count deltas.- Document root cause, fix, and preventative monitoring.

Throughout, capture timestamps, job IDs, and any error messages; escalate if cross-team dependencies (source owners, BI admins) are involved.

HardBehavioral

66 practiced

Describe a time when you convinced stakeholders to invest in data observability tooling or governance changes. Explain how you quantified value (reduced incidents, faster MTTR, prevented revenue impact), handled objections, and ensured ongoing funding and support.

Sample Answer

Situation: At my previous company our analytics team was spending ~30% of their time firefighting data issues; weekly pipeline incidents caused ~8 hours of analyst downtime on average and missed SLAs for daily revenue reports. Leadership had rejected paid tooling previously as “nice-to-have.”

Task: As the senior data engineer, I needed to build a business case to secure investment in a data observability tool and governance changes to reduce incidents and protect revenue reporting.

Action:- Baselined impact: instrumented incident logging for 8 weeks to quantify frequency, MTTR (median 6 hours), and downstream costs (estimated $50k/month in delayed decisions and rework).- Ran a 6-week pilot of a lightweight observability tool on two critical pipelines. Measured early wins: detected schema drift and freshness gaps before consumers noticed, cutting analyst interruptions by 60% in pilot scope.- Built ROI model: projected 70% reduction in incidents, MTTR from 6h→1.5h, and avoided revenue/reporting delays worth ~$300k annually. Included costs: tool subscription, 0.5 FTE for rollout, and training.- Addressed objections: - Cost: showed payback period of <6 months and sensitivity analysis. - Complexity: proposed phased rollout starting with highest-risk pipelines and reusing existing metadata/catalog integrations. - Vendor lock-in: negotiated short-term contract and exportable telemetry. - Change management: included training plan, runbooks, and a SLAs + alerting playbook.- Secured cross-functional sponsor (Head of Analytics + Finance) by aligning KPIs to faster close, fewer audit findings, and faster time-to-insight.- Institutionalized governance: created a Data Reliability Council with monthly reviews, added observability metrics to engineering dashboards, and embedded tool subscription into yearly infra budget.

Result:- After full rollout incidents decreased ~68%, MTTR fell to ~1.2 hours, analyst unplanned work dropped from 30%→8%, and we avoided estimated $320k annual cost. Leadership renewed funding annually; governance council used observability KPIs to prioritize pipeline investments.

Learning: Quantify impact in business terms, de-risk with a pilot and phased plan, and convert sponsors into long-term stewards by embedding metrics into regular governance and budget cycles.

HardTechnical

84 practiced

Design an anomaly detection pipeline specifically for categorical cardinality shifts (e.g., sudden increase in unique country codes). Explain detection algorithms, how to reduce false positives (e.g., filtering bots), and how to surface actionable alerts to data owners.

Sample Answer

Requirements & constraints:- Detect sudden cardinality shifts in categorical fields (e.g., country_code) in near-real-time and batch, low false positives, actionable alerts to data owners, scalable to billions of rows.

High-level pipeline:1. Ingest: stream events via Kafka (or Pub/Sub), batch loads via S3/GCS.2. Pre-filtering: enrich and normalize keys, apply bot/IP/user-agent filters.3. Cardinality estimation store: maintain per-entity windows using streaming sketches (HyperLogLog++) + time-decayed counts in Redis or Flink state / Spark Structured Streaming state.4. Anomaly detection engine: compare short window vs baseline (see algorithms).5. Alerting & feedback: push alerts to Slack/email/PagerDuty + dashboard and feedback loop to mark false positives.

Detection algorithms:- Streaming estimate: HyperLogLog per time window to estimate unique count quickly (memory efficient).- Statistical test: compute ratio R = HLL_short / HLL_baseline; flag if R > threshold (e.g., 3σ above historical ratio) or absolute jump.- Change-point detection: use CUSUM on daily unique counts for persistent shifts.- Context-aware scoring: combine new unique count, velocity (new uniques per minute), and provenance score (trusted sources vs unknown).

Reducing false positives:- Pre-filter bots: maintain IP UA allowlist/blocklist, use ML bot classifier, filter known scrapers and internal test traffic tags.- Noise smoothing: require persistence across multiple windows (e.g., 3 consecutive minutes or 2 hours depending on SLA) or exponential smoothing before alerting.- Whitelist expected events: e.g., known marketing campaigns or product launches flagged in metadata.- Combine signals: only alert when both HLL ratio and velocity exceed thresholds or when new high-frequency unique values appear.

Surfacing actionable alerts:- Alert content: impacted dataset/table, field, current vs baseline cardinality, top new values, sample rows, suspected sources, confidence score, suggested actions.- Routing: map dataset to data owner via catalog (e.g., Data Catalog/Govern) and send to their Slack/email; high-severity to on-call.- Dashboard: time-series of unique counts, heatmap of new values, drill-down to sample events and provenance.- Runbook & feedback: include quick-run steps (quarantine, rollback producer, block IPs) and a feedback button to mark false positive—this feeds model to reduce future FP.

Implementation notes:- Use Spark/Flink for batch/stream processing, HLL libraries (RedisBloom, stream-lib), store baselines in timeseries DB (Influx/Prometheus) or Delta tables.- Monitor system health and backfill capability; log all alerts for audit and model retraining.

This design balances memory-efficient estimation, statistical rigor, and practical filtering to reduce noise while providing data owners clear, actionable alerts.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.