Databricks Certified Data Engineer Professional Practice Questions

Databricks Certified Data Engineer Professional exam preparation

Databricks Certified Data Engineer Professional practice questions in English: 178 questions with explanations. The mock exam has 60 questions and 120 minutes.

Databricks Certified Data Engineer Professional free question 3 / 10 · Domain 3: Data Manipulation

GROUP BY CUBE(region, product, channel) and GROUP BY ROLLUP(region, product, channel) are each equivalent to how many grouping sets?

  1. CUBE gives 7 and ROLLUP gives 3
  2. CUBE gives 8 and ROLLUP gives 3
  3. CUBE gives 7 and ROLLUP gives 4
  4. CUBE gives 8 and ROLLUP gives 4
Show the answer and explanation

Answer: D

Why this is correct
CUBE aggregates over every combination of the listed columns, so N elements give 2^N grouping sets; with 3 columns that is 2^3 = 8, including the empty grouping set () for the grand total. ROLLUP aggregates over a hierarchy by dropping one column at a time from the right, so N elements give N+1 grouping sets. With 3 columns they are (region, product, channel), (region, product), (region), and (), which makes 4.

Why the other options are wrong
"CUBE gives 7 and ROLLUP gives 4" … This count for CUBE leaves out the empty grouping set. The 2^N of CUBE already includes the grand total ().
"CUBE gives 8 and ROLLUP gives 3" … The +1 in the ROLLUP count of N+1 is the empty grouping set that represents the grand total. This answer drops it.
"CUBE gives 7 and ROLLUP gives 3" … This answer leaves out the empty grouping set, which stands for the grand total, in both CUBE and ROLLUP.

Key point
CUBE and ROLLUP are shorthand for GROUPING SETS. When a GROUP BY lists several GROUPING SETS (including CUBE and ROLLUP), Databricks SQL takes their cross product and treats it as a single GROUPING SETS.

Go to the Databricks Certified Data Engineer Professional practice app (10 free questions; purchase unlocks all questions)

See the full practice set