OrchestrationGENERALBROKER-SPECIFICSOURCE-SPECIFIC

The High-Water Mark

"Processed through offset X" — the one piece of state that decides what a restart re-reads, and why a log position is a promise and a timestamp is a guess.

What actually happens How to build it Can I trust it?

Who needs this, what one row is, and why the obvious build breaks

Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.

The question

After a crash, where does this pipeline resume — and is the thing it resumes from an exact position or an approximation of one?

Who needs this

Anyone reading a dataset built incrementally. The high-water mark is the reason yesterday's rows are all present or quietly not, and no consumer can see which (Missing Rows).

What one row is

The unit is a position in an ordered sequence: a log offset, a WAL location, a file-and-position, or — weakest of the family — a timestamp. What makes something usable as a high-water mark is that "everything at or before this point is processed" is a true and checkable statement.

The obvious build

Store last_processed_timestamp in a small table, and on each run read everything strictly greater than it. It is easy to implement, it is readable by humans, it works with any source that has a modification timestamp, and it is what most pipelines do.

Why it breaks

A transaction begins at 10:00:00, updated_at is assigned then, and it commits at 10:00:04. A run at 10:00:02 reads up to now(), stores 10:00:02, and that row — timestamped 10:00:00 and committed after — is never greater than the watermark again. It is gone, silently, and no check inside the pipeline can see it (MVCC: Multi-Version Concurrency Control).

How it breaks with real data

A transaction begins at 10:00:00, updated_at is assigned then, and it commits at 10:00:04. A run at 10:00:02 reads up to now(), stores 10:00:02, and that row — timestamped 10:00:00 and committed after — is never greater than the watermark again. It is gone, silently, and no check inside the pipeline can see it (MVCC: Multi-Version Concurrency Control).
The source clock and the pipeline clock disagree. A watermark set from the reader's clock skips rows the source stamped slightly in the past; one set from the source's clock inherits every adjustment that source makes (Processing Time).
Two rows share the exact watermark value. Strictly-greater misses one of them; greater-or-equal reprocesses both, which is only safe if the write is idempotent (Idempotent Data Pipelines).
A row is updated with a backdated timestamp — a correction, a migration, a partner re-delivery — and lands permanently below the watermark (Late-Arriving Data).
The watermark is advanced before the write commits. The process dies between the two, and the range is marked processed while its output does not exist (Partial Failure).
Two workers advance the same watermark concurrently, and the surviving value reflects whichever wrote last rather than what was actually processed (Reasoning About Races: A Method, Not an Instinct).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

A high-water mark is a claim: everything at or before this position has been processed and its output is durable. Whether the claim is true depends entirely on whether the position and the output are committed together (Transactions and ACID).
A log position is exact. Offsets in a partitioned log, a WAL location, a binlog file-and-position: each identifies one record, is assigned by the source in commit order, advances monotonically, and can be replayed from (Offsets and Commits, Write-Ahead Logging).
A timestamp is an approximation of that ordering. It is assigned at some point during a transaction, becomes visible at a different point, is not unique, is not monotonic across concurrent writers, and can move backwards when a clock is corrected. It answers "roughly when" and not "exactly where".
The difference is a difference in guarantee, not in precision. Adding decimal places to a timestamp does not make it a position; the gap that loses rows is between timestamp assignment and commit visibility, and no resolution closes it (Isolation Levels).
Advancing the mark is the commit point of the whole pipeline. Store it in the same transaction as the output if you can; if you cannot, advance it after the output is durable, so a crash re-processes rather than skips (Checkpointing).
That ordering choice is exactly the at-least-once versus at-most-once choice, and there is no third option. Advance-after gives duplicates on restart, which idempotent writes absorb; advance-before gives gaps, which nothing absorbs (At-Least-Once Delivery).

A position, and the gap a timestamp cannot close

SOURCE-SPECIFICThe size of the assignment-to-visibility gap depends on the source: it is bounded by transaction duration in a row-store under MVCC, is effectively zero for an append-only log that assigns positions at commit, and is entirely undocumented for a SaaS API whose modified-since semantics you cannot inspect. The failure mode is the same everywhere; the width of the window is not.

Two things are commonly called a watermark and only one of them supports the claim that matters. A log position identifies a record in an ordering the source itself produced; a timestamp is a value written into a column at some point during a transaction. The first can carry the statement "everything up to here is processed". The second cannot, and the reason is worth working through slowly, because it is the mechanism behind a very large share of silently missing rows.

The sketch below shows both. In the log, positions are assigned at commit and are dense and monotonic: storing 1047 means every record through 1047 is done and 1048 is next, with no ambiguity and no dependence on any clock. In the timestamp world, updated_at is assigned when the statement runs and the row becomes visible when the transaction commits, and those are different moments.

Follow row T3. Its timestamp is 10:00:00 and it commits at 10:00:04. A run at 10:00:02 sees rows T1 and T2, stores 10:00:02 as the mark, and finishes. When T3 becomes visible, its timestamp is already below the mark, so no future run with a strictly-greater predicate will ever return it. The row exists in the source, it is absent from the target, both systems are behaving correctly, and nothing anywhere raises an error (MVCC: Multi-Version Concurrency Control).

The loss is caused by the gap between timestamp assignment and commit visibility — not by clock precision, and not by a bug in anyone's code.
The gap widens with transaction duration and with concurrency, so this failure gets worse exactly as the system gets busier (Isolation Levels).
Whose clock matters independently: the reader's clock skips rows the source stamped in its own past; the source's clock inherits every correction and every replica's drift (Processing Time).
A row updated with a backdated timestamp — a correction, a migration, a re-delivered partner file — lands below the mark by construction and is invisible to every future run (Late-Arriving Data).
Ties are a separate, smaller problem: strictly-greater loses rows sharing the mark's exact value, greater-or-equal reprocesses them, and only the second is safe — and only with an idempotent write.

LOG POSITION — exact, monotonic, replayable

  offset:  1044   1045   1046   1047 │ 1048   1049   1050
  record:   r1     r2     r3     r4  │  r5     r6     r7
                                     │
                        stored mark ─┘  "processed through 1047"
                                        resume at 1048. no ambiguity.


TIMESTAMP — approximate, non-monotonic under concurrency

  txn   updated_at assigned     committed / visible      seen by a run at 10:00:02?
  ───   ───────────────────     ───────────────────      ─────────────────────────
  T1    10:00:00                10:00:00                 yes
  T2    10:00:01                10:00:01                 yes
  T3    10:00:00                10:00:04                 NO  ← below the mark forever
  T4    10:00:02                10:00:06                 NO  ← below the mark forever

              stored mark := 10:00:02      (the reader's clock, at read time)

  T3 and T4 are committed, correct, and permanently invisible to
  "WHERE updated_at > mark". Nothing failed.

Storing the mark, and the order that decides gaps or duplicates

Once you have chosen what the mark is, one decision remains and it has no middle option: is the mark advanced before or after the output is durable? Advance-before gives at-most-once — a crash between the two leaves a range marked processed and empty. Advance-after gives at-least-once — a crash re-processes a range that was already written.

Advance-after is the correct default, and it is correct only because idempotent writes make the duplicate harmless. This is the point where this lesson and the idempotency lesson become the same lesson: without a write that replaces its slice, both orderings are lossy in different directions and there is no safe choice available (Idempotent Data Pipelines).

The better option, where the storage system allows it, is neither: commit the output and the mark in one transaction, so the two facts cannot disagree. That is available when the mark lives in the same database as the target, and unavailable when the output is files in object storage and the mark is a row somewhere else — in which case advance-after plus idempotency is the honest reconstruction of it (Atomic Publish).

When to advance the mark

Advance the mark, then write the output

Read from the mark, compute the new mark, store it, then write the results. If the process dies after storing the mark and before the write completes, the range is recorded as processed and its output does not exist. Nothing will ever revisit it, because the mark says it is done.

Write the output durably, then advance the mark — or commit both together

Publish the output atomically, confirm durability, then store the new mark. A crash between the two causes the next run to re-process a range that has already been written, which an interval overwrite or a merge on the business key absorbs without trace. Where the mark and the output share a transaction, commit them together and the question disappears.

The two orderings are the at-most-once and at-least-once choice, and they are not symmetric in consequence. A duplicate is detectable and, with an idempotent write, free; a gap is undetectable from inside the pipeline and permanent once the source has moved past retention. Choosing the failure you can absorb over the failure you cannot see is the whole of the reasoning.

Advancing a mark in the same transaction as the output

1-- Available when the target and the mark live in the same database.
2-- The output and the claim about it cannot then disagree.
3BEGIN;
4 
5  MERGE INTO fct_orders AS t
6  USING (
7    SELECT * FROM stg_orders
8     WHERE source_lsn >  :last_mark          -- exact position, not a clock
9       AND source_lsn <= :new_mark
10  ) AS s
11     ON t.order_id = s.order_id
12   WHEN MATCHED     THEN UPDATE SET amount_minor = s.amount_minor
13   WHEN NOT MATCHED THEN INSERT VALUES (s.order_id, s.order_date, s.customer_id, s.amount_minor);
14 
15  -- Guard against two workers advancing the same mark: the update only
16  -- applies if the stored value is still the one this run started from,
17  -- and the mark may only move forward.
18  UPDATE pipeline_state
19     SET last_mark = :new_mark, updated_at = now()
20   WHERE pipeline   = 'orders'
21     AND last_mark  = :last_mark
22     AND :new_mark  > last_mark;
23 
24  -- Zero rows updated means another run moved it. Abort rather than
25  -- overwrite: the losing run's output is idempotent and harmless.
26COMMIT;

Three defences in one statement. The range is bounded by a log position rather than a timestamp, so there is no visibility gap. The mark update is conditional on the value this run read, which turns a lost update into a detectable conflict (Optimistic Concurrency: Versions and If-Match). And the mark may only move forward, so a clock correction or an out-of-order worker cannot silently rewind the pipeline's claim about itself.

Checking a mark you cannot see inside

SIMPLIFIEDThe gap-scan check assumes a dense monotonic sequence and no rollbacks, which is true of some sources and not others: sequences that cache blocks per session, distributed identifier schemes and any source with rolled-back transactions all produce legitimate holes. Where that holds it is a strong signal; where it does not, it is a source of false alarms rather than a check.

The defining property of this failure is that it produces no internal evidence. A skipped row was never read, never counted and never written, so every metric the pipeline computes about itself is consistent with the rows being absent. Detection therefore has to come from outside — from the source, or from a property of the mark's own behaviour over time.

The checks below are the practical set. Note how few of them look at the data: two examine the mark itself, one compares against the source, and only one looks at output rows. That distribution is the lesson — the thing to monitor is the state, because the data cannot tell you what is missing from it.

The misses column is unusually important here because these checks fail in a correlated way. A reconciliation query that filters the source by the same timestamp semantics the pipeline used will reproduce the gap exactly and report agreement. Count against something the source computes independently — its own aggregates, its own row counts by primary key range — or the check confirms the bug rather than finding it (Reconciliation).

Detecting a mark that is lying

Check	Expresses	Catches	Still misses
Monotonicity: the mark never decreases except by explicit reset	The pipeline's claim about itself only ever moves forward.	Clock corrections, two workers racing on one mark, a restored backup of the state table.	A mark that moves forward too far, which is the failure that actually loses rows.
Rate of change: the mark advances every run, and by a plausible amount	The pipeline is keeping up and not jumping.	A stalled source, a stuck consumer, and a suspiciously large single advance after an outage.	Small, steady losses — a few rows per run is a normal-looking advance (Pipeline Metrics).
Source-side count per closed interval versus target count	Completeness against the system of record.	Rows lost to the visibility gap, backdated updates, and any range the mark skipped.	Everything if the source query filters on the same timestamp column the pipeline used — the check then reproduces the gap and agrees with it.
Advance with zero rows processed	A run that moved the mark without finding anything.	The signature of a skip, and of a filter that has silently stopped matching.	Genuinely quiet periods, which look identical — this check needs the source count to be conclusive (Volume Anomalies).
Overlap re-read finds rows already present	The overlap window is doing its job.	A rising rate of late-committing rows, which is the early warning that the window is too narrow.	Rows that arrive later than the overlap, which are exactly the ones being lost.
Primary-key range gap scan on the target	Densely allocated identifiers should have no holes.	Missing rows directly, with no dependence on any clock, where the source uses a dense sequence.	Sources with non-sequential keys, and legitimate gaps from deletions or rolled-back transactions.

The last row is the underused one. Where the source allocates identifiers densely, a gap scan detects missing rows without involving a timestamp at all — which makes it the one check here that cannot be fooled by the same clock semantics that caused the loss.

How to build it

Most important first.

Use a log position wherever the source has one. Reading a database's own change log by position converts the whole problem from "did I miss anything" into arithmetic (Change Data Capture).
Where only a timestamp exists, never trust it alone. Read with an overlap behind the mark, make the write idempotent so the overlap costs nothing but compute, and reconcile counts against the source for closed periods (Reconciliation).
Advance the mark only after the output is durably published, and store the mark and the output in one transaction where the storage system allows it (Atomic Publish).
Keep the mark per partition, per shard or per table rather than one global value. A single global mark is a serialisation point, and it makes one slow source hold back every other (Topics and Partitions).
Record what the mark means and keep it resettable. "updated_at from the source, assigned at statement start, visible at commit" is a sentence that prevents an entire class of incident, and recovery from a mark that has skipped ahead is to move it backwards — which requires knowing what it was, who changed it and that moving it is safe (Dataset Documentation, Replay from the Log).
Prefer interval-bounded processing over a stored mark whenever the data has a usable event-time partition — no state, no race, no reset (Incremental Processing).

What this actually promises

Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.

A log-position mark guarantees exact resumption: every record after the position will be delivered, and records before it will not be re-delivered unless you rewind. Combined with an idempotent write this gives once-only effect on the output for a replayed range — which is a statement about the output write, not about consumption, and it depends on deterministic recomputation (Exactly-Once: Input Consumption, State Update, Output Write).
A timestamp mark guarantees only that rows whose timestamp exceeded the stored value at the moment of reading were considered. It does not guarantee that every row belonging to that range was visible then, and it never can (Incremental Extraction).
Advancing after the write guarantees no gaps and permits duplicates. Advancing before the write guarantees no duplicates and permits gaps. Nothing gives both without a shared transaction (Job Idempotency).
A per-partition mark guarantees resumption per partition only. Cross-partition ordering is not implied, so a global "processed through" statement built from many marks is true only if you take the minimum (Ordering Guarantees: Four Levels, Four Prices).
Retention bounds the guarantee. A position is replayable only while the log still holds it; past that, the mark refers to data the source has deleted (Retention and Replay).

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

The standing check is per-interval reconciliation against the source for closed periods: count records the source says fall in a window and compare with what the target holds. It is the only check that can detect a watermark that skipped rows, because that failure leaves no trace inside the pipeline (Reconciliation).
It misses open periods entirely, and it misses cases where the source count is itself derived from the same timestamp semantics that lost the rows — a source query filtered on updated_at reproduces the gap faithfully.
A second check worth having is monotonicity: assert the mark never moves backwards except by explicit reset, and alert when it does. A mark that regresses is either a clock correction or two writers racing, and both need to be seen (Data Incidents).

Freshness

The mark defines the lower edge of what a run reads, so watermark lag — the gap between the mark and the source's newest record — is the most direct freshness signal a pipeline has (Freshness Monitoring).
An overlap window trades a fixed amount of repeated reading for tolerance of the commit-visibility gap. It does not delay output; it widens the input.
A mark that stops advancing is an outage and a mark that jumps is a gap, and the two need opposite responses. Monitoring only the value, rather than its rate of change, distinguishes neither (Pipeline Metrics).

When the schema or meaning changes

Changing the mark's meaning — from updated_at to a log position, or from the reader's clock to the source's — is a migration. The two do not compare, so the cutover needs a period processed by both (Semantic Changes).
Repartitioning a source invalidates per-partition marks: an offset in partition 3 means nothing after the partition count changes, and the safe move is a full re-snapshot rather than a translated position (Topics and Partitions).
A source that changes how it assigns timestamps — a new ORM, a different default, a switch from statement time to commit time — changes the pipeline's completeness without any schema change and without any notification (CDC and Schema Drift).

How to re-run this safely

Restart is the ordinary case and it is why the mark exists: resume from the stored position, re-process anything after it, and rely on idempotent writes to make the overlap harmless.
If the mark skipped ahead — advanced past unprocessed records — recovery is to reset it backwards to a known-good position and re-process forward. This is safe only for idempotent writes, and is otherwise a duplication event (Idempotent Data Pipelines).
If the mark is lost entirely, recovery depends on what the source retains: a log within retention can be replayed from the earliest available position, and a source without one requires a full re-snapshot (Snapshot and Stream: the Bootstrap Problem).
For a suspected gap of unknown size, do not guess. Reconcile per interval against the source to find the affected range, then re-run exactly that range (Planning a Backfill).
Keep the mark's history — every value it has held and when — because "when did this pipeline stop seeing rows" is otherwise unanswerable (Structured Logging: Fields a Program Can Read).

What can go wrong

Failure modes

The mark advancing past rows that had not yet committed, which is the default behaviour of a naive timestamp watermark under any concurrent write load.
The mark advanced before the output was durable, so a crash leaves a range marked done and empty.
Two workers writing the same mark, so it reflects the later write rather than the smaller position.
A clock correction moving the source's timestamps backwards, making the mark skip a window that never existed (Processing Time).
A log position that outlives its retention, so resumption is impossible and the only recovery is a re-snapshot (Retention and Replay).
The mitigation failing: an overlap window sized from the typical commit duration, against a source that occasionally runs a long batch transaction and backdates every row in it.

Misreads

"last_processed_timestamp is a high-water mark." It is an approximation of one. A real high-water mark supports the claim "everything at or before this point is processed", and a timestamp cannot support it in the presence of concurrent commits (Incremental Extraction).
"More precision fixes it." The gap is between timestamp assignment and commit visibility, not between milliseconds and microseconds. Nanosecond timestamps lose exactly the same rows.
"We use >= so nothing is missed." That prevents the tie-value loss and does nothing about rows that commit after the mark has moved past their timestamp. It also requires idempotent writes to be safe at all.
"The watermark is fine, the row count looks normal." A skip removes rows that were never counted anywhere. Normality is the expected appearance of this failure (The Pipeline Succeeded. The Data Is Wrong.).
"Offsets and timestamps are interchangeable." One is a position in an ordering the source itself defined; the other is a value the source wrote into a column. Only the first supports resumption (The Event Log).

Operating it

How you see it in production

Watermark lag per source and per partition, alerting on both stalls and jumps (Freshness Monitoring).
The mark's value over time as a series, so a regression or a discontinuity is visible rather than inferred (Pipeline Metrics).
Records processed per run against the source's own count for the same window, which is the only external check on completeness (Reconciliation).
Whether the mark advanced on a run that produced zero rows — the signature of a skip, and indistinguishable from a genuinely quiet period without the source count (Volume Anomalies).

What changes at 10x and 100x

At 10x throughput a single global mark becomes a write-contention point, and the design moves to one mark per partition or shard (Topics and Partitions).
At 100x, the commit-visibility gap widens because transactions are longer and more concurrent, so timestamp-based marks lose more rows precisely as the data becomes more valuable (Isolation Levels).
More partitions make the global "processed through" statement weaker: it is the minimum across all marks, and one lagging partition defines the whole pipeline's honest position (Consumer Groups and the Parallelism Ceiling).

What drives cost here

The mark itself costs almost nothing to store and its correctness costs the overlap: repeated reading of a trailing window on every run (Scan Cost).
Per-partition marks cost more state and remove a contention point. That trade improves with parallelism and is worth making early (What Actually Drives Data Platform Cost).
The reconciliation that proves the mark is sound costs a source-side aggregate per interval, which is the cheapest insurance in this module and the most commonly skipped.

What this approach costs

A log position gives an exact, replayable guarantee and requires a source that exposes one plus the operational work of consuming it. A timestamp works against anything and is an approximation whose error is invisible.
Advancing after the write buys completeness and costs duplicates on restart, which is only acceptable because idempotent writes make duplicates free. Without idempotency, both orderings are wrong in different directions.
An overlap window buys tolerance for late commits and costs repeated reads on every single run, forever. It is a small permanent cost against a large occasional loss.

Where this applies

Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.

GENERALThe distinction between a position in an ordering and a timestamp holds everywhere, as does the rule that the mark must be advanced after the output is durable. What varies is whether a source offers a position at all, which decides whether you get a guarantee or an approximation.
BROKER-SPECIFICKafka offsets are per-partition, dense and monotonic, so a committed offset is an exact resumption point within that partition and says nothing across partitions. Kinesis sequence numbers are per-shard and opaque; Pub/Sub has no client-visible position at all and acknowledges individual messages, so "resume from X" is not expressible and redelivery scope is different in kind.
SOURCE-SPECIFICA Postgres LSN and a MySQL binlog file-and-position are exact positions in the source's own commit order. A SaaS API that offers only a modified-since filter gives you the vendor's clock, the vendor's definition of modification, and no statement about visibility — the same code over that source has a materially weaker guarantee.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasewrite-ahead-logging mvcc isolation-levels transactions-and-acid crash-recovery

Backendjob-idempotency idempotency-storage at-least-once-delivery optimistic-concurrency duplicate-detection retries

Architectureidempotency event-sourcing

Observabilityqueue-age

Concurrencyordering-guarantees reasoning-about-races atomics-are-not-magic

Distributed Systemsclock-skew watermarks

Domains that do not exist yet

— Distributed Systems owns why a timestamp is not a position: without a synchronised global clock there is no total order across concurrent writers, so a value read from a clock can never be a cut across a running system. That domain is being written separately, and this lesson is one of its most practical consequences.
— DevOps / Production Engineering owns the operational half — where pipeline state lives, how it is backed up, and what restoring a state table from a snapshot does to a pipeline that has since advanced. Restoring the mark alone is a data incident dressed as a routine recovery.