Best way to make part of a mesh transparent?

Actually, I was thinking about the effect right at 9:05 in the video, where the octopus enters the hole in the wall, but I think it is pretty much the same effect as what you mentioned.