How to design a reliable data engineering pipeline

A reliable data pipeline isn't one that never fails — it's one that fails in a way that is visible and recoverable. These are the principles I follow when designing them.

1. Validate at the boundary, not in the middle

Every external source (APIs, third-party exports, manual uploads) should go through schema validation as soon as it enters the pipeline. If bad data takes three steps to fail, the error is far more expensive to diagnose.

2. Idempotency first

Every task in the pipeline should be retryable without duplicating data. A simple example: use MERGE/upsert instead of a plain INSERT.

python

def upsert_batch(rows, table, key_columns):
    """Insert or update without duplicating, using the key columns."""
    for row in rows:
        table.merge(row, on=key_columns)

3. The cost of a poorly measured transformation

If $n$ is the number of rows and a transformation costs $O(n^2)$ because of a poorly indexed join, the running time grows like this:

T(n) = c \cdot n^2

Doubling the data volume ( $2n$ ) doesn't double the processing time — it quadruples it. That's why profiling before scaling matters more than adding compute.

In summary

Fail fast, and in a single place.
Design for retries, not for "it never fails".
Measure the real complexity of your transformations before scaling infrastructure.