Pipelines & ETL

Data pipeline tooling moves data from where it is produced to where it is analysed, and transforms it on the way. The launches here cover ingestion connectors, transformation frameworks, orchestration, and the streaming tools for when batch is not fast enough.

The most consequential architectural decision is where transformation happens. Transforming before loading was the historical default when storage and compute were expensive; loading raw and transforming inside the warehouse is now standard, and it is better for a specific reason: the raw data is preserved, so when you discover a transformation was wrong you can fix it and reprocess rather than having permanently lost the original. Any pipeline design that discards the source is one bad assumption away from an irrecoverable problem.

Connectors are the least interesting and most valuable part. A managed connector to a common source handles pagination, rate limits, schema changes, incremental sync and the specific API's peculiarities — all of which are individually trivial and collectively a maintenance burden. Building your own is reasonable for one source and unwise for twenty.

Schema drift is where pipelines break in production, because upstream systems change without telling you. A source adds a column, changes a type, or starts sending nulls in a field that never had them. Tools that detect drift and either adapt or fail loudly are far better than ones that silently coerce, because coercion produces wrong numbers rather than an error, and wrong numbers are found much later.

Orchestration is about dependencies and recovery. What runs after what, what happens when a step fails halfway, and can you rerun a single day without reprocessing a year. Backfill capability is the feature you will need urgently and unexpectedly, and evaluating it during a calm week is much better than during an incident.

Idempotency deserves explicit attention: a pipeline step that produces different results when run twice will eventually be run twice, and the duplicate rows will surface as a metric that inexplicably doubled.

Streaming is genuinely necessary for a narrow set of use cases — fraud detection, operational alerting, live personalisation — and substantially more complex to operate. If hourly is acceptable, batch is the right answer.