Shadows of Doubt is probably the closest thing to a pure detective game. It's definitely a bit rough around the edges, but the core concept is really cool. I had a lot of fun playing it even in its current state, and look forward to coming back once it's further along in development.
Return of the Obra Dinn is worth checking out like others have mentioned. I also really enjoyed The Case of the Golden Idol, which is somewhat similar.
I'm surprised no one has mention The Outer Wilds yet. The whole game makes you feel like part detective, part archeologist, and one of the best games I've ever played.