Easily move your data from BigQuery to Snowflake to enhance your analytics capabilities. With Hevo’s intuitive pipeline setup, data flows in real-time—check out our 1-minute demo below to see the seamless integration in action!

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 Logo

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 Logo

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.

Curious about costs? Check out the Snowflake Pricing Calculator for accurate estimates.

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.

BigQuery to Snowflake: Configure your BigQuery Source

Step 2: Configure Snowflake as a Destination

Now, we will configure Snowflake as the destination.

BigQuery to Snowflake: configure Snowflake as the destination
Methods to Migrate from BigQuery to Snowflake

Method 1: Using Hevo to Migrate from BigQuery to Snowflake

Skip the complexity of coding and multiple setup steps. With Hevo, you can seamlessly migrate data from BigQuery to Snowflake in minutes. Simplify your data integration process effortlessly!

Method 2: Using Manual SQL Scripts to Migrate from BigQuery to Snowflake

Manually migrating data from BigQuery to Snowflake using SQL scripts can be complex and time-consuming. Instead, use a step-by-step approach to ensure a straightforward and efficient data migration solution. Follow our simple steps to establish your connection with ease!

Start your Migration with Hevo Today!

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 FrequencyMinimum Pipeline FrequencyMaximum Pipeline FrequencyCustom Frequency Range (Hrs)
5 Mins5 Mins24 Hrs1-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/');
Integration Successfully Created

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;
GCP Service Account Credentials

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

Create Role IAM and Admin

Step 4. b) Create a role

Fill in the required fields and then click on Add permissions

Adding Permissions

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

Add Permissions

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

Load Data from BigQuery to Snowflake
Load Data from BigQuery to Redshift
Load Data from MySQL to Snowflake

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

Add Permissions to Bucket

Step 5. b) Click on add permissions 

Add Principal as Service Account

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

Added Service Account

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;
Final Table Output

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

How do you import data from BigQuery to Snowflake?

To import data from BigQuery to Snowflake, follow these general steps:
1. Export data from BigQuery
2. Transfer data to Snowflake
3. Create Stage in Snowflake
4. Load data into Snowflake

How do you migrate data from SQL to Snowflake?

To migrate data from an on-premises SQL database (like MySQL, PostgreSQL, SQL Server) to Snowflake, you can use one of the following methods:
1. Export and Load
2. ETL Tools
3. Snowflake Connector

Is Snowflake better than BigQuery?

Snowflake might be better if you need flexibility in resource scaling, cross-cloud compatibility, and robust data-sharing capabilities.
BigQuery might be better if you prefer a fully managed service with no infrastructure to manage and strongly prefer or rely on the Google Cloud ecosystem.

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

Hevo Product Video

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.

Kamya
Marketing Analyst, Hevo Data

Kamya is a dedicated data science enthusiast who loves crafting comprehensive content that tackles the complexities of data integration. She excels in SEO and content optimization, collaborating closely with SEO managers to enhance blog performance at Hevo Data. Kamya's expertise in research analysis allows her to produce high-quality, engaging content that resonates with data professionals worldwide.