How to Load Data from Stripe to Snowflake?

Unlock the full potential of your Stripe 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!

Do you analyze your Customer’s spending patterns made through Stripe? I am sure it is not a cakewalk.

Stripe is an API-based credit provider that helps businesses process their customers’ payments. The data that Stripe contains can help you measure the buying behavior of customers, purchasing patterns across geographies, and the efficiency of different marketing channels. But how can we connect the dots?

To make the most out of Stripe data, you need to have a 360-degree view of your data. This is where the concept of a Data Warehouse solution comes into the picture —- Snowflake, is undoubtedly one of the top solutions in the market.

Having said that, after moving your data from Stripe to Snowflake, you can now directly focus on deriving insights from it. You can deep dive into the data and explore new market opportunities.

This article will uncover two methods to connect these two platforms. You can trade off between your requirements and the limitations associated with the manual method. 

How to Set up Stripe to Snowflake Integration?

Learn how to set up a reliable Stripe to Snowflake integration and streamline your reporting process in these detailed methods. 

Method 1: Using Hevo Data: The Best Method

Step 1 – Configure Stripe as a Source in Hevo

• Navigate to Pipelines
  • In the Hevo UI, click Pipelines on the Navigation Bar.
  • Click + Create Pipeline on the Pipelines List View.
• Select Source Type
  • On the Select Source Type page, choose Stripe.
• Configure Stripe Source
• On the Configure Your Stripe Source page, provide the following details:
  • Pipeline Name: A unique name for your Pipeline (max 255 characters).
  • API Key: Paste the API key obtained from your Stripe account
    Use a restricted key when possible, and store it securely (avoid hardcoding).
  • Historical Sync Duration: Choose how much existing data to ingest.
    • Default: 3 months.
    • If you select all available data, Hevo ingests everything from Jan 01, 2010 onward.
• Advanced Settings
  • Use Stripe Schema v2:
    • If enabled → Hevo loads data without expanding nested objects (cleaner schema, raw JSON preserved).
    • If disabled → Hevo uses Schema v1, expanding nested objects into separate fields.
  • Refer to the Stripe Schema v1/v2 documentation for details on which schema suits your needs.
• Test & Continue
  • Click TEST & CONTINUE to validate your configuration.
  • If the test passes, you’ll move on to select the Destination (Snowflake) and complete pipeline setup.

Step 2 – Configure Snowflake as a Destination in Hevo

Prerequisites:

• You need an active Snowflake account. 
• The user connecting from Hevo needs certain roles in Snowflake: ACCOUNTADMIN or SECURITYADMIN to create necessary roles. For regular use, a non-admin user with correct privileges (e.g. USAGE, CREATE TABLE, etc.) is required. 
• Permissions needed: USAGE on warehouses, CREATE SCHEMA, MONITOR, USAGE on database(s), CREATE EXTERNAL TABLE, MODIFY, etc. 

Connect methods:

1. Using Snowflake Partner Connect (recommended for new users).  

• If you are new to Hevo, the easiest way to get started is by connecting Snowflake as a Destination via Partner Connect.
• (If you are already an existing Hevo user, we recommend connecting using Snowflake Credentials instead.)

Steps to Configure Snowflake via Partner Connect:

• Click CONTINUE to finalize the setup.
• Log in to Snowflake with a user that has the ACCOUNTADMIN role.
• In the left navigation pane, click Data Products → Partner Connect.
• In the search bar, type Hevo, then click the Hevo tile.
• In the pop-up that appears:
  • If your organization’s domain is already registered with Hevo:
  • Click Launch, enter your email and password, then log in.
  • In Hevo, you can either:
  • Create a new workspace (recommended for trial users to start exploring right away).
  • Or request access to an existing workspace (you’ll need approval and the correct role to create Pipelines/Destinations).
• If your organization’s domain is not registered with Hevo:
  • Click Connect.
  • In the Your Partner Account window, click Activate.
  • Set a password to create your Hevo account, then click Continue.
• In Hevo:
  • On the Select Source Type page, choose and configure your Source (e.g., Amazon RDS MySQL).
  • Click TEST & CONTINUE.
• On the Select Destination page, select Snowflake.
• Configure the Destination details:
  • Specify a Destination Table Prefix.
  • Ensure Auto-Mapping is enabled if you want Source Event Types to be automatically mapped to Destination tables.
  • Choose the Ingestion Frequency (default or custom in hours).

2. Or using Snowflake Credentials:

• Hevo by using Snowflake credentials. This involves creating a Snowflake account (if not already available), configuring a warehouse, and then connecting with either username/password or a key pair.

Step 1: (Optional) Create a Snowflake Account

• If you don’t already have a Snowflake account, sign up and provision a new one.

Step 2: Create and Configure Your Snowflake Warehouse

• Hevo provides a ready-to-use script to set up your warehouse, role, and user with the minimum permissions required.
  • Log in to your Snowflake account.
  • From the left navigation bar, click Projects → Worksheets.
  • Click + to create a new worksheet.
  • Copy-paste the script provided by Hevo (customize role, user, password, warehouse, database, and schema names).
• Example values in the script:

set role_name = 'HEVO'; 

set user_name = 'HARRY_POTTER';  

set user_password = 'Gryffindor';  

set warehouse_name = 'HOGWARTS';  

set database_name = 'RON';  

set schemaName = 'DARK_ARTS';  

• Replace these placeholders with your own values (ensure they’re in UPPERCASE).
• Run the script (CMD+Return on Mac / CTRL+Enter on Windows).
• Once successful, the credentials defined in lines 2–7 of the script are what you’ll use to connect Snowflake to Hevo.

Note: If your Snowflake account was created after BCR Bundle 2024_08, Snowflake recommends creating a service user for ETL tools like Hevo:

ALTER USER <your_snowflake_user> SET TYPE = SERVICE;

• Service users cannot use password authentication; they must connect via key pair authentication (see Step 3).

Step 3: Obtain a Private and Public Key Pair (Recommended)

• For secure, production-grade connections, generate a public/private key pair and use it to authenticate Hevo with Snowflake. (Refer to Snowflake’s key pair generation guide.)

Step 4: Obtain Your Snowflake Account URL

• You’ll need your account’s unique URL to complete the setup.
• Format:
  https://<account_name>.<region>.<cloud>.snowflakecomputing.com
• Example:
  https://westeros.us-east-2.aws.snowflakecomputing.com
• To find your account URL:
  • Log in to your Snowflake console.
  • Go to Admin → Accounts.
  • Hover over the Locator field for your account and copy the URL.

Configure Snowflake as a Destination in Hevo

Perform the following steps to configure Snowflake as a Destination in Hevo:

1. Navigate to Destinations
   • Click DESTINATIONS in the Navigation Bar.
   • Click + CREATE DESTINATION in the Destinations List View.
2. Select Destination Type
   • On the Add Destination page, select Snowflake.
3. Configure Snowflake Destination
   • On the Configure your Snowflake Destination page, provide the following details:
     • Destination Name: A unique name for your Destination (max 255 characters).
     • Choose a Connection Method:
     • a) Key Pair Authentication (Recommended)
       • Snowflake Account URL: The account URL obtained earlier.
       • Database User: A non-administrative user with the public key assigned.
       • Private Key: Upload the private key file generated in Step 3.
       • Passphrase: Password used while generating the encrypted private key.
       • Warehouse: Warehouse associated with your database (created or existing).
       • Database Name: The Destination database where data will be loaded.
   • Database Schema: The schema in the database the user can access. Schema names are case-sensitive.
   • b) Access Credentials
     • Snowflake Account URL: The account URL obtained earlier.
     • Database User: Non-administrative user (created or existing).
     • Database Password: Password of the database user.
     • Warehouse: Warehouse associated with the database (created or existing).
     • Database Name: Destination database where data will be loaded.
     • Database Schema: The schema in the database the user can access.Schema names are case-sensitive.
4. Advanced Settings (Optional)
   • Populate Loaded Timestamp: Enable to add a __hevo_loaded_at column in the Destination table showing when the event was loaded.
   • Create Transient Tables: Enable to create transient tables.
     • Transient tables have the same features as permanent tables except the Fail-safe period.
     • Use transient tables to reduce storage costs if your data can be reconstructed outside Snowflake or does not require full data protection.
5. Test & Save
   • Click TEST CONNECTION to validate your setup.
   • Once successful, click SAVE & CONTINUE to complete the configuration.

Method 2: Using Manual Method

You can use the REST API principles for accessing, storing, and retrieving your data from Stripe. To know more about their API, refer to this documentation. They support JSON for their response. They also offer two different types of keys for authentication – testing mode and live mode. With testing mode, you can test every aspect of your API without messing with your actual data. Calls to Stripe API are only over HTTPS because of security reasons.

Stripe API supports ten core resources – charges, balance, refunds, customers, tokens, events, transfers, dispute, etc. All these resources support CRUD operations using HTTP verbs on their associated endpoints. In this section, you will use CURL, but you can also work with Postman, Hyper, etc. 

To load data from Stripe into Snowflake, you need to follow these steps:

• Data Extraction from Stripe
• Prepare Stripe Data for Snowflake
• Load Data into Snowflake

1. Data Extraction from Stripe

• Suppose you want to perform a churn analysis for your organization. For this purpose, you need to request a Stripe object containing customer data t To find out when a client has canceled their subscription.  
• You can carry out the following operation:

curl https://api.stripe.com/v1/charges?limit=3 
   -u sk_test_BQokikJOvBiI2HlWgH4olfQ2:

• Your response will look as follows:

{
  "object": "list",
  "url": "/v1/charges",
  "has_more": false,
  "data": [
    {
      "id": "ch_17SY5f2eZvKYlo2CiPfbfz4a",
      "object": "charge",
      "amount": 1000,
      "amount_refunded": 20,
      "application_fee": null,
      "balance_transaction": "txn_17KGyT2eZvKYlo2CoIQ1KPB1",
      "captured": true,
      "created": 1452627967,
      "currency": "usd",
      "customer": null,
      "description": "xyz@abc.com Account Credit",
      "destination": null,
      "dispute": null,
      "failure_code": null,
      "failure_message": null,
      "fraud_details": {
      }, …….

• Your customer object will look as follows:

{
  "id": "sub_7hy2fgATDfYnJS",
  "object": "subscription",
  "application_fee_percent": null,
  "cancel_at_period_end": false,
  "canceled_at": null,
  "current_period_end": 1455306420,
  "current_period_start": 1452628020,
  "customer": "cus_7hy0yQ55razJrh",
  "discount": null,
  "ended_at": null,
  "metadata": {
  },
  "plan": {
    "id": "gold2132",
    "object": "plan",
    "amount": 2000,
    "created": 1386249594,
    "currency": "usd",
    "interval": "month",
    "interval_count": 1,
    "livemode": false,
    "metadata": {
    },
    "name": "John ",
    "statement_descriptor": null,
    "trial_period_days": null
  },
  "quantity": 1,
  "start": 1452628020,
  "status": "active",
  "tax_percent": null,
  "trial_end": null,
  "trial_start": null
}

• These objects include the necessary information required for performing churn analysis.
• You will use Webhooks to stream your data. You can register some events with Webhooks, and Stripe will push the data into Webhooks whenever an event occurs.

2. Prepare Stripe Data for Snowflake

• Before ingesting data into Snowflake, you must first create a well-defined schema. Snowflake organizes all of its data into tables with columns. Each column has its own data type.
• Snowflake supports a wide range of data types. Along with that, it also supports a variety of semi-structured data formats, including JSON, Avro, XML, ORC, etc.
• To load data from Stripe to Snowflake, you must first create a schema in Snowflake. The tables in the schema can be mapped with API endpoints. Each key in the Stripe API endpoint response must be mapped to a column in the table. These keys must be converted to the appropriate data type aligned with the columns in Snowflake.

3. Load Data into Snowflake

• You can use the ‘COPY INTO’ command for loading your data at a time (in bulk) in Snowflake. 
• Files containing data in JSON format are either stored in a local file system of Amazon S3 buckets. For loading these files to the Snowflake data warehouse, you can invoke the COPY INTO command.
• You can even first transfer the data to a staging environment using the PUT command. Following that you can carry out the usual process of invoking the COPY INTO command.
• Another method for importing data into Snowflake is to upload your data straight into Snowflake using the services provided by Amazon S3. You can use the following command to copy data from Amazon S3 to Snowflake.

copy into table_name   
from s3://snowflakebucket/data/abc_files 
credentials=(aws_key_id='$KEY_ID' aws_secret_key='$SECRET_KEY')   pattern='filename.JSON';

• This command requires the source file name, destination table name, and a set of parameters to accomplish the copy operation. It uses matching to identify the file in the source repository.

Voila, your Stripe data is now successfully loaded into Snowflake.

Check out the Snowflake tutorial to handle updates, especially using primary keys. 

Limitations of Using Manual Method

Stripe offers financial services to businesses. Marketers import data from Stripe to Snowflake to monitor customer activities like ROI (Return Forth Investment), sales, churn rates, etc. Snowflake serves as a Single Source of Truth for all the customer data. And it is crucial to keep careful track of each service. 

1. Prior understanding of APIs, Webhooks, and Amazon S3 is required in setting up this integration. Marketers with no technical experience will face problems while moving forward with the integration.
2. Since data in Stripe grows at an exponential rate, you must continually insert and update Snowflake data. You need to perform regular updates using the UPDATE command.
3. Another problem with manual techniques is identifying and removing duplicate entries in Snowflake. This is where you can take the help of an automated, fully-managed ETL solution like Hevo.

Hevo will ease the process of data loading from Stripe to Snowflake. 

Stripe Overview

Stripe is a leading payment processing platform that enables businesses to accept online payments from customers. It provides a suite of APIs for integrating payment gateways into websites, mobile apps, and other platforms. Stripe is popular among developers and companies due to its simplicity, scalability, and support for a wide range of payment methods.

Key Features of Stripe

1. It supports several payment methods, including credit/debit cards, digital wallets like Apple Pay and Google Pay, bank transfers, and ACH payments.
2. It supports more than 135 different currencies and is available in over 40 countries, making it suitable for international business.
3. Comply with the PCI-DSS standard, demonstrating security in handling customer payment information.
4. Highly scalable, flexible APIs and SDKs make integrating with your website or mobile application easy.

Snowflake Overview

Snowflake is a cloud-based data warehousing platform designed for analytics and big data processing. It offers high-performance querying capabilities, scalability, and integrates seamlessly with various cloud platforms like AWS, Azure, and Google Cloud. Snowflake is a popular choice for businesses that need to store, manage, and analyze large datasets.

Key Features of Snowflake

1. It provides multi-cluster virtual warehouses for concurrency with no resource contention, thus allowing for high performance across multiple users and queries.
2. Snowflake is cloud-built and cloud-hosted (AWS, Azure, Google Cloud); it separates storage and computing and can scale independently based on business needs.
3. Snowflake automatically scales resources up or down depending on the workload requirements to make sure performance remains consistent as data volume grows.

What can you achieve by replicating data from Stripe to Snowflake?

By migrating your data from Stripe to Snowflake, you will be able to help your business stakeholders find the answers to these questions:

• How does CMRR (Churn Monthly Recurring Revenue) vary by Marketing campaign?
• How much of the Annual Revenue was from In-app purchases?
• Which campaigns have the most support costs involved?
• For which geographies are marketing expenses the most?
• Which campaign is more profitable?
• What does your overall business cash flow look like?
• Which sales channel provides the highest purchase orders?

Conslusion

In this blog, you have learned about Stripe, and Snowflake, and how to load data from Stripe to Snowflake using two different methods. You also came across the limitations of the manual method.

This blog has touched down the method of manually connecting the two platforms using API Integration. However, it would eat up quite a bit of your time to set up the integrations. 

What if you don’t have prior knowledge of APIs and Webhooks? We can relieve you of this headache. This is where a no-code, fully automated data pipeline like Hevo comes to your rescue.

Hevo will save you a mountain of time in setting up your integration in a simple 3-step process. And it will uncover enormous opportunities for you to direct your focus on generating insights on your spending patterns, customer activities, Return on Investment (ROI), churn analysis, etc.

Visit our Website to Explore Hevo

Hevo Data is a No-code Data Pipeline. It supports pre-built integrations from 150+ data sources at a reasonable price. Hevo will fully automate your data flow from Stripe to Snowflake within minutes.

Frequently Asked Questions (FAQs)