Toady's spent a good deal of energy explaining what he doesn't want DF magic to be, while putting equal force explaining that he has no idea what he wants to do.
Some things have been specified concretely...
He wants to do a Wizard Tower mode, eventually. This is where the player is most likely to get the most powerful magic at their fingertips -- I
think the general implication is that in Dwarf Fortress mode, it's more likely to be used against you than by you.
Regarding calling down thunderbolts or hurling mighty blasts of fire, he's said that he wants the game to have a generalized framework for
creating magic systems, rather than focusing on making one specific vanilla magic system. The idea is that individual worlds might even have their own randomized magic systems or whatever. So, you might have one world where magic is all about chanting words and making things burst into flames, and another where it's all about scratching runes or catching a special disease or who knows what else.
However, the key thing to remember is that Dwarf Fortress is not supposed to be a generic fantasy world -- it is supposed to be a generic fantasy world generator. The things we associate with generic fantasy will be there and available, just not hard-coded into one concrete form.