The spy mechanic is actually very useful late game. The big factions have the funds to keep a lot of generals on payroll, and they also tend to lose a lot of them in big fights, so turnover is high, which makes the likelihood of your agents getting picked up fairly high as well. Since there's less chance of the faction getting wiped out, your agents usually have time to rise in the ranks and get into useful positions. Visibility is great, but the options to kill army movement, open city gates, and reduce enemy satisfaction across the board are both cheap in terms of resources and incredibly effective.