When we are given a dataset of a function sampled in many points, and we want to find approximation of the unknown function in-between these points, one of the go-to methods for practitioners is fitting a Cubic Spline.
I am using such a method to approximate derivatives at intermediate points.
My QUESTION is, for what kind of functions a cubic splines may not a be a good choice for approximating?

Since a cubic spline can contain multiple cubic segments, it is capable of approximating almost any function as close as possible (if the number of cubic segments in the spline is not limited). If your approximation method is to sample points along the function and create a cubic spline interpolating these sampled points, then the approximation accuracy is highly related to the number of sampled points and how the points are distributed.
Cubic splines might not be as appropriate for approximating functions with $C^1$ discontinuities (such as a step function) as they will almost always produce bad approximation around where the $C^1$ discontinuity occurs.