Question 1

Describe a scenario where you had to optimize a slow-running data pipeline.

Accepted Answer

**STAR**: **Situation**: Nightly job took 6 hr; SLA 2 hr. **Task**: Optimize. **Action**: Profiled—found full scan and skew. Partition pruning; broadcast join for dimension; salting for skew. **Result**: 1.5 hr; 4x improvement....

Question 2

How would you monitor and reduce disk-based queries (disk spilling)?

Accepted Answer

Disk spilling occurs when memory is exceeded—often 10–100x slower. WHY: Spilling indicates memory-pressure; it kills SLAs and increases I/O cost. MONITOR: Spark UI (spilled_bytes), Snowflake query_history (bytes_spilled_to_local_storage), Redshift SVL_QUERY_METRICS. REDUCE: (1) Increase executor memory or use memory-optimized instances. (2) Broadcast joins for small tables—tune spark.sql.autoBroadcastJoinThreshold. (3) Repartition before expensive ops to distribute load....

Question 3

What are the best practices for logging and monitoring bad data?

Accepted Answer

(1) Structured logging—JSON with record_id, rule_id, severity, sample. (2) Centralized—ELK, Splunk, CloudWatch. (3) Metrics—failed count per rule; alert on spikes. (4) DLQ—store bad records for reprocessing. (5) Dashboards—DQ score, trends. (6) Sampling—log sample, not all. WHY: Root cause without full rerun. SCALABILITY: Sampling and aggregation; avoid log explosion....

Question 4

What are the limitations of Assert Transformations in complex data flows?

Accepted Answer

Limitations: (1) Single-dataset—hard to express cross-table. (2) Performance—full scan per assertion. (3) Granularity—row vs aggregate; complex rules need custom. (4) Order—upstream changes break. (5) Reporting—limited context (partition, key). (6) Schema evolution—manual updates....

Question 5

How do you ensure the scalability of a data pipeline handling rapidly growing data volumes?

Accepted Answer

**Section 1 — The Context (The 'Why')**
Scaling a data pipeline under rapidly growing volumes exposes fundamental limits: single-partition bottlenecks, consumer lag that compounds exponentially, and backpressure cascades that can stall entire systems. A naive design—monolithic consumers, unbounded queues, or hardcoded parallelism—fails when volume doubles: either the queue overflows, the source gets throttled, or downstream systems drown....

Question 6

How do you handle pipeline failures or delays?

Accepted Answer

**Section 1 — The Context (The 'Why')**
Pipeline failures and delays create cascading business impact: stale dashboards, missed SLAs, and downstream jobs blocked on unavailable data. A naive design—no retries, opaque dependencies, or manual runbooks—forces firefighting instead of self-healing. At Moonfare scale, fund data pipelines have strict regulatory windows; delays breach compliance....

Medium System Design Interview Questions

Companies asking these questions

All 6 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Medium System Design Interview Questions

Companies asking these questions

All 6 Questions

More Interview Prep Guides

Get All Answers in PDF Format