you will never see a full STEAM store on PS3. PSN would be direct competition to it so Sony would never allow it. Plus STEAM is PC gaming, to optimize those games to work on PS3 requires porting you can't just run them on PS3 without rethinking the code to work with the hardware.
The new Counter Strike is PS3 lead platform and all the STEAM goodness will be in it. All Valve can do is make it so your PS3 and STEAM can talk to each other and share some similar content.
Console gamers aren't the audience for TF2, though you would probably get a small dedicated group of people who love and enjoy the game, the chances of it being big enough to take a bite out of BF3 or MW3 audience is almost zero.
STEAM wouldn't be the comparative program to Netflix on PS3 it would be OnLive. Which could in theory work on PS3 no problem. Until we hit the day of one console STEAM will be stuck with add-on functions for a larger presence like PSN. But it is a good step forward for consumers.