Migrating from Heroku PostgreSQL to Snowflake: Top 3 Methods

Easily move your data from Heroku PostgreSQL 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!

In today’s data-rich world, businesses must select the right data storage and analysis platform. For many, Heroku PostgreSQL has long been a trusted solution, offering a reliable relational database service in the cloud. However, as organizations grow, Heroku PostgreSQL becomes more expensive and has slower performance when handling large data volumes compared to other cloud-based platforms. Since the Heroku platform for Postgres databases is standardized, you have limited customization options for your application. The solution to this problem is migrating to a cloud-based data warehousing platform like Snowflake.

Snowflake’s architecture offers unparalleled scalability, elasticity, and performance for data storage and analytics workloads of any size or complexity.

Let’s look into migrating data from Heroku PostgreSQL to Snowflake.

Heroku PostgreSQL: A Brief Overview

Heroku is a cloud platform that allows the direct deployment of database applications and email services. Heroku PostgreSQL is Heroku’s powerful database service that is compatible with PostgreSQL. With its fully managed service, you can focus more on building applications based on your data than database management tasks.    

Heroku PostgreSQL is known for its reliability, extensibility, and robustness. It provides features including continuous protection, auto-scaling, and automated health checks.  

You can access PostgreSQL instances on Heroku using any of its officially supported languages with a PostgreSQL driver and manage them efficiently with Heroku CLI commands.

Read more about Heroku Kafka Deployment.

Snowflake: A Brief Overview

Snowflake is a software-as-a-service (SaaS) that offers a unified data analytics platform for data warehousing, data lakes, and application development. All the hardware and software components can execute on a cloud infrastructure such as AWS, Azure, or GCP.

Snowflake employs a shared-disk architecture that maintains a central data repository accessible from all virtual compute instances. This allows your organization to scale the storage and compute resources independently based on your needs. 

To connect with Snowflake, you can choose a web-based interface, OBDC or JDBC drivers, SnowSQL CLI, and native connectors such as Python or Spark.

Why Migrate Data from Heroku PostgreSQL to Snowflake?

When you migrate from Heroku PostgreSQL to Snowflake, your organization can drive innovation and insights from their data. Here are a few reasons to migrate to Snowflake:

• Compatibility with Existing Infrastructure: Snowflake’s native support for semi-structured data, such as CSV files, ensures smooth integration into existing data workflows. This eliminates the need to reconfigure the existing infrastructure before adopting Snowflake.
• Enhanced Cloud Security: Snowflake provides the highest level of security for financial or any other organizations that deal with sensitive data using a Virtual Private Snowflake(VPS). It is a separate Snowflake environment that does not share data with others outside the VPS.  

Methods for Data Migration from Heroku PostgreSQL to Snowflake

To convert Heroku PostgreSQL to Snowflake, you can utilize Hevo Data, SQL utilities, and Python modules.   

Method 1: Export Data from Heroku PostgreSQL to Snowflake File Using Hevo Data

Prerequisites

• An active Heroku account with a PostgreSQL database instance.
• Retrieve PostgreSQL database credentials.
• You must assigned a Pipeline Administrator, Team Collaborator, or Team Administrator role in Hevo.
• An active Snowflake account or create one.
• Create and configure a new Hevo role in your Snowflake Destination using Hevo’s ready-to-use script.
• Retrieve the URL of your Snowflake account.
• You must be assigned a Team Collaborator or an administrator role in Hevo, excluding the Billing Administrator role.

Let’s see how to integrate Heroku PostgreSQL into Snowflake using Hevo Data.

Step 1: Configure Heroku PostgreSQL as Your Source Connector

1. Navigate to the Select Source Type page and choose Heroku PostgreSQL as your source type.
2. Provide the required information in the Configure your Heroku PostgreSQL Source page.

3. Click the TEST CONNECTION > TEST & CONTINUE button to complete the source configuration.

Step 2: Configure Snowflake as Your Destination Connector

Here are the steps to set up Snowflake as your destination in Hevo:

1. In the Add Destination page, select Snowflake as your destination type.
2. Specify the mandatory fields on the Configure your Snowflake Destination page.

3. Click the TEST CONNECTION > SAVE & CONTINUE to complete the destination configuration.

Some of the key features of Hevo Data are given below:

• Data Transformation: Analyst-friendly data transformation approaches, such as Python-based transformation scripts or drag-and-drop transformation blocks, allow you to clean, prepare, and transform data before importing it to the desired destination.
• Incremental Data Load: Hevo Data facilitates real-time data transfer, optimizing bandwidth utilization at both ends of the data pipeline.
• Auto Schema Mapping: Hevo’s Auto Mapping feature eliminates the tedious task of managing schema manually. It identifies and transfers the incoming data format to the desired destination schema. You can choose either full or incremental mappings based on your data replication needs.

Method 2: Transfer Heroku PostgreSQL Data to Snowflake Using SQL Utilities

For this migration, you must export the data from Heroku PostgreSQL to a CSV file using the psql utility. Then, using the SnowSQL CLI commands, load the staged CSV file into the Snowflake table. 

Perform the following steps to transfer Heroku PostgreSQL to Snowflake using SQL utilities:

1. Connect to your Heroku PostgreSQL database with psql using the following command:

heroku pg:psql -a <app-name>

2. Sign into the psql utility and run the following command to copy the data to a local CSV file:

\\copy sample_data TO sample_db.csv WITH (FORMAT CSV);

3. Log in to SnowSQL CLI using the following command:

snowsql -a <account_name> -u <user_name> -d <snowflake_db>

4. Enter your Snowflake password and create a new database in your Snowflake warehouse using the following command: 

CREATE DATABASE IF NOT EXISTS UTILS_DATABASE

5. Use the following command to activate the current database:

USE DATABASE UTILS_DATABASE

6. Create a table that matches the source table using the following syntax. Replace the placeholders with your specific credentials.

CREATE OR REPLACE TABLE tableName (column_name1 datatype, column_name2 datatype, column_name3 datatype,......., column_name4 datatype)

7. Run the following command to put the CSV file into a staged file:

PUT file://sample_db.csv @%sample_data;

8. Load the staged file into the main table using the COPY INTO command:

COPY INTO sample_data FROM @%sample_data FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '\"');

With the Heroku and SnowSQL CLI commands, you can transfer the Heroku PostgreSQL data to Snowflake tables.

Limitations of Heroku PostgreSQL to Snowflake Migration Using SQL Utilities

• High Complexity: When migrating to Snowflake with CLI commands, you may encounter potential complexity in mastering the command-line interfaces of both platforms. This may require extra resources and time for training, especially for individuals who are not proficient with command-line tools.
• Data Leakage: The plain text format allows CSV files to be easily read during data migration from Heroku PostgreSQL to Snowflake. This vulnerability could result in data leakage if the data is sensitive.  

Method 3: Load Heroku PostgreSQL Data to Snowflake Using Python Modules

If you are a Python expert, you can alternatively utilize psycopg, a popular PostgreSQL adapter for Python, to extract data from Heroku PostgreSQL. Then, import the extracted data into Snowflake using its own Python connector. Let’s look at the steps for using Python utilities:

1. Open your command line interface and install psycopg2 and snowflake-connector-python modules using the pip package manager.
2. Write the following code in a new Python file: 

import psycopg2
import snowflake.connector
import csv
postgresConnection = psycopg2.connect(
    database=<database_name>,
    user=<user_name>,
    password=<password>,
    host=<host_name>,
    port='8866'
)
postgresCursor = postgresConnection.cursor()
postgresCursor.execute("""SELECT tableName FROM information_schema.tables
      WHERE table_schema = 'public'""")
tableName = cur.fetchall()[0][0]
postgresCursor.execute("""select *
              from information_schema.columns
              where table_schema NOT IN ('information_schema','pg_catalog')
              order by table_schema, tableName""")
schema = "("
for row in cur:
   if row[7] == 'character varying':
       schema += f"{row[3]} string, "
   elif row[7] == 'integer':
       schema += f"{row[3]} {row[7]}, "
schema = f"{schema[:-2]})"
postgresCursor.execute("SELECT * FROM usersdemo")
extract_rows = postgresCursor.fetchall()
with open('usersdemo.csv', 'w') as f:
    writer = csv.writer(f)
    writer.writerows(extract_rows)
postgresCursor.close()
postgresConnection.close()

3. Once you run this code, you will obtain a CSV file containing all the data that is ready to import into Snowflake.
4. Update the existing Python file to load the data in CSV format to Snowflake using the following code:

snowflakeConnection = snowflake.connector.connect(
    user=<user_name>,
    password=<password>,
    account=<account_name>
)
snowflakeCursor = snowflakeConnection.cursor()
databaseName = 'python_database' snowflake_cursor.execute(f"CREATE DATABASE IF NOT EXISTS {databaseName}")
snowflakeCursor.execute(f"USE DATABASE {databaseName}")
tableName = 'usersdemo'
snowflakeCursor.execute(
   "CREATE OR REPLACE TABLE "
   f"{tableName}{schema}")
snowflakeCursor.execute(
    f"PUT file://usersdemo.csv @%{tableName}")
snowflakeCursor.execute(
    f"COPY INTO {tableName} FROM @%{tableName} FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '\\"')")
snowflakeCursor.close()
snowflakeConnection.close()

Limitations of Heroku PostgreSQL to Snowflake Migration Using Python Modules

• Lack of Real-time Integration: When working with Python, any updates to the Heroku PostgreSQL databases will not be reflected in Snowflake until the next migration is performed. This delay affects the decision-making process that relies on up-to-date data. 
• Security Concerns: Storing database credentials directly in the Python code poses security risks, especially when the code is shared in a version control system like Git.  

Use Cases of Heroku PostgreSQL to Snowflake Migration

• Ad-hoc Analysis: When migrating to Snowflake, your organization can implement data warehouses more quickly to perform ad hoc analysis by executing SQL queries.     
• Micro-Partitioned Data Storage: Unlike traditional static partitioning, Snowflake tables automatically partition data into micro-partitions, with contiguous storage units ranging from 50 MB to 500 MB in size. 

Conclusion

Heroku PostgreSQL to Snowflake migration offers organizations a pathway to improved data management and analytics capabilities. Whether performing ad-hoc analysis or optimizing data storage, migrating Snowflake allows you to unlock new scalability, performance, and data integrity levels in your operations.

In this article, you have learned how to utilize Hevo Data, SQL utilities, or Python modules to integrate Heroku PostgreSQL to Snowflake. The SQL method is only suitable for smaller datasets and is accompanied by security considerations.  If you use Python utilities, you must be highly proficient in Python coding to import data into Snowflake from Heroku Postgres.  

Utilizing pre-built connectors in Hevo is the ideal solution for quickly migrating large data volumes with real-time capabilities. 

To learn more about SnowSQL, read Snowflake SQL Command-Line Client. 

Frequently Asked Questions (FAQs)