There's some intrinsic production & game issues currently with how cannons could be implemented that need solutions first.
Gunpowder: How will we aquire it in the game (whether there's prerequisite steps) and keep it relevant as a resource to gather and hoard before the player is introduced to cannons.
Velocity: Velocity in DF is one of the most important aspects of ranged combat, perfectly demonstrated by blowdarts; blowdarts of respective materials can effectively penetrate most armors more competently than bows & crossbow bolts and arrows because they are fired with much more force and focus a much
- Now lets say that in a fantasy scenario, a dwarf literally carries a cannon underneath is its arm and shoots small but full size cannonball at someone with a extreme amount of velocity, the projectile is going to fly fast, have a wide contact area and hit like a full strength flying hammer, downsizing the gun or ammunition will recieve the same results
- Shotgun shells are just little balls shot with maximum amount of force to shred, cannon ammunition used to be like that with grapeshots.
Destroying landscape: On the roadmap but not there yet, smashing a large creature into meaty gibbets or punching its teeth out causes enough item-spam and lag already, defining each individual tile that is affected, how much damage is taken to it and where it should fly off and land are technical challenges.
Im not totally against cannons but there needs to be a lot more arguements about the feasability of its presentation & execution so it can slip into the game & the lore world without offsetting everything with a arms race to develop the better weapon. A catapult fufills the current purpose of bombarding people (offensive use yet to be seen), a ballista works like a culvering for anti-infantry, and crossbows are essentially as powerful as they need to be.
So naturally you can see, a cannon (siege,hand,regular) is just a expensive and metal intensive improvement on the arnaments with the power to severely make things onesided. We'll know what toady has envisioned for it when boats eventually roll by.