I have personally led, witnessed and supervised waterboarding of hundreds of people. It has been reported that both the Army and Navy SERE school's interrogation manuals were used to form the interrogation techniques employed by the Army and the CIA for its terror suspects. What is less frequently reported is that our training was designed to show how an evil totalitarian enemy would use torture at the slightest whim.
Having been subjected to this technique, I can say: It is risky but not entirely dangerous when applied in training for a very short period. However, when performed on an unsuspecting prisoner, waterboarding is a torture technique - without a doubt. There is no way to sugarcoat it.
In the media, waterboarding is called "simulated drowning," but that's a misnomer. It does not simulate drowning, as the lungs are actually filling with water. There is no way to simulate that. The victim is drowning.
Unless you have been strapped down to the board, have endured the agonizing feeling of the water overpowering your gag reflex, and then feel your throat open and allow pint after pint of water to involuntarily fill your lungs, you will not know the meaning of the word.
How much of this the victim is to endure depends on the desired result (in the form of answers to questions shouted into the victim's face) and the obstinacy of the subject. A team doctor watches the quantity of water that is ingested and for the physiological signs that show when the drowning effect goes from painful psychological experience, to horrific suffocating punishment to the final death spiral.
Waterboarding is slow-motion suffocation with enough time to contemplate the inevitability of blackout and expiration. Usually the person goes into hysterics on the board. For the uninitiated, it is horrifying to watch. If it goes wrong, it can lead straight to terminal hypoxia - meaning, the loss of all oxygen to the cells.
UPDATE (November 14, 2007): Some doubts have been raised about Nance's reliability and whether waterboarding actually involves water filling the lungs (as opposed to triggering the gag reflex and some drops of water entering the lungs), though it's clear that the psychological effects are extremely strong, with the average CIA Officer able to withstand 18 seconds before begging for it to end. For the doubts on Nance and the details of waterboarding, see the comments on these posts at Positive Liberty and Captain's Quarters.
UPDATE (December 31, 2007): Here's a guy who experimented with waterboarding techniques on himself, and vividly explains the results.