Understanding Cardinality & Grain in UNNESTing Arrays in BigQuery

When you UNNEST an ARRAY in BigQuery, you create a Cartesian product between the row and the array contents. This means that for each element in the array, a new row is created in the output, containing the data from the original row combined with the data from the array element.

If you unnest multiple arrays in BigQuery, you'll get a Cartesian product between the output of the previous unnest and the elements in the current array. For example, if a student has an array of grades and an array of food allergies, and you unnest both arrays, you would end up with count_of_grades x count_of_allergies rows for each student. In a case where there are 4 grades and 3 allergies, this would result in 12 rows for that student.

Multiple UNNEST operations in BigQuery result in a Cartesian product because each array is treated independently. When you unnest an array, each element of the array is paired with the original row. If there is no inherent relationship between the elements of two different arrays, unnesting them both will combine each element of the first array with each element of the second array, relative to the original row. This is why unnesting arrays that refer to the same student, such as grades and food allergies, results in a Cartesian product, as they have no relationship other than referring to the same student.