Summary: Baking powder produces a puffier cookie; baking soda a flatter cookie.
Some cookies call for baking soda, some for baking powder, many for both. They do different things and produce different cookies.
Baking soda reacts with the acids in ingredients like buttermilk, sour cream, yogurt, and molasses to produce carbon dioxide, which makes the cookie puff up. If you use too much baking soda, the cookie will taste slightly metallic.
Baking powder is about 25% baking soda, mixed with dry acid and cornstarch. The cornstarch prevents the baking soda and acid from reacting while they're dry, but when water is added, the two come in contact and produce carbon dioxide, which makes the cookie puff. Baking powder works in dough that doesn't naturally have enough acid to make baking soda work.
(Double-acting baking powder has two kinds of acid, one that starts reacting when you add the powder to the liquid mixture, and one that starts reacting in the oven. The double puff-up produces – not surprisingly – twice the puff.)
Substituting baking powder for baking soda makes a cookie puffier. Observe:
This is the same recipe. On the left, I used only baking powder. On the right, I used only baking soda.
Both are delicious, chewy cookies, but the baking powder version puffed up more and stayed puffed after baking. The baking soda version puffed slightly, but collapsed a little after coming out of the oven, making a flatter, more wrinkled-looking cookie.