There are still a number of higher-end but older cameras supporting CF but it's being phased out. CFExpress (both A and B) are strong contenders for a replacement in cameras that need the speed and storage sizes offered. Type A is roughly the same size as an SD (slightly smaller, slightly thicker). Some of the newer Sony cameras offer a slot design that supports both CFExpress A and SD. Type B is compatible with XQD cards. XQD is also being phased out, I think. Type B has a higher theoretical maximum throughput than Type A (two PCIe lanes rather than one).
SD itself will likely be around for a while as it still supports a lot of use cases and is relatively cheap.