set theory - What is the idea behind stationary sets?

One answer to your question about intuition is simply that stationary sets arise very naturally once you begin to think of the natural measure surrounding club sets. The stationary sets are simply those that have positive outer measure with respect to the club filter. So if you care about club sets being large, then the concept of stationary sets arises naturally.

More specifically, if you consider the collection of sets that either contain or omit a club, then you have a natural two-valued measure (measure 1 means containing a club, measure 0 means omitting a club). The stationary sets are precisely the sets that do not have measure 0, and this is the same as having outer measure 1.

Many uses of the club sets rely on the fact that they can be thought of as the large subsets of $kappa$, in these sense that they have measure 1 with respect to this measure. However, many of these uses generalize from club to stationary because it is sufficient for the application that the set is larger merely in the sense that it does not have measure $0$, rather than actually have measure $1$.

By the way, I'm not so sure I share your intuition that the club sets are those that "contain big enough ordinals". On the one hand, if $C$ is the set of limit ordinals below $kappa$, then it is club, but if I add one (or any fixed non-zero ordinal) to every ordinal in $C$, making $D={lambda+1 | lambdain C}$, then it would seem to have just as big ordinals or bigger, but $D$ is not club.