Well, as it will be running on Steam, theoretically any game that is sold on Steam could be bought, downloaded, and played on the Steam box.
That's theoretically, of course. Since the Steam Box will be using Linux, things get trickier. You also have to take system requirements into account, as newer games will require more horsepower and, thus, may struggle or not run on the Steam Box. That said, once Valve issues developmental units, there's no doubt a lot of developers will actually have an incentive that they didn't have before to port their games over to Linux, so I really don't think the library will be a problem.
It will be interesting to see if this thing has a disk drive at all or if it will be entirely download-based. I'm guessing on the latter, but you never know.
The one thing this could do is really change the console game. For too long we've gotten screwed by publishers setting games at sixty bucks and rarely putting them on sale. Steam is different. They have two major sales a year (Summer around the 4th and Winter around Christmas) with smaller sales in between (like horror games on sale near Halloween). These sales typically include massive savings, such as getting relatively new games for 50% off and slightly older games for much cheaper than that, with older games being sold for a pittance. Contrast that to the digital stores for Microsoft and Sony that rarely offer significant sales and it's easy to see why people might opt to switch to the Steam Box, provided Valve can convince enough developers to port their titles to Linux.
And let's not forget that the Steam Box will obviously use Steam, meaning that if you already have a Steam account and games already, any games that meet the Steam Box specs will run on it. So for those of us that have, say, over 200 games, we buy a console with a fully loaded library.
Also, if this ships with Half-Life 3...game over, man.