> The insert is upper boundable in advance
A concurrent DML happening then suddenly your MERGE INTO WHEN NOT MATCHED INSERT/INSERT INTO SELECT is way larger that you thought? I thought "some workloads can suddenly be way larger that I expected" was supposed to be a thing in all non-trivial DML.
You don't even need a complex query; even the simplest of insert statements can cause cascade side effects if you have temporal tables or materialized views (or, Codd forbid, ON INSERT triggers).
I will die on the hill that triggers are a perfectly fine tool, when used reasonably. ON INSERT isn’t usually the one I point at causing problems; that’d be ON DELETE CASCADE. 1:M relationships with large values of M are already iffy for deletions or updates; couple that with unnecessarily wide columns (or just storing large text / json / blob), and worst-case, non-clustering indices, and “delete this user” turns into “fetch thousands of pages.”