Spying is amazing in Civ6. Of course it has the caveat that applies to all aspects of the game in that the AI is super dumb. It is not that difficult to protect yourself because they will only ever target 1-2 biggest cities. You can also game them pretty well since you can go level up in backwaters and then send your master spies into the important spots.
My biggest gripe is that like a few other sections of the game it seems like this is a section that is waiting for an expansion to get "filled out". I'd prefer if the promotions weren't quite so random and if there were promotion tracks similar to units. So instead of having your counterspy earn his first promotion and get the choice of Disguise, Driver, and Cat Burglar have the game understand that it earned the promotion in a friendly city and offer the Quartermaster or Seduction promotion instead. It kind of sucks to have to take a Master Spy off of defense because it was only ever offered offensive skills.
It would also be cool to have second level effects. What if Quartermaster had Skunkworks as a second level effect that earns you an amount of science depending on the level of foreign spy that your counterspies thwart. Maybe Seduction gives you Double Agent which automatically gives you the Gain Sources mission against a civilization who's spy you thwarted. You could do similar things with offensive spies by making a Saboteur, Thief, and Guerrilla promotion track. You could then move all the "support" promotions to separate tracks (Disguise versus Driver type stuff) or you could create Espionage Support units similar to ground units -- this could truly let you build your shadow government and play Cold War type games.