Improve BQ performance by changing 'LEFT JOIN' by ', UNNEST()'

[kokevidaurre]

Hi,
As suggest, you can get better performance on large dataset using UNNEST instead LEFT JOIN when joining subrecords of the BQ schema.

Also is necessary to add hit id to all that subrecords tables to keep consistency on counts.

[ernestoongaro]

Hi @kokevidaurre - do you have any examples/documentation of where UNNEST is faster than LEFT JOIN UNNEST?

[kokevidaurre]

Hi @ernestoongaro.
No, just the official BigQuery documentation, but also experience within big datasets.
If you see the BigQuery Export Schema, you may find that is just a single table and there's no need to do a LEFT JOIN with it self.