Data Engineering Interview: Why Can PostgreSQL 18 MERGE Fail on Duplicate Source Rows?

Question and scenario

A daily customer snapshot table, stagingcustomer, is merged into customerdim. In one batch, the same customerid appears twice: once from the CRM and once from a manual correction. The target has no duplicate key, yet the statement raises a cardinality violation. Explain how PostgreSQL 18 forms candidate change rows, why one target row cannot be modified again by another source row, and how RETURNING mergeaction() can produce an auditable result.

What the interviewer is testing

• Distinguishing duplicate source rows, duplicate target keys, and an overly broad ON condition.
• Explaining that MERGE executes only the first matching WHEN branch for each candidate change row.
• Making a defensible choice between deduplicating, rejecting the batch, and keeping the latest correction.
• Using RETURNING for row-level audit evidence without treating it as a transaction log.
• Handling concurrency, reruns, privileges, and data-quality alerts.

Clarifying questions before answering

• Is customer_id the business key, or must tenant and effective date form a composite key?
• Do both source records have a trustworthy version, event time, or correction priority? That answer determines deduplication.
• Should an invalid batch be rejected atomically, or can settled rows be written first? This changes transaction and replay design.
• Does audit require old values, new values, source fields, and action, or only row counts?
• Can source records arrive late across batches, and does the target allow soft deletion?

30-second answer framework

“I first prove that the ON condition maps each target key to at most one source row. PostgreSQL MERGE builds candidate change rows and then executes one action per row in WHEN order; multiple source rows reaching one target row cause a cardinality error and the transaction fails. I would deterministically deduplicate by version or event time, and reject a batch when the conflict cannot be resolved. RETURNING merge_action() records inserts, updates, and deletes, while a batch-control table, unique constraint, and idempotency key make reruns safe.”

Step-by-step deep answer

Prove matching cardinality first

Run a data-quality query with exactly the keys used by ON and find target keys with multiple source rows. Do not merely apply DISTINCT to the source: two rows with the same key can still describe conflicting facts. If the business key is (tenantid, customerid), use both columns in the MERGE and in the quality query.

Make deduplication deterministic

Prefer a version number. Without one, use event time, a trusted source priority, and a stable tie-breaker. A window function can choose one winner:

WITH ranked AS (
  SELECT s.*, row_number() OVER (
    PARTITION BY tenant_id, customer_id
    ORDER BY version DESC, event_at DESC, source_priority DESC, ingest_id DESC
  ) AS rn
  FROM staging_customer AS s
)
SELECT * FROM ranked WHERE rn = 1;

If neither source can be shown to be newer, write the conflict to a quarantine table and reject the batch. Never rely on the database to pick an arbitrary row.

Design WHEN branches and audit output

WHEN conditions are evaluated in written order, and the first true branch runs. Put protection conditions first, such as updating only when the incoming version is higher, then handle NOT MATCHED inserts and any intentional NOT MATCHED BY SOURCE cleanup. PostgreSQL 18 RETURNING can expose source columns, old and new target values, and merge_action(), but it reports rows changed by this statement; it does not replace a batch-control table.

Control transactions and concurrency

Keep the batch MERGE, audit insert, and batch-status update in one transaction. Give each input batch an idempotency key and detect successful batches before replay. Follow the database isolation rules for concurrent runs, and guarantee at most one candidate source row per target row before execution rather than discovering duplicates at runtime.

High-quality sample answer

“The error is not explained by a duplicate target key alone; the important fact is that the ON condition connects one target row to multiple source rows. I would check source cardinality using the same tenant and customer keys, then rank by version, event time, source priority, and ingest ID. Unresolvable conflicts go to quarantine and fail the batch. Because MERGE executes the first matching WHEN branch, I put the higher-version guard first. PostgreSQL 18 RETURNING merge_action() records insert, update, or delete plus old and new values; the audit and batch-control rows stay in the same transaction so reruns, alerts, and replay have evidence.”

Common errors

• Error: Applying DISTINCT to the source → Why it fails: Different facts can be merged incorrectly → Correction: Define a winner with version and business priority, and quarantine conflicts.
• Error: Assuming MERGE picks a source row randomly → Why it fails: Multiple modifications of one target row raise a cardinality error → Correction: Prove one-to-one matching before execution.
• Error: Treating RETURNING counts as batch success → Why it fails: Zero-change, failure, and replay state are missing → Correction: Use a separate batch-control table and transaction state.
• Error: Testing only one thread → Why it fails: Isolation, late data, and replay are unverified → Correction: Test concurrency, reruns, and late batches.

Follow-up questions and responses

What if one duplicate source row is a delete marker?

Put deletes and updates into the same version ordering and let only the newest version win. If the delete has no comparable version, quarantine the conflict rather than letting one batch silently decide customer state.

Can RETURNING record source rows that matched nothing?

It returns rows affected by INSERT, UPDATE, or DELETE; it is not a source-side unmatched-quality report. Run an anti-join statistic separately, or store candidates and intended actions in a pre-audit table before executing MERGE.

Why not use INSERT ... ON CONFLICT directly?

For a simple unique-key insert-or-update, ON CONFLICT may be simpler. Choose MERGE when source matching, missing-source cleanup, or multiple conditional branches are required. Do not assume their concurrency and privilege semantics are interchangeable.

What changes when the target has triggers?

Verify that triggers do not change the match key or make the same target row a new candidate within the statement. Distinguish the MERGE action from trigger side effects in audit data, and test rollback in an integration environment.

References

• PostgreSQL Documentation 18: MERGE
• PostgreSQL Documentation 18: Merge Support Functions
• Greg Low: SQL Interview: 35 T-SQL Merge Statement Clauses
• Simplyblock: PostgreSQL MERGE tutorial

Answering tip

Prove one-to-one ON cardinality first, explain WHEN ordering and cardinality violation, then give deterministic deduplication, transaction control, RETURNING merge_action() audit, and replay handling.

One-sentence takeaway

A strong MERGE answer connects source cardinality, branch order, and audit output into one replayable data contract.