What interests me isn't so much the magic attacks. Yeah swords that freeze the target in a block of ice or whatever are cool, but we've seen that kind of stuff in hundreds of games already. Imagine all the other possibilities. We've got a pretty substantial list of spheres, and it sounds like those spheres are going to be part of the magic system, being magically imbued onto artifacts (probably via runes, at least for the more dwarf-centric part of the magic system). So imagine what possible combinations you could get.
What I would do as far as magical artifacts go is generalize it like this: weapons tend to impart an effect on things that it hits (and deals damage to, otherwise sparring is too much of a pain to micromanage) and in some cases the wielder, clothing/armor imparts an effect on the wearer, and furniture has an AoE effect. You'd probably want to stick in edge cases for interesting items that deserve their own special conditions, but this feels like a good baseline to build off of to me.
Some ideas that have been mulling around in my head:
A healing-associated bed that heals injuries in everybody who sleeps in it, but need to be 'fed' corpses by stockpiling them nearby for the effect to work. If you let it drain all the corpses and don't replace them, the bed will start rotting/skeletonizing nearby dwarves as it heals the occupant. Generalize the system, and you could make any other piece of furniture have the healing as an AoE effect on nearby injured dwarves. A weapon version could heal the wielder and drain the victim, but will drain any nearby units if the wielder is injured and not hitting anything. This would add a cost to it: if the wielder is fighting near other dwarves, you could be killing your own allies. A wearable version would just heal the wearer and drain the victim, probably at a greater rate for both than the passive weapon effect. Stick the wearer in a squad full of disposable recruits for maximum survivability of your most important dwarf at the cost of survivability for everyone else.
An ocean-associated floodgate that produces unlimited water when activated with a lever. Leave it closed for too long, and it will start leaking water out on its own, potentially flooding your fort if you aren't careful. Great for defense and providing your dwarves with an infinite water source, potential for water megaprojects, but could also destroy your fort. Harder to generalize the effect, perhaps furniture artifacts that can't be hooked up to a lever could have a lower rate of flow, but still always produce water when constructed. Wearable items and weapons would probably need a totally different effect, because a leaky dwarf is not particularly helpful for a fortress.
A forgiveness/mercy associated hood that causes foes to spare the wearer when heavily wounded. The wearer's personality is shifted to be more merciful whenever the effect saves their life, which could cause them to be less useful in combat (they would eventually start sparing enemies as well, or just become more shaken when they're ordered to kill). Keeps important dwarves alive, but potentially at the cost of reducing the lethality of your army. Something like this could have diplomatic uses too. Maybe when they add diplomacy to end wars, you could send a diplomat equipped with one of these to increase your chances of success. A furniture version could cause the same effect in an AoE, making enemies less likely to kill your dwarves when nearby, at the cost of slowly making many of your dwarves more merciful.
A pickaxe associated with volcanos that causes a lava tube (probably a lot smaller in diameter than a natural volcano) to rise from the magma sea straight to the surface centered on whatever X/Y location is dug out by the pick, replacing an entire column of the world with magma with an obsidian boundary, as though a volcano had been there all along. 100% kills the user, the artifact is nearly impossible to recover after one use (maybe it could float on magma?) destroys a lot of ore, could easily destroy your fort, but could straight up delete an army when you first create it, it gives you access to magma forges/smelters/kilns, and lets you build magma mega projects/defenses if you didn't embark on a volcano to begin with. Pretty hard to generalize this, maybe military dwarves could be instructed to just "use magic from [X Artifact]" on command, letting you use the same effect with any held or worn volcano artifact. Volcano associated furniture would probably need a whole different effect (maybe just the oceans effect but with magma instead).
And then there's some really fucking weird spheres like "Pregnancy". Uh, maybe a sword associated with pregnancy that causes the victim to spontaneously give birth right there on the spot, and flee with the baby rather than fight any longer? That artifact might be too weird for dwarf fortress. A more reasonable one would be piece of furniture that induces pregnancy in livestock nearby, even if there's no males about. That's lots of free food, but could potentially lead to a cat grey goo scenario. For bonus fun, make it so dwarves are also affected by it, and you need to be careful to make sure that dwarf exposure is kept to a minimum to prevent too many children from being born. Pregnancy associated clothing would be less useful. Maybe you could stick it on your strongest dwarf for a dwarven eugenics breeding program. Even then you'd need the offspring to magically age faster for it to be useful. Doesn't matter if you have 30 incredibly strong dwarves if it takes longer than the average fort's lifespan for them to be old enough to wield a sword.
There's just so many possibilities, and that's JUST artifacts. The magic system they've proposed is incredibly ambitious.