I have encountered the same error using a Snowflake warehouse.

This is a standard "out of memory" error.

The CTE has to be heavily filtered when a large amount of data is present or it will overrun the memory available to your query.

The same thing is true in SQL Server and the solution in SQL Server was to create a temporary table #<name> and work with it.