How to Migrate from BigQuery to Snowflake Easily?

To manage vast amounts of data, teams generally use multiple data warehouses. However, data replication between two or more data warehouses may be required at times.

For instance, assume that your sales data in your US-based office is stored in BigQuery, while your marketing data in your India-based office is saved in Snowflake. You can connect BigQuery to Snowflake to acquire a comprehensive perspective and provide insights to aid in data-driven decision-making. 

This blog post is a walkthrough of 3-easy-steps to establish a connection between BigQuery and Snowflake using Hevo’s Automated Data Pipeline Platform.

What is BigQuery?

BigQuery is a serverless, fully managed, and highly scalable data warehouse that allows one to analyse vast amounts of data. It will enable anyone to run super-fast SQL queries with the raw processing power of Google’s infrastructure.

Here are some of the critical features of BigQuery:

• Serverless: No need to manage infrastructure or perform resource provisioning.
• Scalable: Can handle massive datasets, scaling to petabytes and beyond.
• SQL Queries: Supports standard SQL, making it accessible for those familiar with SQL.
• Fast Performance: Uses a distributed architecture to process queries quickly.
• Integration: Easily integrates with other Google Cloud services and third-party tools.

What is Snowflake?

Snowflake is a cloud-based data warehousing platform known for its unique architecture, which permits work on large-scale data processing and analytics.

Here are some of the critical features of Snowflake:

• Cloud-Native: Designed for the cloud, it can run on major platforms like AWS, Azure, and Google Cloud.
• Separation of Storage and Compute: Allows independent scaling of storage and compute resources, providing flexibility and cost efficiency.
• Performance: Optimized for high performance, enabling fast query execution even with large datasets.
• Concurrency: Handles multiple concurrent users and queries without performance degradation.

Prerequisites

• Snowflake account
• Billing enabled BigQuery account
• The bucket and dataset that need to be moved are present in BigQuery. For help loading data into BigQuery, refer to Hevo’s blog.

Why Replicate data from BigQuery to Snowflake?

Replicating data from BigQuery to Snowflake can be due to several business or technical requirements. Some of the requirements can be:

• Performance Optimization: Because Snowflake is architected differently, many workloads may execute far more efficiently on Snowflake. Replicated data can benefit from those performance optimisations Snowflake sends for specific scenarios.
• Cost Management: BigQuery charges both for storage and query processing; its costs rapidly get prohibitively costly for some query types. On cost management, Snowflake’s independent scaling of storage and computing could, in the future, provide lower cost choices for individual use cases.
• Disaster Recovery: Having data on both platforms creates redundancy and backup options, guaranteeing business continuity if one of the platforms experiences an outage or data corruption.
• Data Integration and Sharing: Snowflake has robust data-sharing features that make it easier to share data with other partners, customers, or even various departments of the company. Replicating data from BigQuery to Snowflake can make data sharing easier and more secure.

Migrate from BigQuery to Snowflake

Method 1: Replicate Data from BigQuery to Snowflake using Hevo

Step 1: Configure BigQuery as a Source

Authenticate and configure your BigQuery source.

Step 2: Configure Snowflake as a Destination

Now, we will configure Snowflake as the destination.

Congratulations! You’ve successfully constructed your data pipeline with  BigQuery as your data source and Snowflake as your data destination. The Hevo data pipeline will replicate new and updated data from your BigQuery every five minutes. But, depending on your requirements, the data replication frequency can be customized — from 5 minutes to 24 hours.

Data Replication Frequency

Default Pipeline Frequency	Minimum Pipeline Frequency	Maximum Pipeline Frequency	Custom Frequency Range (Hrs)
5 Mins	5 Mins	24 Hrs	1-24

Method 2: Connecting BigQuery to Snowflake Manually

Step 1: Move Data from BigQuery to Google Cloud Storage

We need to move the data to GCS so that we can load it to Snowflake

• Open BigQuery Console
• Export the Data using the bq tool
• Upload the Dataset into GCS Bucket

Step 2: Configure a snowflake storage integration 

To create a new storage integration, run the following command:

create or replace storage integration gcs_int

type = external_stage

storage_provider = 'GCS'

enabled = true

storage_allowed_locations = ('gcs://test_bucket_hevo/');

Step 3: Retrieve the cloud storage service account for our Snowflake account

To get the details of your integration, run the following command;

desc storage integration gcs_int;

Step 4: Grant the service account permission to access the bucket object

Step 4. a) Go to the Google Cloud Console and navigate to IAM and Admin >> Roles

Step 4. b) Create a role

Fill in the required fields and then click on Add permissions

Step 4. c) Add these few permissions and click on Create

Step 5: Assign the role to the cloud storage service account

Step 5. a) Go back to cloud storage and select the bucket

Step 5. b) Click on add permissions 

Step 5. c) Click on Add Principal and select the service account that you got in Snowflake

Step 6: Create an external stage

Step 6. a) Go back to the snowflake worksheet and create a role using the command

create role my_role;

Step 6. b) Next, we have to create a stage for this role that uses the command.

grant create a stage on schema public to role my_role;

Step 6. c) Now grant access for the integration to the role using the command.

grant usage on integration gcs_int to role myrole;

Step 7: Create a CSV file format 

Use the command below to create a CSV file format to load your data from Google Cloud Storage.

Create or replace file format my_csv_format

type = csv

field_delimiter = ‘,’

skip_header = 1

Error_on_column_count_mismatch = false;

Step 8: Create a stage

Create stage my_gcs_stage

url = ‘’

Storage_integration = gcs_int

file_format = my_csv_format;

Note: U can check the details of your stage using the command

list @my_gcs_stage;

Step 9: Create a table in snowflake 

CREATE TABLE your_schema.your_table_name (

  id INT,

  timestamp DATETIME,

  col3 INT,

  col4 INT,

  amount FLOAT,

  status STRING,

  description STRING,

  bank STRING

);

Step 10: Copy data from Google Cloud Storage to Snowflake

copy into <table_name>

from @my_gcs_stage

file_format = my_csv_format

on_error = continue;

Learn More About:

Deep Dive into Snowflake Replication

Final Thoughts

We hope we’ve helped you add value to your quest. This article explored two methods for replicating data from BigQuery to Snowflake. We used Hevo, an automated data pipeline, to obtain our desired results, making the BigQuery to Snowflake data replication process much faster and fully automated.

FAQ BigQuery to Snowflake

Check out this video to know how Hevo seamlessly replicates data from vast data sources.

Initiate your journey with Hevo today and enjoy fully automated, hassle-free data replication for 150+ sources. Hevo’s free trial gives you limitless free sources and models to pick from, support for up to 1 million events per month, and a spectacular live chat service supported by an incredible 24/7 support team to help you get started.