Most materials ought to have scientific weaknesses that stand on their own, regardless of whether that particular creature has any weakness resulting from their sphere/mythological nature or not. Of course, having metaphorical weaknesses too is also good.
The problem I see is that down that path lies a lot of the same arguments we had about 8 years ago, where anyone trying to suggest anything remotely fantastical was shouted down because Dwarf Fortress was totally a 100% realism simulator with zero fantastical elements, shut up about the dragons and amethyst men already! A lot of these specific counters are likely to be rendered obsolete when Toady changes the procedural generation mechanics again (in particular, filth may well disappear at some point), so spending a lot of time getting into the weeds on specific FBs is going to be wasted effort, while talking more generally about creating dynamic matching of procedural materials to procedural weaknesses and the way to ensure that these are available to a player and that a player can reasonably find a way to apply them to weapons or employ them as weapons is more likely to actually still be relevant when Toady comes trawling the suggestions forums for ideas to flesh out what he's already planning.
Rather than worrying about specific FB materials, I think that there's more to be mined in vampires, experiments, vault denizens, and night trolls. Night trolls in particular are fairly unique horrors that are prime targets for adventurers and not a big issue in fortresses.
I think that specific weaknesses suits Adventurer Mode much better than it suits Fortress Mode, since these sorts of stories where the hero barely escapes a disastrous battle, has to learn a secret weakness, build their weapon to defeat the one that killed their buddy, then return and wreak revenge are bread-and-butter adventurer stories, while it's more of a chore to keep every possible weakness stockpiled in your fortress where you just say "Oh, the waffle trolls are back, time to break out the syrup casks..." In that vein, the reclusive night trolls, or the occasional experiment are much better suited to having specific weaknesses than other things. (And experiments and such would be better suited to being just really, really hard to kill without a specific weakness, not outright impossible, since it's a horde battle, anyway.)
The topic of vampires is also one I think is suited to a specific weakness, since vampires in the myth of the Christian world are weak to crucifixes because the Church for some reason felt it was necessary to spend time spreading that idea around, but in DF, vampires are often made (at least in their zero patient) because of offending a god and getting a curse. Hence, it makes sense that the weakness of a vampire (and their progeny) be the symbol of the specific god that cursed them. A sea god represented by a fish that cursed a vampire for desecrating their temple might have a vulnerability to anything with a fish symbol engraved on it. (This might require more gods to diversify their symbols, though, as most gods are just represented as men or women of the race they belong to, while even a human Athena is often represented through an owl, while Hera is represented with peacock feathers, or Hermes with winged shoes or the Caduceus, for example.)
I think, come-myth&magic, we need to consider procedural symbolic/metaphoric weaknesses. Like what is the elemental antipode to fire, defined in the procedually generated wheel of elements? Use that against a being of flames and cinders. The filth-monsters of the deep, are they the remains of some great chaos cast away by a diety that also created willows? Then they may be weak to willows and any weapons made of their wood. etc. etc.
Of course, then there is the problem of exposition, but I think it’s ok for the players to not always know how to deal with a monster. Having to send out your dwarves on a quest to find the only weakness is fine, so is setting up a library to collect all bestiaries of the world. Perhaps some evolved form of the current ”monster hunters” could help as well, being schooled in the ways of monster slaying, and teaching it to your dwarves.
One thing I have to note is that I do wish Dwarf Fortress doesn't fall into having four Greek elements (or five Chinese elements, which are honestly even more nonsense) be some main theme, as that's
extremely tiresome and cliche by this point, and would come off very JRPG-ish. Yes, I know the spheres are all there for that, but they don't have to be the be-all-end-all, at least, and there are enough spheres that not all of them are represented in every game.
Anyway, rather than having hard counters for creatures of one sphere (I.E. all "air-sphere" creatures are inherently weak to rocks because they represent "earth"), it makes more sense to just have a big list of possible strengths and a big grab bag of weaknesses. Depending upon the magnitude of the strength, the weakness should be correspondingly common or dire. (For example, an otherwise invincible monster made of pure fire should be weak to something very common and suffer total annihilation when faced with it, such as water or a nethercap weapon that inherently absorbs all heat. A creature that is "merely" larger and denser than normal, or which just has a supernatural power such as a song that can confuse its listeners, but which is otherwise easily defeated with a axe to the skull like anything else can have a more esoteric or less devastating effect if its weakness is brought up. The song-based creature, for example, may find itself unable to sing and take a moderate movement penalty if presented with its weakness, a tuning fork made of tin or a skilled bard playing a specific counter-melody on a tin lute.)
The ability of players to find weaknesses (especially when weaknesses are procedural and therefore the wiki can only give you a broad outline of what weaknesses are possible) and to be able to survive until such a weakness can be procured is also a concern. I'm personally not against just raising the drawbridge and ignoring a FB that wants to rampage around the caverns until I can make a trap that will cage it, but some players think not charging headlong into danger isn't 'dwarfy' enough, so there will be complaints if the usual tactics don't succeed against it, and they have to research an esoteric weakness that is difficult to actually produce and implement.
Again, weaknesses work better for Adventurer than Fortress Mode in general, since it's more geared towards cautiously countering specific opponents rather than having random challenges tossed into your lap, so I think weaknesses (especially weaknesses that take a lot of work to make) in general are better geared towards things you more often encounter as an adventurer, and things that have weaknesses that might show up in Fortress Mode should be more mundane items players should have on hand anyway, like plump helmets or buckets of water.
The mechanics of learning a weakness are also definitely worth exploring. The most obvious thing to go for would be a book, possibly ones you have to buy from sketchy sources, but in a procedural game where you can't proactively find more books (nevermind the possibility of accidentally creating necromancers because the local library accidentally bought a copy of the Necronomicon), and traveling sages, although getting them to actually spill on specific weaknesses to the specific FB that might show up at your fort a year from now can be a major hassle, since you're trying to get every weakness of every beast out of them, just in case. A fortress's own sages might want to pen tomes of monster hunting and spread copies around once they've gained such knowledge. (It would probably also be a good story beat to have the Mountainhome send out a request for aid because it is being assailed by a horrible beast, and they don't know the weakness - your fortress will receive promotion or accolades or treasure if they can discover the weakness and save the mountainhome in time.)