Data Pipeline Scalability and Performance Questions

Design data pipelines that meet throughput and latency targets at large scale. Topics include capacity planning, partitioning and sharding strategies, parallelism and concurrency, batching and windowing trade offs, network and I O bottlenecks, replication and load balancing, resource isolation, autoscaling patterns, and techniques for maintaining performance as data volume grows by orders of magnitude. Include approaches for benchmarking, backpressure management, cost versus performance trade offs, and strategies to avoid hot spots.

MediumTechnical

37 practiced

Your Hive/Presto table has slow queries due to many small partitions and files created by upstream ingestion. How would you diagnose the issue and implement fixes such as compaction, re-partitioning, and ingestion pattern changes to prevent recurrence while minimizing downtime?

Sample Answer

Approach summary: diagnose with metrics and query plans, fix by compacting small files and consolidating partitions, then change ingestion to produce fewer, larger files and coarser partitions. Implement fixes incrementally to avoid downtime and monitor.

Diagnosis (what I check)- Query profiling: EXPLAIN (Presto/Hive) to see many file splits / RPC overhead.- Metastore and filesystem stats: count files per partition, average file size, total partitions (hdfs/ S3 list + aws s3api or hdfs dfs -count).- Job logs: many small map tasks / high scheduling overhead.- Data freshness/ordering needs—do we need per-minute partitions or can we coarsen?

Immediate remediation (low-risk, rolling)1. Background compaction per partition using Spark/Hive to merge small files into larger ORC/Parquet files:

python

# PySpark example: compact a single partition into 256MB files
df = spark.read.parquet("s3://data/table/partition=2025-11-01")
df.repartitionByRange(int(df.rdd.getNumPartitions() / 4)).write.mode("overwrite") \
  .option("parquet.block.size", 256*1024*1024).parquet("s3://data/table_compacted/partition=2025-11-01")
# After validation, swap partition directories or update metastore

- Run per-partition compaction in parallel but rate-limited; validate checksums/counts before swapping.- Use INSERT OVERWRITE PARTITION in Hive for transactional/managed tables.

2. Repartitioning/partition-merge:- Merge overly granular time partitions (per-minute -> hourly/daily) by rewriting partitions.- Use Hive ALTER TABLE PARTITION or backfill aggregated partitions, keep aliasing for backward compatibility.

Prevent recurrence (ingestion changes)- Produce larger files: buffer writes in the ingestion layer (Staging topic/window) and flush when a target size (e.g., 128–256MB) or time threshold is reached.- Use file-format and writer settings: Parquet/ORC with proper block/stripe sizes, compression.- Adopt transactional/merge table formats (Apache Hudi, Iceberg, Delta) that handle small-file compaction and incremental writes.- Use partitioning strategy aligned with query patterns (date for time-series, plus hashed bucketing for high-cardinality keys).

Minimizing downtime and risk- Do rolling compaction per partition; validate each before switching read pointers.- Use shadow table + atomic metadata swap (rename tables/partitions) for zero-downtime cutover.- Schedule heavy compactions off-peak; throttle resource usage to avoid impacting production.- Start with hot partitions that cause the worst latency.

Monitoring & metrics- Track avg file size, files per partition, query latency, job task counts, and compaction throughput.- Alert when file count per partition or avg file size falls outside thresholds.

Trade-offs- Coarser partitions reduce partition pruning granularity—balance with filter patterns.- Using Hudi/Iceberg adds operational complexity but gives long-term benefits (merges, upserts, compaction).

Expected outcome- Reduced number of files and map/split overhead → lower query planning and execution time.- Sustained ingestion changes prevent re-creation of small files, keeping cluster efficient.

This plan emphasizes measurable diagnostics, safe rolling compaction, ingestion fixes (buffering, writer tuning, or table format), and operational controls to minimize downtime.

EasyTechnical

39 practiced

Explain idempotency in ETL and streaming pipelines. Describe at least two practical patterns to make writes idempotent so that retries do not create duplicate records: for example, using unique deduplication keys, upserts with primary keys, or write-ahead transaction logs.

Sample Answer

Idempotency in ETL and streaming means that reapplying the same write/op (e.g., after a retry) has the same effect as applying it once — no duplicated or inconsistent records. It’s essential because pipelines fail or retry (network, consumer crashes, replays), and downstream stores must stay correct.

Pattern 1 — Deduplication using unique deduplication keys:- Idea: Each logical event has a stable id (event_id, message_id, source_timestamp+source_id). Before writing, check if that id was already processed; drop duplicates.- Example: In a streaming job write to a dedupe table keyed by event_id, or use a Redis/DB bloom filter + TTL for recent dedupe.- Pros: Simple, works with append-only stores; low latency.- Cons: Needs storage for seen ids (memory/TTL) or GC policy; long-lived IDs require more storage.

Pattern 2 — Upserts with primary key (or merge/INSERT ... ON CONFLICT):- Idea: Use a deterministic primary key for the record (order_id) and perform idempotent upsert/merge; repeated writes replace/update the same row.- Example: Use Spark’s DataFrame write with merge into Delta Lake or PostgreSQL ON CONFLICT DO UPDATE.- Pros: Works well for stateful records, avoids separate dedupe store, handles late-arriving updates.- Cons: Requires a key that identifies final state; higher write cost (random I/O) vs pure appends.

Pattern 3 — Write-ahead transaction / idempotent transaction log:- Idea: Append writes to a durable WAL with transaction IDs; consumer applies each transaction once and marks it committed. Replays skip committed txns.- Example: Use Kafka offset + transactional producer or a database transaction table with processed_txn_id.- Pros: Strong correctness, exactly-once semantics when combined with transactional sinks.- Cons: More operational complexity and latency.

Practical tips:- Prefer deterministic keys that encode source + sequence where possible.- Combine patterns: use event_id dedupe for events and upserts for derived state.- Monitor duplicate rates and track TTL/GC for dedupe state.- Test by simulating retries and partial failures to validate idempotency.

HardTechnical

36 practiced

As a senior data engineer you must prioritize between (A) rewriting a brittle pipeline to improve reliability, (B) adding new analytics features that drive revenue, and (C) reducing monthly cloud costs. Describe a decision framework with metrics you would use to decide priorities, how you'd phase the work, and how you'd communicate trade-offs to stakeholders.

Sample Answer

Decision framework (how I decide)- Clarify objectives and constraints with stakeholders (growth targets, uptime SLAs, budget caps).- Score each option against 4 weighted axes: Risk to business (incidents/month, MTTR, data accuracy), Revenue impact (ARR uplift, conversion or retention delta, time-to-value), Cost impact (monthly $ savings, payback period), and Strategic value (compliance, technical debt reduction enabling future features).- Compute a simple weighted score and expected ROI/time-to-payback. Example weights: Risk 30%, Revenue 35%, Cost 20%, Strategic 15% (adjustable).

Key metrics I use- Incidents/month, % of pipelines failing, data quality error rate, SLO attainment, MTTR- Estimated incremental revenue (monthly ARR or feature-driven uplift), time-to-market for analytics- Monthly cloud spend savings, one-time implementation cost, payback period- Probability-adjusted expected value = (benefit * probability) - cost

Phasing the work1. Triage (0–2 weeks): quick wins — fix high-risk brittle pieces causing most incidents (80/20), add monitoring/alerting and runbooks.2. Parallel tracks (1–3 months): small cross-functional team for revenue-driving features in sprints while infra team executes a prioritized rewrite in increments (strangler pattern) to reduce risk gradually.3. Optimization (3–6 months): implement cost measures (rightsizing, reserved instances, tiered storage) after reliability and revenue features are validated to avoid harming availability.4. Iterate: measure impact, re-score priorities monthly.

Communication & stakeholder alignment- Present a one-page decision memo with scored options, expected ROI, risks, and timelines.- Use dashboards (SLOs, cost, revenue metrics) and weekly updates.- Define RACI, clear success criteria for each phase, and contingency/legal/compliance risks.- Frame trade-offs quantitatively: e.g., “Rewriting reduces incidents by 70% in 3 months (avoids $X/month in lost analytics ops), delays feature launch by 4 weeks — net NPV = …”This approach balances short-term stability, revenue growth, and cost, and makes trade-offs transparent and measurable.

MediumTechnical

40 practiced

Compare at-least-once, at-most-once, and exactly-once delivery semantics in streaming systems. For a pipeline ingesting financial transactions into an analytics warehouse, which semantics would you choose and why? Discuss practical implementation considerations and trade-offs.

Sample Answer

At-least-once, at-most-once, and exactly-once describe how systems handle message delivery in the face of failures:

- At-most-once: Each message is delivered zero or one time. Fast and simple (ack before processing), but can lose data on failure. Use when occasional loss is acceptable.- At-least-once: Each message is delivered one or more times until acknowledged. Reliable for durability but can produce duplicates; consumers must handle idempotency or deduplication.- Exactly-once: Guarantees each logical message affects the destination exactly once despite retries. Achieved via coordinated transactions, idempotent writes, or deduplication with stable identifiers. More complex and often more expensive.

For ingesting financial transactions into an analytics warehouse I would choose exactly-once semantics (or functionally equivalent guarantees). Rationale: financial analytics require correctness for balances, fraud detection, reporting — duplicates or losses can cause incorrect metrics, regulatory issues, and business risk.

Practical implementation considerations and trade-offs:- Tooling: Use Kafka transactions + Kafka Connect exactly-once sinks or streaming frameworks with two-phase commit or atomic writes (e.g., Spark Structured Streaming with idempotent sinks, Flink with checkpointed two-phase commit).- Idempotence: If exact atomic transactions to warehouse aren’t supported, implement idempotent upserts using a unique transaction_id and CDC/upsert logic in the warehouse (MERGE on primary key). This reduces need for strict exactly-once plumbing.- Performance & complexity: Exactly-once increases latency, operational complexity, and resource use (coordination, state storage). At-scale, it may limit throughput.- Ordering and dedup state: Keep durable checkpoints (offsets, watermarking). Maintain a deduplication window or persistent dedup store to bound state growth.- Failure modes: Plan for partial failures (sink failures after commit), schema evolution, and compaction. Test recovery paths and data reconciliation (periodic audits, checksums).- Cost: Evaluate business cost of duplicates/losses vs engineering/infra cost of exactly-once; hybrid approaches often used — at-least-once ingestion with idempotent writes to the warehouse.

Summary: Prefer exactly-once (or at least idempotent upserts keyed by transaction_id) for financial pipelines. If full distributed transactions are impractical, implement strong uniqueness guarantees in the sink and use at-least-once delivery upstream to ensure durability while preventing logical duplicates.

MediumTechnical

40 practiced

Describe ingestion patterns including push vs pull, batch vs streaming, and change-data-capture (CDC) from OLTP databases to a data lake/warehouse. For each pattern explain throughput, latency, complexity, and best use cases where you would prefer it.

Sample Answer

Start by clarifying terms: push vs pull (who initiates transfer), batch vs streaming (how data is grouped and delivered), and CDC (capturing row-level changes from OLTP for near-real-time replication).

Push vs Pull- Description: Push — source emits data to sink (events, webhooks, agents). Pull — sink polls source or reads from an API/connector.- Throughput: Push can achieve high throughput if source scales; pull throughput limited by polling frequency and parallelism.- Latency: Push often lower latency (near real-time); pull has higher and variable latency tied to poll intervals.- Complexity: Push requires source instrumentation and backpressure handling; pull simpler to implement but needs scheduling and idempotency.- Best use: Push for event-driven microservices, logs, IoT. Pull for legacy systems or APIs where you can't change source.

Batch vs Streaming- Description: Batch — collect windows of data and transfer/transform in jobs. Streaming — continuous processing of individual events or small windows.- Throughput: Batch optimized for very large volumes (bulk transfers); streaming handles steady, continuous throughput.- Latency: Batch high latency (minutes to hours); streaming low latency (sub-second to seconds).- Complexity: Batch simpler (retries, checkpoints are coarse); streaming requires state management, windowing, exactly-once semantics, and operational complexity.- Best use: Batch for daily reports, large historical loads, bulk ETL. Streaming for real-time analytics, monitoring, personalization.

Change Data Capture (CDC) from OLTP- Description: CDC reads DB transaction logs (binlog/WAL/redo) to emit inserts/updates/deletes to downstream systems.- Throughput: High — leverages DB write-ahead logs; efficient for high TPS without full table scans.- Latency: Low — near real-time (seconds), depending on connector and network.- Complexity: Medium–high: requires schema evolution handling, ordering, transactional boundaries, and idempotency. Must manage initial snapshot, backpressure, and late-arriving data.- Best use: Keep data warehouse/lake near real-time for reporting, analytics, and feeding streaming consumers; transactional audit trails; syncing master data.

Trade-offs & when to choose- Use batch when data freshness is less important and you want simplicity and cost-efficiency at large scale.- Use streaming (or push) when low-latency decisions matter (fraud detection, personalization).- Use CDC when migrating or continuously replicating OLTP state without impacting source performance; combine CDC + stream processors (Kafka + Debezium + Kafka Connect + Spark/Flink) to provide exactly-once transforms and load into a data warehouse (e.g., Snowflake, BigQuery) or data lake (Delta/ICEBERG).- Hybrid: initial batch snapshot + CDC for ongoing deltas is a common pattern to balance complexity and correctness.

Operational considerations: idempotency, schema evolution, ordering, backpressure, monitoring/SLAs, and cost (eg. egress, compute).

Unlock Full Question Bank

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

Join thousands of developers preparing for their dream job.