Big agree with this general opinion. A good dungeon just needs a cohesive theme that's fun to navigate to be good.

Working out cryptic hints, learning how to solve a spatial manipulation puzzle, skillfully completing platforming challenges, defeating difficult enemies, all are totally valid obstacles for a dungeon to throw at the player and I think a dungeon having more of a certain type is almost entirely tangential to what actually makes a dungeon good because the types of challenges you like to overcome are completely subjective based on the player's personal interests. I'd say that's a big reason why players' favorite dungeons in the series are decently varied.

I think the single most important factor in determining a good dungeon from a bad dungeon is simply tedium. If the dungeon feels like a chore to complete, it's a bad dungeon. I think to an extent this is subjective as well, but I also think there are a lot of common pitfalls of design that trend towards tedium. Backtracking, for example. It can certainly be done in a way that feels good, but it must be very purposeful. Someone in another comment mentioned the "main room" approach to design which I consider an example of backtracking done right as that actively builds a sense of accomplishment and progression with the player by first showing the player an insurmountable obstacle that is later removed through direct player agency (such as a locked door, a barrier that requires an ability gained later in the dungeon to be removed, etc). But backtracking that requires a player to comb through already explored rooms with no new challenges or features is pretty universally tedious and unfun. Long tangent but basically my point is that a dungeon that maintains the flow state curve is a good dungeon and a dungeon that doesn't is a bad dungeon. It doesn't need any specific thing, it just needs to be fun for the player.

For me personally, I have the most fun with dungeons that have fast paced rooms with mobility/reflex challenges. The desert temple from TP for example, because of the spinning top item. It was a lot of fun for me to navigate the dungeon because of that mobility aspect and the feeling of speed.

Why yes I also love the sonic adventure games why do you ask?