> Is sift still relevant today?

It is extremely relevant.

The fact that there exist other methods that "consistently surpass SIFT in all reasonable metrics" is inconsequential. You can say the same thing about JPEG compression, but JPEG is not going to disappear anytime soon.

SIFT has very good performance (but there are better methods), it is reasonably easy to compute (but there are easier) and fast (but there are much faster). Its shortcomings are well-known and easy to understand. The combination of SIFT+Ransac is the bread and butter of image matching, and if you work on this problem you cannot seriously propose a new method unless you at least compare to it.

There's a good paper on this:

https://demuc.de/papers/schoenberger2017comparative.pdf

"Our evaluation confirms that, as expected, learned descriptors often surpass SIFT on all evaluation metrics. However, we also observe that advanced versions of hand-crafted descriptors perform on par or better than the state-of-the-art learned feature descriptors, especially in the more complex SFM scenarios. As such, our paper demonstrates that there is still significant room for improvement for learning more powerful feature descriptors."

As a computer vision practitioner, I would argue that SIFT is still very relevant today. In most real-world scenarios it seems to hold up as well or better than the deep learning approaches I've tried, and it is easier to implement and maintain. Failures are often easy to understand and mitigate. In practice I often end up using FAST or ORB features due to "good enough" accuracy but much faster processing rates, especially on embedded devices. Feature detection and matching is an area where "classical" computer vision is very much alive and well.

Imho a CNN is like a generalization of what SIFT does, so a CNN can be trained to be equivalent to SIFT, but it can also be trained with more specific features for your use case.