When thinking of what types of spells can be brought into DF, I realized there already are spells in DF. Farm plots built underground are able to produce infinite food with fertilizer, re-watering or sunlight. If farms required a certain amount of fertilizer in order to produce food, then a spell to fertilize an area without fertilizer would be useful. There should be a certain amount of fertility in the land already, so your startup farms won't require a bag of potash or spell casters to start up. Maybe instead of a check for each tile, a simple check of the biome to see how fertile the playing area is.
EX: A fertile hill has 200 fertility in total, meaning that you may grow 200 plants without fertilizer. A good biome may regenerate fertility while a glacier may have none whatsoever.
Spell 0 - Scrying/Seer (DF, ADV, W)
Used currently right before embark, a scryer would check the land for any mineral deposits hidden underground, search for caverns and check for aquifers. A seer would check for hostile civilizations, evil biomes and land fertility. Each one could be used by a home civilization if they are capable of doing so. You'd also be able to use it somewhat like DFhack reveal cheat, searching underground for the caverns, minerals and gems, although with certain limits and restraints.
Spell 1 - Fertile Land (DF)
Cast on a fallow farm plot to allow that land plot one growing season without fertilizer. If cast in a ritual, it could increase the fertility of the region. Would help explain how a farm underground can produce food without fertilizer or SUNLIGHT!
Spell 2 - Dry Air(DF/ADV)
The air around the caster becomes dry, causing water to evaporate faster and from deeper levels. A level 2/7 water tile could start evaporating, 3/7, etc depending on the caster's level. Seriously, how is it that water can evaporate underground in the first place? Maybe make it a target spell against water tiles if area effects are too hard to do.
Spell 3 - Summon Vermin(DF/ADV)
Uses old clothing and food(such as cheese) to produce a swarm of vermin. The quality of food will determine the number of vermin produced. Used to help feed your cats during winter years.
While I am all for gems for spell casting, there should be more available regents to use, such as lumber, food, metal, water, booze, and blood. There should also be a mana, fatigue, and DnD style casting points for lower level spells to use. Higher level spells should require higher level regents in order to use, but lower level spells could get away with using mana and fatigue.
Each spell would require casting point (for each spell) to reduce the number of times that spell can be cast, mana to reduce the total number of spells that can be cast and fatigue, to reduce the number of actions a spellcaster can make after they finish using all their mana. This will reduce the number of spell casters who, after casting their spells, goes about farming and creating crafts just like a normal dwarf.
PS: It seems that DF logged me out while I was typing my first draft of this. This is my second attempt to write this so I may of left out a few spells that I had thought of the first time. This draft is definitely worse than my last one.