I think it will somewhat depend on what note the show ends on. A really bad ending can sour rewatch potential. It can even turn away prospective new watchers because somewhere they will hear/read not to bother. And starting a 15 Seasons show can be a commitment.
Also, the content of discussion will likely change. I mean, we occassionally still talk about 5.22 and it still seems to be hated/loved by whomever hated/loved it. It`s just that everyone knows the ep. Once everyone knows how SPN concludes, the question of "was it worth it?" can be answered individually.
I thought he was a great character upon introduction. His Season 4 scenes are some of the best. For your description, I would nominate Jackie-poo, the endless tease of "oooh, will he go darkside...just kidding, he is an uberpure, saintly baby who will never grow" with deus ex machina powers.
But I do believe Cas will get a big heroic death scene where he sacrifices himself. Probably not for the Winchesters but for sweet little Saint Jack who can do no wrong (even when he does). Misha all but spoiled that.