The ashes of the heifer, mixed with other substances and dissolved in water, were sprinkled on a person who was ritually impure due to contact with the dead. Though this ritual purified him, it contaminated the person who carried it out, who was unclean until sunset.
The Mishnaic tractate Parah sets out the laws and procedures. The Biblical passage is read this Shabbat, as a person who had not been purified by the red heifer ritual could not take part in the Passover sacrifices.
The paradox that the red heifer causes both purity and impurity has been explained homiletically; one view is that the person who desires to help others must be prepared to step into the mud himself.
Halachically there is actually no paradox, as two different levels of impurity are involved and the person who administers the ritual contracts a lower level of impurity and is more easily purified.