I generally think about the relationship differently than Jacob, probably because I'm coming from an algebraic topology background rather than an algebraic geometry one.  I would say that if $\mathcal{E}$ is any $(\infty,1)$-topos, with $f:\mathcal{E}\to \mathcal{S}$ its unique geometric morphism to $\infty$-groupoids (homotopy spaces), then $f_*$ is cohomology of $\mathcal{E}$ with coefficients in some ($\infty$-)sheaf (of spectra, say), and so $f_* f^*$ is cohomology with coefficients in a constant sheaf.  The *homology* of $\mathcal{E}$ with coefficients in some sheaf would use instead $f_!$, the *left* adjoint to $f^*$: the difference is that such a left adjoint doesn't always exist, only when $\mathcal{E}$ is locally contractible.

If $X$ is a topological space, we can make it into an $(\infty,1)$-topos in multiple ways.  One is the slice $\mathcal{S}/X$, where $X$ is regarded as its homotopy type: this is always locally contractible, and in this way we get ordinary algebraic-topological homology and cohomology, as well as homology and cohomology with local coefficients in the classical sense (i.e. locally constant coefficients).  Another is $\mathrm{Sh}(X)$, consisting of $\infty$-sheaves on the site of opens in $X$; this is not locally contractible unless $X$ itself is.  Thus we can define cohomology with coefficients in an arbitrary sheaf on an arbitrary space, whereas for homology with such coefficients we need the space to be locally contractible --- or else to define the homology as only a "pro-object".

I am not an expert on six-functor formalism, but my understanding is that it includes both algebro-topological homology and algebro-geometric compactly supported-cohomology: the former arises in the [Wirthmuller context](https://ncatlab.org/nlab/show/Wirthmüller+context) $f^* = f^!$, while the latter is a generalization of the [Grothendieck context](https://ncatlab.org/nlab/show/Grothendieck+context) $f_! = f_*$ (which is the case when $f$ is proper, i.e. $\mathcal{E}$ is compact).  In topos theory there is a fundamental duality between local-connectedness conditions and compactness conditions (see for instance chapter C3 of *Sketches of an Elephant*), and the two perspectives on homology come from focusing on one or the other of these worlds.