Will Schenk has an interesting post entitled Emacs Tramp tricks. It’s not really so much about obscure things you can do with Tramp as an exposition of how to do the normal things you use Tramp for.
The most unusual use case is connecting to a local or remote Docker image. You need a little bit of configuration in your init.el but after that you connect to the image and edit files in it in the usual way.
The big thing about Tramp is that you can use it to edit files on a remote machine with Emacs without having to have a copy of Emacs on the remote machine. That can be a real win when dealing with servers that may not any editor other than Vim—or, horrors, Nano—installed. You can still edit files or even open a shell on that machine without having to leave Emacs.
If you don’t regularly use Tramp, Schenk’s post will serve as a reminder of how to do things. Tramp really is one of Emacs’ most useful but underappreciated tools.