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.
MediumTechnical
38 practiced
Design SLAs/SLOs for two distinct dataset types: 1) operational near-real-time product inventory used by customer-facing APIs, and 2) daily aggregated analytics used by BI for forecasting. For each dataset, propose measurable SLO metrics (e.g., freshness, completeness, accuracy), target values, error budgets, and suggested remediation actions when SLOs are violated.
Sample Answer
Operational near-real-time product inventory (customer-facing APIs)Requirements/context:- Millisecond–second latency for reads, strong consistency for inventory counts, high availability.Measurable SLO metrics & targets:- Freshness (age): 95% of records updated within 5s of source change.- Completeness: 99.9% of SKUs present and not null in API view.- Accuracy (correctness vs. source of truth): 99.99% match rate versus transactional DB.- Availability: API read availability 99.95% (p95 response time <200ms).Error budget:- Freshness: 5% allowance per week.- Completeness: 0.1% missing per month.- Accuracy: 0.01% discrepancies per month.- Availability: 0.05% downtime per month.Suggested remediation when violated:- Immediate: Auto-alert to on-call; roll back recent pipeline deployment if incident coincides with release.- Fast mitigation: Serve degraded but safe view (reserve-based or stale-but-consistent snapshot) and enable read-only fallback to master DB for critical SKUs.- Root cause: Re-run incremental ingestion (CDC) for affected partitions; reconcile diffs with source via targeted replays.- Postmortem: Increase monitoring granularity (per-partition freshness), add circuit-breakers, tighten idempotency and checkpointing.Daily aggregated analytics (BI forecasting)Requirements/context:- Daily batch with high completeness & stability; eventual consistency acceptable; correctness critical for models.Measurable SLO metrics & targets:- Freshness: 99% of daily runs complete by 02:00 local time (data window closed at 00:00).- Completeness: 99.5% of expected input files/rows present for each upstream source per day.- Accuracy: Aggregation checksum match / row-count reconciliation within 0.1% vs. expected.- Pipeline success rate: 99% success for scheduled DAG runs.Error budget:- Freshness: 1% missed deadlines per month (≈0.3 days).- Completeness: 0.5% missing entries per month.- Accuracy: 0.1% discrepancy per month.- Success rate: 1% failed runs per month.Suggested remediation when violated:- Immediate: Alert data engineering + BI consumers; mark affected datasets with an “issue” flag in metadata/catalog.- Fast mitigation: Trigger targeted re-ingest/recompute for missing sources; run incremental/partitioned recompute rather than full rebuild if time-constrained.- For accuracy breaches: Run validation jobs to isolate bad transformations, revert to last good snapshot for BI reports, and surface confidence intervals to downstream users.- Long-term: Add upstream SLA enforcement (schema & delivery contracts), implement row-level lineage, automated data quality tests (expectations) in CI, and buffer windows or windowed retries for unstable sources.General best practices (applies to both):- Implement observability: freshness, lag, completeness, and accuracy dashboards + alerting thresholds.- Maintain a clear data contract and runbooks for on-call remediation.- Tie SLOs to consumer impact and iterate targets with stakeholders.
HardSystem Design
45 practiced
Architect an end-to-end automated data quality framework integrated with existing ETL pipelines that supports: policy-as-code validations, realtime alerts for streaming jobs, scheduled batch checks, quarantining bad records, automated backfills, a human-in-the-loop remediation workflow, and audit logs. Scale target: 10k pipelines, 100 TB/day. Provide components, interfaces, storage, and a failure/recovery strategy.
Sample Answer
Requirements (clarify): functional—policy-as-code, realtime streaming alerts, scheduled batch checks, quarantining bad records, automated backfills, human remediation workflow, audit logs. Non-functional—scale 10k pipelines, 100 TB/day, low-latency alerts for streaming, high availability, multi-tenant.High-level architecture:- Ingestion layer: Kafka/Confluent for streaming, cloud object storage (S3/GCS/ABFS) or ADLS for landing batch files. Producers add pipeline metadata headers.- ETL orchestration: Airflow (Kubernetes executors) / Dagster for batch; Flink/Structured Streaming (Spark Streaming) for streaming jobs.- Data Quality (DQ) platform: 1. Policy-as-code engine: OPA/Rego + custom rules registry (git-backed repo) exposing REST/gRPC to runners. 2. Validation runners: - Streaming validator: lightweight Flink/Spark operator that subscribes to topics, applies compiled Rego rules or WASM validators per event, emits verdicts. - Batch validator: Spark jobs launched by orchestrator to run checks on partitioned data. 3. Rules catalog & versioning: Git + CI that compiles/validates policies, stores artifacts in artifact store (e.g., S3 + metadata DB). 4. Quarantine service: write failed records to quarantine storage (partitioned by pipeline/date/reason) with schema and provenance metadata. 5. Backfill/orchestration service: reconciler that computes missing-good partitions and launches idempotent reprocessing DAGs; uses checkpointing and lineage metadata to avoid double-processing. 6. Human-in-loop UI: web app showing alerts, sample bad records, suggested fixes, approve/reject backfills; integrates with ticketing (Jira/ServiceNow). 7. Audit & lineage: event-sourced audit log (Kafka topic + immutable data lake table), metadata DB (Postgres/Bigtable) for fast queries; integrates with OpenLineage/Marquez for provenance. 8. Alerting/Observability: Prometheus + Grafana, alertmanager, and notification hooks (Slack/email/PagerDuty).Interfaces:- Policy API (gRPC): validate(policy_id, payload, schema) -> verdict, reasons.- Validator sidecar interface: each ETL worker has DQ sidecar/SDK for sync checks and async reporting.- Quarantine API: write(record, metadata) / read(range) / mark-resolved(id).- Backfill API: request_backfill(pipeline, partition_range, reason) -> job_id.- UI API: list-alerts, get-samples, approve-fix.Storage choices:- Hot store for metadata & alerts: Postgres or CockroachDB (for scale + multi-region).- Time-series metrics: Prometheus long-term in Cortex/Thanos.- Large object/quarantine/backups: S3/GCS with lifecycle policies.- Audit/event log: Kafka + compacted topics; sink to Parquet in data lake for immutability.Scalability & performance:- Validator scaling: autoscale Flink/Spark clusters per topic/tenant; use WASM-compiled policies for low-latency per-record checks (~ms).- Shard policies by pipeline & use policy caching; warm workers with precompiled rules.- Partition quarantine by pipeline/day to allow parallel scans.- Use incremental checks and sampling for heavy rules; provide full-run option for strict SLAs.Failure & recovery strategy:- Idempotent validation + exactly-once where possible (Kafka EOS or Spark structured streaming checkpoints).- On validator failure: consumer group rebalancing replays from last committed offset; quarantined records remain immutable.- For lost metadata: reconcile service compares audit logs, dataset manifests, and orchestrator history to detect divergences and trigger backfills.- Backfill retries: exponential backoff with dead-letter for manual review after N attempts.- Disaster recovery: cross-region replication for Kafka, multi-region object storage, and warm-standby metadata DB with automated failover.- SLA for alerting: fallback lightweight validators in orchestrator to generate minimal alerts if DQ platform unavailable.Operational practices:- Policy CI: unit tests, integration tests on sample datasets, semantic checks, canary rollout of policy changes.- SLOs and dashboards per pipeline: data freshness, % records passed, quarantine rate.- RBAC and encryption, signed policies, tamper-evident audit logs.Trade-offs:- Using WASM+OPA gives low latency and policy safety but increases complexity; alternative is embedding validators in ETL jobs.- Full-scan batch checks are expensive; prefer incremental partition-level checks and sampling for scale.This design delivers policy-as-code, realtime streaming validation, scheduled batch checks, safe quarantining, automated backfills with human approval, and comprehensive auditability at the required scale.
MediumTechnical
36 practiced
Given a metadata table 'table_partitions(table_name varchar, partition_date date, last_loaded_at timestamp)', write SQL to compute freshness lag in hours for each table (max lag across partitions) and flag tables whose max lag exceeds 4 hours. Include timezone handling and an explanation of how to schedule this check.
Sample Answer
Approach:- For each table, compute lag per partition = current time (in target timezone) - last_loaded_at, convert to hours.- Take the maximum lag per table (worst partition).- Flag tables where max lag > 4 hours.- Use AT TIME ZONE (Postgres/Redshift syntax) or convert_timezone for other engines; include UTC baseline.SQL (Postgres-style; adjust timezone function for your DB):
sql
WITH now_tz AS (
-- define evaluation time in desired timezone (e.g., 'America/Los_Angeles')
SELECT (now() AT TIME ZONE 'UTC') AT TIME ZONE 'America/Los_Angeles' AS current_ts
),
lags AS (
SELECT
tp.table_name,
tp.partition_date,
tp.last_loaded_at,
-- normalize last_loaded_at to same timezone then compute hours difference
EXTRACT(EPOCH FROM (
(now_tz.current_ts)::timestamptz - (tp.last_loaded_at AT TIME ZONE 'UTC') AT TIME ZONE 'America/Los_Angeles'
)) / 3600.0 AS lag_hours
FROM table_partitions tp
CROSS JOIN now_tz
)
SELECT
table_name,
MAX(lag_hours) AS max_lag_hours,
CASE WHEN MAX(lag_hours) > 4 THEN true ELSE false END AS stale_flag
FROM lags
GROUP BY table_name
ORDER BY max_lag_hours DESC;
Key points / reasoning:- Convert both now() and last_loaded_at to the same timezone to avoid DST/offset mismatches. Here last_loaded_at is assumed stored in UTC; adapt the AT TIME ZONE usage if stored with tz.- EXTRACT(EPOCH) gives seconds, divide by 3600 for hours (supports fractional hours).Edge cases:- Null last_loaded_at: filter or treat as very stale (e.g., IS NULL => set lag = large number).- Partitions with future timestamps (clock skew): clamp negative lags to 0 if desired.- Different DBs: use convert_timezone('UTC','America/Los_Angeles', last_loaded_at) (Redshift) or SWITCHOFFSET/AT TIME ZONE (SQL Server).Scheduling the check:- Run this query on a monitoring schedule matching SLA frequency; for a 4-hour threshold, run hourly (or every 15 min for faster detection).- Implement as a scheduled job in Airflow/Cloud Scheduler/Managed DB job; persist results to a monitoring table and emit alerts (Slack/email) when stale_flag = true.- Add rate-limiting to alerts (dedupe) and include table, max_lag_hours, top stale partitions in the notification for debugging.
MediumTechnical
35 practiced
Describe safe schema evolution strategies for streaming pipelines that use Avro/Confluent Schema Registry or Delta Lake/Parquet for storage. Discuss backward, forward, and full compatibility, how to validate compatibility on producer changes, and how to deploy schema changes with minimal consumer disruption.
Sample Answer
Start by defining compatibility types and their practical meaning:- Backward compatible: New schema can read data written with old schema (consumers with new code can handle old records). Achieved by adding optional fields with defaults, making fields nullable, or not removing fields.- Forward compatible: Old consumers can read data written with new schema (producers add fields consumers ignore). Achieved by adding fields with defaults or making fields optional.- Full compatible: Both directions (safe for both old producers and old consumers).Avro + Confluent Schema Registry- Enforce compatibility mode in Registry (BACKWARD, FORWARD, FULL) per subject.- Validate on producer change: Run schema compatibility check via Registry REST API or CI step that posts the new schema to the subject with ?compatibility=…; fail build if incompatible.- Safe change patterns: add optional fields with default, add nullable union types, add new enum symbols at end (careful), avoid renaming or deleting fields unless using evolution metadata.- Deployment strategy to minimize consumer disruption: - Use backfill + dual-write for controlled cutover: producers write both old-topic (or format) and new-topic; consumers migrate gradually. - Consumer decoupling: deploy consumer that can handle both schemas (use Avro generic/container reader or schema-aware deserialization) before flipping producers. - Feature flags & canary: enable new schema for small producer subset, monitor errors/metrics. - Use “schema version header” so consumers can branch logic.Delta Lake / Parquet storage- Parquet is schema-on-write; Delta supports schema evolution but with constraints.- Use Delta’s schema evolution settings: enableAutoMerge (or explicitly use mergeSchema) when writing new columns, but prefer explicit ALTER TABLE ADD COLUMNS to make changes explicit.- Validation on writer change: include unit tests that read historical Parquet/Delta files with the new writer schema using Spark session to catch incompatibilities (e.g., type changes).- Safe patterns: add nullable columns with defaults, avoid changing types (widening primitive types is safer, e.g., int->long), avoid reusing column names with different types/meanings.- Deployment strategy: - Add new columns with defaults and keep consumers reading existing columns; consumers can opt-in to new columns when ready. - For breaking type changes, create a new table or partition namespace and backfill data; switch consumers after backfill. - Use views to provide stable schema facade: create a view that selects and coerces columns to a stable contract; update view atomically to migrate consumers.Operational practices- CI/CD: include automated compatibility checks (Schema Registry API and Spark read tests), unit/integration tests, and contract tests between producer and consumer teams.- Observability: track deserialization errors, schema-related exceptions, and consumer lag; alert on anomalies during rollout.- Documentation & governance: maintain a schema registry governance policy: allowed change types, required default values, migration owners, and runbooks for rollback.- Communication: announce schema changes, expected timelines, and migration steps to downstream consumers; provide adapters (views, compatibility layers) where possible.This combination of Registry-enforced checks, CI validation, staged deployment (canaries, dual-write, views), and governance minimizes consumer disruption while enabling safe evolution.
EasyTechnical
49 practiced
Define the difference between data quality and data governance. Provide two concrete examples of governance policies that directly improve data quality, and name one tool or automation you would use to enforce each policy.
Sample Answer
Data quality refers to the fitness of data for its intended use — accuracy, completeness, timeliness, consistency, and validity of records. Data governance is the set of policies, roles, processes, and controls that ensure data is managed, accessed, and used responsibly across the organization. In short: quality is an attribute of the data; governance is the organizational framework that creates and enforces rules to achieve and maintain that attribute.Two concrete governance policies that improve data quality (with enforcement automation):1) Schema and contract enforcement for upstream producers- Policy: All event streams and tables must publish a versioned schema/contract; incompatible changes require a compatibility check and approval.- Why it improves quality: Prevents unexpected nulls, type mismatches, and breaking changes that corrupt downstream pipelines.- Enforcement tool/automation: Confluent Schema Registry (or AWS Glue Schema Registry) hooked into CI/CD to block incompatible schema pushes and run integration tests.2) Mandatory data-quality checks before data is promoted to production datasets- Policy: Any dataset marked “production” must pass standardized checks for completeness, null thresholds, range validity, and freshness.- Why it improves quality: Catches missing partitions, drift, or invalid values before analysts consume bad data.- Enforcement tool/automation: Great Expectations or dbt tests run in Airflow/GitHub Actions as part of the pipeline; failed checks prevent deployment/promotion.These policies combined with roles (data owners, stewards) and a catalog (Amundsen/Apache Atlas) give visibility, accountability, and automated gates that raise overall data quality.
Unlock Full Question Bank
Get access to hundreds of Data Quality and Governance interview questions and detailed answers.
