It is more loud than actually scary. It is very bouncy and jerky, like riding a jeep over very rough roads fast.
I agree with this take. The kids in our family have always been fine riding it, but it's not their favorite ride. It's actually a few adutls who cover their faces. The older adults sometimes skip it too, if they dont' feel like being bounced around. It's doesn't spin or have any real drops, but it does make a few quick turns in the drak and bounces you around. Again, the issue is not motion sickness, it's age. Some members of my family are prone to muscle soreness.
Hopefully I'm not giving away too much when I say the ride uses loud noises, darkenss, and strobe effects. I honestly wish WDW would lower the volume on several attractions. I personally don't find it scary, but others have posted different feelings. As other posters have said, if you close your eyes, then most of the effect, and all of the visuals goes away. If you also plug your ears, then you've got little more than bumpy jeep ride. (only a little more bumpy/speedy than the safari ride.)
It's well worth trying at least once.
It's not my all time favorite ride, but we ride it every visit. The pre-preshow waiting room - which you miss if you use FP or it's slow, is actually pretty neat (not the movie preshow, the room that looks like a museum.) It's probably my favorite part.
If you are very reluctant, you can try going to just this and the preshow movie - then skip the ride vehicle part. At least that will give you a good sense of what the attraction is like. Probably nobody will assume you are exiting because you are chicken - they will just think you have some kind of injury.