As per my understanding:
A custom resource is just an AWS Lambda function that runs whenever the stack is provisioned or updated or deleted.
A resource provider is plain old code where one writes hooks for all the Stack operations (update, create, delete, etc).
I can't see why anyone would use the former over the latter. Resource providers seem easier to write and test.
One historical reason is that custom resources were the only option until recently: