Steam doesn't really have a lot to do with mods, they've clarified that the mod browser doesn't work through Steam, and it's a separate beast. Given that the game has been shipped with Steamworks, there shouldn't be a lot of trouble with the mods and Steam (don't know if there are any problems with CIV for example), the only thing I can think of right now would be that if you install mods manually you're probably going to have to go to the Steamapps directory and find the game there, which can be a bit confusing, but that's not a big deal. I'm not terribly familiar with modding on Civ, but as long as the mods don't touch any executables (in Oblivion for example, I think there were some problems with some mods that required a separate executable), I don't think there should be any incovenience.