I believe the inital system should be somewhat simplified. Even adding a new job "Study Arcane" at a study room. Where a dwarf with innate arcane ability (or just any dwarf, and make arcane a learn-able skill) can start research ways to discover words and form them into spells (after the words go through some form of "mangler", similar to how english -> dwarf translation works for fortress name).
The spell would have two parts, a simple label to quickly identify spell's ability, and a second part showing a rough translation of the full spell incantation.
The spell would show up in a list of spell available now in your fortress (and maybe as a scroll in a stockpile).
Incantation should be simplified somewhat to include...
Source (sphere/deity), Source Specifier (specific effect of sphere), Action, and Target.
And each of the above can have a prefix similar item quality.
Examples:
Spell 1
Name: Wrath of Karsari against Enemy
Incant: I call forth Karsari's wrath to strike down my enemy.
Source = Karsari, a deity
Specifier = Wrath, any Karsari's arsenal
Action = Strike Down, attempt to kill/injure
Target = Enemy, any enemy of caster, may target just another dwarf (that the caster dislike) within range (randomly targets).
Possible effect: Say Karsari's sphere is fire and plague, any one target within range that satisfy as an enemy of caster may get burned or diseased, depending on caster's closeness to Karsari.
Spell 2
Name: Fire of Karsari on enemy Goblins
Incant: I call forth Karsari's fire to incinerate my goblin foes.
or
Incant: I summon forth Karsari, god of flame, to burn my goblin foes.
Possible effect: Set several goblin enemies within caster's range on fire, randomly chosen. Number and strength depends on closeness of caster to the deity Karsari.
Spell 3
Name: Plague of Karsari on my friends
Incant: I call forth Karsari's plague to sicken my friends.
Possible effect: Er... make the caster's friends within range sick.
Spell 4
Name: Gift of Anari
Incant: I ask for Anari to grant me a gift.
Possible effect: (Say Anari is a goddess of pregnancy and food). Either a stack food of random size gets dropped near the casting dwarf or a random dwarf with caster's range (even the caster) will get pregnant.
Other possible effect is to give a group of military dwarves extra damblock for a while. Cause enemy's weapon to melt. Or on a much larger side, cause a torrential rain that generates 1/7 on any tile the raindrop hits (with a lot more rain drops).
Of course, the player should be able discard a spell from a list (and dump/destroy the scroll). And all spell will have a "review" period that allow you to discard extremely devastating spell against your own dwarves.
Casting Spells:
Spell can be cast on battlefield, but I generally don't like that kind of combat spell-casting (kind of weird standing there casting spell while goblins of shooting at you), plus it might be difficult getting your dwarf to "guard" the spell caster. I like spell that are relatively complex and require extensive preparation.
I want to be able to construct a altar/arcane circle. To cast spell you can queue up a task at the altar cast a specific spell. Where an arcane dwarf will go there and start preparation and incantation (speed depends and success rate depends on skill of the dwarf). A spell may "fail" if a dwarf is unskilled. A large chance is that failure would do nothing. But a small chance could be that a failure causes 1 or more word within a spell of change randomly (Instead of flame against one goblin, flame against several dwarves), or simply damage the caster.
In essence, I imagine a spelldwarf in an altar deep underground. Tracing/carving runes into the floor. Chanting incantations to set many goblins on the surface on fire.
Enchanting:
Dwarves should be more of an enchantment race. And the spell above can be applied directly to the weapon/armor. Of course, the effective target should be reduced to either the target being attacked, the dwarf themselves, target attacking dwarf, and those close to the dwarf. And the effect weakened to prevent them from being overpowering.
Variations:
Spell can be written into scrolls, requiring a dye and cloth (paper if its ever included). Or a tablet with stone. A requirement to cast the spell can be the possession of such scroll/tablet. The scroll/tablet can either be used repeatedly (combat-type spelldwarf) or consumed (altar, large scale casting). The "construction" of such an object should take a significant amount of time (maybe only 1~8 per year, even with highly skilled dwarf), maybe taking into account the power of the spell.