PostgreSQL on Google Cloud SQL to Snowflake: 2 Easy Methods to Load Data

Unlock the full potential of your PostgreSQL on Google Cloud SQL data by integrating it seamlessly with Snowflake. With Hevo’s automated pipeline, get data flowing effortlessly—watch our 1-minute demo below to see it in action!

Google Cloud SQL for PostgreSQL, a part of Google’s robust cloud ecosystem, offers businesses a dependable solution for managing relational data. However, with the expanding need for advanced data analytics, it is required to integrate data storage and processing platforms like Snowflake. Migrating data from PostgreSQL on Google Cloud SQL to Snowflake will allow you to extract actionable insights from your data. Such insights can be crucial to drive strategic business decisions and optimize marketing strategies to improve customer experiences.

Let’s look into the different methods that can help you integrate these two platforms.

Methods to Load Data from PostgreSQL on Google Cloud SQL to Snowflake

Prerequisites

• Host name or IP address of your PostgreSQL server is available.
• PostgreSQL version of 9.4 or higher.
• An active Snowflake account.

Method 1: Automating the Data Replication Using a No-Code Tool like Hevo

Additional Prerequisites

• Whitelist Hevo’s IP addresses.
• SELECT, USAGE, and CONNECT privileges granted to the database user.
• If the Pipeline mode is Logical Replication:
  • Enable Log-based incremental replication.
  • PostgreSQL database instance is a master instance.
• Hevo is assigned:
  • USAGE permissions on data warehouses.
  • USAGE and CREATE SCHEMA permissions on databases.
  • USAGE, MONITOR, MODIFY, CREATE TABLE, and CREATE EXTERNAL TABLE permissions on the current and future schemas.
• The user must be assigned the following roles:
  • If a warehouse is to be created, ACCOUNTADMIN or SYSADMIN role in Snowflake.
  • To create a new role for Hevo, ACCOUNTADMIN or SECURITYADMIN in Snowflake.

To connect PostgreSQL on Google Cloud SQL to Snowflake using Hevo Data, here are the steps you can follow:

Step 1: Configure PostgreSQL on Google Cloud SQL as the Data Source

Step 2: Configure Snowflake as the Data Destination

Method 2: Move Data from PostgreSQL on Google Cloud SQL to Snowflake Using CSV Files

The method exports your data from Google Cloud SQL PostgreSQL as CSV files and then loads these files to Snowflake tables.

Here are the steps involved in this data migration process:

Step 1: Export Data from PostgreSQL on Google Cloud SQL as CSV Files

This involves exporting data from a database on a Cloud SQL instance to a CSV file in a Google Cloud Storage (GCS) bucket. Here are the steps to export data as a CSV file:

• Log in to your Google Cloud account. Navigate to the Console > Cloud SQL Instances page.
• Click on an instance name to open the Overview page of the instance. Then, click on Export.
• You can select Offload export to allow other operations to run while the export progresses.
• In the Cloud Storage export location, specify the name of the bucket, folder, and file that you want to export. Alternatively, click on Browse to find or create a bucket, folder, or file.

If you select Browse:

• Select a GCS bucket or folder in the Location section.
• In the Name box, select an existing file from the list in the Location section or add a name for the CSV file.
• Click Select.
• For Format, click CSV, and for the Database for export, select the database name from the drop-down menu.
• In SQL query, enter a SQL query specifying the table you want to export data from. The query must specify a table in the specified database since you can’t export an entire database in CSV format.
• Click on Export.

This will export data from your PostgreSQL instance on Google Cloud SQL as a CSV file to a GCS bucket.

Step 2: Load the CSV Files to Snowflake

Now, you must load the CSV files containing the PostgreSQL on Google Cloud SQL data to Snowflake tables. To do this, you must:

• Create a storage integration in Snowflake to access GCS buckets. Use the Snowflake UI to run the following SQL command:

CREATE STORAGE INTEGRATION integration_name
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = ‘GCS’
ENABLED = TRUE
STORAGE_ALLOWED_LOCATIONS = (‘GCS_storage_path');

This command creates a new storage integration object in Snowflake with the provided integration name. The EXTERNAL_STAGE indicates the storage integration is for use with Snowflake external stages. GCS is mentioned as the cloud storage provider. ENABLED = TRUE specifies that the storage integration is enabled. You can list the storage locations in STORAGE_ALLOWED_LOCATIONS that this integration is allowed to access. An example format of this is gcs://your_bucket_name/.

• Create a stage in Snowflake to load the data from GCS. Here’s an example SQL code:

CREATE STAGE my_gcs_stage
URL = 'gcs://your_bucket_name/path'
STORAGE_INTEGRATION = integration_name;

This will create a new external stage in Snowflake named my_gcs_stage. You must specify the location on the GCS that this stage points to. This stage is tied to the storage integration, the name of which must be provided for STORAGE_INTEGRATION.

• Use the COPY INTO command to load the data from GCS to the Snowflake target table.

COPY INTO mytable
FROM @my_gcs_stage
FILE_FORMAT = (TYPE = CSV);
ON_ERROR = CONTINUE;

This loads data from the data source specified by @my_gcs_stage to the Snowflake table named mytable. The file format is specified as CSV, and even if Snowflake encounters an error while loading the data, it will still continue the loading process.

Here’s a list of benefits associated with using CSV export/import for PostgreSQL on Google Cloud SQL to Snowflake migration:

• Using an intermediary storage solution like GCS bucket serves as a buffer. If the data loading process in Snowflake encounters any issues, the original data files will remain intact in the storage.

• It is ideal for one-time or infrequent data transfers, wherein the associated latencies won’t significantly impact the operations.

Limitations of Using CSV files to Migrate Data

• For large databases, the CSV export process in Cloud SQL can take an hour or more. During the export, you cannot perform any other operations.
• Owing to the latency involved in the export process and manual loading of data to Snowflake, it isn’t suitable for real-time data migrations.
• Using GCS storage to store large CSV files can lead to additional costs.

You can use no-code tools to overcome these limitations. Hevo’s no-code platform provides a seamless migration experience for you to replicate your GCP PostgreSQL data to Snowflake within Minutes!

What Can You Achieve With PostgreSQL on Google Cloud SQL to Snowflake Integration?

Migrating data from PostgreSQL on Google Cloud SQL to Snowflake can provide answers to the following questions:

• How to personalize marketing campaigns based on individual customer behaviors and preferences?
• Are there any gaps in the skills or knowledge of teams that could benefit from additional training?
• What is the estimated revenue that a customer will generate during the time they’re associated with the business?
• Are there any external factors, such as global trends or regional events, influencing customer preferences?
  • How can you adapt to the external factors?

Conclusion

Migrating PostgreSQL from Google Cloud SQL to Snowflake is a strategic step for businesses aiming to scale their data infrastructure and enhance analytics. Snowflake’s cloud-native architecture, along with its powerful performance and seamless integration capabilities, makes it an ideal platform for handling large datasets and streamlining data management. This migration empowers organizations to access deeper insights and optimize their data workflows.

Learn how to integrate PostgreSQL on Google Cloud SQL with Redshift to streamline your data workflows. Get practical advice for a successful and efficient connection.

To ensure a smooth migration, it’s crucial to follow best practices like planning for schema compatibility, optimizing data transfer, and implementing strong security protocols. By doing so, businesses can confidently transition to Snowflake and unlock its full potential for modern data analytics. Sign up for Hevo’s 14-day free trial and use your data to its full potential.

Frequently Asked Questions