Keep the Latest Needs a Tie Rule in Your Data Agent's Deduplication

One row per customer can still be the wrong row

A data agent receives several exports and is asked to remove duplicates, keeping the latest record. It sorts a timestamp, keeps one row per customer and returns a clean file.

The result is not ready for acceptance until latest has a precise meaning. Does it mean the newest business event, the last source revision, or the record imported most recently? And what happens when two conflicting records have the same timestamp?

This workflow is for teams building reporting snapshots with agents. It does not authorize deleting source history, resolving customer disputes or replacing an authoritative system.

Methodology: examine selection rather than deletion

We reviewed PostgreSQL window functions, SQL ordering and pandas duplicate removal on October 18, 2026. The example below is fictional and reasoned from those documents; it is not an executed database or pandas test.

The selection criteria are entity identity, the clock that defines recency, the authority of a conflicting version and the evidence retained for rejected candidates.

Our join-cardinality guide explains why duplicate lookup keys can multiply totals. Here the narrower question is how to select a current record without pretending a convenient ordering proves which version is true.

A timestamp tie is a business question

Imagine four records for account A:

If latest means source event time, r2 and r3 tie. If it means import order, r4 wins even though its event happened earlier. These rules produce different states without any syntax error.

Do not infer that r3 is authoritative merely because its identifier sorts after r2. An identifier can make a choice repeatable, but it may carry no revision semantics.

Ask the source owner whether a revision sequence, source priority or correction marker settles the conflict. If not, preserve both candidates and mark the account unresolved rather than manufacture certainty.

Choose the clock and eligibility first

Before sorting, write down which entities are eligible and which time field controls selection. Preserve the original timestamp, its timezone interpretation and the source record identity.

Missing or unparseable event times need a separate policy. Our proposed default for a reporting snapshot is to retain them in an exception output. Silently replacing a missing event time with the import time changes the meaning of latest.

Also separate exact repeated deliveries from distinct versions. Two rows with the same entity ID but different statuses are not necessarily duplicate observations. Reducing them to one row is a state-selection decision.

This preparation produces two reviewable inputs: eligible versions with interpretable ordering fields, and exceptions requiring a decision. No source row needs to be destroyed.

Make ordering explicit, then handle ties

PostgreSQL's window-function tutorial states that ROW_NUMBER assigns tied rows in an unspecified order when the window ordering does not distinguish them. Filtering to row number one therefore does not resolve the r2/r3 conflict on its own.

PostgreSQL's ordering reference explains that later sort expressions break earlier ties. It also allows explicit NULLS FIRST or NULLS LAST; in descending order, PostgreSQL defaults to nulls first.

For a snapshot that puts known newest times first, specify the null treatment rather than relying on a default. But placing nulls last is still an ordering rule, not proof those records can be ignored.

A proposed selection sequence is: authoritative source revision when available, approved event-time rule, then a stable unique identifier only for equivalent candidates. If remaining candidates disagree on meaningful fields, retain a conflict instead of using that final identifier to settle business truth.

Do not confuse pandas last with newest

The pandas drop_duplicates reference defines keep='first' and keep='last' by occurrence. It also states that indexes, including time indexes, are ignored.

Consequently, dropping duplicates with keep='last' does not independently inspect a date and discover the newest event. The preceding ordering and selected duplicate columns determine the result.

Our recommendation is to review the entire transformation: which columns define identity, how dates were parsed, which ordering was applied, and which conflicts were identified before selection. Do not review only the final duplicate-removal call.

For account A, an import-ordered frame would keep r4 under an entity-only last-occurrence rule. That is a predictable consequence of the fictional ordering, not a pandas malfunction.

Keep a selection ledger and test shuffled input

Deliver a selected snapshot together with a ledger containing entity ID, candidate IDs, chosen ID if any, policy version and the reason for each disposition. Retain conflicts and missing-time records separately.

Before adopting the workflow, propose checks for out-of-order imports, equal timestamps with equal content, equal timestamps with conflicting content, missing timestamps and missing entity IDs. Write expected outcomes before executing them.

Shuffle the same input records and compare chosen identities and conflict sets. If the stated business policy is independent of import order, the outcome should not change. This is a proposed acceptance exercise, not a claim that a particular Skill passed it.

Limitations and handoff

A deterministic selection can still be wrong when clocks are inaccurate, source revisions are incomplete or identity matching is flawed. A stable result is useful evidence of reproducibility, not evidence of authority.

For high-impact states such as permissions or financial balances, this reporting pattern is not a substitute for the system owner's reconciliation rules. Keep unresolved conflicts out of automated downstream actions unless an authorized policy says otherwise.

Browse the skill directory for data workflows, then ask a more demanding question than whether they remove duplicates: can they explain why a particular version survived, and can that choice be reviewed without losing the alternatives?