PostgreSQL Native Application Private Preview

PostgreSQL is a native application from Fivetran that is available in the Snowflake Marketplace. The application allows you to seamlessly sync data from your PostgreSQL databases directly into your Snowflake environment by using Fivetran's data integration features.

The PostgreSQL application runs within Snowflake's infrastructure. This ensures that your data remains secure and compliant with your organization's policies. With the PostgreSQL application, you can leverage Fivetran's orchestration and data integration capabilities without moving the data through Fivetran cloud. You can configure and monitor the connection using the Fivetran dashboard. We only collect some metadata from your Snowflake environment where the application is running to understand the data pipeline's health and to display the details in the dashboard.

Key capabilities

• Seamless integration: The application integrates directly with Snowflake, allowing you to activate and configure using the Snowflake user interface.
• Automated data sync: The application enables automated and scheduled data syncs between PostgreSQL and Snowflake, including initial and incremental syncs.
• Enhanced security: Data moves directly from your PostgreSQL database to your Snowflake destination. Only essential metadata is excahnged between your Snowflake environment and Fivetran cloud.

Setup guide

This section provides step-by-step instructions on how to install, configure, and manage the PostgreSQL app.

Prerequisites

To install the PostgreSQL app and connect your PostgreSQL database to Snowflake, you need the following:

• Snowflake account:
  • A Snowflake account hosted on Amazon Web Services (AWS) or Microsoft Azure (Azure) and provisioned in one of the regions supported by Snowflake. For more information, see Snowflake documentation.
  • The Snowflake account must be associated with a user role that has the necessary permissions to create a user, role, and warehouse for Fivetran (such as the sysadmin or securityadmin roles).
  • To install the native app, the user must use the ACCOUNTADMIN role or another role with the IMPORT SHARE and CREATE DATABASE privileges.
• Collation requirements: Your Snowflake data warehouse's collation should be capable of handling both case-sensitive and non-ASCII characters to ensure the integrity of your data.

  NOTE: We do not recommend applying custom collation to your account, database, schema, table, or column. Instead, use collation directly in your queries. Custom collation reduces column storage (STRING) from 16 MB to 8 MB. If we attempt to load more than 8 MB of your data into a column with collation enabled, you will encounter a String is too long to be compared using collation error.

• Snowflake configuration parameters: The PREVENT_LOAD_FROM_INLINE_URL and REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_OPERATION parameters of your Snowflake data warehouse must be set to FALSE.
• Fivetran role:
  • For existing Fivetran users: The Destination Administrator role with permissions to create destinations.
  • For new Fivetran users: During the sign-up process, a new user with the Account Administrator role will be created automatically. By default, this role has the permissions to create destinations.

IMPORTANT: In Snowflake, if you use double quotes around an identifier name, it makes the identifier name case-sensitive. We recommend using the create <identifier> <identifier_name> or the create <identifier> "IDENTIFIER_NAME" format. For more information, see Snowflake documentation.

Setup instructions

Install application

1. Log in to your Snowflake account.
2. On the navigation menu, go to Data Products > Marketplace.
3. Search for the PostgreSQL app and click Get next to the app's name.
4. In the pop-up dialog, click Options and enter an Application name. Make a note of the name you specify for the app. You will need it to provide access to your PostgreSQL database.
5. Select the warehouse that you want to use to install the app.
6. Click Get to add the app to your Snowflake account.

Activate application

1. On the navigation menu, go to Data Products > Apps.
2. In the Installed Apps section, select the PostgreSQL app you have installed.
3. In the Grant Account Privileges section, click Grant to provide the permissions necessary for the app to load your data into Snowflake.
4. In the Allow Connections section, click Review.
5. Review the endpoints and network rule that will be used by your data pipeline to communicate with Fivetran.
6. Click Connect.
7. Click Activate to deploy the app and Fivetran components in your Snowflake account. Once activation is complete, the app’s homepage will display detailed information about its status and settings.

   NOTE: Activation may take several minutes.

Provide access to PostgreSQL database

1. On the navigation menu, go to Projects > Worksheets.

2. In the top right corner, click the Create SQL worksheet icon to create a new worksheet.

3. In the drop-down menu, select the Snowflake database and schema where you want us to load your data.

4. Copy the following SQL command to the worksheet:

   BEGIN;
       SET application_name = '<APPLICATION_NAME>';
       SET network_rule = concat($application_name, '_APP_DATA.CONFIGURATION.', $application_name, '_CONSUMER_EXTERNAL_ACCESS_NETWORK_RULE');
       ALTER NETWORK RULE identifier($network_rule) SET VALUE_LIST=('0.0.0.0:443','0.0.0.0:80','<POSTGRES_DATABASE_ADDRESS>:<PORT>');
   COMMIT;
   

5. In the SQL command, do the following:

   • Replace <APPLICATION_NAME> with the name of your app.
   • Replace <POSTGRES_DATABASE_ADDRESS> with the address of your PostgreSQL database.
   • Replace <PORT> with your PostgreSQL database's port number.

6. Run the SQL command. This allows the app to connect to your PostgreSQL database.

Launch application

1. On the navigation menu, go to Data Products > Apps.

2. In the Installed Apps section, select your app.

3. Click Launch app. For security reasons, you will be prompted to log in to your Snowflake account.

4. Log in to your Snowflake account. After successfully logging in, you will be redirected to the app’s main interface.

5. Click Connect to Fivetran.

6. Log in to Fivetran.

   • For existing Fivetran users: Enter your Fivetran credentials to log in.
   • For new users: Follow the sign-up process. Provide your email address, create a password, and complete the email verification process. Once the email verification process is complete, log in using your new credentials.

   After logging into Fivetran, you will be directed back to the app's main interface.

7. Click Create Connector. This opens the setup form for the PostgreSQL connector in the Fivetran dashboard.

Set up PostgreSQL connector

In the Fivetran dashboard, set up your PostgreSQL connector by following the instructions in our setup guide.

IMPORTANT: After you set up the PostgreSQL connector, you can monitor the app’s status in both Snowflake and Fivetran interfaces. While the app's homepage in Snowflake offers a basic overview of the app's installation and operational status, for in-depth troubleshooting, log in to your Fivetran account. This grants full visibility into the app's performance, allowing you to effectively diagnose and resolve any issues.

Related articles

assignment PostgreSQL Native Applicationp FAQ

home Documentation Home