Currently if you use GROUP BY CUBE clause in Snowflake, it has the limitation of supporting only 7 elements, or the equivalent of 128 grouping sets. This is documented in below Snowflake’s official documentation:
GROUP BY CUBE Usage Notes
Snowflake allows up to 7 elements (equivalent to 128 grouping sets) in each cube.
This same limitation also applies to both GROPU BY ROLLUP and GROUP BY GROUPGING SET.
The reason that such limitation was added was because of performance concern. If you have too any elements in the those clauses, it will result in 2^N grouping sets, so the limit of 7 elements will result in 128 grouping sets, and if you have 8, it will be 256 and 10 will be 1024. You can see that the number of grouping sets will increase exponentially and it will cause too much memory pressure on compiler and also during data processing and hence greatly affects query performance, even if compiler can handle it during compilation stage.
With my current test on version 4.31.2, the compiler will return below error if more than 17 elements passed into GROUP BY CUBE clause:
CREATE OR REPLACE TABLE test ( c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int, c17 int, c18 int, c19 int, c20 int, c21 int, c22 int, c23 int, c24 int, c25 int, c26 int, c27 int, c28 int, c29 int, c30 int ); SELECT c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, sum(c30) FROM test GROUP BY CUBE ( c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18 ); SQL compilation error: Compilation memory exhausted.
Currently there is no way to increase such limit from user side. If needed, Snowflake Support needs to be contacted and the limit needs to be increased from Snowflake side.
