I think I have a four-way tie for my biggest moment.
The first would be the first time I found a Red Marker for obvious reasons. I didn't even know that was in the game at the time. Those things essentially besieged my fort from the outside. It kind of turned into a survival horror game for a bit as room after room is broken in by those things, picking off dwarf after dwarf. It didn't end well for that fort. Even after I made the proper building for destroying the marker, the ritual killed my dwarf and didn't destroy the things it made.
The second would be the first time trying to harvest souls caused dead monsters to revive and then proceed to attack and kill at least one of my dwarfs. For some reason, the shrine of armok causes corpses to be fully resurrected instead of harvesting souls sometimes. The first few times that happened to me the rezzed corpses weren't hostile for whatever reason. Then when one of the rezzed monsters attacked and killed a nearby dwarf I was really surprised.
The third moment would be when I noticed my map suddenly being engulfed by fire, a map without any barriers to stop the fire from engulfing my main workshop hub. I am fairly certain that the cause was probably the flame ammo an archaeologist dug up and a hunter equipped. I've been instantly forbidding those things every time they get dug up now. So yes, I now religiously inspect any discoveries my archaeologists make the moment I get the message it happened.
The fourth moment would be the first time I encountered a seige with spell casters. When I enabled the jotun, I didn't expect them to have fire ball casting mages that would set my map and dwarves on fire.