Dust Community Icon

Join the Community

Troubleshooting Dust BigQuery Table Query Issues: Help Needed | Dust Community

Troubleshooting Dust BigQuery Table Query Issues: Help Needed

·

·

Hello ! I'm struggling to have Dust query a particular BigQuery table with the Query Tables feature. My agent keeps on failing with this message: Query uses tables that are not allowed: MY_GCP_PROJECT_ID.MY_DATASET.MY_TABLE Some facts:

the Service Account i'm using for the BigQuery connection has the sufficient roles (bq viewer, bq user and bq job user)
I am applying those roles at the project level (so no IAM conditions here)
A table from the same project (but a different dataset) is queried fine
Although the agent fails to query the table, it seems able to analyze the data and compute a SQL query to execute

So it seems that the Table is read, accessed, but BigQuery jobs are not running for some reason 🤔 What I did so far:

refresh the BigQuery connection using new SA key
removed the table from the space data then moving it back again
created fresh agents that use that table

None of the above worked for me... I also looked for traces of a bigquery job started by my SA on the bq job history or logs in GCP, but I found nothing So right now I'm not able to understand what is happening here. Is this a known issue ? Maybe some kind of IAM propagation involved here ? Any help is appreciated ^^ NB: the only difference I can see between the two tables (one working and the other not) and all the setups that i've made so far; is that the working table name is lowercase, and the non-working table name is uppercase 😅

29 comments

Abboud Hammour
·
·
Hey Khaled K, while I'm not 100% sure what's happening here, given your last point, have you tried adding in the instructions "when querying table "x" query it in lowercase, to see if it makes a difference?
Abboud Hammour
·
·
Another thought - is this table a view on top of other tables by any chance? In this case, when selecting tables via Query Tables, you also have to select all the tables that the view is built off of
Khaled K
·
·
Hi Abboud Hammour thank you for your answer 1- yes I did rename that table to lowercase : but same resut and 2- you are right about your intuition When i first got that error of Query uses tables that are not allowed , I was indeed connecting a view. I understood that I needed to also include the tables that the view was querying but I didn't want to bias the agent with raw data (as the view is basically unnesting and preprocessing some columns) so I basically created a physical table (with a different name) that does the same job as the view and connected it while removing completely the view from the synced data in the BQ connection. But the agent kept on behaving as if it is still processing a view (thus showing the same error). I did try creating new agents, but they also behaved the same. I am wondering if there is some caching behaviour involved here ? I could try creating a new dataset that would host that physical table and create a new sync.
Abboud Hammour
·
·
Hmm I'm not 100% on caching it's possible I'll have to check, but yes I think for now your plan may be a good bet
Abboud Hammour
·
·
Other than trying a little bit later to see if caching was truly an issue
Khaled K
·
·
Hi Abboud Hammour I'm still having the same error even after trying a few things:
delete the connection and recreate it
connect a new dataset from a new gcp project
use completely different tables
I am wondering what I might be doing wrong here 🤔
added data to the connection
added the connected data to the space
added the data to the agent
my service account has the roles documented here (and I even tried with Admin, but same)
the agent is still able to read the data, infere a schema and calculate a query but I still get that Query uses tables that are not allowed error Did I miss something ?
Abboud Hammour
·
·
Hmm yes you're right, everything sems ok to me.. I guess only 2 things I can think of:
1.
If you run that same query (copy the query from Tools Inspection when trying to run) in your BQ console via that same service account, does it work?
2.
Is it possible you have a specific type of underlying storage, for example I know when it's Delta Lake tables or a diff. kind of storage there are extra permissions we need to give
Khaled K
·
·
1- yes it does, just tried with the last query the agent calculated (cf. screenshot) 2- I'm only using native BigQuery tables. Some are results of scheduled queries, some are imported from firestore backups. Some are created from loading csv files after scrapping APIs... But they all end-up written in native tables. And again, the SA with least privilege does run the query succesfully
Khaled K
·
·
and another screenshot with more robust impersonation of Service account (other than just setting the env var)
⭐1
Abboud Hammour
·
·
Got it, might be a bit out of scope for what I'm able to figure out so will have to see with the team which may take a few days. I'll create a ticket though and let you know in case of any updates
🙏1
Khaled K
·
·
Unserstood, thanks ! I'll stay tuned Also: please feel free to register me as beta tester to any upcoming BigQuery-related feature
🙏1
Khaled K
·
·
Hi Abboud Hammour this just a kind reminder about the ticket, if not already created ^^ have a great day
Abboud Hammour
·
·
Hey Khaled K! Thanks for the reminder. May take a bit of time as there is a bit of a queue but I'mm be tracking it 🙂
🙏1
Ben Gauthier
·
·
Hey Abboud Hammour Any update on this? It's a blocker for us
Sébastien Flory
·
·
Query uses tables that are not allowed
This error is coming from us when we compare the tables used in the query (from an EXPLAIN) and the allowed tables (from your dust setup) to make sure the model doesn't reach out to non allowed tables for some reason.
Sébastien Flory
·
·
NB: the only difference I can see between the two tables (one working and the other not) and all the setups that i've made so far; is that the working table name is lowercase, and the non-working table name is uppercase
This is probably the root cause, thanks for investigating. Will look for a fix.
🙌1
👍1
Khaled K
·
·
Hi Sébastien Flory thank you for your answer; actually, I have tested with lowercase tables as well but got the same result, that is unless using the uppercase view at the beginning somehow created a state of lock where that process of comparing tables got disrupted ? Is this check made in some kind of stateful approach ? and not everytime by checking the existing tables explicitly ? 🤔 (I can certainly understand the desire to economize on the read operations ^^)
Sébastien Flory
·
·
We check before running any query with the live data to make sure we have no leaks.
👍1
Sébastien Flory
·
·
It would be useful to us for debugging if you can share the query that the agent tried to execute (if you can).
Khaled K
·
·
Of course, I already did a bit further up in this thread (I have added an emoji ⭐ so you can spot it quickly) That was one of the queries the agent was producing.
Khaled K
·
·
Sébastien Flory maybe this is an important hint as well https://dustcommunity.slack.com/archives/C06SHT0F20G/p1743067551442709?thread_ts=1743006925.675239&cid=C06SHT0F20G when I first got this error, I was using a view but ever since, the error persisted even with using native tables from a different dataset, even using completely different credentials and refreshing the BQ connection
Khaled K
·
·
hello Sébastien Flory any updates please ?
Sébastien Flory
·
·
not yet sorry, deep deep in MCP but it's on my todo 🙂
👍1
Sébastien Flory
·
·
(and high on the todo list)
😊1
Khaled K
·
·
alright thanks
Sébastien Flory
·
·
Khaled K can you try again ? I might have fixed it.
Khaled K
·
·
Hi Sébastien Flory I confirm that the issue is fixed The queries are working again 🤝 For my culture, im curious to know what was the issue and the fix Thanks !
Sébastien Flory
·
·
We introduced a bug that led the list of allowed tables id to be only the table name vs the fully qualified name, which broke the comparison.
Sébastien Flory
·
·
database.dataset.table vs table
👍1