Migrating a Segment dataset in BigQuery
We have several Segment datasets in BigQuery which we wanted to rename. Some we named wrong in the past,
and people had trouble finding the right dataset, on others we used special characters incompatible with BigQuery schema,
and Segment picked a weird name like
This begs a question - what's the right way to do it?
I couldn't find it in the docs, but Segment's support promptly responded I needed to take 4 steps: disable the sync, rename the dataset, rename it in Segment & reenable the sync.
It turned out a little more complicated - so here's some extra detail.
1. Disable the warehouse destination for the source you wish to rename
Make sure no sync is running, and just click the switch. Easy enough.
2. Rename the dataset in BigQuery
Mind you, renaming the dataset is not really a thing. But you can definitely create a new dataset with the desired name, and copy the data over.
This is easy enough in the UI. Just click your project, and create a new dataset with the new name. Then go to the old dataset, and copy it over to the new one.
3. Re-create the views
Segment BigQuery exports come with raw data with duplicates in tables, and then deduplicated data in views. The views also have an annoying property of only showing the last 60 days of data. The copy above doesn't copy the views, so we need to recreate them.
This might get a bit tedious to do by hand, so let's automate it.
3.1 Create a BigQuery integration
On the left side in the Integrations, create a new BigQuery integration and copy your service account json with sufficient permissions.
3.2 Create all the views
4. Rename the destination table in Segment
You need to go to your source, then Settings, then SQL Settings, and change the Schema Name.
5. Switch it back on
Now go back to the BigQuery destination settings, and switch it back on for the desired source.
6. The gotcha - IAM
I did all this, and waited for the next sync to happen. It failed on insufficient permissions.
Now perhaps it's because I didn't follow the guide right during setup or it changed since I first did it,
but our service account for segment had a role
BigQuery User, and it managed to create its own tables so far,
but after the migration it failed on writing to existing ones.
Now the guide recommends
BigQuery Data Owner and
BigQuery Job User, and once I updated this, the next sync ran fine. It also backfilled the day it missed because of the permission issue.
So just double check if you have the right IAM settings for the service account.