SMD IC mounted upside-down inside a drill hole for extreme low-profile requirements

Common enough. The process is called "Z-axis milling". Used for LEDs sometimes too.

You can even bury low-profile parts such as bypass capacitors and resistors in cavities entirely within multilayer PCBs.

It requires extra steps so expect extra costs or MOQ or both. For small quantities the costs may be prohibitive, even from China.

Is this common practice? And how difficult would it be to use on my own designs? Are there any references or guidelines I could read? This design really impressed me, and gave me many many new ideas for future projects I would like to try out.

No it's not a common practice, it would probably incur some kind of cost outside of regular charges because of the additional time and effort it would take to install the part (most likely by hand). But they needed a hall effect sensor on the board and a good way to keep it there, which is ingenious.

There are no rules for this type of thing, just a lot of creativity. It may have taken them a revision or two (or three) to get it right. But the sky is the limit, if you can dream it up and the board house can manufacture it then you can build it.

I think the most limiting factor would be your layout software and the ability to make components on multiple layers.

This isn't best practice from the DFM (design for manufacturing) standpoint. The PCB assembly house will charge more for mounting that part upside down. It's a non-standard operation for them.

It makes me wonder why the designers didn't mount the sensor on the other side of the board in a normal way, and make a pocket for it in the enclosure. Maybe this arrangement was a last moment kludge (albeit a good looking one). Having said that, there are SMT parts made specially for through-board mounting. When they come on tape, they are in the correct orientation, and pick&place machines can work with them.