Baking powder consists of two main components: baking soda (sodium bicarbonate) and an acidic component – usually a diphosphate such as disodium diphosphate, more rarely an acid salt like cream of tartar. It usually also contains an anti-caking agent such as starch, which keeps the two powders separate and dry so they don't already react with each other during storage.
As soon as the batter is mixed, the baking soda and acid dissolve in the moisture and react with each other. This first produces carbonic acid – but it's unstable and immediately breaks down further into water and carbon dioxide (CO₂). In the oven, the heat additionally speeds up the reaction and causes the CO₂ already formed to expand.
Because the batter becomes firm at the same time during baking, these gas bubbles stay trapped instead of simply escaping. The result: a light, porous structure that we perceive as a "fluffy" cake when we cut into it.