However, can you please explain to me the use case of "Siri, create an appointment at 3PM tomorrow" - where I would want no alert, at time of event, at the very least? I am pretty good at imagining edge cases, and I cannot imagine even one.

I have never been more upset at a default setting. I want to name and shame, and worse. Who made this call, a hippo? Think of the lost productivity at scale. "It just works UX" was supposed to be the entire point of Apple.