Currently I have a table that saves data quality results (using Dataplex), in this table it leaves me a query to see the data that does not meet the quality rule. Example:
In order to know which fields do not comply with the rule defined in Data Quality, I must execute that query. How can I read that field, execute the query and save the data in a table to view in looker?
I was thinking about cloud function, but should I make one for each table?
You have multiple way to achieve it, but you must use a procedural language.
Indeed, you can use Cloud Functions, or Cloud Run, to run a first query to get all the query-strings, and then iterate over this list to execute each query one by one (be careful to the timeout if you have long and/or many queries. If you are in this case, drop a comment, I will explain you how to scale out).
The other option is to use procedural language in BigQuery. Same as before, perform a first query to get the query-strings, iterate over this list and use the execute immediate operation to run a query from a string. In that case, you won't be able to scale out easily, and the SQL procedural language is much more unfamiliar and difficult to read/write/debug/improve.
In both cases, if you need to store the output in a table to reuse the result in dataviz tool, you have to wrap your query-string in a "INSERT INTO .... " query.
