We are using BigQuery and SNOWFLAKE(Azure hosted) and we often export data from big query and import to SNOWFLAKE and vice versa. is there any easy way to integrate both systems like automatically sync big query table to SNOWFLAKE rather than exporting to file and importing ?
3 Answers
You should have a look on Change Data Capture Solutions for automate sync. Some of them got native Big Query and Snowflake connectors.
Some examples :
- HVR
- Qlik Replicate
- Striim
- ...
There are many ways to implement this, and the best one will depend on the nature of your data.
For example, if every day you have new data in BigQuery, then all you need to do is set up a daily export of the new data from BigQuery to GCS. Then it's easy to set up Snowflake to read new data in GCS whenever it shows up with Snowpipe:
But then how often do you want to sync this data? Is it append only, or does it need to account for past data changing? How do you solve conflicts when the same row changes in different ways on both sides? Etc.
I have the same scenario. I've built this template in Jupyter Notebook. I've done a gap analysis after a few days and, at least in our case, it seems that Firebase/Google Analytics adds more rows to the already compiled daily tables even a few days later. We have about 10% more rows in a BigQuery older Daily than what was captured in Snowflake so be mindful of the gap. To this date, the template I've created is not able to handle the missing rows. For us it works because we look at aggregated values (daily active users, retention...etc) and the gap there is minimal.
Related