Oh, a megabeast or titan showing up early will wreck you, not doubt. But against an established fort, they're nothing. A dragon might cause considerable damage with the gouts of flames before one of your squaddies impales it's face, but that face is going to get impaled. A hydra might kill a few of your soldiers or even wipe out a squad, but it will bleed out soon after. If the best a god can do is attack your fort in person, mano-et-forto, then that's a bit unimpressive.
If they bring or just send an army of supernatural supersoldiers, then there's a challenge. One demon is no match for a well-armed, well-trained squad, but demons end forts because not only do they each have considerable power, they also come hordes. Even a necromancer with a steady supply of refuse tends to be more of a problem. You can drop a 500 ton block of granite on even the most terribly powerful monster, but against overwhelming numbers, there's no slab wide enough. unless they're all lying down on the same tile.
Again, though, if Hell kills you though combat, then it's a bit boring if Heaven does the same. Maybe they gods can do more traditional god-y things, like dousing your fort in syndrome rain.
Dropping massive slabs of 'heavenstone' on you from above?
Unrelated, but would anyone know of a mod where dragons are at least kind of what I detailed above? I got a fort attacked by one a few days ago and was severely disappointed.