NOTE (July 28, 2012): In OS X Mountain Lion, you can no longer open hidden folders normally (by double-clicking). However, you can still open hidden folders by right-clicking on them, and clicking "Open".
You can force Finder to show hidden files by editing a Finder property through Terminal. Open Terminal (located in /Applications/Utilities) and enter the following line:
defaults write com.apple.Finder AppleShowAllFiles YES
To stop showing hidden files, enter:
defaults write com.apple.Finder AppleShowAllFiles NO
Entering these lines into Terminal will not have any immediate effects. You will have to relaunch Finder for any change to apply. You can relaunch Finder by logging off. Alternatively, you can relaunch Finder through the "Force Quit Applications" window, which you can open with the keyboard shortcut Command+Option+Esc (known as Alt+Windows+Esc on Windows keyboards).