I did a lot of theory study last year to try to get to the bottom of such questions.

Most stuff of course resolves modally, and therefore makes sewnse this way. Music is also often tension and release tension and release. So what of the tension part? Well there are varying degrees. To have tension one must overturn the expected chord, but this can be dome by one note two or three or even four. Whtat I came to realise is that any tension has its beauty IF it is resolved contextually.

I have also found that the dimininished scale has its own logic. It is unsettled, unless it returns to the root (no perfect fifth). However, it can reproduce it's own structure on every second note - so as to potentiallly nominate that note as new root. This gives it a kaelidoscopic patternating quality, a kind of daisy chaining property that is not possessed by the major or minor scales but is also present in the whole tone scale. Certain harmonic structures can be based on this rather that the conventional modal thinking. So sometimes the diminished functions to imply another, non modal context. Think Debussy.

Obviously it also has a role as a tension in modal harmony as cited above in various manners

Win 11 64, Asus Rog Strix z390 mobo, 64 gig RAM, 8700k