BigQuery scheduled queries - Create table and add date suffix to its name in a different project

Question

According to the documentation, (https://cloud.google.com/bigquery/docs/scheduling-queries#destination_table) the same project must be used when defining a destination for a scheduled query.

However, I'd like to schedule a query with the capability to write tables during the query steps to other projects (e.g., CREATE TABLE xxx.dataset.name_{run_date}) and to preserve the {run_date} as a suffix. Is it possible to do that in BQ?

Answer 1

It is indeed a limitation of Google BigQuery. A workaround could consist of scheduling a query without a specific destination table in the UI and executing the query dynamically, i.e.,

DECLARE my_table_name STRING;
SET my_table_name = other_project.my_table_ || CURRENT_DATE();

--- Create the table with the desired table name  
EXECUTE IMMEDIATE 'CREATE TABLE ' || my_table_name || ' AS SELECT * FROM `xxx` ';