Firstly you have to consider how enticing lead is as an industrial material. It is one of the easiest to mine and produce, and was very probably the first metal smelted by humans, paving the way for the bronze, iron, and industrial ages that followed. Lead is comparatively cheap, and it is easy to cast into various forms, so it is very attractive to use it in things like pipes.

In a historical sense, many things that are hazardous to human health but not dramatically acutely so in all cases (even radiation) took a long time for people to fully appreciate the dangers of, and for that to result in a curtailment of use. For lead pipes used for moving drinking water there are several phenomena which make them safer in normal use than you might naively imagine them to be. Under the right conditions lead pipes will form a "passivation layer" of minerals which protect the metallic lead from corroding into the water being carried. These are usually made up of lead oxides and sulfates or phosphates, and the formation of this layer is favored in conditions of high pH (alkalinity). This passivation layer massively reduces the amount of free lead that ends up in the running water, protecting anyone using the water from being poisoned by it. Many modern cities still have significant amounts of lead water pipes or fittings put in place from the 20th century or earlier and lead levels in the water are kept under control by the use of additives such as phosphates and the maintenance of appropriate pH and chlorine levels to maintain and encourage this passivation layer (though, of course, occasionally incompetence or negligence can come into play here).

Coincidentally, water from distant mountain sources brought into cities via aqueducts is almost always hard water, having a high-pH (alkalinity) and dissolved sulfates. Precisely the conditions which encourage formation and maintenance of passivation layers inside the lead plumbing the water would make its way through before being used. In this way the amount of lead exposure to the population would have been vastly reduced compared to what it might have been. Exactly how much this protected, say, the Romans from lead poisoning in their drinking water is hard to say without more detailed analysis than has been done, but it certainly would have helped considerably.

More importantly, especially in pre-modern times it was much more difficult to conclusively connect the deleterious effects of lead poisoning with their proper cause, especially with so many other potential explanations at hand. And while it's easy to say that even the Romans "knew" of the dangers of lead exposure it's more accurate to say that some tried to warn about the potential danger, though it was not widely or universally understood to be such a risk. For example, Vitruvius (an engineer and architect) in the first century BCE warned of the dangers of lead pipes and lead mining on health, but this didn't have much of an impact on the practice of using lead for pipes. And even decades later Romans were routinely doing things like boiling grape syrup in lead pots, which produces the sweetener lead acetate. These sorts of things would have greatly increased the likelihood of lead poisoning in individuals, but fortunately they were not universally common. However, one of the points worth noting here is that if certain individuals would intentionally consume lead acetate in significant quantities on a regular basis that does illustrate how challenging it was to conclusively link the health effects to lead exposure in pre-modern times. For the Romans in particular the data that we do have seem to indicate that the biological uptake of lead tended to be less than modern humans living in industrialized countries.

There's a lot more to be said here on the subject though, especially about the 19th and 20th centuries. Industrial interests pushed lead-based products because they were inexpensive and effective, and fought against restrictions on their use. Lead paint was so popular for so long because it was cheap and it worked well. And even though it had been understood in scientific circles to be potentially hazardous to health going back centuries and pretty much universally by the turn of the 20th century it took a tremendous amount of time for it to actually be significantly regulated and banned in common use. It was not until the 1970s that it was banned in the US and Canada, for example. A similar example exists in the use of the gasoline additive tetraethyl-lead, which was a cheap way to boost octane (which improved engine handling in early automobiles), though at the cost of widescale public exposure to lead in engine emissions. Introduced in the 1920s it remained popular until being phased out in most countries starting in the 1970s, though even today it is still in use in some areas and still present in aviation gas used in small propeller driven piston-engined aircraft.