Robust data integration and storage solutions are essential in today's data-driven community. Organizations often use Snowflake and Google Sheets as tools for data management and analysis. This tutorial will walk you through the step-by-step process of importing data from Google Sheets into Snowflake, allowing you to use your data to its fullest capacity without any hassle.

You might need to add data from various teams within your business to your data warehouse in many different circumstances. This information may include records of marketing expenses or information on inventory costs. This data will typically come from teams like operations, finance, or growth rather than your application directly or through third-party interfaces.

In today's data-driven world, organizations largely rely on data analysis to make wise decisions. Platforms for data warehousing and analytics like Snowflake have grown in popularity because of its scalability, usability, security, simplicity of integration, performance advantages, and cost-effectiveness. 

This article will guide you on how to connect Google Sheets to Snowflake's data warehouse and enhance your workflows utilizing two approaches, regardless of whether you are a data analyst, data engineer, data scientist, or someone who has to evaluate data and produce some value from it. 

[CTA_MODULE]

Introduction to google sheets

Spreadsheets function as digital worksheets with rows and columns that allow users to enter, manage, and perform mathematical operations on the data. To make data analysis easier, they offer a flexible platform for making tables, charts, and graphs.

Along with tools like Google Slides, Google Forms, Google Docs, and Google Keep, Google Sheets, a component of Google's Docs Editor suite, includes a powerful spreadsheet program.

As part of its office application suite, Google offers Google Sheets, a robust and user-friendly spreadsheet program. It functions similarly to a digital spreadsheet, letting you arrange and modify data in rows and columns.

With Google Sheets, you can carry out a variety of tasks, from constructing simple lists and tables to carrying out intricate computations, creating charts and graphs, and working in real-time collaboration with others. With this tool, you can work on your spreadsheets from any location, whether you're at home, at work, or on the go. 

Introduction to snowflake 

Snowflake is a cutting-edge, highly versatile cloud-based data warehousing technology. Consider it as a strong and safe home for all the data in your company. It is made to effectively store, organize, and analyze enormous amounts of data, assisting you in transforming unprocessed data into insightful knowledge.

Snowflake is unique due to its scalability and flexibility. It is ideal for companies of all sizes because it easily handles both small datasets and large data volumes. You can combine data from many sources, such as databases, applications, and even external data like social media feeds, using Snowflake.

A few features that distinguish Snowflake as a Software as a Service (SaaS) solution are listed below:

• Snowflake uses the SaaS (software-as-a-service) model.
• AWS (Amazon Web Services) is the foundation on which Snowflake's infrastructure is constructed, and it provides an architecture based on a new SQL database engine that enables quicker, more dynamic queries with effective data integration.
• The analytics layer connects with modern tools and services to promote and enhance Data Science exploration.

Methods to load data from google sheets to snowflake 

Prerequisites: you need the following in order to link your Google sheets to Snowflake:

• To manage your Google Sheets, you must have a Google account.
• You would need a Snowflake account in order to make your migrations since Snowflake is the destination. 
  

Method 1 : using fivetran tool 

Which authentication metthod you select will determine how Fivetran connects to Google Sheets. Two types of authentication are supported by Fivetran:

User OAuth: Google Sheets that your user has access to are accessible to Fivetran with read-only access via the User OAuth authentication technique. This is for businesses that prohibit the addition of users from other domains to Google Sheets.

Service Account: You may fine-tune who has access to your data using the Service Account authentication technique. As a read-only user, you add a special Fivetran service account to the particular sheets you want to sync.

Follow these steps to sync your Google Sheets to your location using Fivetran.

User OAuth setup guide

Step 1: choose a range 

To begin, open your Google Sheet and pick out the specific range of data that you wish to include in your destination. Remember that you can adjust this selection later if necessary.

To make your selection, you have two options:

• Manually highlight the desired range, as demonstrated below.
• Opt to select only specific columns (e.g., Sheet1!A:D). When you choose to select columns, Fivetran will generate rows only up to the last row containing data in your sheet (e.g., Sheet1!A1:D6).

Image Source

You can have as many named ranges as you wish in a single Google Sheet worksheet. The column headers in the destination table will be taken from the first row of the named range.

Step 2: create named range

• Go to Data > Named ranges in your Google Sheet.

• Give your new range a name in the Named ranges option, then click Done.

Step 3: authorize access for fivetran

• Use the OAuth procedure to authorize Fivetran.

Step 4: get the URL for your spreadsheet.

• From the address bar of your browser, copy the spreadsheet's URL.

Step 5: completing fivetran configuration 

Remember that for each named range you wish to replicate, you'll need to create a new Google Sheets connector within Fivetran.

Here's what you need to do in your connector setup:

• Choose a destination schema name that suits your preference.
• Specify the destination table name.
• Opt for "Grant User Access" as your authentication method.
• Hit the "Authorize" button.
• Insert the Sheet URL you discovered in your Google Sheets.
• From the Named Range drop-down menu, select the appropriate named range.
• Finally, click on "Save & Test." After this step, Fivetran will take over, seamlessly syncing your Google Sheets data.

Service account setup guide

Step 1: locate your fivetran email 

• Please bear in mind that for each named range you intend to replicate, you'll need to create a new Google Sheets connector within Fivetran.
• In your connector setup form, opt for "Authorize Service Account" as your chosen authentication method. This will initiate the process of creating a Google Service Account email for your Fivetran account. Once generated, take note of this automatically-generated email address. You'll require it to grant access to your

Step 2: google sheet for fivetran.

• Select a Specific Range link To begin, open your Google Sheet and choose the specific range of data that you wish to include in your destination. Keep in mind that you can adjust this selection later if necessary.
• You can select a range in either of these two ways:

• Manually highlight the desired range, following the example below.
• Opt for selecting specific columns (e.g., Sheet1!A:D). If you decide to choose columns, Fivetran will only create rows for data up to the final row containing values in your sheet (e.g., Sheet1!A1:D6).

Step 3: create a named range

• Inside your Google Sheet, head over to the "Data" tab, and then select "Named ranges."
• Go to Data > Named ranges...
• Within the Named ranges menu, provide a name for your new range and hit the "Done" button.
• Give your range a name

Step 4: share your google sheet with fivetran

• At the top-right corner of your Google Sheet, click on the "Share" button.
• Enter the email address you discovered in your connector setup form, just like the example below, and grant it "View" permissions.
• Include the service account in your Google Sheet

Step 5: locate your spreadsheet's URL

• Simply copy the URL of your spreadsheet from your web browser's address bar.
• Find the URL of your Google Sheet
• If you haven't previously created a schema with a matching name, don't worry; we'll create one for you.

Step 6: finalize fivetran setup

• In the setup form for your connector, input your desired "Destination schema name."
• Specify the "Destination table name."
• Paste the Google Sheet URL you found earlier.
• Click "Find Sheet" to verify that the Service Account has access to the spreadsheet.
• From the Named Range drop-down menu, pick the appropriate named range.
• Lastly, click "Save & Test." Fivetran will take it from here, seamlessly synchronizing your Google Sheets data.

  Advantages of using fivetran 

The following are some benefits of integrating Fivetran into your data platform:

• Enhanced efficiency: Fivetran puts efficiency first, simplifying data integration and eliminating the need for time-consuming procedures. Fivetran enables businesses to concentrate their time and resources in investigating and gaining insights from data rather than being weighed down by laborious integration procedures by automating tasks like data extraction, transformation, and loading (ETL).
• Easy connectivity: Fivetran integrates diverse data sources in an effortless manner, making integration simple and trouble-free. The wide range of pre-built connectors, which cover databases, cloud apps, and marketing platforms, guarantees interoperability and makes it possible to examine your data as a whole.
• Fivetran's virtually real-time data synchronization collects the most recent data source updates, giving you an accurate and current representation of your data.
• Data transformation is made easier by Fivetran thanks to its built-in procedures and careful schema mapping. This guarantees that data structures are changed smoothly, removing complexity and guaranteeing compatibility and consistency. Organizations may realize the full potential of their data thanks to this accelerated transformation procedure.
• Scalability for Growth: As businesses grow, Fivetran's scalable architecture can handle rising data volumes. In addition to supporting rising needs without sacrificing speed, it offers a solid basis for data integration and guarantees efficient operations as data ecosystems develop.

Method 2 : using custom ETL scripts 

In this approach, we'll utilize Python tocreate custom ETL scripts and transfer data from Google Sheets to Snowflake. To achieve this, follow these steps:

• You'll need to enable public access to your Google Sheets. You can do this by navigating to "File" >> "Share" >> "Publish to the web."
• Once you've published it to the web, you'll obtain a link that follows this format:

https://docs.google.com/spreadsheets/d/{your_google_sheets_id}/edit#gid=0

• To access and manipulate this data, ultimately converting it into a dataframe and sending it to Snowflake, you'll need to install specific libraries. This includes pandas, snowflake.connector, and pyarrow.
• You can install pandas using the command "pip install pandas." Similarly, you can install the Snowflake connector by running "pip install snowflake-connector-python," and pyarrow can be installed with "pip install pyarrow."
• Next, you can read the data from your Google Sheets by executing the following code:

import pandas as pd

data=pd.read_csv(f'https://docs.google.com/spreadsheets/d/{your_google_sheets_id}/pub?output=csv')

Remember to substitute "{your_google_sheets_id}" with the actual id of your spreadsheet. You can get an initial look at the data by running "data.head()" and assess the number of columns and records with "data.shape."

‍

Setting up snowflake credentials

You must set up a data warehouse, a database, a schema, and a table in your Snowflake account. Run the following commands in your Snowflake console to achieve this.

CREATE WAREHOUSE DATA_WH;

CREATE DATABASE DATA_DB;

CREATE SCHEMA DATA_SCHEMA;

CREATE USER DATA_USER;

‍

CREATE TABLE DATA_SCHEMA.DATA_TABLE(

ID INT PRIMARY KEY,

TITLE VARCHAR(200),

SOURCE VARCHAR(20),

RELEASE_YEAR VARCHAR(20),

CATEGORY VARCHAR(20),

COMPANY VARCHAR(20),

NA_REVENUE FLOAT,

EU_REVENUE FLOAT,

JP_REVENUE FLOAT,

OTHER_REVENUE FLOAT,

GLOBAL_REVENUE FLOAT

)

Loading data into Snowflake

To load the data into Snowflake in Python, use the Snowflake connector that was previously deployed. You can accomplish this by running the code block below.

import snowflake.connector from snowflake.

connector.pandas_tools import write_pandas

def write_to_snowflake(input_data):

    sf_conn = snowflake.connector.connect(

                    user='your_username',

                    password='your_password',

                    account='your_account',

                    warehouse='DATA_WH',

                    database='DATA_DB',

                    schema='DATA_SCHEMA'

                    )

    columns = []

    for column in input_data.columns:

        columns.append(column.upper())

    input_data.columns = columns

    write_pandas(sf_conn, input_data, table_name='DATA_TABLE', database="DATA_DB", schema='DATA_SCHEMA')

‍

When you execute the "write_to_snowflake(data)" function, it will import all your data into your Snowflake data warehouse. To check the data, you can perform the following:

To preview the first 10 records, use the query:

SELECT * FROM VIDEO_TABLE LIMIT 10

You can also find out how many records were imported by running:

SELECT COUNT(*) FROM VIDEO_TABLE

It's evident that all your data has been successfully loaded into the Snowflake data warehouse. Now, you have the option to schedule your Python jobs to run at regular intervals, be it daily or hourly, using an orchestration tool like Apache Airflow or Prefect.

However, please be aware that this method comes with security risks because the Google Sheet was made public.

Disadvantages of using ETL scripts 

Using ETL (Extract, Transform, Load) processes to integrate data from sources such as Google Sheets to Snowflake can present a number of obstacles and disadvantages, particularly for enterprises with limited resources or knowledge.

• The first consideration is price. Implementing and maintaining ETL methods can be costly. It necessitates investments not only in technology but also in trained employees who can effectively design, develop, and manage these processes.
• Another issue is complexity. ETL operations can be complicated and difficult to set up correctly. Organizations that lack the appropriate skills may struggle to handle data conversions and integrations correctly.
• Furthermore, ETL procedures have flexibility and scalability constraints. They may struggle with unstructured data or deliver real-time data streams, rendering them unsuitable.
  

Conclusion 

While there are various ways to connect your Snowflake data to your Google spreadsheets, the two approaches described in this post are the most convenient.

Simplifying the Snowflake to Google Sheets connection procedure eliminates the time-consuming and energy-draining components of importing, exporting, and syncing your data. You're better off automating key elements of the process to gain insights, prepare reports, and execute analysis more efficiently. As a result, your company's workflows are optimized, enhancing productivity and effectiveness.