Pipelines that run at 2 AM without anyone watching

Idempotency first. A pipeline must produce the same result whether it runs once or five times. In practice this means writes are merges keyed on a business identifier rather than appends, partitions are overwritten atomically rather than added to, and any external side effect is guarded by a deduplication key. Without this, retries are dangerous and backfills are terrifying — which is why so many organisations avoid both and instead accumulate wrong data.

Then dependency on data rather than time. A job that runs at 03:00 because the source usually lands by 02:30 will eventually run against yesterday's file. Sensors wait for the actual arrival, with a timeout that raises an alert rather than proceeding silently.

Then observability. Every run records rows in, rows out, rows rejected, duration and the version of the code that produced it. Anomalies against historical norms — a table that usually receives 40,000 rows receiving 400 — trigger investigation before the number reaches a report.

Then reconciliation. For any pipeline that carries financial or operational significance, counts and control totals are compared against the source and published. This is what lets a finance team trust the platform rather than re-checking it in Excel.

Databases are the easy part — PostgreSQL, MySQL, SQL Server, Oracle and MongoDB, extracted either as full snapshots for small tables or through change data capture for large ones. CDC is materially better where it is available: it captures deletes, it does not hammer the source, and it gives you a genuine event stream rather than a periodic photograph.

ERPs are the interesting part. Tally through its XML interface, SAP through extractors or direct table reads, and a long tail of bespoke systems where the only reliable interface is a scheduled export to a folder. We build defensively here: validate that a file is complete before processing, handle the day the format changes without warning, and keep the raw file so a bad load can be replayed.

Then everything else: REST and GraphQL APIs with rate limiting and pagination handled properly, SFTP drops from partners, plant historians and OPC servers, Google Analytics and advertising platforms, spreadsheets from a shared drive that somebody insists on maintaining, and web sources through our scraping practice.

For analytics transformation we use dbt, and the reason is organisational as much as technical. Transformation logic written as SQL models in version control, with tests and documentation attached, is legible to analysts as well as engineers. That widens the group of people who can contribute and review, which is usually the real bottleneck.

The discipline dbt brings matters more than the tool: every model is a select statement with a single responsibility, dependencies are inferred rather than declared manually, tests run on every build, and documentation with lineage is generated automatically. A new analyst can open the docs and see what a table means, where it comes from and what tests guard it.

Where transformation genuinely needs procedural code — complex parsing, machine learning feature generation, plant-specific calculations with iterative logic — we use Spark or Python and keep it clearly separated rather than contorting SQL to do something it does badly.

We build streaming pipelines for control-room alerting, live operational dashboards, fraud and anomaly detection, and any case where a delayed number means a missed intervention. Kafka provides durable, replayable ingestion; Spark Structured Streaming or Flink does the processing; and the output lands both in a serving store for immediate query and in the lakehouse for history.

The engineering discipline here is different from batch. Late data must be handled explicitly through watermarks. Exactly-once delivery requires idempotent sinks and checkpointed offsets. State must be bounded or it grows until the job dies. And operational tooling must let you replay from an offset when logic changes, which is why we keep raw events in Kafka with a retention period long enough to reprocess.

For clients who need low latency but not true streaming, micro-batch every one to five minutes is often the better engineering trade — most of the benefit, considerably less operational complexity.

Many engagements start with an estate of legacy pipelines — SSIS packages, Informatica jobs, cron-driven shell scripts, or a folder of Python files that one person understands. Migration is done incrementally and with proof.

We run old and new in parallel, comparing outputs row by row until they match, and only then decommission the old. Where the old pipeline was subtly wrong — which happens more often than clients expect — we surface the discrepancy and let the business decide which behaviour is correct rather than assuming. That conversation is frequently the most valuable part of the migration.

We also take the opportunity to remove what is not used. In a typical estate, twenty to thirty per cent of scheduled jobs feed reports nobody has opened in a year. Turning them off is free performance and free cost saving.

A pipeline estate needs an operating model, not just code. We define ownership per pipeline, an SLA appropriate to its business use, and an escalation route that reaches someone who can act. Alerts are tuned to be meaningful — an estate that pages for everything trains people to ignore it, which is worse than no alerting.

Every failure mode we can anticipate gets a runbook: what the alert means, how to diagnose, what to check, and whether the correct action is retry, skip, backfill or escalate. This is what allows a client's own team to operate the platform overnight without needing the engineer who built it.

For clients who prefer us to run it, we offer managed operation with defined response times, including overnight cover for the batch window when most failures actually happen.