Cats and Dogs is my favourite EP after Seasons, so I definitely recommend it if you like the idea of having pets in your game. The Create A Pet tool is very versatile and well done, and the pets themselves are very cute and full of character. I am glad they are not controllable either since this makes them feel more 'real' and also actually less burdensome for me. You can often tell what they want from their thought bubbles and asking them What's Wrong? anyway. I don't find pets too demanding/needy, but dogs are naturally more so since they like walks, play, and require some training. That's part of the fun though and you can largely decide how needy your pet is based on their traits e.g. a clever pet will learn quickly, a lazy pet won't need much exercise, etc. and a pet with the independent trait just wants food and a place to sleep, while an affectionate pet will follow your sims and want more cuddles/attention. I will often tailor the number and type of pets to a household just like in real life e.g. a busy household may prefer an independent cat, while a work from home sim may have more time to take the dog/s out.
Beyond pets and the self-employed vet career option, C&Ds also comes with some really lovely CAS and build objects, and my favourite world so far, Brindleton Bay. BB is really beautiful with lots of open space and a seaside town feel to it. It does rain a lot due to the sort of area it is based on, but not all the time and I wouldn't let this put you off. My main household live there and are very happy. If it's just raining, my sim might decide it's okay for their dog to have a rest for the day or go out for a late evening walk, and if it's raining again the next day (but not a storm) then my sim might then suck it up and take their dog out for a short walk. They will feel a little uncomfortably wet, but this only lasts a few hours. All responsible dog owners should be prepared to brave the elements anyway (as a dog owner living in the UK, I've certainly had to). There are strays around and yes some of them are sick. It's kind of sad and I hope this changes, but it's not a huge deal for me. There isn't an overwhelming number in my game, or at least not where my sims frequent - just a few stray cats prowling around outside their home lot which is near a park with the Cat Hangout lot trait. You shouldn't get strays in any of the other residential worlds unless you choose one of the pet lot traits. I've read there are some in Selvadorada though, but my sims haven't been there so I can't confirm this.
I'm not experiencing any major or game breaking C&Ds bugs right now. Sometimes my cats prompt my sims to pet them, which can be annoying when I'm trying to get them to do something else, but they have the Affectionate trait so it may be due to that. I just cancel it anyway so it's only a minor annoyance. I think there was an issue with training/scolding dogs for unwanted behaviours, but I'm not sure as I haven't experienced this myself.
Anyway, I hope some of that helps? You might want to read more about it on the SimsVIP guides and other reviews if you haven't already, but if it sounds fun to you then I say go for it! C&Ds is a great EP. :)