SQL Interview: Safely Delete Duplicate Rows and Keep the Latest Record

Prompt and Applicable Context

A PostgreSQL customercontacts table has 200 million rows. Retries in an old importer created multiple rows for the same (tenantid, externalid). The latest row by updatedat should survive; if timestamps tie, the greatest id wins. A contactevents.contactid foreign key may point to any copy, so deleting losers before repointing references would fail or lose the relationship.

customer_contacts(
    id bigint primary key,
    tenant_id bigint not null,
    external_id text not null,
    updated_at timestamptz not null,
    payload jsonb not null
)

contact_events(
    id bigint primary key,
    contact_id bigint not null references customer_contacts(id),
    event_type text not null,
    created_at timestamptz not null
)

Design a PostgreSQL repair that keeps one deterministic canonical contact per business key, preserves every event, processes destructive work in bounded transactions, can be audited and retried, and ends with database-enforced uniqueness. Reads must remain available. A controlled final write pause is allowed, but its duration must be measured rather than assumed.

The 200 million rows and final write pause are interview constraints. This is a hard data-engineering and SQL question because the window function is only the selection mechanism; the real answer must also protect referential integrity, concurrency, rollback, storage health, and future writes. Current public SQL interview material continues to use ROW_NUMBER deduplication and deterministic tie-breaking as explicit interview patterns. No verifiable company attribution is available, so companyName is null.

What the Interviewer Evaluates

The first signal is whether the candidate defines “duplicate” and “latest” before writing DELETE. The business key is (tenantid, externalid), while id identifies a physical row. The total order updatedat DESC, id DESC makes exactly one row the winner even when timestamps tie. RANK can retain several tied rows; ROWNUMBER assigns exactly one position 1.

The second signal is control of the destructive boundary. A production answer previews counts and samples, freezes an immutable loser-to-winner map, preserves the losing rows or a restorable snapshot, and deletes by primary key. Recomputing the ranking independently during every batch can change the target set while writes continue and makes the run hard to explain.

The third signal is referential and transactional correctness. Every child reference must move from loser to winner before its parent is deleted. Child-table uniqueness rules may make that update collide, so “update all foreign keys” is incomplete without an inventory and conflict policy. Each batch should be idempotent, short, observable, and safe to stop.

The final signal is whether cleanup closes the cause. A query that removes today's duplicates leaves tomorrow's importer free to recreate them. The durable invariant belongs in a unique constraint or unique index, with an explicit null and normalization contract. The candidate should also distinguish correctness evidence from performance evidence: zero duplicate keys and zero orphan references prove semantics; EXPLAIN, lock waits, WAL rate, replication lag, dead tuples, and autovacuum behavior determine an acceptable operating rate.

Questions to Clarify Before Answering

• Which columns define the business entity? Here it is the exact pair (tenantid, externalid). Case folding, whitespace trimming, or Unicode normalization would define a different key and must be agreed before repair.
• Which row wins? Greatest updated_at, then greatest id. A timestamp alone is not a total order when two rows tie.
• May loser rows contain unique data? If payloads must be merged, “keep latest” is insufficient. This prompt treats the latest payload as authoritative and archives losers for review.
• Which tables reference customercontacts.id? Inventory declared foreign keys and application-level references. contactevents is shown, but a real run must search the catalog and ownership documentation.
• Can repointing create child duplicates? If a child has UNIQUE(contactid, eventtype, created_at), two equivalent events may collide after convergence. Decide whether to merge, retain, or reject them before the update.
• May writes continue during repair? Historical batches may run while guarded writers continue, but the final duplicate scan and uniqueness handoff need either a verified database-level write guard or a measured write pause. An application convention that some writers bypass is not a guarantee.
• What is the rollback requirement? Keep a backup or archived loser rows plus the original child mappings until validation and the rollback window finish. A loser-to-winner map alone cannot reconstruct discarded payloads.
• How much load is acceptable? Batch size is a control, not a constant. Start small and throttle on lock waits, write p99, WAL, replication lag, dead tuples, and autovacuum progress.
• How should nulls behave? Both key columns are non-null here. If nulls are allowed and should collide, PostgreSQL uniqueness needs NULLS NOT DISTINCT; default unique semantics allow multiple nulls.

30-Second Answer Framework

“I would first freeze the business rule: duplicates share (tenantid, externalid), and the winner is greatest updatedat, then greatest id. I would preview the ranked result, archive loser rows, and materialize one immutable runid map from each loser to its winner. In short idempotent batches I would record and repoint all child references, verify none still target a loser, then delete losers by primary key. I would reconcile counts, payload samples, duplicate keys, and orphan references after every phase. Finally, under a verified writer guard or measured write pause, I would run a last delta cleanup and build a unique index, then make all writes use the same key contract. I would throttle from production metrics and keep the archive until the rollback window expires.”

This framework states selection, dependency order, destructive controls, convergence, and prevention. The SQL follows those decisions; it does not replace them.

Step-by-Step Deep Answer

Step 1: Establish Invariants, Ownership, and a Run Boundary

Write the postconditions before touching data:

• exactly one contact exists for every (tenantid, externalid);
• its ID is the maximum under (updated_at, id) ordering;
• every preexisting contact_events row still exists and references that winner;
• no declared or application-level reference points to a deleted ID;
• a retry of a completed batch changes zero rows;
• new writes cannot create another business-key duplicate.

Assign a run_id, owner, source snapshot or backup, start time, code version, batch cursor, dashboards, pause thresholds, and rollback deadline. Record the pre-repair row count, distinct business-key count, duplicate-key count, loser count, and event count. Keep control totals per tenant so a global total cannot hide a tenant-level loss.

Step 2: Preview the Deterministic Ranking

Start with a read-only query and inspect counts and payload differences:

WITH ranked AS (
    SELECT
        id,
        tenant_id,
        external_id,
        updated_at,
        ROW_NUMBER() OVER (
            PARTITION BY tenant_id, external_id
            ORDER BY updated_at DESC, id DESC
        ) AS rn
    FROM customer_contacts
)
SELECT tenant_id, external_id, COUNT(*) AS loser_count
FROM ranked
WHERE rn > 1
GROUP BY tenant_id, external_id
ORDER BY loser_count DESC, tenant_id, external_id
LIMIT 100;

ROWNUMBER is appropriate because the contract requires one winner. RANK or DENSERANK would assign the same rank to equal ordering values unless id is included, and a missing deterministic tie-breaker would make repeated runs capable of choosing different winners. DISTINCT only removes identical selected output rows; it cannot preserve a complete preferred row by business rule.

Step 3: Materialize an Immutable Loser-to-Winner Map

Do not recalculate winners separately for child updates and deletion. Materialize the decision once under the agreed run boundary. The snippets use :name to denote bind parameters supplied by the repair runner:

WITH ranked AS (
    SELECT
        id,
        tenant_id,
        external_id,
        ROW_NUMBER() OVER w AS rn,
        FIRST_VALUE(id) OVER w AS winner_id
    FROM customer_contacts
    WINDOW w AS (
        PARTITION BY tenant_id, external_id
        ORDER BY updated_at DESC, id DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )
)
INSERT INTO contact_dedup_map (
    run_id, loser_id, winner_id, tenant_id, external_id
)
SELECT :run_id, id, winner_id, tenant_id, external_id
FROM ranked
WHERE rn > 1;

contactdedupmap should have PRIMARY KEY(runid, loserid), a check that loser and winner differ, and an index on (runid, winnerid). Archive the complete losing contact rows under the same run_id, or retain a tested point-in-time restore path. Validate that every map winner exists, no winner also appears as a loser in the same run, each loser maps once, and map size equals the measured loser count.

For 200 million rows, the ranking may require a large scan and sort. Run EXPLAIN on production-shaped data, confirm temporary space and workload impact, and schedule or throttle accordingly. A covering index may help a measured plan but is itself expensive to build and should not be prescribed without evidence.

Step 4: Repoint References Before Deleting Parents

Inventory every reference first. For contact_events, preserve the original mapping in an audit table and then update bounded ID ranges:

UPDATE contact_events AS e
SET contact_id = m.winner_id
FROM contact_dedup_map AS m
WHERE m.run_id = :run_id
  AND e.contact_id = m.loser_id
  AND e.id > :after_event_id
  AND e.id <= :batch_end_event_id;

The retry is idempotent: after an event points to the winner, it no longer matches m.loser_id. Commit each bounded batch, persist the cursor only after commit, and reconcile updated rows with the reference-audit rows. If a child uniqueness constraint collides, apply the pre-agreed merge or rejection rule; do not disable constraints and hope the final state is valid.

Before parent deletion, this query must return zero for every child table:

SELECT COUNT(*) AS remaining_loser_references
FROM contact_events AS e
JOIN contact_dedup_map AS m
  ON m.run_id = :run_id
 AND m.loser_id = e.contact_id;

Step 5: Delete Losers in Short, Restartable Batches

Delete only IDs from the frozen map:

WITH batch AS (
    SELECT loser_id
    FROM contact_dedup_map
    WHERE run_id = :run_id
      AND loser_id > :after_loser_id
    ORDER BY loser_id
    LIMIT 10000
)
DELETE FROM customer_contacts AS c
USING batch AS b
WHERE c.id = b.loser_id
RETURNING c.id, c.tenant_id, c.external_id;

10000 is an initial interview value, not a universal optimum. Adjust batch size and delay from measured lock duration, p99, WAL, replica lag, dead tuples, and autovacuum. The RETURNING output becomes deletion evidence. If the process crashes after commit but before cursor persistence, replaying the batch simply finds already-deleted IDs and remains safe.

Large PostgreSQL deletes create dead tuples and WAL. Plan normal VACUUM and monitor table and index bloat; do not default to VACUUM FULL, which rewrites the table and takes a strong lock. Reads remain available, but resource saturation can still violate the service SLO.

Step 6: Converge Writes and Install the Durable Guardrail

Bulk repair alone cannot close a moving target. Before final convergence, deploy one write contract that serializes creation for the same normalized business key and updates the canonical row instead of inserting another copy. Verify every API, importer, job, and direct database writer follows it. If that proof is unavailable, pause writes for the final delta cleanup and index handoff.

After the last duplicate scan returns zero, create the unique index outside a transaction block:

CREATE UNIQUE INDEX CONCURRENTLY customer_contacts_business_key_uidx
ON customer_contacts (tenant_id, external_id);

CONCURRENTLY keeps writes from being blocked by the normal index-build lock, but it does more work, cannot run inside a transaction block, and can fail on a uniqueness violation while leaving an invalid index. Inspect validity, diagnose data or writer races, drop the invalid index according to the runbook, and retry; do not assume IF NOT EXISTS proves success. Once valid, attach it as a named unique constraint in a short controlled DDL step if schema governance requires constraint semantics.

If all writes are paused and a measured regular build fits the allowed window, a non-concurrent unique index is simpler and faster. The concurrent form is the right tradeoff when verified guarded writes need to continue; the SLO and rehearsal choose between them.

The final writer should use the database uniqueness invariant and an explicit conflict policy. Do not treat catching a unique violation after cleanup as the only idempotency design: decide whether the same key updates the canonical row, rejects conflicting payloads, or enters review.

Step 7: Validate, Observe, and Close the Rollback Window

Reconcile at least these checks:

• total contacts after repair equals contacts before repair minus archived losers;
• GROUP BY tenantid, externalid HAVING COUNT(*) > 1 returns zero;
• every map winner exists and every loser is absent;
• child event count is unchanged, remaining loser references are zero, and orphan references are zero;
• sampled winners match the (updated_at DESC, id DESC) rule and archived payloads;
• a duplicate insert is rejected or follows the declared update policy;
• rerunning completed mapping, repoint, and delete batches changes zero rows.

Monitor batch rate, errors, row-lock waits, write p50/p95/p99, WAL bytes, replication lag, dead tuples, autovacuum progress, map/archive growth, remaining loser references, remaining duplicate keys, and unique-index build progress. Keep archive and reference-audit data through the tested rollback window. Only then remove temporary write guards and repair tables according to retention policy.

High-Quality Sample Answer

“I would define duplicates as equal (tenantid, externalid) and choose the canonical row by updatedat DESC, id DESC; the unique ID makes ties deterministic. Before deletion I would record baseline counts, inspect payload differences, inventory every reference, take a restorable backup, and create an immutable runid map from each loser ID to its winner using ROWNUMBER and FIRSTVALUE.

I would archive losing rows and original child mappings. Then I would repoint contact_events in short primary-key ranges. Each batch commits before its cursor advances, and a retry is idempotent because already-moved events no longer match a loser ID. Child constraints stay enabled; any collision follows an explicit merge or rejection rule. Only after every child table reports zero loser references would I delete contacts by IDs from the frozen map, using bounded transactions and RETURNING as evidence.

The operating rate follows lock time, request p99, WAL, replication lag, dead tuples, and autovacuum rather than a fixed batch number. I would validate row-count conservation, one row per business key, no losers, no orphans, unchanged event count, deterministic survivor samples, and zero-change retries.

To prevent recurrence, all writers must converge on the same normalized business key. Under a verified writer guard or measured pause, I would perform a final delta cleanup and build a concurrent unique index on (tenantid, externalid) outside a transaction. I would verify that the index is valid because a failed concurrent build can leave an invalid index. The permanent write path then updates, rejects, or reviews conflicts according to the declared contract. The archive remains until rollback evidence and the observation window are complete.”

This answer makes the irreversible operations depend on explicit gates and keeps the SQL, foreign keys, writer behavior, and database constraint under one business-key contract.

Common Mistakes

• Running DELETE immediately after finding duplicates → references, payloads, and rollback evidence can be lost → preview, archive, map, repoint, validate, then delete.
• Partitioning by externalid alone → equal external IDs in different tenants collapse together → use the complete business key (tenantid, external_id).
• Ordering only by updated_at → tied timestamps allow a different winner on another run → add unique id as the final tie-breaker.
• Using RANK with rn > 1 → tied latest rows can both receive rank 1 → use ROW_NUMBER with a total order when exactly one survivor is required.
• Recomputing winners in every batch → concurrent changes can shift the target and destroy auditability → freeze one versioned loser-to-winner map.
• Deleting parents before repointing children → foreign keys reject the delete or cascades remove valid history → inventory and migrate all references first.
• Disabling constraints for speed → the repair can create silent orphans or child collisions → keep constraints active and define conflict handling.
• Calling 10,000 the correct batch size → hardware and workload decide safe throughput → start small and adapt from SLO, WAL, lag, and vacuum metrics.
• Stopping after cleanup → the faulty writer recreates duplicates → converge writers and enforce uniqueness in PostgreSQL.
• Assuming CREATE UNIQUE INDEX CONCURRENTLY always succeeds → a race or duplicate can leave an invalid index → inspect validity and follow an explicit recovery procedure.
• Using VACUUM FULL as routine cleanup → it rewrites and strongly locks the table → monitor ordinary vacuum and bloat, then schedule exceptional rewrites separately.

Follow-Up Questions and Responses

Follow-up 1: Why not delete with one CTE and finish in a single transaction?

For a small isolated table with no references and paused writers, a CTE DELETE may be sufficient. On 200 million rows, one transaction can retain locks and old row versions, generate a large WAL burst, delay replicas, complicate vacuum, and create an all-or-nothing recovery event. The frozen map plus bounded batches gives progress, auditability, throttling, and restartability. Use the simpler query only after proving its scale and dependency assumptions.

Follow-up 2: What if two rows share the same timestamp but have different payloads?

The prompt's rule chooses the greatest id, so the result is deterministic, but determinism does not prove the payload is semantically correct. Measure conflicting payloads before repair and route high-risk fields to review or a domain-specific merge rule. Archive both rows. Do not invent a field-by-field merge after deletion has started.

Follow-up 3: What if external_id can be null?

First define whether null means “unknown and independent” or one duplicate value. PostgreSQL unique constraints and indexes treat nulls as distinct by default, so several null keys are allowed. If nulls should collide, use NULLS NOT DISTINCT and rank with the same semantics; if unknown contacts are independent, keep default behavior or use a partial uniqueness rule for non-null IDs. The cleanup and constraint must agree.

Follow-up 4: Can the repair be fully online with no write pause?

Yes only if every writer is proven to use a database-backed serialization or reservation rule for the business key before the final scan. Then historical cleanup can converge and a concurrent unique index can be built. If a legacy importer or direct writer bypasses the guard, a new duplicate can appear during the build and make it fail. A shadow table and controlled cutover is another option, but it adds dual-write, backfill, foreign-key, and rollback complexity and should be justified by the downtime SLO.

Follow-up 5: How do you find every foreign key and hidden reference?

Query PostgreSQL catalogs for foreign keys whose referenced relation is customer_contacts, then inspect views, triggers, CDC consumers, search indexes, data exports, and application schemas for stored contact IDs. Declared foreign keys provide enforceable evidence; ownership documentation and code search cover application-level references. The delete gate lists every discovered consumer and its reconciliation check.

Follow-up 6: What if repointing events violates a child unique constraint?

Pause before updating. The collision means two child rows become equal after their parent IDs converge. Define whether they are duplicate events, distinct observations that need a new key, or a data conflict. Materialize collision groups, archive them, and apply a deterministic merge or rejection rule before repointing. Dropping the child constraint changes business semantics and is not a repair plan.

Follow-up 7: How do you prove the selected winner was the latest after the table changed during a long run?

The map proves the winner under its recorded snapshot or run boundary, not under unlimited future writes. Guard writers so later operations update the mapped canonical row, record a source version, and perform a final delta scan before uniqueness handoff. If business rules require a later update to replace the payload, update the winner; do not create another physical contact. State the temporal guarantee in the audit record.