Explain how you would optimize an ETL pipeline for performance.

Official answer

Measure end-to-end freshness, throughput, scanned/transferred bytes, CPU, memory, spills and stage timings. Identify the constrained resource and preserve a reference result before changing the pipeline.

Reduce work first: select needed columns, prune partitions and avoid repeated scans or unnecessary shuffle. Pushdown can reduce transfer, but may overload a transactional source or change semantics across engines. Check null, timezone, collation and numeric behavior.

Incremental extraction must capture updates and deletions as well as inserts. A timestamp watermark may miss late corrections or ties unless its ordering/overlap policy is explicit. Couple source progress to durable output and make retries replay-safe. A faster job that silently skips rows is not an optimization.

Use bounded parallelism and suitable batches/files, respecting source and target limits. Inspect skew and join cardinality before adding workers. For example, preaggregating line items to order grain can avoid multiplying an order total, but is valid only if the required analysis still has its necessary detail.

Compare correctness, cost and latency over representative sizes and adverse cases, including restart and partial failure. Publish output only after validation, retain a rollback or rebuild path, and monitor the same metrics after the change. Report measured results instead of assumed speedups.