how execute a federated query in Google BigQuery with the federated query containing a variable

Question

Not to fall into the XY problem, I'll explain my use case first.

I have a Cloud SQL instance running at GCP. I want to incrementally load data from this Cloud SQL instance into Google BigQuery. the first challenge I'm facing is that the federated query contains a dynamic component (to enable the incremental fetching of data). in addition, I'm using Google Dataform for the scheduled execution of the incremental (and non-incremental) fetching of data.

To work around the 'dynamic component' problem of a federated query, I found a nice solution that involves the 'EXECUTE IMMEDIATE' statement.

So in summary, my real question is how to fetch incremental data in Google BigQuery using a dynamic federated query (to a Google Cloud Run instance).

I cannot seem to be able to combine all of this into a working solution.

This is what I have thus far, however I seem to be stuck.

The error I'm facing is; 'Syntax error: Expected keyword JOIN but got identifier "federated_query"'

Current sql in bigquery (it made be so I'm looking in the wrong direction);

-- a simplified example, but the dynamic_id' in my script comes from another query
DECLARE dynamic_id INT64 DEFAULT (0);
DECLARE sql_query STRING;
DECLARE federated_query STRING;
SET sql_query = "SELECT * FROM `table_name` WHERE `id` > "|| dynamic_id;
SET federated_query = "SELECT * FROM EXTERNAL_QUERY('connection_id', \""|| sql_query||"\")";
  
CREATE OR REPLACE TABLE `bigquery.data.set`
  PARTITION BY DATE(created_at)
  CLUSTER BY client_id, client_name, product_id
  OPTIONS(require_partition_filter=true)
  AS (
    SELECT 
      created_at, 
      client_id, 
      client_name, 
      product_id 
    FROM (
      EXECUTE IMMEDIATE federated_query
    )
  );