How should a beginner learn about arithmetic schemes (interpret this as you wish, or as a regular scheme, proper and flat over Spec(Z))? What are the most important examples of such schemes? Good references? What kind of intuition do people have for such schemes?
[Math] Examples and intuition for arithmetic schemes
ag.algebraic-geometryarithmetic-geometryarithmetic-schemeexamplesintuition
Related Solutions
This is going to be perhaps vague, but I'l try to write down the idea. I've been told that in doing scheme theory, most of the time what is studied is not a scheme but a morphism of schemes $f:X\rightarrow S$. The studied of such a maps can be enriched allowing a chance of "base" $S$. Such a scheme $S$ can be crazy, but I'll impose finite properties to the map $f$ to keep everything under control.
Now, as you say, if we have two $S$-schemes $f:X\rightarrow S$ and $Y\rightarrow S$ then we can consider the fiber product of them (which is a categorical product of $X$ and $Y$ over $S$). Such a process can be though of as replacing the base $S$ by the scheme $Y$. In doing so, the new map $f_Y:X_Y\rightarrow Y$ (standing for $p_2:X\times_{S}Y\rightarrow Y$) may be easier to work with than the map itself $f$. This very construction is generalizing the idea of "extending scalars".
Here is a sort of example of a product described above. Given the point $s\in S$ and its residue field $k(s)$ of the local ring $\mathcal{O}_s$ at that point. Then it is well known that $X_s=X\times_S Spec(k(s))$ has an underlying space the "fiber" $f^{-1}(s)$ (as long as $f$ is "finite"). This space can be considered as an algebraic variety over the field $k(s)$. This way $S$ is the scheme parameterizing the varieties $X_s$ some of which a priori may have different ground fields $k(s)$.
On the other hand, back to the fiber product, in considering a variety $X$ over the field $k$ (scheme of finite type over $k$), we can extend the scalars from $k$ to a field extension $K$. In doing so, we may think of such a variety now over a field extension $K$. Here is where the product comes into play. Vaguely enough, this is saying that if you have an equation and you have solved it over the field $k$ now you might ask yourself if you'd be able to solved it again but over a field extension $K$, here you want to perform an extension of scalars and the ideas written above may help out.
Needless to say that we can consider as well $X\times_k Spec(\overline{k})$ and due to the fact that field $k$ may not have been algebraically closed, such a fiber product can be handful.
Now, to get intuition, let's take a look at an example: $$\pi:Spec \mathbb{Z}[i]\rightarrow Spec(\mathbb{Z})$$
Let $X=Spec\mathbb{Z}[i]$ and notice that the fiber $$X_p=X\times_{\mathbb{Z}}\mathbb{F}_p=Spec(\mathbb{Z}[i]\otimes\mathbb{F}_p)$$
Such a fiber is going to have cardinality 2 if $p\equiv 1 mod(4)$ and cardinality 1 if $p\equiv 3 mod(4)$ (this is the fact that $p=a^2+b^2$ where $a,b\in \mathbb{Z}$ if $p\equiv 1mod 4$). These fibers are $X$ reduce mod $p$. Notice that at the generic point we have $X_0=\mathbb{Q}[i]$.
Now the $T-points$ in $X$ where $T=\mathbb{F}_p[i]$, and all that story give rise to the functor which represents the scheme $X$. If I am not wrong, the info that carries such a functor is nothing but that of the fibers of the map $\pi$. So naively it is like having $X$ fibers wise.
If you want to learn about stacks, I can recommend 'Fundamental Algebraic Geometry: Grothendieck's FGA Explained'. Vistoli's exposition of the basic theory of stacks is hard to beat, I think. Moreover, the chapter about Picard schemes is also good if you want to learn when a functor is representable and what you might have to do to make it representable. In any case, the theory of Picard schemes is indispensable in arithmetic geometry, e.g. it is a way to obtain the group structure on an elliptic curve (see the book of Katz, Mazur: Arithmetic moduli of elliptic curves) or to study duality of abelian varieties.
I am not sure if there exists an ideal book if you want to learn about the moduli stack of elliptic curves. The classic source is Deligne-Rapoport 'LES SCHEMAS DE MODULES DE COURBES ELLIPTIQUE', which you should look into, but the proofs are often very brief. The book by Katz and Mazur has good parts, but for some reason they decided to avoid the language of stacks. The book by Olsson on stacks has some parts about elliptic curves as well. At some point, I wrote a note together with Viktoriya Ozornova that contains a really detailed proof that the moduli stack of elliptic curves is really an fpqc stack and that Weierstraß equations exist http://www.staff.science.uu.nl/~meier007/Mell.pdf (but it is certainly not meant to provide a flair of the subject).
Best Answer
If you are looking for good first examples, Mumford's Red Book and Eisenbud and Harris's 'Geometry of Schemes' have some good pictures and examples.
Its worth playing around with Spec(O_c), where O_c is the ring of integers in the extension of Q by the square root of c, and thinking about it as a scheme over Spec(Z). In particular, several somewhat mysterious number theory terms like 'ramified' and 'split' make geometric sense in this context.
Its also worth thinking about what the p-adics should look like as a scheme - a formal neighborhood of p in Spec(Z) (though to make this precise you need to know what formal schemes are).
Its also not a terrible idea to pick up a book on algebraic number theory and try to translate everything that is said into a geometric statement (the trick is to realize every time talk about a field, they are really talking about the ring of integers in that field).