This may be already clear to you, but from my perspective, the clearest manifestation of this duality is in the setting of mixed Hodge modules (or some other version of ``mixed sheaves'').

Let $f: X \to Y$ be a morphism of complex algebraic varieties, and let $D_m(X)$ and $D_m(Y)$ refer to the derived categories of mixed Hodge modules. Then there are functors
$$
f^!, f^\ast : D_m(Y) \to D_m(X)
$$
$$
f_\ast, f_! : D_m(X) \to D_m(Y)
$$
Let me also define the shifted functor $f^\dagger = f^! [\dim Y - \dim X]$.
We also have the Verider duality functors $\mathbb D_X$ and $\mathbb D_Y$. The functors are related as follows:
$
f^! \mathbb D_Y = \mathbb D_X f^\ast$, and $f_! \mathbb D_X = \mathbb D_Y f_\ast$.

We have that: $f_\ast$ and $f^!$ increase weights, whereas $f_!$ and $f^\ast$ decrease weights.

In this language:

If $f$ is smooth (i.e. submersive) then $f^\dagger$ commutes with $\mathbb D$, i.e. $\mathbb D_X f^\dagger \simeq f^\dagger \mathbb D_Y$

If f is proper then $f_\ast$ commutes with $\mathbb D$.

Thus smoothness gives a relationship between relative dualizing sheaf and constant sheaf, and properness relates relative cohomology with relative compactly supported cohomology.

For example, in the case $f: X \to pt$, we have
$$ f_\ast f^\ast \mathbb Q \simeq H^\ast (X) $$
$$ f_! f^! \mathbb Q \simeq H_\ast(X)$$
$$ f_! f^\ast \mathbb Q \simeq H^\ast _c(X)$$
$$ f_\ast f^! \mathbb Q \simeq H_\ast ^{BM}(X)$$.

Smoothness of $X$ means the dualizing sheaf ($f^! \mathbb Q$) is isomorphic to the constant sheaf ($f^\ast \mathbb Q$) up to a shift. Properness means that compactly supported cohomology (with coefficients in some sheaf) is isomorphic to ordinary cohomology. This recovers your first observation.