Make Latest Row Queries Repeatable When Timestamps Are Tied

A nightly job that never gives the same answer twice

The order pipeline at Belgavi's Book Store loads status events every night and keeps the latest status per order: placed, packed, shipped, cancelled. A reconciliation report compares the result with the previous night's run on the same input and keeps finding a few thousand differences, although nothing changed upstream. Some orders flip between shipped and cancelled from run to run.

The query is the standard one: number the events per order by updated_at descending and keep row 1. The orders that flip are those whose last two events carry the same timestamp, because the source system writes timestamps at whole-second or whole-minute precision and two events landed in the same tick.

Why SQL allows a different answer each time

ROW_NUMBER assigns distinct numbers to rows that compare equal under the window's ORDER BY. The PostgreSQL tutorial states it directly: rows are numbered in the order defined by ORDER BY, with tied rows numbered in an unspecified order. Rows that compare equal are called peers, and nothing in the query says which peer comes first. A single-threaded engine reading a file in order often gives the same answer every time, which hides the problem. A parallel engine that partitions and sorts in several threads does not.

Measured: 10 runs, 9 answers

I built a synthetic table in DuckDB 1.5.3 with Python 3.13.5: 200,000 orders with three events each, where for 10 percent of orders the last two events (shipped and cancelled) share a timestamp, 20,000 tied orders in total. The rows were physically shuffled. Each query ran 10 times and I fingerprinted the full result.

With one thread, the answer was stable but still arbitrary: it picked cancelled for about half the tied orders. With eight threads, nine of ten runs produced a different result. arg_max, a common shortcut, has the same problem, because it also has to choose among equal maxima. Adding a column that makes the ordering total made every run identical.

The fix: a tie-breaker that both makes the order total and means something.

SELECT order_id, status
FROM (
  SELECT *, row_number() OVER (
           PARTITION BY order_id
           ORDER BY updated_at DESC, source_seq DESC) AS rn
  FROM order_events)
WHERE rn = 1

Deterministic is not the same as correct

In the measured fix, ingest_id was a hash of the order and event number. It made the output identical across runs, and it still chose cancelled for only 10,065 of the 20,000 tied orders. In the synthetic data the cancellation is always the later event, so the hash is consistently wrong for about half of them. A hash, a random UUID or a load timestamp makes a pipeline reproducible without making it right.

The tie-breaker has to reflect the order in which the source applied the changes. Good candidates, roughly in order of preference: a log sequence number or commit position from change data capture; a per-key version or sequence number the source increments; an event offset within one partition of a log such as a Kafka topic, if all events for a key share a partition; the line number within one source file. When nothing like that exists, fix it at the source by recording timestamps with enough precision and a sequence, and meanwhile make the tie explicit with a business rule, such as a status precedence where cancelled beats shipped, so a person can read why one row won.

Finding ties before they bite

Counts keys whose latest timestamp is shared by more than one row. Run it as a data test on every load.

WITH latest AS (
  SELECT order_id, max(updated_at) AS ts FROM order_events GROUP BY order_id)
SELECT count(*) AS tied_keys
FROM (
  SELECT e.order_id
  FROM order_events e JOIN latest l ON e.order_id = l.order_id AND e.updated_at = l.ts
  GROUP BY e.order_id
  HAVING count(*) > 1);

A simpler test that catches most cases is to count rows with a duplicate pair of key and timestamp. If either is above zero and the job has no total ordering, its output can change between runs. The same check applies to DISTINCT ON, QUALIFY ROW_NUMBER() = 1, FIRST_VALUE and dataframe drop_duplicates(keep='last') after a sort that is not stable or not total.

Failure modes

• Precision loss in transit. A source with microsecond timestamps feeds a pipeline that stores seconds, and ties appear only after the load. Check the column type at every hop.
• Merging two sources. Sequences from two systems are not comparable. Order by source precedence first, then by each source's own sequence.
• Late events. An event that arrives in tomorrow's load with an older timestamp should not win; a total order by source sequence handles it, a load timestamp does not.
• Tests on tiny data. A unit test with ten rows on one thread passes every time. Test with parallelism and with deliberate ties.

Why one thread hid the problem

With one thread the result was identical in all ten runs, and still arbitrary. A single-threaded engine tends to read and sort in the same physical order every time, so it keeps returning whichever tied row it happened to meet first. That stability is a property of the execution plan, not of the query, and it disappears when the data grows past the point where the engine parallelises, when the file layout changes after compaction, or when the job moves to another engine. A test suite that runs on a laptop with small inputs will usually pass for this reason.

The output also changed in a way that a row count cannot catch: every run returned exactly 200,000 rows, one per order. Only a comparison of values, or a fingerprint over the sorted result, shows the difference. Reconciliation reports that compare counts between runs will report success.

Choosing between equally plausible rows

Sometimes the source genuinely recorded two changes in the same instant, and neither a sequence nor a log position survives. Then the pipeline must choose by a rule someone can defend. A status precedence (cancelled beats shipped beats packed beats placed) is one; for numeric fields, keeping both and flagging the key for review is another. Write the rule into the ORDER BY as an explicit CASE expression, after the timestamp, and record in the output which rule decided, so the decision can be audited later.

Verification

Run the deduplication twice on the same input with full parallelism and compare fingerprints, as part of the pipeline's tests. Count tied keys on every load and alert when the count jumps. When you change the tie-breaker, compare the before and after output for the tied keys only, and check a sample against the source system to confirm the new rule picks the change the source applied last. The probe script used for the table above is short enough to adapt to your own data.

Practice and related reading

Further reading:

• Hive Window Functions: OVER, PARTITION BY, ROW_NUMBER, RANK
• Slowly Changing Dimension Type 2 With MERGE
• Debezium CDC to Kafka, in depth

Sources and scope

Checked against these sources on 3 November 2026. Measured on 3 November 2026: DuckDB 1.5.3 on Python 3.13.5, one Windows 11 machine, a synthetic 600,000-row table, 10 runs per query. Counts will differ on other machines; the instability is the point.

• PostgreSQL documentation: Window Functions tutorial (tied rows numbered in an unspecified order)
• PostgreSQL documentation: Window Functions (row_number, rank, peers)
• DuckDB documentation: Window Functions
• DuckDB documentation: Aggregate Functions (arg_max)