Firstly, not all modders will chose to use Steam to distribute mods. Limiting yourself to just Steam will mean you miss out on a lot of cool mods.
Secondly, harder, more complex mods will still need external tools (like Wrye Bash) to work.
Thirdly, you have a few mods, and load order will start to matter. Again, you'll need a mod manager to sort load order out.
It's not an easy way to install mods. It's just another distribution platform.
I better not need steam to run the CS.
It's been confirmed Steam is required in order to run and use the Creation Kit.