It has a slightly tart taste, like plain yogurt or sour cream. It's great in mashed potatoes -- you really don't need any butter, just enough buttermilk to moisten the potatoes enough to mash them, plus some salt and pepper. Much more healthful way of making mashed potatoes.
Scotch is right about the taste. It is definitely tart. As for drinking, that's more an individual thing. My father used to drink a big glass of it before bed every night. He crushed up several saltine crackers in it before he drank it. I don't care for it as a drink, but it's wonderful for cooking.