Data Pipeline and Data Quality Questions

Designing, operating, and optimizing reliable data pipelines and ensuring data quality across ingestion, transformation, and consumption. Covers extract transform load and extract load transform patterns, efficient incremental and batch loading, idempotent processing, change data capture, orchestration and scheduling, and performance tuning to meet service level objectives. Includes data validation strategies such as schema enforcement, null and type checks, range and referential integrity checks, deduplication, handling late arriving and out of order data, reconciliation processes, and data profiling and remediation. Emphasizes observability, monitoring, alerting, and root cause analysis for data quality incidents, as well as data lineage tracking, metadata management, clear ownership and process discipline, testing and deployment practices, and governance to maintain data integrity for analytics and business operations. Also covers data integration concerns across customer relationship management systems, marketing automation systems, reporting systems, and other operational systems, including pipeline error handling, data contracts, and how test and validation checks can be integrated into pipelines to prevent regressions.

MediumTechnical

27 practiced

Design an error-handling strategy for data pipelines that automatically retries transient failures, quarantines malformed records to a dead-letter queue, notifies owners, and supports easy replay of quarantined records after fixes. Include practical components and how the system avoids blocking downstream consumers indefinitely.

Sample Answer

Requirements:- Automatically retry transient failures with exponential backoff.- Quarantine malformed/poison records to a dead-letter queue (DLQ).- Notify owners on quarantine with context.- Support easy replay of quarantined records after fixes.- Do not block downstream consumers indefinitely.

High-level design:Producer/ingest → Validation & Enrichment Layer → Processing Workers → Output topic / downstream ↘ DLQ (quarantined) & Metadata storeAuxiliary: Retry service, Notification service, Replay service, Monitoring/alerts

Components & responsibilities:- Validation & Enrichment (stream/batch): lightweight schema + semantic checks. Reject clearly malformed records immediately to DLQ with error codes.- Processing Workers (e.g., Spark/beam/Flink or Kafka consumer + worker): handle business transforms. On transient errors, use retry policy (in-process try with exponential backoff + jitter, bounded retries). If retries exhausted, send record + context to DLQ asynchronously.- DLQ storage: append-only durable store (Kafka DLQ topic or S3/GCS + manifest). Each DLQ entry includes original payload, error code, stack trace, timestamps, attempt count, schema version, producer metadata, and unique id.- Metadata & Index: small database (Postgres/Elastic) indexing DLQ entries for search, owner, and status (quarantined, acknowledged, fixed, replayed).- Notification service: on DLQ write create alert to relevant owner (Slack/email) with link to DLQ entry, pre-signed payload access, and suggested remediation.- Replay service: UI/CLI that queries metadata, allows selecting entries, optionally applying transformations/fixes (bulk patch), and re-enqueues to ingest topic or a dedicated replay topic with idempotency keys. Replay marks entries as replayed and records outcome.- Monitoring & Circuit Breaker: system-wide metrics for DLQ growth, retry rates, consumer lag. If processing latency or error rate spikes, open circuit to prevent unbounded retries and accelerate fallthrough to DLQ.- Idempotency & Ordering: include unique idempotency keys to prevent double-processing during replays. Preserve ordering where necessary by replaying to keyed topics/partitions.

Avoiding indefinite blocking:- Downstream consumers should never block awaiting fixes. Strategies: - Side-output pattern: invalid records diverted to DLQ; downstream receives only validated records. - Placeholders / tombstones: if a record cannot be produced for a logical key, produce a "null" tombstone or status-message so downstream consumer can progress and mark data as missing rather than stall. - Backpressure limits and bounded queues: processing workers have bounded retry budgets and move to DLQ when exceeded; they do not retry forever. - Circuit breaker ensures high failure rates switch to fail-fast behavior, protecting downstream consumer SLAs.

Practical tech choices:- Streaming platforms: Kafka topics for main stream and DLQ; use Kafka Connect or Debezium for ingestion. For cloud: Pub/Sub + Dead Letter topics or SQS with DLQ.- Storage for large payloads: S3/GCS with DLQ manifest (if payloads large), index in DB.- Orchestration/Replay: Airflow / custom service + web UI for triage and replay.- Notifications: PagerDuty/Slack/email via serverless functions.

Example flow:1. Record fails JSON schema -> Validation layer writes DLQ entry (error=SCHEMA) + increments metric → Notification to owner.2. Record transiently fails in processing -> worker retries 3 times with backoff; if still failing, write DLQ (error=TRANSIENT_MAX_RETRIES), index entry, notify.3. Owner fixes schema or enrich transformation; through UI selects DLQ entries and replays to replay-topic. Consumer consumes replayed records with idempotency key -> successful processing marks DLQ entry status=resolved.

Operational notes:- Define retry policies per error class (transient vs permanent).- Keep DLQ retention and access controls; track ownership per data source.- Automate alerts thresholds (e.g., >X DLQ/day triggers on-call).- Test replay path regularly to ensure idempotency and ordering are preserved.

This strategy balances reliability, observability, and operational workflow: transient problems get retried automatically; permanent/malformed data is quarantined and surfaced to humans without blocking downstream consumers; and quarantined data can be inspected, fixed, and replayed safely.

MediumTechnical

45 practiced

Explain how partitioning strategy and file compaction affect query performance for an S3 + Parquet data lake. Discuss partition key selection, the small-files problem, compaction cadence, trade-offs with update/append patterns, and how partition pruning works for common analytic queries.

Sample Answer

Partitioning and file compaction strongly affect query latency and cost in an S3 + Parquet data lake because they determine how much data a query must read and how many objects the engine must open.

Partition key selection- Choose high-cardinality keys carefully: too many distinct values (e.g., user_id) creates many small partitions; too coarse (single file) prevents pruning. Time-based (date/hour) is common for ingestion streams because queries often filter by time. Combine keys (date + country) when query patterns justify it.- Pick keys aligned to query predicates: look at top analyst queries and SLA to balance pruning effectiveness vs. number of partitions.

Small-files problem & compaction- Many small Parquet files increases per-query overhead (object metadata calls, task scheduling), reduces compression/columnar encoding efficiency, and hurts vectorized reads.- Compaction merges small files into larger row-group-optimized Parquet files (e.g., 256 MB compressed target). This improves throughput and reduces overhead.

Compaction cadence & trade-offs- Cadence depends on ingestion rate and query freshness needs: near-real-time systems use frequent small compactions or streaming formats (Delta/Iceberg) with background compaction; batch systems can compact daily.- Trade-offs: aggressive compaction increases write IO and can delay commit visibility; infrequent compaction keeps many small files and higher read cost. Use incremental compaction (merge files within a partition) and prioritize hot partitions.

Update vs append patterns- Pure append fits parquet + S3 well. Updates/deletes require a table format (Delta, Iceberg, Hudi) with rewrite/merge strategies. Update-heavy workloads entail more compaction/rewrite and bookkeeping; consider micro-partitions or change tables to limit rewrite scope.

Partition pruning mechanics- Engines (Spark, Presto, Athena) use partition metadata to skip entire prefixes on S3—no object read if partition values are not in predicate. Effective pruning requires predicates on partition keys and consistent partition naming (e.g., date=2025-11-22/).- Column pruning further reduces IO by only reading needed columns inside Parquet files, but it doesn’t avoid listing many small files.

Practical guidance- Profile query predicates first; choose date-based primary partitioning, second-level partitions only when justified.- Aim for large Parquet files (100–512 MB compressed) via scheduled compaction.- Adopt a table format (Iceberg/Delta/Hudi) if you need updates or ACID.- Monitor file counts, object sizes, and query latency; tune compaction cadence and partition scheme iteratively.

MediumSystem Design

27 practiced

Design an orchestration and scheduling layer for company-wide data pipelines. Requirements: 200 daily pipelines, support for DAG dependencies, ad-hoc backfills, SLA of 99% on-time completion, multi-tenant isolation, retry policies, parameterized runs, and observability. Explain component choices (e.g., Airflow/Kubernetes/Celery), multi-cluster vs single cluster trade-offs, and how you would implement priority and resource fairness.

Sample Answer

Requirements clarification:- 200 daily pipelines, DAG dependencies, ad-hoc backfills, parameterized runs, SLA 99% on-time, multi-tenant isolation, retries, observability, and fairness/priority.

High-level architecture:- Orchestrator: Apache Airflow (managed or self-hosted) for DAG semantics, scheduling, parameterized runs and backfills.- Execution: Kubernetes for containerized task execution (KubernetesExecutor / KubernetesPodOperator) to enforce resource isolation and autoscaling.- Queueing/worker: Celery or native Kubernetes jobs for short tasks if needed, but prefer Kubernetes for unified infra.- Metadata & state: Airflow metadata DB (Postgres) with HA and backups.- Artifact storage: S3/GCS for logs and intermediate data.- Observability: Prometheus + Grafana for metrics, ELK/Fluentd for logs, OpenTelemetry traces for long tasks; Airflow UI + custom SLA dashboard.- RBAC & multi-tenant isolation: Use namespaces per tenant in Kubernetes + Airflow instance-per-tenant or multi-tenant Airflow with Role-Based Access Control, DAG tagging and queue separation. For stricter isolation, run multiple Airflow clusters (one per team) on separate k8s namespaces or clusters.

Key features implementation:- DAG dependencies & parameterized runs: Native Airflow DAGs, TriggerDagRun API supporting conf for parameters and ad-hoc backfills (BackfillOperator or CLI).- Retry policies: Task-level retry/backoff configured in DAG, plus centralized policy templates enforced via DAG factory.- SLA enforcement: Use Airflow SLA callbacks to alert and automated remediation (retries, restart tasks). To meet 99% on-time, overprovision critical task queues, set higher priority classes and autoscale worker pools; implement canary runs and capacity planning with SLO monitoring.- Priority & fairness: Use Kubernetes QoS and PriorityClasses + fair queueing in Airflow pools and weighted pool slots per tenant. Implement a scheduler tier: high-priority pipelines mapped to dedicated node pools with resource reservations; best-effort jobs run on preemptible nodes.- Ad-hoc backfills and resource control: Backfills require orchestration to avoid impact—use capacity-aware scheduling: backfill jobs acquire tokens from a global semaphore, and run in lower-priority pool or dedicated backfill pool.- Multi-cluster vs single-cluster trade-offs: - Single cluster pros: simpler ops, shared resources, easier cross-pipeline communication, lower cost. - Single cluster cons: noisy neighbor risk; harder to guarantee strict isolation/SLA. - Multi-cluster pros: strong isolation, per-tenant SLAs, independent upgrades. - Multi-cluster cons: operational overhead, cross-cluster data movement complexity. - Recommendation: Start single cluster with strong namespace/resource quotas and PriorityClasses; move high-value tenants to separate clusters when isolation needs grow.

Scalability & reliability:- Autoscale node pools based on queue length/metrics.- HA Airflow: multiple schedulers, webservers behind load balancer; replica DB with failover.- Chaos/testing: Fault-injection tests and regular disaster recovery drills.- Monitoring & alerts: SLO dashboards, incident runbooks, automated retry/rollback playbooks.

Trade-offs:- Airflow provides DAG features and ecosystem; heavier for small tasks. Kubernetes unifies execution but adds platform complexity. Celery helps for legacy quick tasks but complicates topology. Balance by standardizing on KubernetesExecutor and reserve Celery only if needed.

This design balances DAG expressiveness, tenant isolation, SLA guarantees, and operational simplicity while providing mechanisms for priority, fairness, and observability.

MediumTechnical

33 practiced

How would you design metadata management and a data catalog to support discoverability, dataset quality tracking, ownership, automated lineage ingestion from orchestration systems, and integration with access control? Describe schema for metadata, ingestion methods, and how to keep the catalog fresh.

Sample Answer

Start by clarifying requirements: support discovery (search/tags), dataset quality metrics and alerts, explicit ownership, automated lineage ingest from orchestrators (Airflow/Prefect), and integrate with RBAC/ACLs.

High-level design- Catalog service (metadata DB + search index + API)- Ingestion layer: connectors for orchestration events, data stores, and manual UI/forms- Lineage processor: parses DAGs and emits directed graph entries- Quality engine: attaches metrics and statuses to dataset versions- Auth connector: syncs with IAM to expose ACLs in catalog

Recommended metadata schema (JSON stored in a metadata DB; simplified example):

json

{
  "dataset_id":"db.table@v1",
  "name":"payments.transactions",
  "description":"Transaction records",
  "owner":{"user_id":"u123","team":"payments"},
  "tags":["finance","pii","sensitive"],
  "schema":[{"name":"id","type":"uuid"},{"name":"amount","type":"decimal"}],
  "quality":{"last_run":"2025-11-20T10:00Z","metrics":{"null_rate":0.01,"row_count":12345},"status":"PASS"},
  "lineage":{"upstreams":["raw.payments_s3"],"downstreams":["analytics.revenue_daily"]},
  "access":{"policies":[{"principal":"team:analytics","permission":"read"}]},
  "provenance":{"created_by":"airflow:dag/payments_etl","created_at":"2025-01-01T00:00Z"}
}

Ingestion methods- Orchestrator hooks: Airflow/Prefect plugins emit events (task start/complete, dataset produced) to catalog API; parse DAG to create node/edge lineage.- Connectors: crawl data catalogs (Hive Metastore, Glue, BigQuery), schema registries, and object stores for initial bootstrap.- Streaming CDC: capture dataset changes for freshness.- UI & APIs: allow stewards to edit descriptions, tags, and ownership.

Keeping catalog fresh- Event-driven updates: prefer push from orchestrators and CI jobs to update metadata and lineage immediately after jobs run.- Periodic crawlers: scheduled reconciliations (daily) to catch missed changes.- Versioning: store dataset versions; retain historical lineage and quality snapshots.- Monitoring & alerts: detect stale metadata (no updates > threshold) and broken lineage; notify owners.- Data quality automation: run lightweight checks post-job that report metrics into catalog.

Integration with access control- Sync IAM groups/roles into catalog; store ACLs alongside metadata.- Enforce via query-time checks or integrate with policy engines (e.g., Open Policy Agent) to return only allowed search results.- Record audit logs for access and metadata changes.

Trade-offs & notes- Event-driven gives low latency but needs orchestration changes; crawlers are easier but eventual consistency.- Keep schema extensible (JSON fields) to add domain-specific properties.- Prioritize developer ergonomics: SDKs, CLI, and web UI to drive adoption.

EasyTechnical

33 practiced

Explain the differences between ETL and ELT in modern cloud data platforms. Describe concrete scenarios where you would prefer ETL versus ELT (examples: Snowflake, BigQuery, S3-based data lake) and discuss trade-offs including compute locality, cost model, transformation ownership, governance, reprocessing cost, and query performance for downstream analytics and ML feature stores.

Sample Answer

Definition & core difference: ETL (Extract → Transform → Load) runs transformations before loading data into the target; ELT (Extract → Load → Transform) loads raw data first and transforms inside the target. Both aim to deliver clean, queryable data but differ in where compute happens and who owns transformations.

When to prefer ETL:- Classic data warehouse targets with limited compute elasticity or licensing (on-prem/older appliances) where you want compact, curated tables loaded—transform upstream to reduce storage and downstream compute.- S3-based data lake where you need to canonical, schema-on-write Parquet/ORC (e.g., for downstream systems that expect cleaned files). Use Spark/Flink to transform, partition, and optimize files before landing.- Scenarios with strict governance and schema enforcement before data is visible to analysts.

When to prefer ELT:- Cloud warehouses with cheap, elastic compute and strong SQL engines (Snowflake, BigQuery). Load raw data and push transformations into the warehouse using SQL, dbt, or materialized views. Fast iteration, easy reproducibility, and flexible ad-hoc analytics.- Feature store pipelines where you want raw histories in the warehouse to derive new features quickly without re-ingesting.

Trade-offs:- Compute locality: ETL runs compute where transformation engine is (Spark cluster, ETL service); ELT runs inside target (warehouse compute). Locality affects data movement: ETL moves transformed smaller data; ELT moves raw data then transforms in-place.- Cost model: ETL often pays for separate compute clusters and storage; ELT shifts cost into warehouse execution and storage. With pay-per-query warehouses, frequent heavy transformations can be costlier in ELT; but ELT avoids duplicated compute for multiple consumers.- Transformation ownership: ETL centralizes transforms in engineering jobs (good for strict pipelines); ELT democratizes transforms (analysts use SQL, aided by frameworks like dbt). Ownership should be explicit—use CI, tests, and version control.- Governance & lineage: ETL enforces schema-on-write and simpler access control; ELT preserves raw data for lineage and reprocessing but requires stricter governance (access policies, catalogs, and masking).- Reprocessing cost: ETL requires re-running upstream jobs and reloading; ELT can re-run SQL transformations on raw data more cheaply if warehouse compute is elastic—however reprocessing massive historical data can be expensive either way.- Query performance for analytics & ML: ETL can precompute and optimize storage (columnar files, partitions) for low-latency reads and smaller storage footprints—good for BI dashboards with tight SLAs. ELT benefits from the warehouse's query optimizer and materialized views; for feature stores, ELT makes building derived features faster but may need additional materialization/serving layers for low-latency online features.

Practical patterns:- Hybrid: Ingest raw into S3 or staging schema, run light cleaning (ETL) to enforce PII removal, then ELT in Snowflake/BigQuery for modeling with dbt. This balances governance, cost, and agility.- Large-scale batch-heavy transformations (e.g., video/frame processing) often require ETL on specialized clusters before loading.

Conclusion: choose ETL when pre-load cleansing, strict governance, or storage optimization outweighs iterative agility. Choose ELT when you want rapid iteration, centralized raw data for lineage, and to leverage modern warehouse compute and SQL tooling.

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.