So I saw that there are a few threads out there about holidays/festivals, but a lot of them seem kind of flawed to me, because most of the proposals seem to be for holidays / festivals keyed to events in the fortress rather than the civilization.
So here is how I think festivals should work:
1) Festivals should be keyed to either religious figures (a feast for a certain important god per year), major historical events that affect the fortress' civilization (Demonwhacked Festival to commemorate when XXX Slayed a God-Demon), or maybe just a general time of year (yule tide, harvest, spring, etc). I am not saying that a festival should be created for every single event; at most maybe there should be two or three major festivals per year.
2) Major festivals include certain customary activities: "dwarves of civ X customarily eat kitten tallow biscuits on Demonwhacked festival" or "dwarves of civ X wear leather kilts during Demonwhacked" or whatever. Heavy drinking should probably be mandatory. Maybe even gifts could be exchanged between pals?
3) Festivals raise the morale of everyone in the fortress BUT ONLY IF they are able to celebrate properly. If they are able to make enough kitten tallow biscuits in preparation for the festival, it will be a big success; everyone will be happier, inter-dwarf relations improve for a while, bad thoughts erased or mollified, etc. etc.
A failure to properly celebrate a festival shouldn't result in immediate tantrum spiralling of course, but failing to celebrate properly for years ought to make the dwarves start to get mutinous.
4) Minor festivals can simply be cause for brief parties in the eating hall. Maybe in the case of minor religious festivals, only those dwarves dedicated to a given deity take part.
5) Perhaps minor one-time holidays might result based on local events (enemies killed, important dwarf dies, super-valuable artifact made, etc), as per earlier suggestions.
And here I write the obligatory apology if this is all old hat...