A payment succeeds at 10:03. The event is delivered twice, the second copy arrives after a network retry, and yesterday’s backfill is still writing to the same table. By lunch, the revenue dashboard is high by 4%. Nothing is “down.” Every component returned success.

That is a data-pipeline failure.

Moving bytes is usually the easy part. The difficult part is preserving meaning while data is duplicated, delayed, corrected, replayed, and read by consumers the producer has never met. ETL and ELT matter, but only as one design choice inside that larger problem.

This article builds a concrete order pipeline and uses it to examine the decisions that make a system recoverable.

The Pipeline We Are Designing

Assume an online service stores orders in PostgreSQL. Several consumers need the data:

  • an operations dashboard within five minutes;
  • a finance table that closes daily revenue;
  • fraud features with low latency;
  • a historical object-store copy for replay;
  • analysts who need corrected records without rewriting production data.

One reasonable architecture captures committed PostgreSQL changes into a durable event log, preserves a raw copy in object storage, and feeds stateful stream processing. That processor writes a low-latency operational sink and warehouse staging tables; tested transformation models then produce finance, product, and ML datasets.

The product names are deliberately absent. Kafka, Kinesis, Pub/Sub, Flink, Spark, BigQuery, Snowflake, and many other systems could fill parts of this diagram. The invariants matter more than the logo attached to each box.

Begin With the Data Contract

The producer and consumer need a shared statement of what an order event means. A useful event envelope might look like this:

{
  "event_id": "01JQ8Y6F6JZ1V8K6Q7R4M5D2T9",
  "event_type": "order.paid",
  "schema_version": 3,
  "occurred_at": "2025-03-25T10:03:14.219Z",
  "recorded_at": "2025-03-25T10:03:14.306Z",
  "order_id": "ord_84217",
  "currency": "INR",
  "amount_minor": 249900,
  "source": "checkout-api"
}

Several details are doing real work:

  • event_id identifies one occurrence and supports deduplication;
  • event_type states the business transition rather than exposing a database row blindly;
  • schema_version makes evolution explicit;
  • occurred_at records business event time;
  • recorded_at helps measure source and ingestion delay;
  • amount_minor avoids floating-point currency ambiguity;
  • currency prevents an amount from being interpreted without its unit.

A schema registry can check structural compatibility. It cannot decide whether changing amount_minor from the amount charged to the amount before tax preserves meaning. Semantic changes require ownership, review, and migration plans.

The Transaction Boundary Comes First

A fragile service performs two independent actions:

  1. commit the payment state to the database;
  2. publish an order.paid event.

If it crashes between them, the database and event log disagree. Reversing the order merely changes which inconsistency is possible.

The transactional outbox pattern writes the business change and an outbox record in the same database transaction. A connector then publishes committed outbox rows to the event log. Change-data capture can provide a similar bridge from the transaction log.

This does not create magic exactly-once delivery. The connector can still retry and a consumer can still see duplicates. What it gives us is a durable record that can be published again without guessing whether the business transaction happened.

Delivery Semantics: Name the Boundary

“Exactly once” is one of the most abused phrases in data engineering. It may refer to an operator’s state, a message in a broker, or the final effect in an external database. Those are different boundaries.

A stream processor can restore checkpointed state so each input affects its managed state exactly once. End-to-end exactly-once behavior additionally requires a replayable source and a sink that is transactional or idempotent. An email, webhook, or third-party API may not participate in that protocol.

For many pipelines, at-least-once delivery plus idempotent writes is the more useful contract:

MERGE INTO analytics.paid_orders AS target
USING staging.paid_orders AS source
ON target.event_id = source.event_id
WHEN NOT MATCHED THEN
  INSERT (event_id, order_id, occurred_at, currency, amount_minor)
  VALUES (
    source.event_id,
    source.order_id,
    source.occurred_at,
    source.currency,
    source.amount_minor
  );

MERGE syntax and concurrency guarantees vary across engines, so the actual sink must be tested under retries. The important property is that replaying the same event does not add revenue twice.

The idempotency key also has to match the business operation. Deduplicating on order_id would incorrectly discard a legitimate refund or second payment attempt. Deduplicating on a random ingestion ID would fail to recognize a replay of the same source event.

Event Time Is Not Processing Time

Suppose an order is paid at 10:03 but reaches the processor at 10:11 after a mobile connection recovers. Which ten-minute revenue window owns it?

  • event time says when the business event occurred;
  • processing time says when a worker handled it;
  • ingestion time says when it entered a particular platform.

Finance usually wants event time. An operations dashboard may care about both event time and arrival delay.

A streaming system cannot wait forever for an older event. A watermark represents the system’s estimate that event time has advanced to a point. A five-minute watermark delay is an operational policy: wait longer for completeness, or publish sooner and accept more corrections.

Late data needs an explicit path. Depending on the product, a pipeline may:

  • update a previously emitted aggregate;
  • route late events to a side output for reconciliation;
  • hold a provisional window open for a defined period;
  • reject events beyond a contractual lateness limit;
  • restate a daily table after settlement.

There is no universally correct lateness threshold. Measure the arrival-delay distribution, then choose a policy that reflects the cost of waiting and the cost of correction.

Ordering Is Usually Local, Not Global

A partitioned log can preserve order within a partition, but not across the entire topic. If events for one order must be processed in sequence, order_id is a plausible partition key. That creates another problem: a small number of busy keys can produce hot partitions.

Even per-key ordering does not resolve every business race. Consider an order.paid event at version 7, followed by order.refunded at version 8, and then a late order.corrected event carrying version 6.

Blind last-write-wins processing would let the stale correction overwrite newer state. Include a source version or sequence number and reject regressions, or model events as immutable facts and derive current state with a rule that understands their types.

Raw Data Should Be Replayable, Not Lawless

Keeping a raw layer makes recovery and new transformations possible, but “raw” should not mean undocumented, world-readable, or retained forever.

A usable replay layer needs:

  • immutable or versioned objects;
  • source and ingestion metadata;
  • checksums or manifest counts;
  • encryption and narrowly scoped access;
  • retention and deletion policies;
  • a record of the schema used to decode each file;
  • partitioning that does not create millions of tiny objects.

Sensitive fields should be minimized before broad analytical access. Loading every production column “in case it becomes useful” turns a convenient lake into a long-lived privacy and security liability.

ETL and ELT Are Placement Decisions

ETL transforms data before it reaches the analytical target. ELT loads a raw or lightly normalized form first and transforms it inside the target platform.

Neither is inherently modern.

ETL is attractive when:

  • regulated or dangerous fields must be removed before the target;
  • the destination should receive one controlled schema;
  • transformation requires an engine or library the target does not support;
  • warehouse compute is scarce or expensive;
  • source-specific normalization belongs at a shared ingestion boundary.

ELT is attractive when:

  • the warehouse can scale transformation work effectively;
  • analysts need governed access to detailed history;
  • transformations change often and should be replayed without re-extracting sources;
  • SQL-based models, tests, and lineage fit the team;
  • storage is cheaper than repeatedly calling the source.

Most serious platforms use both. The order pipeline may tokenize or remove sensitive payment fields before loading, preserve a governed raw envelope, then perform business transformations in the warehouse. Calling the whole architecture ETL or ELT loses more information than it provides.

Transformations Need Executable Invariants

“Clean the data” is not a test. Useful invariants are specific:

-- event identifiers must be unique
SELECT event_id, COUNT(*) AS copies
FROM analytics.paid_orders
GROUP BY event_id
HAVING COUNT(*) > 1;


-- amounts must use known currencies and valid units
SELECT *
FROM analytics.paid_orders
WHERE amount_minor < 0
   OR currency NOT IN ('INR', 'USD', 'EUR');


-- no paid order may reference a missing order
SELECT p.order_id
FROM analytics.paid_orders AS p
LEFT JOIN analytics.orders AS o USING (order_id)
WHERE o.order_id IS NULL;

These queries can run during a build, after a load, or continuously on a sample. Their severity should differ. A duplicate payment event may block publication; a small freshness delay may warn first and page only after an objective is breached.

Tests also need a quarantine path. Rejecting a malformed record is safer than corrupting a finance table, but silently dropping it is not. Store the rejected payload securely with its reason, source position, and retry status.

Backfills Are Production Traffic

A backfill is not an oversized ordinary run. It competes for source bandwidth, broker partitions, warehouse slots, storage I/O, and downstream API quotas.

Before starting one, record:

  • the exact input range and code version;
  • the destination or partition it may modify;
  • expected rows, bytes, and cost;
  • rate limits and pause controls;
  • how ordinary traffic is isolated;
  • validation and rollback criteria;
  • who owns the decision to publish its result.

Write backfills to a shadow table when possible. Compare counts, sums, null rates, distributions, and representative records before swapping or merging. If a transformation is not deterministic, preserve the model version, external lookup snapshot, or random seed needed to explain the result.

The worst backfill procedure is “rerun the job and see.” It turns historical correction into an uncontrolled production experiment.

Observability Should Follow the Data

CPU and task status tell us whether machinery is running. They do not tell us whether the data is usable.

For each important dataset, define service-level indicators such as:

  • freshness: age of the newest complete event-time interval;
  • completeness: received records versus an expected manifest or source count;
  • validity: fraction satisfying schema and domain constraints;
  • uniqueness: duplicate rate for the declared key;
  • reconciliation: difference between source and target totals;
  • lateness: arrival-delay percentiles and records beyond policy;
  • recovery: checkpoint age, replay lag, and failed-record backlog.

Monitor distributions rather than one global count. A pipeline can be healthy overall while one region, tenant, partition, or event type has stopped moving.

Lineage completes the picture. When a dashboard is wrong, a responder should be able to trace the displayed field to its transformation, upstream tables, source event, code version, and last successful run. A graph that only shows table names but not column logic or run versions is useful, but not sufficient for an incident.

A Design Review Before Production

Before approving a pipeline, I would want concrete answers to these questions:

  1. What is the business key, and what is the event identity?
  2. Where is the source of truth for replay?
  3. What happens after delivery is repeated?
  4. Which ordering guarantee exists, and at what scope?
  5. How are schema and semantic changes introduced?
  6. What is the late-data policy?
  7. Can a backfill run without starving current traffic?
  8. Which invariants block publication?
  9. How are sensitive fields minimized and deleted?
  10. Can an on-call responder trace one bad output to one source record?
  11. What does recovery cost in time and money?
  12. Which consumer owns the decision that the data is good enough?

If the answer to most of them is a product name, the design is not finished.

Conclusion

ETL and ELT describe where transformation happens. They do not tell us whether a pipeline can survive duplicates, late events, schema changes, retries, and backfills without quietly changing a business result.

A dependable pipeline keeps evidence. It preserves a replayable source, names its delivery boundary, makes writes idempotent, separates event time from processing time, tests business invariants, and exposes data health rather than only task health. Once those decisions are explicit, choosing where a transformation runs becomes a practical trade-off instead of an architectural identity.

References