The (current) obstructions for a cohomological interpretation of the Riemann zeta function I am interested in the idea of a cohomological interpretation of the Riemann hypothesis (suggested by Deninger/Connes).
I am a beginner in  étale cohomology, and I would like to ask the following

Question. Why does  étale cohomology not offer a cohomological interpretation of
the local zeta function of the scheme $\operatorname{Spec} \mathbb{Z}$
in terms of the (étale?) cohomology of the associated topological
space?

I would appreciate any answer as well as a reference. I understand this question may be a bit annoying since the obstructions should supposedly be immediate for if otherwise this would probably be well known. Nonetheless, I am curious and do not know who to ask.
 A: One can't give a complete answer to this question without first understanding how etale cohomology does give a cohomological interpretation of the zeta function in the function field case.
Let $X$ be a smooth projective curve over a finite field $\mathbb F_q$. Then
$$ \zeta_{X}(s) = \frac{ \det (1 - \operatorname{Frob}_q q^{-s}, H^1(X_{\overline{\mathbb F_q}}, \mathbb Q_\ell))}{\det (1 - \operatorname{Frob}_q q^{-s}, H^0(X_{\overline{\mathbb F_q}}, \mathbb Q_\ell)) \cdot \det (1 - \operatorname{Frob}_q q^{-s}, H^2(X_{\overline{\mathbb F_q}}, \mathbb Q_\ell))}$$
It is crucial that, to get this formula, one passes to the curve $X_{\overline{\mathbb F_q}}$ obtained by base-changing to the algebraic closure. The reason this is important to note is that there is no analogue of "the algebraic closure of the base finite field" after replacing $X$ with $\operatorname{Spec} \mathbb Z$, at least not in the usual world of schemes.
An optimistic guess is that it suffices to take the etale cohomology of $\operatorname{Spec} \mathbb Z$. If that were true, then it would presumably likewise suffice in the function field world to take the etale cohomology of $X$, without base-changing to the algebraic closure. What happens when we do this?
We can see this using the long exact sequence
$$ \to H^i(X, \mathbb Z_\ell) \to H^i (X_{\overline{\mathbb F_q}}, \mathbb Z_\ell) \to  H^i (X_{\overline{\mathbb F_q}}, \mathbb Z_\ell) \to$$
where the third arrow denotes the map $\operatorname{Frob}_q-1$. Examining this exact sequence and the known description of the action of Frobenius on $H^0$ and $H^2$, we see that
$$H^0 (X, \mathbb Z_\ell) = \mathbb Z_\ell$$
$$ H^1 (X, \mathbb Z_\ell)= \mathbb Z_\ell$$
$$H^2(X, \mathbb Z_\ell) = H^1(X_{\overline{\mathbb F_q}}, \mathbb Z_\ell)/ (1 - \operatorname{Frob}_q $$
$$H^3(X, \mathbb Z_\ell) = \mathbb Z_\ell/ (q-1)$$
The only interesting cohomology group here is $H_2$, which is a finite abelian $\ell$-group. Its order is the maximum power of $\ell$ dividing the determinant of $\operatorname{Frob}_q-1$ acting on $H^1$. Equivalently, this is the maximum power of $\ell$ dividing the residue of the zeta function of $X$ at $s=1$.
Combining this information for all primes $\ell$, we have a cohomological interpretation of the residue of the zeta function at $s=1$ (at least up to a power of $p$).
Similarly, over rings of integesr of number fields, not necessarily $\mathbb Z$, we can obtain a cohomological intepretation of the residue of the zeta function. This cohomology group turns out to be dual to the $\ell$-part of the class group, so this is just the Dirichlet class number formula.
There is one last thing you can do here. The field of functions on $X_{\overline{\mathbb F_q}}$ is a Galois extension of the field of functions on $X$, with Galois group $\prod_p \mathbb Z_p$. We can just pick an extension of $\mathbb Q$ with Galois group $\prod_p \mathbb Z_p$, or even just $\mathbb Z_p$, pretend that this is $X_{\overline{\mathbb F_q}}$, and take etale cohomology of its ring of integers.
This, modulo technical details, is the approach of Iwasawa theory, which gives a cohomological interpretation of $p$-adic $L$-functions. Some $p$-adic $L$-functions are related to special values of the Riemann zeta function at negative integers, so there is, in a sense, a cohomological interpretation of these specific values.
You will note some synchrony here with Peter Scholze's point in the comments that etale cohomology is only suitable for constructing $\ell$-adic $L$-functions, and is only able to construct complex-analytic ones in the function field setting by a trick using the fact that the $L$-functions are polynomials.
