It'll be a combination of nascent technologies on the cusp of viability.
First, something like this will have to be manufactured with a future process node about 2-3 generations past what is currently planned. Intel has plans in place for "18A", so we're talking something like "5A" here, with upwards of 1 trillion transistors per chip. We're already over 200 billion, so this is not unreasonable.
Power draw will have to be reduced by switching materials to something like silicon-carbide instead of pure silicon.
Then this will have to use 3D stacking of some sort and packaging more like DIMMs instead of a single central CPU. So dozens of processor chips per box with much weaker memory coherency guarantees.
Some further simplifications might be needed. This might end up looking like a hybrid between GPUs and CPUs. An example might be the IBM POWER server CPUs, some of which have 8 hyper-threads per physical core. Unlike POWER, getting to hundreds of kilocores or one megacore with general-purpose compute might require full-featured but simple cores.
Also worth noting that a high-end GPU already has 16K cores, although the definition of a "core" in that context isn't as clear-cut as with normal CPUs.
These server CPUs are still being made with 5nm or 4nm technology. Sure, that's just a marketing number, not a physical size, but the point is that there are already firm plans from both Intel and TSMC to at least double the density compared to these current-gen nodes. Another doubling is definitely physically possible, but might not be cost effective for a long time.
Still, I wouldn't be surprised to see 4K vCPUs in a single box available in about a decade in the cloud.
After that? Maybe 3D stacking or volumetric manufacturing techniques will let us side-step the scale limits imposed by the atomic nature of matter. We won't be able to shrink any further, but we'll eventually figure out how to make more complex structures more efficiently.