How to migrate data from BigQuery (marketplace) transfers to BigQuery (Hub)

If you have historical data in BigQuery transfers created from the Google Marketplace version of BigQuery and are looking to start using BigQuery directly from the Supermetrics Hub, follow these steps to continue updating your historical data with the BigQuery transfer from the Supermetrics Hub.

Instructions

1. Create a dataset in BigQuery SQL Workspace.
   The historical data will be migrated to this dataset, and the BigQuery (Hub version) transfer will continue to update the data.
2. Follow these instructions for setting up the prerequisites for a BigQuery destination.
   These include a Google Cloud service account and service account key, which are used to connect Supermetrics with your BigQuery dataset.
3. Create a destination on the Supermetrics Hub following the instructions in this guide. Use the dataset name and service account key created above.
4. Create a data warehouse transfer from the Supermetrics Hub to BigQuery.
   Make sure to use the same schema, data source settings, and account selection as you have in the BigQuery (marketplace) transfer. Point the transfer to the destination created in step 3.
5. After you have created a transfer, run a short (today + yesterday) backfill to check the data lands in the dataset created in step 1 as expected.
6. Verify that the data in the new dataset looks consistent and has the same columns as the historical data in the tables updated by the BigQuery (marketplace) transfers. 
7. Copy the historical data from the BigQuery (marketplace) tables to the BigQuery (Hub) tables. The SQL script below merges the historical data from the BigQuery (marketplace) tables to the BigQuery (Hub) tables. The script removes overlapping dates while preserving the data in the partitioned table that the BigQuery (Hub) transfer updates. You'll need to repeat the script for each table in your transfer.

   INSERT INTO `dataset_name.bq_hub_table_name`
       SELECT * FROM `dataset_name.bq_marketplace_table_name_*`
           WHERE DATE NOT IN (
               SELECT DATE FROM `dataset_name.bq_hub_table_name`
           )

8. Pause your BigQuery (marketplace) transfers. In the future, when you see that the old data is migrated and the new BigQuery transfer from the Hub updates the merged dataset, you can remove all data from the old sharded tables following the instructions in this guide.

Note that any downstream processes or visualization tools will need to be repointed to the new dataset.