To say it is good is sort of redundant, I believe, as all (with perhaps the exception of one Simon's Quest, but I do not think that we are discussing that one in particular here) classicvanias are generally exemplars of good and careful game design (i.e. they are rather difficult, make no mistake, but they are also rather rarely punishing, at least not too much so).
How does it fare in comparison to other classicvanias, though? Note, this is strictly my perspective on things. Your mileage may vary.
Graphically, it is the series' first and last venture into gothic realism (earlier games simply couldn't afford the realism part, whereas the latter didn't bother to). This has, as other posters in the thread have pointed out, resulted in a dark, almost horror-like atmosphere (and this was, it seems to me, completely intentional, given the attention to detail that was present in the graphics). Music seems to have been designed to complement this, and it was done so rather masterfully (though this was, perhaps, result of the limitation SNES posed to composers), as the music simultaneously maintains its atmospheric qualities while still being a memorable chiptune-like.
The result of this synthesis of graphics and music was a highly developed atmosphere (a bit redundant of me, certainly there were not many other things such work could result in?). In relation to the rest of series, it presented the qualitatively highest development of the classicvania (NB: By this, I mean the first four games, rather than what followed after SCV4) formula of atmosphere. It seems to me that it maintained all the intentional B-horror flick cheesiness that its predecessors possessed while simultaneously adding elements of actual horror (within reason, of course, as you simply cannot have Kubrick on the SNES).
Aesthetically, therefore, it seems to be the best of the classicvanias. However, we mustn't forget that there exists one key aesthetical component of video games that I haven't mentioned: Gameplay.
Therein lies SCV4's largest successes and its largest failings.
I think that there exists a general consensus among CV fans that SCV4 is the easiest of all classicvanias. To me it seems that this is born of two elements: How the game introduces you to its functioning and how the game plays in general.
The way the game starts (difficulty wise) is perfect: Rather than being than requiring that you spill blood and sweat getting used to how it plays, the game allows you to get adjusted to it (this trait is reminiscent of other SNES games, namely Mega Man X and Super Metroid, indicating it was part - perhaps even the genesis - of this shift in game making philosophy). In my opinion (though quite possibly solely in it), it allows this lack of difficulty to go on for a bit too long (maybe even longer than intended), though it certainly does pick it up later. Overall, I'd argue this was a positive development.
Another positive development is the controls. Gone are the clunky controls and death-bringing jumps, and instead they are replaced with pinpoint controls that are almost universally praised. No further comments seem necessary here.
There does exist, however, an element that creates rather large imbalance in the game. The whip.
Allow me to make myself clear here: I do not dislike the whip mechanics as such. They are quite the interesting mechanics and Konami obviously put quite a bit of effort into them. I dislike the whip mechanics in relation to other parts of gameplay.
Namely, the subweapons. There seems to be a general trend (from what I've noticed, anyway, by playing the game and seeing others let's play it) that you rarely, if ever, have to use the subweapons. This is because the whip is simply too versatile.
Think about it: It can attack in any direction you want it to (with more than decent range!), it can block attacks, it deals more damage than most subweapons, it helps you get about the levels, if I recall correctly it can stun enemies, you can mess around with it if you're bored... I mean, what doesn't the whip do?
Let's compare the whip to the whip of previous games: There, the whip had pretty short range, limited moveset (you could only attack forwards), it couldn't block anything (unless you count attacking the projectiles), etc. It is exactly this sort of limitation that rendered the subweapons meaningful. I mean sure, it still was your primary weapon, but a rather flawed one.
In SCV4 I'd go as far as to argue that the whip simply deals far too much damage, so much in fact that you can tank quite a few of the bosses (admittedly not all of them, and the tankable ones generally limit themselves to the first couple of levels, but since bosses are supposed to be the test of all you learned of the gameplay, this isn't necessarily good design). Essentially, the whip is Mary Sue levels of overpowered.
How does this affect the gameplay in general? It does certainly make it a lot easier, however it didn't make it any less enjoyable for me - and that is the most important factor, I suppose.
Another slightly negative thing I can think of is the removal of alternate paths and characters from the previous game. The game did substitute with a metric ton of levels, though, but that does little to soothe the lost replayability. However, due to how fun the game is, it actually manages to compensate that in another form, so I don't think any less of it for that.
To summarize, the game presents two steps forward and one back from the previous games. It is the best of the classicvanias (and here I mean classicvanias including the games that came after SCV4), without a single doubt in my mind. Is it the best Castlevania ever? I can only offer doubt there. The metroidvania shift brought some quality games with it, however those games share a different core aesthetic than classicvanias (though they have more in common with each other than with the latest shift in the series) making them rather difficult to compare.
Is the game overrated? Slightly, I think (quite possibly due to AVGN's review, and its overall greater accessibility to your average player - and there I include myself - among other things), but not too much.
So yeah a good game. Got a little carried away writing a post about it, though, so sorry about that.