How to Export to Snowflake

How to Export to Snowflake

Info
Exporting your marketing data to Snowflake allows you to integrate it with various applications and run complex data queries. This guide will walk you through the process of setting up and managing your Snowflake exports using Power My Analytics. A Custom plan is required to use Snowflake exports.

How We Store Data in Snowflake Exports

Creating a table

Our Snowflake export feature will create a table with the same name as the Data Builder report you are exporting, with all spaces replaced by underscores (_). The table has columns corresponding to the fields in the Data Builder report being exported.

Partitioning and updating rows

Tables in Snowflake exports are partitioned by month. New data from the export is appended to the appropriate monthly partition, while any changed data will be updated in the affected rows.

Adding columns

When you edit the report in Data Builder to add another field, this field will be added to the table in Snowflake as a new column the next time the export runs. This column will be populated with the field's values going forward. Historical tables from before the new column was added will not include this field; the new column will only appear in the updated table going forward.

Removing fields

When you edit the report in Data Builder to remove an existing field, the corresponding column is not dropped from the table in Snowflake. Instead, the column will remain in the table, and its values will be set to null in all future exports.

Prerequisites

Before you begin:

  • Make sure you have a Custom plan with Power My Analytics
  • Create a security integration in Snowflake for Power My Analytics
  • Obtain your Snowflake credentials:
    • Account URL
    • Client ID
    • Client Secret
  • Decide which Snowflake warehouse, database, and schema you're going to use to store your exports
  • Whitelist the following IPs in Snowflake:
    • 35.188.118.242
    • 35.209.185.69

Create a Security Integration for Power My Analytics

Before creating a data export from PMA, you'll need to create a new security integration in Snowflake and obtain the Client ID and Client Secret. 
Alert
This security integration must be created by a user with the Account Admin role in your Snowflake workspace.
First, run the following commands in Snowflake to create the security integration for PMA:
  1. CREATE SECURITY INTEGRATION PowerMyAnalyticsOauthIntegration
        TYPE = OAUTH
        ENABLED = TRUE
        OAUTH_CLIENT = CUSTOM
        OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
        OAUTH_REDIRECT_URI = '    https://us-central1-power-my-analytics.cloudfunctions.net/connectors-snowflake-auth/token'
        OAUTH_ISSUE_REFRESH_TOKENS = TRUE;
Next, run this command to obtain your Client ID and Client Secret:
  1. select SYSTEM$SHOW_OAUTH_CLIENT_SECRETS( 'POWERMYANALYTICSOAUTHINTEGRATION' );
Copy your Client ID and Client Secret for use when configuring your Snowflake destination in Power My Analytics.

How to Find Your Snowflake Account URL

You'll need your Snowflake account URL when setting up your Snowflake destination in PMA. To obtain your account URL:
  1. Go to Snowsight and click on your account name to open the menu.
  2. Under the Account submenu, click View account details under your account.



  3. Copy your account URL. 
For additional help, please see the Snowflake support article Locate your Snowflake account information in Snowsight.

Step 1: Configure Your Data Builder Report for Initial Backfill

  1. In your PMA hub, go to Reports > Data Builder and find your report. Click the three-dots action menu and select Edit. 



  2. Go to Available Data Range and set it to the entire range of data you want to export (up to 2 years for most PMA connectors). To backfill from a specific start date up to yesterday, choose Custom start to date and enter a start date.



  3. Click Save Changes to save the report.


Step 2: Connect to Snowflake

  1. Go to Exports > Snowflake in the left sidebar.
  2. Click + Create Export.



  3. Click the blue plus button under Destination to add a new destination.



  4. In the Connect Snowflake dialog:
    • Enter your Snowflake account URL and the Client ID and Client Secret you obtained after creating the Power My Analytics security integration.
    • Click Connect. You will be redirected to the Snowflake authentication page.



  5. Choose the Snowflake account you just added under Destination.



  6. Enter your Snowflake warehouse, database, and schema, then click Next.


Step 3: Choose Your Report

  1. Under Report or Report Group, select either Single report or Group of reports.



  2. Choose the specific report or report group from the dropdown. Click Next.



  3. For now, select Monthly under Schedule and choose any day and hour (we'll adjust this later). Click Add to create your export, then click Confirm.


Step 4: Run the Initial Backfill

  1. On the Exports to Snowflake page, find your new export. Click the play icon to run the export manually.



  2. The export will begin running, as indicated by the three dots icon. Wait for the three dots icon to change back to a play button, indicating the export has finished.



  3. Click the paper icon to view the logs and confirm the export was successful.


Step 5: Set Up Rolling Updates

Configure your Data Builder report

  1. Go back to your report in Data Builder. Click the three-dots action menu and select Edit.
  2. Set the Available Data Range to a rolling window (e.g., last 30 days for e-commerce to account for returns).


  3. Then click Save Changes.


Configure your Snowflake export

  1. Return to Exports > Snowflake. Find your export and click the pencil icon to Edit Export.



  2. Under Schedule, choose your desired frequency: monthly, weekly, daily, or hourly. Select the appropriate day/time for the export to run.
  3. Click Update.


Alert
Important: Ensure your report's date range in Data Builder is at least as long as the refresh period in the Snowflake export in order to avoid missing data. For example, if your report's date range is one week but your export refreshes monthly, your export will fail to include data from before the last week of the month.
Your export will now run automatically at the scheduled time, keeping your Snowflake database up-to-date with the latest data from your PMA reports.

Troubleshooting

If you encounter any issues with your exports:

  1. Check the export logs by clicking the paper icon next to your export.
  2. Verify that your Snowflake credentials are correct and that the PMA IP addresses are whitelisted.
  3. Ensure your report's date range aligns with or exceeds your export schedule to prevent data gaps.
You can also submit a ticket to our customer support team.
If you receive the error message "Invalid consent request" when attempting to use your Snowflake export destination, your default user role may be set to an administrative role of ACCOUNTADMIN, ORGADMIN, GLOBALORGADMIN, or SECURITYADMIN. Users with these default roles will be unable to connect Snowflake to Power My Analytics.

Change the default role for your Snowflake user to a non-administrative role by running the following command in Snowflake:
  1.  ALTER USER <user_name> SET DEFAULT_ROLE = 'PUBLIC';
For additional help, please see the Snowflake support article "Invalid consent request" error when signing into Snowflake Openflow Deployment.

    • Related Articles

    • How to Export to PostgreSQL

      Our PostgreSQL export feature enables flexible access to your data in the form of tables in a PostgreSQL database hosted on the server of your choice. Once you've prepared a report in Data Builder, you're ready to export your data to your PostgreSQL ...

    • How to Create an FTP Export

      FTP can be a valuable way for Custom plan users to export their Data Builder reports. In this article, we will walk you through creating an FTP export, including selecting an FTP destination, scheduling data refreshes, and managing your exports to ...

    • How to Use the Exports Add-On

      Power My Analytics offers the option to export data from your reports into destinations like BigQuery, MySQL, Azure SQL Database, PostgreSQL, and FTP. With our easy-to-use Exports Add-On, you can set up a report in PMA's Data Explorer and schedule ...

    • How to Export to MySQL

      Our MySQL export feature enables flexible access to your data in the form of tables in a MySQL database hosted on the server of your choice. Once you've prepared a report in Data Builder, you're ready to export your data to your MySQL server. With ...

    • How to Export to BigQuery

      Once you have a report ready in Data Builder, you are able to export it to BigQuery. This export option is included in the Custom plan. Follow the instructions in this article to get started. Grant Required Roles and Permissions A successful ...