The story is probably well-known: given a map $f:X\to Y$ of spaces (say schemes, but there are many other contexts), we have two classical operations between sheaves on $X$ and those on $Y$: the pushforward $f_*:Sh(X)\to Sh(Y)$ and the pullback $f^*:Sh(Y)\to Sh(X)$, which form an adjoint pair. Slightly less obvious is another operation, the pushforward with compact support $f_!:Sh(X)\to Sh(Y)$. Finally, we have its adjoint, which is the least obvious of them all: the exceptional inverse image $f^!:Sh(Y)\to Sh(X)$. Together with tensor product and the internal Hom they form Grothendieck's six operations.
There is only one issue: $f^!$ doesn't actually exist at the level of sheaves, and instead has to be specified at the level of derived categories. As far as I know, there is also no easy description of it, except as the right adjoint of the derived $f_!$ (from what I heard, the proof of Verdier's duality, asserting its existence, proceeds by an "abstract nonsense" argument using the adjoint functor theorem).
Despite that I wanted to ask: even if there is no proper description of this exceptional inverse image in general, is there some intution which would help illuminate how it works? I know that if $f$ happens to be a (locally closed) immersion, then one can identify $f^!(F)$ with the sheaf of sections of $Y$ whose support is contained strictly inside $X$. Are there other examples where one can describe it similarly?
What should one make of the fact that there may be some nontrivial cohomology involved in general though? Are there some notable examples which one should keep in mind when thinking about $f^!$?