We found out that when a column name has the same name as a dataset, BigQuery will try to use that column instead of the dataset.
In the following examples, I was querying from my BigQuery database that has a table ads in the dataset named dat_bui.
But as shown below, BigQuery was trying to access a.dat_bui.ads instead.
In order to make sure BigQuery is referencing the dataset correctly, we need to explicitly specify the BigQuery project identifier as follows:
To address this issue, Holistics has developed a Feature Toggle that when enabled, will always include the BigQuery project identifiers during SQL generation. If you see that Holistics auto-generated queries are having this issue, please let us know and we will enable the Feature Toggle for you.
In case you encounter this issue in your own queries, you can also apply this solution.
Cheers!