I'm new here, so feedback is appreciated...
Hat tip to tblue for linking the comprehensive article.
To me, the most convincing argument is that after Moses had scattered it in the brook, it might have been inappropriate to allow them to drink from the water, as it contained their sin. The continuation of "וישק" "and he made them drink it" is not exact, rather he allowed them to still drink from their water source (this translation can also fit with other times the word is used through the Bible). This approach is suggested by a number of commentaries. This would also help explain its omission in Deuteronomy 9:21 due to being insignificant to the retelling of the story.
According to this approach, Moses allowed them to drink (i.e. for their own good), and it was not at all an act of atonement.
Some Targums and commentaries (see the article linked above) describe the drinking akin to Sotah (Numbers 5), and that upon drinking, either those who were guilty died, or some sign arose on them that had them "marked for killing" by the Levites after the fact (verse 26 here), this would make for a significant omission in Deuteronomy, but explain how the Levites knew exactly who to target.
According to this, Moses forced them to drink in order that he should know who was guilty and who innocent, and this would not have served as an act of atonement.
A few other approaches can be found in the commentators, listed below with only one source for each opinion (even if there are many others that support it).
Henry: "His mixing this powder with their drink signified to them that the curse they had thereby brought upon themselves would mingle itself with all their enjoyments, and embitter them; it would enter into their bowels like water, and like oil into their bones."
This would help them atone by reminding them of their sin, and he later compares these waters to the bitter waters of Marah.
Nahmanides essentially renders it as contempt for the calf such that its end would be human waste.
This would help them atone, as it is some form of degrading their sins.
There are a few other explanations of the drinking, but this should be enough to address the question.