When there is high memory consumption on CTE, what happens to the data?

When a CTE (Common Table Expression) requires a lot of memory, data typically begins to use temporary disk storage (swap or spill to disk) if there is not enough RAM. This can lead to slower query performance, as disk operations are significantly slower than memory.

Thus, with high memory consumption, data is partially unloaded to disk to prevent failures and memory overflow, but this impacts performance.