As mentioned in the question, we are seeking the right adjoint $f^!$ of $f_!$; connection to the classical form is well explained here. I want to write down my understanding of why the dualizing sheaf or complex $f^!(\mathcal{O}_Y)$ should take the above form, or a shift of the canonical line bundle when $f$ is smooth. Of course I'm missing many details, and much of the analogy and intuition is subjective.
We start from the simplest case. Let $f:X\to Y$ be a finite separable morphism. In fact, there is no need to go to the derived category in this case (to some extent $-$ without the flatness, derived functors are not so simple). That is, we have the adjunction $\text{Hom}_X(\mathcal{F},f^!\mathcal{G})\simeq\text{Hom}_Y(f_*\mathcal{F}, \mathcal{G})$ for sheaves if we define $f^!(-)=\mathcal{Hom}_Y(f_*\mathcal{O}_X, \mathcal{G})$, Hartshorne III. ex 6.10. This is more or less the Hom-tensor adjunction $\text{Hom}_B(N, \text{Hom}_A(B, M))\simeq\text{Hom}_A(N\otimes_B B, M)$ where $A\to B$ is a ring map and $M\in (A-\text{mod})$, $N\in (B-\text{mod})$. We need the fact that $f$ is affine to reduce like that. I believe this adjunction only requires the affine hypothesis; but $f$ should be proper to have $f_!=f_*$, and affine+proper implies finite.
Here the counit, or the trace morphism is very close to the usual trace (when $f$ is flat), as explaned here. Since $f$ is of relative dimension zero, (e.g. in better cases a finite etale cover), there is no shift of degree (as we already know the answer). This trace map matches well with our intuition. For an analogy, let $\pi: E\to M$ be a (oriented) vector bundle over a smooth manifold. Then $R\pi_!$ can be calculated by integration along fiber, which is the Thom isomorphism. It gives the trace map for the Poincare duality of $\mathbb{R}^n$, which can be glued by Mayer-Vietoris arguments. If we imagine $E$ being something like rank 0 bundle (e.g. orientation sheaf), integration should be the trace.
Now we move to the more general case, where $f$ is proper flat. There are two homological lemmas about $f^!$. First, we can reduce to the calculation of $f^!(\mathcal{O}_Y)$, the relative dualizing complex: SP 0A9T. Next, we can relate $f^!$ to upper shriek of the diagonal morphism $\Delta:X\to X\times_Y X$, SP 0E2P.
So when $f$ is smooth, we somehow reduce to the regular embedding case. Hence our second paragraph applies, we have to calculate $R\mathcal{Hom}_Y(\mathcal{O}_X,\mathcal{O}_Y)$. Now here is where the differential sheaf comes into play. By some properties of exterior power and yoneda product of $\text{Ext}$, we reduce to the case of embedding of an effective Cartier divisor $i: D\to X$. Since $R\mathcal{Hom}(\mathcal{O}_X,\mathcal{O}_X)=\mathcal{O}_X$ and $R\mathcal{Hom}(\mathcal{I}_D,\mathcal{O}_X)=\mathcal{O}_X(D)$, by $0\to \mathcal{I}_D\to \mathcal{O}_X\to\mathcal{O}_D\to 0$, $R\mathcal{Hom}(\mathcal{O}_D,\mathcal{O}_X)=(\mathcal{O}_X\to\mathcal{O}_X(D))$. The twist gives $0\to \mathcal{O}_X\to \mathcal{O}_X(D)\to i_*\mathcal{N}\to 0$ which proves $R\mathcal{Hom}(\mathcal{O}_D,\mathcal{O}_X)\simeq \mathcal{N}[-1]$.
For me, this procedure looks like some kind of formal dual of integration. Regular embedding is dual to smooth morphism, in the same sense that cofiber sequence is dual to fiber sequence. Instead of integrating the forms, the trace map uses the normal bundle to describe $\mathcal{O}_X$ (in some sense it does one-dimension less, since it has no information along $D$). Maybe this explanation is nonsense, but I feel the situation is analogous to the invention of negative numbers. Finite flat case lies in the middle of them.
Finally, the cotangent sheaf is defined as the relative conormal sheaf of the diagonal, hence it is not so surprising that after above lemmas, for smooth $f:X\to Y$ we get $f^!(-)=f^*(-)\otimes\Omega_{X/Y}[d]$. Specializing $Y=\text{Spec }k$ gives the classical result.