Baking soda has only one ingredient: sodium bicarbonate. Sodium bicarbonate is a base that reacts when it comes into contact with acids, like buttermilk, yogurt or vinegar. This reaction produces carbon dioxide (CO2) in the form of bubbles, like a liquid foam (think of the grade school experiments involving fake volcanoes, vinegar and baking soda). When making baked goods, the process is called "chemical leavening," because the trapped CO2 gas makes the dough or batter rise.
But when baking soda comes into contact with an acid, it pretty much reacts immediately. And that's a problem.
For many baking recipes, you want an extended reaction, so that the rising doesn't take place all at once.
Baking powder addresses this problem because it is "double acting" – it has different ingredients that create CO2 gas at different stages of the baking process.
All baking powders contain sodium bicarbonate (just like baking soda). But baking powder also contains two acids. One of these acids is called monocalcium phosphate. Monocalcium phosphate doesn't react with the sodium bicarbonate while it's dry. But as soon as the baking powder is stirred into a wet dough or batter, the two ingredients begin to react, releasing bubbles of CO2 and causing chemical leavening.
But to extend the chemical leavening process, baking powder also contains a second acid, either sodium acid pyrophosphate or sodium aluminum sulfate. Neither of these acids react with sodium bicarbonate until they are both: A) wet (i.e., stirred into the batter) and B) hot.
In other words, sodium acid pyrophosphate and sodium aluminum sulfate won't start reacting with the sodium bicarbonate until after you've put the dough or batter in the oven. This means that the batter rises for a longer period of time, making lots of bubbles (and a fluffier cake, muffin, or whatever).
At some point during the baking process, the liquid foam of rising batter becomes a solid foam, because the batter "sets." This is one reason eggs are so common in baking recipes.
The proteins in eggs become irreversibly denatured when exposed to heat (i.e., the proteins unfold and cannot refold into their original shape). These altered egg proteins essentially give the liquid foam a solid structure, allowing it to hold its shape.
By comparison, in recipes that use yeast as a leavening agent, the gluten proteins in flour serve a role similar to that of egg proteins in most chemically-leavened baked goods in the sense that they help to trap air bubbles until the dough sets. (That's why bread flour has more protein than cake flour.)