I'd not personally support the idea of gunpowder firearms. Maybe rockets, but highly random in their trajectories - including exploding in-situ - perhaps a mood/morale-adjuster for the most part, except for anyone who chances to be directly hit (when it's a low-powered dragon-breath sort of attack) or tile-adjacent (when it's a lesser version of a cave-in like concussion).
I'm sort of erring towards gunpowder as viable for one-use and one-tile pickless-digging/deconstruction tool when applied to a floor (downwards) or wall (applies into that given wall). Current cave-in effects affecting anyone in the proximity. Lever activated[1]? Half-half chance of being activated by a neighbouring gunpowder explosion?
Should be 'merely' flammable in raw form, like alcohol. It needs containerising (a workshop job[2]) to be explosive?
Could effectively do the same work as a lever-pulled Support, if used on a lone pillar of rock, etc ('dissapears' a single tile, perhaps give or take strength/nature of the MAT and/or quality level of the device... lower-than-success could convert to cracked-MAT which may be susceptible?). I'm gonna suggest that Spoilermetal veins be a total exception to this, and just absorbs any exploded charge without even weakening (it'd make in-canon sense, given its various qualities, and it also keeps 'in' the creatures it keeps in, even if they also can't (yet) get through even constructed soap walls...) or else it'll make for a too easy 'remote' method for seeing how far you can dig before you dig too far
If combat(/sabotage)-usable by (un)announced hostiles, should not be a quick place-and-leave thing. Should be a delicate or at least purposeful job to set it up by whoever attempts it. (Not as 'simple' as the cinematic Breaching Of Helm's Deep, a la Peter Jackson, for example.)
Probably should be the same for 'peaceful' self-use (perhaps a job like Architect with the skill to prepare the site - low skill might make for unpredictable eventual implementation), but there'd be nothing to stop you 'mining' your Hall Of Columns entryway in creative ways for potential future last-gasp demolition (then worrying about gremlins doing whatever gremlins might do to set it off without your will).
Would be a new way to tap into water/lava, after prepping the rest of aqua-/magma-ducting all the way up until the final lining wall of stone where you place your breaching-charge.
I'm not saying I fully approve of gunpowder, but after much thought (which I know is not at all entirely novel to this thread) the above are some thoughts about how to empower its use and make it non-trivial to do so.
[1] I'm not sure which lore to go for: If dwarves discovered it and use it, Levers are their tool of activation. Other races might instead light it. Perhaps you need an open trail of it leading to the lighting point (I can imagine the graphics/ascii depicting the burning leading along the entrail-like line of laid powder).
[2] Workshop has a chance of exploding, inversely proportional to whatever skill the workshop asks its workers to use... Anyone with "dabbling" is probably not a good opportunity.