Thinking of this in terms of pure "evidence" is the wrong approach to this. What convinces people, even scientists and doctors, moves beyond simple evidence and into what we might call "worldview." Evidence by itself is rarely able to convince someone (again, even scientists and doctors) that their worldview is fundamentally flawed, because they usually see it as being supported from multiple sources. (As a favorite example from the present: would a singular piece of "evidence" be likely to convince a political partisan today of something they fundamentally don't think is true? Answer: obviously not.)

In terms of the waterborn nature of cholera, the resistance was from people who believed not only that it was caused by miasmas, but by poverty, filth, and bad values. What evidence supports this? The very obvious observation that cholera hits poor, dirty, and "immoral" communities hardest (as seen from the perspective of a middle-class 19th-century physician).

Now today we would say, oh, hey, guys, it's not the poverty that's causing the cholera, or the morals, it's the fact that the sanitation in such communities is bad and under-attended to. And that's what Snow was trying to say.

But it took basically a generation for that message to be turned into reality and practice. Why? In part because the other view was not just a simple view about medicine but a complex one about society. We might think these kinds of "confluences" of social and medical (or scientific) views is unusual but it is exceptionally common even today. Public health in particular is rife with such things. Is it the moral fault of the addict that they have become addicted, or a medical condition? Should we have needle-distribution programs to stem public health crises or not? How should we think about HIV and AIDS prevention when it comes to sexuality? All of these issues are wrapped up in far more than just medical science, because public health is always wrapped up in the public, and we regard the public with a very wide view.

As for why it took a generation, as Max Planck famously put it:

A new scientific truth does not triumph by convincing its opponents and making them see the light, but rather because its opponents eventually die, and a new generation grows up that is familiar with it.

Though one can cite some examples where this is not the case, in the case of cholera, this definitely holds. It took the rising to power of a new generation of public health researchers and reformers to overcome both the medical bias and class bias of the previous generation.

Separately, we might also note that there were many reasons to be found if you wanted to doubt the simple connection between the Broad Street Pump and the cholera epidemic. The epidemic itself was already declining when Snow removed the handle, for example. Today we could say, OK, sure, but it might have prevented its recurrence. Fair enough. We know today John Snow was right. But at the time there was ample room for uncertainty and evidentiary bias, and Snow did not know exactly what caused cholera (the identification of the cholera baccilus would take another decade). I say this not to put down Snow, but only to challenge the idea that Snow had presented some kind of iron-clad argument or evidentiary demonstration of his truth.

On the changing views towards cholera, Charles Rosenberg's The Cholera Years still is very valuable as an account of how American public health advocates viewed its etiology during this time, and why it took so long to change. On how scientists and others do and don't change their minds in the face of evidence, the classic starting point is Thomas Kuhn's The Structure of Scientific Revolutions, from which the famous Planck quote is taken.