What is Steam Cloud exactly?

It pretty much means your game configuration, saves and customisations will be saved on a distributed "Cloud Server" and will be available to you - regardless where you log in.  In Valve terms:

The Steam Cloud will "just work," meaning any user changes to their game options will propagate to the Cloud by default. Upon logging into Steam from another PC, these settings will be brought down from the Cloud and automatically leveraged by the game. Any configuration changes on this second machine are then synced to the Cloud for future sessions.  Source

Steam Cloud support will ship with Valve's Left 4 Dead demo later this week and the full game on November 18.