...but it's not infallible. Take the following two shots, taken moments apart.
For the first shot, I've let the camera do its thing. It's in Program-Auto mode, and the flash is mounted on-camera, and set to E-TTL mode. I think even the ISO is set to auto-select. Metered on the Birthday Girl, and made the picture.
As you can see, the exposure is pretty decent. The camera selected a fairly wide aperture of f/4, because it was pretty dark in the room, and aimed for a shutter speed of 1/60, which is pretty standard for the Canon system. ISO ended up at 800, which is good enough. The flash did fire, but, it was pretty low; basically just enough to add a little fill, if I had aimed it straight-on, which I never do.
So, not a bad picture, overall. It's a good snapshot of a very happy event. Unfortunately, I don't like the dark shadows on Gram's face, and there's no detail in the curtains. Technically, it's a rather imperfect shot.
I've got a pretty decent flash that I tote around with me. I'm using Canon's flagship strobe, the 580EX II, and it's got some sauce. I knew that even with a diffuser and at an angle, this flash is more than powerful enough to light a scene of this size without breaking a sweat. With that in mind, I kicked the camera and flash into Manual.
I metered, once again, on the birthday girl, but deliberately underexposed the scene by around four full stops. To compensate for that, I dialed the flash to 1/32 power. I aimed for the same 1/60 second exposure, but stopped down the aperture to adjust the exposure, and to increase my depth-of-field.
Better, no?
If the flash was overpowering, I would have adjusted the power on the flash. If the flash wasn't powerful enough, I could have opened the aperture a bit, and decreased the shutter to compensate to keep the same ambient exposure.
Overall, it's good to keep in mind that brilliant minds have designed your camera, but none of them are looking at your screen after your shot. Don't be afraid to slip the bonds of auto-exposure every now and then.