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 $f^* = f^!$, while the latter is a generalization of the 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.

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 $f^* = f^!$, while the latter is a generalization of the 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.

Edit in response to comment: the duality between local connectedness and properness is indeed not obvious from the usual definitions. One way to see it fairly clearly is in terms of their characterizations using Beck-Chevalley conditions. A geometric morphism is "locally $n$-connected" iff every pullback of it (in the $(\infty,2)$-category of $(\infty,1)$-toposes) satisfies the left Beck-Chevalley condition for $n$-truncated objects, and it is "$n$-proper" (or "proper of height $n$") iff every pullback of it satisfies the right Beck-Chevalley condition for $n$-truncated objects. I don't know whether this is in the literature for general $n$; the locally (-1)-connected (a.k.a. open) and locally 0-connected (a.k.a. locally connected) cases and the (-1)-proper (a.k.a. proper) and 0-proper (a.k.a. tidy) cases, for 1-toposes, are in chapter C3 of Sketches of an Elephant, and the $\infty$-proper case is in section 7.3 of Higher Topos Theory.