Pay attention to cardinality & grain when UNNESTING in BigQuery!
Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified
Whenever you're UNNESing an ARRAY, you're getting a Cartesian product between the row and the array contents. If you were to unnest another array, you'll get another Cartesian product between the output of the previous unnest and the elements in the current array.
Let's look at an example. A student has their grades stored in an ARRAY as well as their food allergies in another ARRAY.
If we are to UNNEST both array we'll end having count_of_grades x count_of_allergies rows for each student, 4x3 in this case.
Why this happens? Well the allergies and grades have no relationship between each other, they just refer to the same student row.
Take this into account when you're working with nested data.
Found it useful? Check out to my Analytics newsletter at notjustsql.com.
Enjoyed this? Here are some related articles you might find useful:
