I think it feels like magic because there's something tautological going on. The story should really culminate with the definition of "faithfully flat" rather than begin with it.

As you suggested, let's consider the case where you cover an affine scheme Spec(A) by finitely many basic open affines Spec(A_{f}). The cover Spec(B)=Spec(∏A_{f}) has the following special property:

(∗) If you want to check exactness of any sequence of A-modules, it's enough to check exactness after restricting to Spec(B) (i.e. tensoring with B over A).

Now if you'd like to show that the Amitsur complex is exact, you know it's enough to find a section B→A. By (∗), we know what it's actually enough to find a section *locally*, and in the case Spec(B)=∐Spec(A_{f})→Spec(A), it's obvious that there's a section locally. This is all very tautological, but it allows you to prove that schemes are sheaves in the Zariski topology, which maybe doesn't impress you so much.

But we have extra assumptions in this argument. We didn't really need B to be of the form ∏A_{f}, we just needed it to satisfy (∗). So let's make a new definition, saying that B is "faithfully flat" over A if (∗) holds. Now we get the result: "If B is faithfully flat over A, then the Amitsur complex is exact." As a consequence, we can show that schemes are sheaves in the faithfully flat topology on affine schemes, but all we've really done is shown that "schemes are sheaves in the strongest topology for which this proof works," and just defined "faithfully flat" to be that topology. Delightfully, we can prove (with commutative algebra) that lots of different properties of affine schemes and morphisms of affine schemes are local in this topology.

Okay, but we'd really like to prove things about schemes and morphisms of schemes, not about affine schemes and morphisms of affine schemes. I think part of your confusion arises from the desire to understand the etale topology (a good desire), rather than continuing the strategy of making definitions which make results trivial (or at least straight-forward). If you indulge your generality tooth, you'll ask the question,

What is the strongest topology on the category of schemes so that every cover can be understood as a combination of (a) Zariski covers and (b) faithfully flat covers of affine schemes by affine schemes?

The answer is the fpqc topology. Basically by construction, if a property of schemes (resp. morphisms of schemes) is local in the Zariski topology and is local in the faithfully flat topology when you restrict to the category of affine schemes, then it is local in the fpqc topology. Similarly, if a functor is a sheaf in the Zariski topology, and its restriction to the category of affine schemes is a sheaf in the faithfully flat topology, then it's a sheaf in the fpqc topology. In particular, we get that

This sounds very impressive given that nobody understands what general fpqc morphisms look like (I think), but that's just because we defined the fpqc topology to be whatever it has to be to make the straight-forward proofs work. One thing we do know is that fppf morphisms are fpqc (by EGA IV, Corollary 1.10.4). In particular, etale morphisms are fpqc.