Everything should be awesome.
This is the mantra Blizzard uses when designing talents and I think it is a good one for building any sort of game. When you have a choice in a game that essentially boils down to being good or being bad players will choose to be good (mostly) and the choice ceases to be one. This has been my essential guideline in redoing a bunch of the units in the modern era in my CiV mod. In the early game it is fairly clear that when you first get Horsemen or Swordsmen they are very strong and your opponents will fear them. In the later game the options are much more widespread though and it is much more difficult. I must make sure that bombers, fighter planes, submarines, antiair guns, artillery, surface ships, missiles, tanks and basic infantry *all* have this characteristic that as soon as you get them you immediately want to build a bunch of them and go crush some dreams. I don't entirely understand why I love the phrase 'crush some dreams' so much but it really epitomizes to me that moment when everything looks to be going well in the game and then you see the one thing you really, really didn't want to see and you think 'Oh no, not like this'. Your dreams get crushed! I want to deliver that moment to my opponents and I want every unit that can be built to have the feeling that if you get there before the other guy you are going to make these units and crush his dreams.
It is challenging to do this because the units interact in a lot of complex ways. Submarines should be good at wrecking ships, ships should be good at controlling waterways for land units to cross and to bombard coastal positions, bombers should be good at smashing cities, fighters and antiair guns should wreck bombers, etc. There are so many different interactions that it is easy to have just one small thing be wrong and suddenly whole classes of units are utterly rubbish. In the base game the standard modern infantry unit (Mechanized Infantry) has 4 speed just like a tank. The trouble is that once tanks aren't faster they are useless since they lack many things an infantry has. This means that no one should build tanks, so anti tank guns and helicopters are useless. Artillery are overpowered and are the best solution to breaking down cities so bombers are junk and when bombers are junk there is no reason to build fighters or antiair guns and no use for carriers or other heavy ships to support the bombers. Even a small miscalculation in this giant mess of dependencies can make nearly all units not worth building and reduce a very complex rock/paper/scissors type matchup to a simple game where everyone builds artillery and infantry and nothing else.
I think it is the fine balance of making things awesome right away but counterable down the road that is so hard to find. Submarines should be great as soon as you get them and you should have fun sinking lots of ships (or getting your ships sunk, depending!) but there needs to be an answer. That answer needs to be decent in and of itself though, otherwise the game gets too swingy depending on if you have the rock to the opponent's scissors. The trouble with this sort of stuff is it requires tons of testing to get right. It is really hard to figure out just how high a bomber's attack strength needs to be to make it useful - 40 and it is useless, 60 and it is okay, 80 and it is unstoppable. You can't figure all this out from looking at a chunk of code, either, you have to actually play it out and see how the game works. That is the sort of testing that is very hard to fit in with rushed production deadlines of course which is why CiV shipped initially with such incredibly terrible balance in the modern era. Dozens of units and only two of them were really worth making... such a waste. I guess they needed to release it when it was done and not before.