Iirc, when you try to connect to a device through the SSH-Remote extension it downloads whatever it needs in the device and you connect to it. I have not connected to a Pi yet, but in any other cloud based or local linux machine it has worked flawlessly so I don't think it will be different. The terminal window also opens a ssh'd terminal, so you can do whatever you do locally, through the app.

I did a quick search and the first link stated "All you have to do is to install the Remote-SSH extension on VS Code" so I guess that yeah, it works just like any other linux device. As an addendum, the folder browser UI works as a sftp drag-and-drop window, you can drop local files and they will get transferred into the SSH'd device.