The Nightly MERGE That Doubled the Warehouse

Two independent defects compound.

1. The ON clause can never match a null warehouse. t.warehouse_id = s.warehouse_id with a NULL on the source side evaluates to UNKNOWN, so the 1,800 rows take the WHEN NOT MATCHED branch and are inserted again. Every subsequent run inserts another 1,800. This is the standard NOT IN / equality-with-NULL trap: NULL is a missing value, not a distinguishable value, so it cannot be equal to itself.

2. The match branch is not idempotent. SET t.qty = t.qty + s.qty treats the staging file as a delta. It is actually a full snapshot of current stock, so replaying it adds last night's numbers on top of themselves. Two runs therefore double every matched quantity. Even with no NULLs and no duplicates, the load would be wrong after any rerun - and reruns happen after every failure.

3. Nothing in the schema prevented the duplicate rows. The unique constraint that should have raised ORA-00001 was left off, and the index is non-unique, so the engine happily stored a second (101, NULL) row. The primary key on the sequence-generated INVENTORY_ID guarantees uniqueness of the surrogate and nothing else; a surrogate key never protects a natural key.

Correct diagnosis order: fix the business key (unique, NULL-free), then make the statement idempotent, then handle bad rows.

-- one-time: make the business key real and NULL-free
UPDATE inventory SET warehouse_id = 'UNASSIGNED' WHERE warehouse_id IS NULL;
ALTER TABLE inventory MODIFY (warehouse_id NOT NULL);
ALTER TABLE inventory ADD CONSTRAINT inv_uk UNIQUE (product_id, warehouse_id);

MERGE INTO inventory t
USING (SELECT product_id,
              NVL(warehouse_id, 'UNASSIGNED') AS warehouse_id,
              qty
       FROM   staging
       WHERE  qty >= 0
       AND    product_id IS NOT NULL) s
ON (t.product_id = s.product_id AND t.warehouse_id = s.warehouse_id)
WHEN MATCHED THEN UPDATE SET t.qty = s.qty          -- snapshot, not delta
WHEN NOT MATCHED THEN INSERT (inventory_id, product_id, warehouse_id, qty)
VALUES (seq_inv.nextval, s.product_id, s.warehouse_id, s.qty);

Reasoning:

• SET t.qty = s.qty makes the statement idempotent. Staging represents the state of the world; replaying it converges on the same answer instead of compounding.
• NVL(warehouse_id,'UNASSIGNED') removes the UNKNOWN comparison, so the virtual warehouse becomes an ordinary, matchable key value. A sentinel was chosen over filtering the rows out because dropping 1,800 rows of real stock silently is worse than labelling them. If the business rules say those rows are invalid, reject them explicitly and log them instead.
• NOT NULL plus the unique constraint turn any future duplicate or stray NULL into an immediate ORA-00001/ORA-01400 at load time rather than a slow divergence discovered at month end.
• MERGE needs at most one source row per target row. If staging can contain two rows for the same key, deduplicate in the USING subquery (GROUP BY product_id, NVL(warehouse_id,'UNASSIGNED') with SUM(qty)), otherwise you get ORA-30926 'unable to get a stable set of rows in the source tables'.

A MERGE is a single statement. Under Oracle's statement-level atomicity, one ORA-02290 discards all 12,000 rows - there is no 'partial load' to inspect. The failure is all-or-nothing, which is correct behaviour but useless operationally.

Two ways out, in order of preference:

1. Error logging (keeps the statement intact).

EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('INVENTORY', 'ERR$_INVENTORY');

MERGE INTO inventory t USING (...) s
ON (...)
WHEN MATCHED THEN UPDATE SET t.qty = s.qty
WHEN NOT MATCHED THEN INSERT (...) VALUES (...)
LOG ERRORS INTO ERR$_INVENTORY ('nightly_load') REJECT LIMIT UNLIMITED;

Rows violating QTY >= 0 are written to ERR$_INVENTORY with ORA_ERR_NUMBER$, ORA_ERR_MESG$, ORA_ERR_ROWID$ and ORA_ERR_TAG$; the other 11,997 commit. The error table is not transactional against your data - it is exactly the audit trail you want for a rejected row.

2. Pre-filter in the USING subquery (WHERE qty >= 0), as in the previous rewrite. Simpler, but it silently discards rows; pair it with an INSERT INTO load_rejects SELECT ... FROM staging WHERE qty < 0 so the rejects are visible.

What this teaches: atomicity is per statement, so a bulk DML statement is the wrong place to be tolerant of dirty input. Either validate the staging data before the merge, or attach LOG ERRORS so a statement can complete while isolating failures. Note also that negative quantities usually mean returns and should be applied as a sign convention, not rejected - that is a business decision, not a SQL one.