I think I tl;dr a few of the posts in this topic, but a lot of the ones I've read were good ones.
Personally, I agree that magic in Dwarf Fortress mode should be rare. Not minor or ineffective - just rare. I think that sorcery should all-in-all have the same impact that artifacts do. I think that there should be skills such as healing, alchemy, etc. which look close to magic, but ultimately either fall short while still being more practical and common than actual magic, or - if a magic user so desires - may be a tool for a magic user's studies.
When it exists, I think that magic should be able to be applied to any area of life, but rarely be worth whatever price tag associated. On the subject of prices, I think that the prices of any particular magical effect should be pretty much randomized, and I think a great deal of a mage's time won't be spent casting magic so much as studying it. I think that entire years could by with a spellcaster doing nothing more than constructing and researching a new spell.
Speaking of randomness, I think that should be a huge factor in making magic generally useless. How useful, for example, is a spell which the sole function of it is to turn a wooden chair into a iron chair, at the cost of a piece of fruit, some of the caster's vitality, and a few hours of casting, particularly if it has the nasty side effect of pushing everyone in a 50 square radius two steps closer to madness, and can at times kill small infants who are nearby? Not very. We've got a large set of side effects and problems, and all we get from it is being able to - very slowly - turn wood and fruit into iron, but only if we've converted the wood first into a chair shape, and after which we must melt the iron down, unless a chair is exactly what we wanted.
But then what's the use of magic if it can't be useful? A spellcaster will answer that any spell, potion, or enchantment can be refined. Over years, a few of those naunces might be removed. Someday, you may even get a powerful art of converting wood into iron, and should you train an army of spellcasters into archmagi, you might even get a working factory.
But I include that I think magic should take time - time to learn, time to master, time to perform. Barring all other costs, magic should take large investments of time. I don't think any mage short of Legendary in whatever magic skills there might be should be able to perform anything remotely useful. A mage short of legendary may be able to learn a few spells, and might even contribute to research - much in the same way a dabbling miner may be able to help mine out a fortress for five hundred dwarves. And even once a mage masters the general skills and theories, it should still take long periods of time studying and mastering each individual spell - even moreso if they're the ones inventing the spell. Time to master the spell, time to copy the spell, make spellbooks, sort through them, research created spells, study the effects of various objects.
One thing common in fantasy settings is that wizards of any reasonable talent are generally old men who have been performing their art since a young age, spending a good amount of time studying under another old man who followed the same pattern. They just seem to forget to include this vital point during actual gameplay for the sake of mechanical balance.
And of course, the point that others raised is the extensive resources it costs to research magic things. Even if you get that fireball spell to accurately function as a weapon with no real side effects and fairly efficient costs, and even if you do spend years training a small squad of mages to be able to cast it effectively... the sheer monetary value of all the resources that would have gone just to research and master such a spell would probably be equivilant of a typical artifact, and the costs to train mages to cast it would likely be even higher, nevermind the original wizard who had to invent and master the spell in the first place.
That all said, I do believe there's a Wizard arc planned, where instead of playing a fortress, you play a wizard. That'll be awesome. I cannot wait to research spells that turn doors into walls and walls into chairs and stuff. Oh yes.