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 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.
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.
Step 2: Configure Snowflake as a Destination
Now, we will configure Snowflake as the destination.
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 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
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
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;
Load your Data from BigQuery to Snowflake within minutes
No credit card required
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.
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 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.