Steam/Steamworks CPU demapnds are so small that its not noticable.
Well, at the start ( boot time ), in my case, it use fully one core and around 130mb ram... but once it is started, it is around 1-2% of a core ( i have 8 core at 2.66ghz ) and between 6-30 mb ram... there was a patch a few month ago who have greatly reduce the CPU use and ram use...
I am a steam user but not a big fan... mainly due to some incompatibility with my hardware ( steam don't like hardware who use ram from cache his bios )... at boot time, i end up 1 time on 2 with a BSOD... but once booted correctly, it is very stable...
In fact, there is a positive side with sins using Steam... Sins/Ent/Dip was working very good on Linux ( without modified exe, crash limit is 4gb in place of the 2gb on windows OS ), multiplayer included... but Impulse have never work on Linux... well, the good side is that Steam work perfectly on Linux ( in my case, better that on windows due to my BSOD problem )... no more complicated work with update on windows via impulse and copy everything on linux...
As for offline mode, steam is not so perfect... sometime, it will not start a game until you are able to be again online... by example, these last Sunday, i have try to start medieval II total war and a pop-up windows have appear with "Steam error : This game is currently unavailable. Please try again at another time"... well, after waiting a few hours without result, i have end up to install the non steam CD version... as today, it is again ok with steam but their server are not reliable at 100%...
So, steam is not perfect but it is certainly better that the few other alternative... and some of the disadvantage are largely compensate with the extra features...
As for Impulse, i have always hate the "US only" thing for a lot of game... by example, i have discover "dragon age origin" on Impulse but it was a "US only" game... so, i have end-up buying it on Steam... for people outside US, in a lot of case, Steam is the only choice, there is no alternative at all...