While I think Tera's execution in the story mode of the game is questionable, I actually really like it as a mechanic in BSS / OU. Grants a good deal of flexibility in other wise really rigid match-ups and does help give me "outs" against certain Pokemon I normally struggle w/ dealing like Volcarona by going Tera Fire on Kingambit for example. Its not necessarily "free" either in most cases since Kingambit will be losing its very amazing initial typing, making it more vulnerable to threats its suppose to check like Gholdengo. There are also some pretty funny galaxy brain strats I've done w/ it like going Tera Fairy with Volcarona and using Will-O-Wisp on an opponent's Umbreon after they used Yawn on me, proccing Synchronise and burning myself. I do enjoy it in BSS more though since I think it fits with the fast-paced nature of that meta a bit more. Despite the BSS metagame being faster though, I am surprised that the drawback nature of Tera is still very much present there, particularly since there were moments where I say, Tera'd with Scizor or Toxapex, and got completely destroyed by the opponent's Toxic / T-Spikes or was forced out by the opponent's own Tera Pokemon that Scizor or Toxapex would normally have countered.
One thing that has been a pleasant surprise to me in terms of the competitive sphere with Terastal is how
flexible the mechanic is in terms of how it can be used.
Like on the surface level it can be used offensively either to change into one of a user's original types for super STAB, or into another type for boosted coverage that turns into STAB, but the type change aspect of Terastal while also allowing the user to keep its original STABs means it's actually surprisingly a lot more flexible in how it can be used than I had imagined.
Namely, that unlike Z-Moves and Dynamax, Terastal can be used not only offensively, but defensively in good ways as well. Sometimes, it can be used
purely defensively. Aside from the examples you outlined, including Kingambit who can also opt for Tera Ghost or Flying to deal with mons it would otherwise be weak to, Terastal has proven to be useful for defensively oriented mons as well to change their type matchups and in turn, which mons they can counter.
Two cases that stand out in this case are Garganacl and Skeledirge, who are naturally great defensive Pokemon with a lot of wonderful defensive qualities that make them the formidable defensive powerhouses they are, but with Terastal they can change their type to one they don't have any moves on, to something like Water or Fairy, to give them an edge over a different set of threats than they would originally. Like Tera Fairy Garganacl can turn into an absolute hard wall against Dragapult, or Tera Water Garganacl can become a solid Gholdengo counter. A Tera Fairy Skeledirge on the other hand can take advantage of Unaware to check Roaring Moon. Defensive builds have definitely found value in using Terastal themselves, and defensive mons can take advantage of it to change the set of Pokemon they can answer to in tandem with their original innate qualities (stats, movepools, abilities, etc.).
A very simple case of Tera being used defensively on more offensive mons would be leads like Glimmora or Meowscarada running Tera Ghost in case they need to act as an emergency spinblocker. While this isn't usually ideal, this does mean it can have its use as such if the situation arises in a given match.
And even one Pokemon can have a variety of different Tera Types that it can viably use depending on what your team needs most and what you want most out of the given Pokemon. Baxcalibur for instance can Tera into one of its own types for super STAB to become an even more powerful nuke, like Choice Band sets can go for Tera Ice for super powerful Ice STAB or a powerful Ice Shard, while both its Choice Band and setup sets alike can go for Tera Dragon for a powerful super STAB Glaive Rush as a nuke button, but it also has the defensive value of giving Baxcalibur a Fire resistance to make better use of its ability, Thermal Exchange, allowing it to switch into things like Volcarona and use its ability to build firepower. Or it can go for Tera Ground for STAB coverage, namely STAB Earthquake, to deal with Steel-types like Kingambit and Gholdengo better. Or its setup sets can also opt for Tera Fairy, using Terastal purely defensively to allow Baxcalibur to set up on and defeat opposing Dragons like Roaring Moon and Garchomp, while also resisting priority from Breloom and Kingambit.
In that regard Terastal has proven to be a very interesting mechanic to me, and really handles being a generalized power-up far better than both Z-Moves and Dynamax ever did: it's very flexible and can be used in a variety of ways, it can feasibly be used by any member on a team and who you use it on and in what way will be different with each given match, and it's appreciated by both offensive and defensive teams alike and both styles can benefit from using Terastal. This is much unlike Z-Moves and Dynamax, which both skewed the power-up scene towards offense almost strictly, the two power-ups only really had value offensively, and they were very one-dimensional in how they could be used during a match.
Terastal didn't seem like much to me at a glance but seeing it in action and seeing it used in so many ways has been a pleasant surprise, and I really like it as a power-up mechanic because it genuinely is quite fun and actually brings a lot of depth to matches, whereas Z-Moves and especially Dynamax were very one-dimensional and skewed playstyles towards one way or another. Of the more "general" power-up mechanics (ie Z-Moves, Dynamax, and Terastal), Terastal to me is definitely the best they have done in that regard.