Let's take coefficients in a field $k$, for simplicity.
On 2): the singular cohomology of a topological space $X$ is the dual of its singular homology, almost by definition. But if $X$ is a space for which singular cohomology is not the same as sheaf cohomology, then the sheaf cohomology of $X$ need not have a predual. For example, if $X$ is the Cantor set, then the sheaf cohomology of $X$ with coefficients in the constant sheaf $\underline{k}$ is the vector space of locally constant functions from $X$ into $k$. This is a vector space of countable dimension over $k$, so it cannot arise as the dual of anything.
On 1) and 4): part of the point of the six-functor formalism is that it incorporates things like homology automatically. For nice spaces $X$, singular cohomology = sheaf cohomology with coefficients in the constant sheaf, and singular homology = compactly supported sheaf cohomology with coefficients in the dualizing sheaf. Or, in six-functor notation,
Cohomology of $X$ = $f_* f^* k$ and homology of $X$ = $f_! f^! k$
(here $f$ is the projection map from $X$ to a point, and all functors are derived). These constructions are related as follows:
a) If the topological space $X$ is locally nice (so that the constant sheaf satisfies Verdier biduality), then cohomology $f_* f^* k$ is the dual of homology $f_! f^! k$. This is satisfied for many spaces of interest (for example, finite simplicial complexes, underlying topological spaces of complex algebraic varieties, ...)
b) If the topological space $X$ is compact, then homology $f_! f^! k$ is the dual of cohomology $f_* f^* k$. This applies even when $X$ is locally very badly behaved, like the Cantor set.
If $X$ is both compact and locally nice, then both of these arguments apply, and the homology and cohomology of $X$ are forced to be finite-dimensional.
The status of the following answer is a bit speculative, unfortunately. To be precise, I believe that all of what I say below is true; I also believe that there is no proof in the literature of some of the things I state below, and I am not enough of an expert to supply those proofs. Nevertheless I'm putting it out there in the hope that it can be helpful.
First of all, when dealing with unbounded complexes of sheaves one runs into the issue of hypercompleteness, and I have to say something about this. Let's first do the purely psychological change that instead of complexes of sheaves, we think of sheaves of complexes. Chain complexes are most naturally thought of as an $\infty$-category, once we localize at quasi-isomorphisms, so one is then led to thinking about sheaves valued in an $\infty$-category. Now a sheaf on a space $X$ in a complete 1-category $C$ is is a functor $F \colon \mathrm{Op}(X)^{op} \to C$ such that if $\{U_i \to U\}$ is an open cover of a subset $U$, then $F(U)$ is the equalizer (limit) of the two arrows $\prod_i F(U_i) \to \prod_{i,j} F(U_i \cap U_j)$. If $C$ is an $\infty$-category then the limit must be a homotopy limit, taking higher coherences into account, and then the proper definition of a $C$-valued sheaf turns out to be that the natural map from $F(U)$ to the (homotopy) limit of the cosimplicial diagram which in level $n$ is given by $\prod_{i_1,\ldots,i_n} F(U_{i_1} \cap \ldots \cap U_{i_n})$, is an isomorphism. This specializes to the usual $1$-categorical sheaf axiom when $C$ is a 1-category.
When we take $C$ to be the $\infty$-category of bounded below cochain complexes modulo quasi-isomorphism, then the $\infty$-category of $C$-valued sheaves on $X$ is an $\infty$-categorical enhancement of the derived category $D^+(X)$. But if we consider instead unbounded complexes, the analogous statement is false. Namely, the $\infty$-categorical sheaf axiom from the previous paragraph describes what's known as descent for Cech covers. To recover the classical unbounded derived category one must instead impose descent for hypercovers. In a sense this has been known since the 60's, and traditionally this has been interpreted as meaning that Cech descent produces the "wrong" answer, and hypercovers "correct" this deficiency. After Lurie, a more modern perspective is that Cech descent is for many purposes more natural. In any case, the upshot is that for a space $X$ there are two typically inequivalent notions one can consider: $\infty$-sheaves on $X$ valued in unbounded complexes of sheaves, and hypersheaves on $X$ valued in unbounded complexes. The latter category can be recovered from the former via the process of hypercompletion, and the latter produces an $\infty$-categorical enhancement of the classical unbounded derived category.
Now you mention the condition $(\ast)$ in Spaltenstein's paper, which looks like it is just an annoying technicality, and whether it can be removed using more modern homotopical machinery. I believe instead that the result is just plain false without some condition like $(\ast)$. More specifically I think that if a space $X$ satisfies condition $(\ast)$ then this forces Cech descent and hyperdescent to coincide for abelian sheaves, and that this is fundamentally the reason that $(\ast)$ appears in Spaltenstein's paper. Namely, Spaltenstein's Theorem B concerns the classical unbounded derived category, and I believe that all of these results fail in general when one works with hypersheaves. But a very general version of Spaltenstein's Theorem B should hold if one works with $\infty$-sheaves throughout, with no condition like $(\ast)$ or finiteness.
Here's one reason to believe this. Part of Spaltenstein's Theorem B is proper base change. In Higher Topos Theory, Lurie proves a very general nonabelian version of proper base change, which implies the classical one. Crucially, Lurie's version of proper base change is a theorem for $\infty$-sheaves, not hypersheaves (and he gives an example where proper base change fails for hypersheaves). In particular it implies a form of proper base change for $\infty$-sheaves of unbounded complexes on a space $X$, and not for hypersheaves. Now I should add that in Higher Topos Theory Lurie only considers proper morphisms, so he works in the setting where $f_! = f_\ast$. So he does not introduce the functors $f_!$ or $f^!$ to state his result.
Another indication that $(\ast)$ is actually about hypercompleteness is that Spaltenstein remarks that locally finite dimensional spaces satisfy $(\ast)$. But locally finite dimensional things should also be hypercomplete. More precisely, Lurie proves this statement in HTT, if "dimension" is interpreted as "homotopy dimension", a notion that he introduces. For paracompact topological spaces, "homotopy dimension" coincides with "covering dimension". Spaltenstein doesn't elaborate on what notion of dimension he's thinking of but I assume cohomological dimension.
In any case, it is true that higher category theory can be used to give constructions of $f_!$ and $f^!$, under milder hypotheses than what Spaltenstein uses. Namely, $f_!$ and $f^!$ should exist for any continuous map between locally compact Hausdorff spaces, for $\infty$-sheaves valued in any complete and cocomplete stable $\infty$-category. Unlike Lurie's proper base theorem which is fully nonabelian (ie works for sheaves of spaces), this part of the story uses stability in a crucial way. In Higher Topos Theory, Lurie proves that on a locally compact Hausdorff space $X$, $\infty$-sheaves on $X$ can be described equivalently in terms of functors taking values on open subsets of $X$, or taking values on compact subsets of $X$. If the target category is moreover stable then this can be used to construct an equivalence of $\infty$-categories between sheaves and cosheaves on $X$. There is a natural pushforward operation on cosheaves, much like the pushforward of sheaves. Translating the pushforward functor via the sheaf-cosheaf equivalence to an operation on sheaves, one recovers precisely the functor $f_!$. By the adjoint functor theorem one directly gets $f^!$, too. This is sketched in https://www.math.ias.edu/~lurie/282ynotes/LectureXXI-Verdier.pdf
In your second question it sort of sounds like you are asking about whether versions of the functors $f_!$ and $f^!$ can be useful in coherent cohomology (and you are not just asking about Grothendieck duality). Something like this is true in the setting of condensed mathematics. One version of six-functors formalism is in the final chapter of Scholze's "Condensed mathematics", but even closer to what you're looking for is I think the material from Dustin Clausen's final lecture in the Copenhagen Masterclass (you can find it on Youtube), which I will not attempt to summarize.
Best Answer
One nice geometric way to view homology is a measurement of your space $X$ given by probing $X$ with other, nicer spaces, for instance, singular homology probes with simplices. One good reason to call this abstract thing homology is that it too admits elements corresponding to nice enough maps from test objects into $X$.
Lets work in a general six functor formalism, but I'm secretly thinking of constructible sheaves on nice spaces. Then we have our unit object $\mathbf{1}$ ($\underline{\mathbb{Z}}$ in our case) and a dualising object $p^!\mathbf{1}:=\omega$, in our case, this is the usual dualising sheaf $p^!\underline{\mathbb{Z}}$.
Then we can define a "smooth" object $Z$ to be one which admits an isomorphism $\gamma:\mathbf{1}_Z\rightarrow \omega_Z[-d_Z]$, for some integer $d_Z$. In the constructible/sheafy setting, manifolds give plenty of examples of "smooth" objects, and $d_Z$ is the dimension.
Then if we have $Z$ a closed subset of $X$, or more generally, if $i_*\cong i_!$ for $i:Z\rightarrow X$, then we have the following map, where all arrows are coming from adjunctions in our formalism, except the second one which says $Z$ is "smooth".
$$\mathbf{1}_X\rightarrow i_*\mathbf{1_Z}\xrightarrow{i_*\gamma} i_*\omega_Z[-d_Z]\xrightarrow{\cong}i_!\omega_Z[-d_Z]\rightarrow\omega_X[-d_Z]$$
So the formalism gives us elements of this group for each "smooth" closed subset $Z$ in $X$, so in my mind, this deserves the label of homology, it admits a direct link to the geometry of nice subsets of your space!