Non-trivial cases where the frozen core approximation is bad? In computational chemistry, it is extremely common to freeze the core electrons after an initial Hartree-Fock calculation, meaning that the core electrons are left out of any treatment of the electron correlation. Which orbitals are considered core depends on the atom, but for the second row, it usually just means the 1s electrons are not correlated.

This is an intuitive approximation, as one would expect that correlation of the valence electrons would have the greatest effect on any actual chemistry.

Now, clearly one can imagine situations where freezing the core electrons is a very bad idea. One such case is if you were interested in modelling x-ray spectroscopy. I am less interested in this type of example as this is almost just a user error, and hence I would consider this a somewhat trivial example.

So, to be concrete, are there any known examples in which treatment of correlation of the core electrons results in a qualitative change in the modelling of a process which is primarily mediated by the valence electrons?

My guess is if there is an example it occurs in the third row and beyond.
The frozen core approximation (FCA) falls apart whenever you need orbital relaxation of the core to accurately describe your system. From a theory-based argument, there are multiple electronic structure methods which rely on this (CCSD, orbital optimized methods) to better account for correlation effects. However, given that the core orbitals do not change appreciably during most processes (ionization, binding, standard chemical reactions), the error introduced by the FCA is all but completely cancelled (you can still get sub kcal/mol accuracy with the FCA).

However, one case where it rears its head is when you're trying to calculate the single-point energy of a system. In this case, the FCA will get in the way of recovering (for example) the full CI result in the full basis set limit.