SQL Deduplication Interview Exercise: Keep the Latest Valid Record, Not Just the Latest Timestamp

ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) is a useful SQL pattern. It is not a complete deduplication policy. A later row may be malformed, a deletion may arrive after an upsert, two tenants may reuse the same identifier, and a replay may carry a newer ingestion timestamp without representing a newer business state.

This SQL deduplication interview exercise makes those choices explicit. You will canonicalize exact copies, detect conflicts, validate payloads, select the authoritative version, and apply deletions without resurrecting old data. Review what SQL DISTINCT actually deduplicates if you need the set-operation foundation.

PostgreSQL documentation is the official source for SQL behavior. The version, validity, and deletion rules below are exercise assumptions; production systems must obtain them from the source contract.

What must you define before writing the query?

Ask five questions first:

• What columns form the business key, including tenant or source namespace?
• Does source_version, event time, or ingestion time establish authority?
• Which fields make an upsert valid, and which fields may a delete omit?
• Are exact retries safe to collapse, and what counts as a conflicting copy?
• Does “latest valid” fall back to an earlier valid version, or quarantine the entity when its latest version is invalid?

For this exercise, the business key is (tenant_id, order_id). source_version is authoritative within that key. A larger version wins even when its event_ts is earlier. ingested_at is an audit field and never decides business state.

An UPSERT is eligible only when amount is non-null and nonnegative. A DELETE is eligible without an amount. Exact business copies may be collapsed even if they arrived twice. More than one distinct assertion for the same business key and source version is a conflict; exclude that entity from the published result and send the conflict to review.

Here, an invalid higher version creates a data-quality issue but the query falls back to the highest eligible version. A stricter system could quarantine the entity instead; it must state that rule.

The staging data and the expected answer

Use this fixture in a scratch database:

CREATE TEMP TABLE staged_order_versions (
  tenant_id TEXT NOT NULL,
  order_id INTEGER NOT NULL,
  event_id TEXT NOT NULL,
  source_version INTEGER NOT NULL,
  operation TEXT NOT NULL,
  amount DECIMAL(12,2),
  event_ts TIMESTAMP NOT NULL,
  ingested_at TIMESTAMP NOT NULL
);

INSERT INTO staged_order_versions VALUES
 ('A', 10, 'e10-1', 1, 'UPSERT', 100.00,
  TIMESTAMP '2026-09-01 10:00:00', TIMESTAMP '2026-09-01 10:00:05'),
 ('A', 10, 'e10-1', 1, 'UPSERT', 100.00,
  TIMESTAMP '2026-09-01 10:00:00', TIMESTAMP '2026-09-02 08:00:00'),
 ('A', 10, 'e10-2', 2, 'UPSERT', 120.00,
  TIMESTAMP '2026-09-01 11:00:00', TIMESTAMP '2026-09-01 11:00:05'),
 ('A', 10, 'e10-3', 3, 'DELETE', NULL,
  TIMESTAMP '2026-09-01 12:00:00', TIMESTAMP '2026-09-03 09:00:00'),

 ('A', 11, 'e11-1', 1, 'UPSERT', 40.00,
  TIMESTAMP '2026-09-01 09:00:00', TIMESTAMP '2026-09-01 09:00:05'),
 ('A', 11, 'e11-2', 2, 'UPSERT', -4.00,
  TIMESTAMP '2026-09-01 12:00:00', TIMESTAMP '2026-09-01 12:00:05'),

 ('A', 12, 'e12-1', 1, 'UPSERT', 50.00,
  TIMESTAMP '2026-09-01 10:00:00', TIMESTAMP '2026-09-01 10:00:05'),
 ('A', 12, 'e12-2', 2, 'UPSERT', 60.00,
  TIMESTAMP '2026-09-01 08:00:00', TIMESTAMP '2026-09-02 10:00:00'),

 ('A', 13, 'e13-x', 1, 'UPSERT', 70.00,
  TIMESTAMP '2026-09-01 10:00:00', TIMESTAMP '2026-09-01 10:00:05'),
 ('A', 13, 'e13-y', 1, 'UPSERT', 75.00,
  TIMESTAMP '2026-09-01 10:00:00', TIMESTAMP '2026-09-01 10:00:06'),

 ('B', 12, 'eB12-1', 1, 'UPSERT', 90.00,
  TIMESTAMP '2026-09-01 10:00:00', TIMESTAMP '2026-09-01 10:00:05');

The current-state table should contain three rows:

Tenant	Order	Winning version	Amount	Why
A	11	1	40.00	Version 2 is invalid; fall back and report the issue
A	12	2	60.00	The larger source version wins despite an earlier event time
B	12	1	90.00	Tenant is part of the business key

Order A-10 is absent because its winner is a delete. A-13 is absent because version 1 conflicts. Neither should reveal an older upsert.

Stage 1: collapse exact business copies

An ingestion retry may change only ingested_at. If business identity and payload are otherwise identical, select those fields once:

CREATE TEMP VIEW canonical_assertions AS
SELECT DISTINCT
  tenant_id, order_id, event_id, source_version,
  operation, amount, event_ts
FROM staged_order_versions;

This removes the second delivery of e10-1 while retaining raw audit copies. It does not treat two amounts as duplicates.

Official SQL behavior: SELECT DISTINCT removes rows that are equal across the selected expressions. It is not a “keep the best row” operator. PostgreSQL SELECT documentation.

Including ingested_at would preserve retries; omitting payload columns could hide disagreements. Select the business assertion precisely.

Stage 2: stop conflicting versions

The contract permits one business assertion per (tenant_id, order_id, source_version). This query finds version keys with more than one distinct assertion:

CREATE TEMP VIEW conflicted_entities AS
SELECT DISTINCT tenant_id, order_id
FROM canonical_assertions
GROUP BY tenant_id, order_id, source_version
HAVING COUNT(*) > 1;

Because exact copies were removed first, COUNT(*) > 1 now means the same source version contains different event IDs, operations, amounts, or event times. Order A-13 is therefore conflicted.

Design judgment: quarantine the entity rather than breaking the tie with event_id, ingestion time, physical row order, or a hash. Determinism does not make an unsupported business decision correct.

If the source permits several events per version, replace this gate with its real uniqueness and aggregation rules.

Stage 3: validate before ranking, but keep the rejects

Build a quality output before filtering:

CREATE TEMP VIEW data_quality_issues AS
SELECT *,
  CASE
    WHEN operation NOT IN ('UPSERT', 'DELETE')
      THEN 'unsupported operation'
    WHEN operation = 'UPSERT' AND amount IS NULL
      THEN 'upsert amount is null'
    WHEN operation = 'UPSERT' AND amount < 0
      THEN 'upsert amount is negative'
    ELSE NULL
  END AS rejection_reason
FROM canonical_assertions
WHERE operation NOT IN ('UPSERT', 'DELETE')
   OR (operation = 'UPSERT' AND (amount IS NULL OR amount < 0));

Then form the eligible set. Notice that a delete is not rejected because its amount is null:

CREATE TEMP VIEW eligible_assertions AS
SELECT c.*
FROM canonical_assertions AS c
LEFT JOIN conflicted_entities AS x
  ON x.tenant_id = c.tenant_id
 AND x.order_id = c.order_id
WHERE x.order_id IS NULL
  AND (
    c.operation = 'DELETE'
    OR (c.operation = 'UPSERT'
        AND c.amount IS NOT NULL
        AND c.amount >= 0)
  );

SQL's ordinary comparisons involving null produce an unknown result, so amount >= 0 alone does not document the required-value rule as clearly as an explicit null check. PostgreSQL provides null-aware predicates such as IS NULL and IS DISTINCT FROM. Comparison functions and operators.

The rejected A-11 version 2 remains in data_quality_issues, while version 1 stays eligible under the stated fallback rule.

Stage 4: rank authority, then apply deletion

Now the window function has a defensible partition and ordering:

CREATE TEMP VIEW current_orders AS
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY tenant_id, order_id
    ORDER BY source_version DESC
  ) AS authority_rank
  FROM eligible_assertions
)
SELECT tenant_id, order_id, source_version, amount
FROM ranked
WHERE authority_rank = 1
  AND operation <> 'DELETE';

SELECT * FROM current_orders
ORDER BY tenant_id, order_id;

Official SQL behavior: ROW_NUMBER numbers rows inside each partition according to the window's ORDER BY. PostgreSQL warns that tied ordering values are numbered in an unspecified order. PostgreSQL window-function tutorial. Here, a tie on source_version cannot reach the ranking step because the conflict gate removes that entity.

Apply the delete after selecting rank 1. Filtering deletes before ranking would expose order A-10's version 2 and resurrect an order the source says is deleted. This ordering is one of the most valuable follow-ups in the exercise.

The ranking does not use event_ts or ingested_at. A-12 proves why: version 2 is authoritative even though its event time is earlier. If event time wins in your contract, state and test that different rule.

Observed local result: the complete fixture and published SQL produced A-11 version 1 amount 40, A-12 version 2 amount 60, and B-12 version 1 amount 90 in DuckDB 1.5.2. It also produced one rejected row and one conflicted entity. This verifies the example's relational logic, not PostgreSQL execution plans or concurrent-load behavior.

Why “latest timestamp” gives the wrong answers

Ranking by ingested_at DESC selects delivery order. A replay could arrive tomorrow and beat a newer business version. Ranking by event_ts DESC selects A-12 version 1, contradicting the exercise's source-version contract. Ranking before validation selects A-11's negative amount. Filtering deletes early returns A-10's obsolete upsert. Partitioning by order_id alone mixes tenants A and B.

“Latest record” also hides a grain decision. An order may have several line items or payment attempts; partitioning them all by order ID becomes data loss. State the output grain and its unique constraint.

MAX(updated_at) plus a join can return several tied rows. PostgreSQL DISTINCT ON can express top-one selection, but it still needs correct keys, eligibility, and ordering.

Make an incremental load replay-safe

The selection query computes a state. A production load also needs a publication protocol. Land immutable raw rows, calculate the affected business keys, rebuild winners for those keys, validate conflicts and counts, and then merge the result with a comparison on authoritative version.

Keep delete winners in the change set even though they do not appear in current_orders; the merge needs them to remove or mark the target. Carry an explicit UPSERT, DELETE, or QUARANTINE action into publication.

Reasoned recommendation: make the target mutation atomic for each batch or publish a validated snapshot. Use an audit table with batch ID, source range, exact-copy count, rejected count, conflict count, inserted/updated/deleted counts, and reconciliation totals. A second run over the same input should report no target changes.

A lookback window, source offset, or durable version checkpoint may be safer than advancing only to maximum ingestion time when delayed records are allowed.

Mutation tests that reveal hidden assumptions

Use expected results as an oracle, then change one condition at a time:

Mutation	Expected observation
Replay all 11 source rows	Same three current rows
Shuffle physical row order	Same winners and quarantine
Move the retry's ingestion time forward	No winner changes
Make A-12 version 2's event time earlier	Version 2 still wins
Remove A-10's delete before ranking	Test fails because an obsolete order reappears
Partition without tenant	Test fails because A-12 and B-12 collide
Add an invalid A-11 version 3	A-11 version 1 remains; issue count increases
Resolve A-13 to one assertion	The entity becomes publishable

Also assert one published row per key, no conflicted published keys, valid winning upserts, and exactly one rank-1 action before applying deletes.

For performance, discuss window sorting and affected-key counts. Verify indexes or clustering with the actual engine's plan.

How to present the answer in an interview

Lead with the contract: “I deduplicate at tenant-plus-order grain. Source version establishes authority; ingestion time is audit-only. I collapse exact business copies, quarantine conflicting versions, validate operation-specific payloads, rank eligible versions, and apply deletes after ranking.”

Then show the three counterexamples: A-11 makes validation order visible, A-12 separates version from timestamp, and A-10 proves why deletion order matters. Those examples communicate more than reciting window-function syntax.

If the interviewer changes the rules, adjust the pipeline deliberately. If any invalid latest version should quarantine the entity, move that check to the entity gate. If event time is authoritative, define a deterministic tie-breaker and clock semantics. If deletes carry versions from another stream, explain how the streams become comparable before ranking.

Sources and Further Reading

• PostgreSQL: SELECT processing and DISTINCT
• PostgreSQL: Window-function tutorial
• PostgreSQL: Window functions
• PostgreSQL: Comparison functions and operators