MERGE Failure After a Duplicate Staging Load

ORA-30926 means the USING clause returned more than one source row for a target row, so MERGE cannot decide which source row wins. The whole statement aborts. Here 3,240 staging rows map to 3,120 CUST_ID values, so 120 keys are duplicated. Confirm the duplicates before changing SQL:

SELECT cust_id, COUNT(*)
FROM   stg_customer
GROUP  BY cust_id
HAVING COUNT(*) > 1;

Pick a deterministic winner in the USING clause rather than hoping the load order is stable:

MERGE INTO customer_dim d
USING (
  SELECT cust_id, cust_name, status
  FROM (
    SELECT s.*, ROW_NUMBER() OVER (
             PARTITION BY cust_id
             ORDER BY load_ts DESC, stg_row_id DESC) rn
    FROM stg_customer s
  ) x
  WHERE x.rn = 1
) s
ON (d.cust_id = s.cust_id)
WHEN MATCHED THEN UPDATE SET d.cust_name = s.cust_name, d.status = s.status
WHEN NOT MATCHED THEN INSERT (cust_id, cust_name, status)
VALUES (s.cust_id, s.cust_name, s.status);

ROW_NUMBER preserves the full winning row; GROUP BY works only if every selected column is aggregated. DISTINCT is weaker because rows can differ in non-key attributes. Also fix the loader so a retry does not append duplicates.

That assignment raises ORA-38104: columns referenced in the ON clause cannot be updated. The match set is defined by d.cust_id = s.cust_id, so changing the key mid-statement would make the statement's own match semantics unstable. Oracle forbids it rather than defining a surprising behavior.

The assignment is also redundant. Because the ON condition requires equality, the value is already the same; removing the line changes no data and lets the MERGE run. Keep the SET list limited to non-key attributes such as name and status. If a customer truly must be re-keyed, do it as a separate, deliberate operation: update dependent foreign keys, handle unique constraints, and manage the transaction explicitly. Updating a key that participates in a primary key or is referenced by a child table can raise ORA-00001 or ORA-02292, neither of which belongs inside a nightly load. Also check for triggers on CUSTOMER_DIM: the MERGE is atomic, but triggers may fire for rows later rolled back and may have side effects outside the transaction.

The claim is correct at statement level: MERGE is one DML statement, and when it raises ORA-30926 its changes are rolled back. It is not a guarantee about the whole job. Any earlier INSERT into an audit table, any commit in the job's error handler, and any sequence NEXTVAL call are outside that rollback. Sequence values are never rolled back, so gaps grow even when the MERGE fails. Before rerunning, inspect commit boundaries and triggers; if the job commits staging cleanup before the MERGE, rerunning may fail earlier. The deduplicated MERGE itself is idempotent for updates and safe to rerun once the source is stable.

Oracle 19c MERGE has no WHEN NOT MATCHED BY SOURCE clause. Handle deletes with a separate statement in the same transaction, after the MERGE:

DELETE FROM customer_dim d
WHERE  NOT EXISTS (
  SELECT 1 FROM stg_customer s WHERE s.cust_id = d.cust_id);

If audit history is required, use a soft-delete flag instead of physical deletion. Validate source and target counts before committing, and commit once at the end so a failure leaves the warehouse unchanged.