Connect Stripe to Redshift: 2 Easy Methods

on Data Integration, ETL, Tutorials • December 1st, 2020 • Write for Hevo

STRIPE TO REDSHIFT-Featured Image

Are you a marketer, who uses Stripe for payment services? Do you want to load data from Stripe to Redshift automatically? If yes, then this blog will answer all your queries. Stripe is an American company that provides financial services to many other companies. The accumulated data of Stripe can help you to perform various analyses on your customer data. Integrating Stripe with Amazon Redshift can help you to consolidate your data in real-time and analyze it to make data-driven decisions.

In this blog, you will learn about Stripe, Amazon Redshift, and two different approaches to load data from Stripe to Redshift. 

Table of content

Introduction to Stripe

Stripe to Redshift: Stripe - Transfer Data for Free from Stripe to your target destination
Image Source: Wikipedia

Stripe is an American payment platform. It provides financial services. The company mainly offers payment processing software and APIs for your E-Commerce websites and mobile applications. APIs can be used by the developers to integrate payment processing with your website and applications. It also acts as a gateway and a third-party payment tool. Stripe offers scalability and security with its cloud-based infrastructure. 

Introduction to Amazon Redshift

Stripe to Redshift: Amazon Redshift - Transfer Data for Free from Stripe to your target destination
Image Source: nightingalehq.ai

Amazon Redshift is a cloud-based, fully managed data warehouse. It is a fast, scalable, and cost-effective column-oriented data warehouse. Its basic architecture includes two or more cluster nodes which are managed by a Leader Node. Nodes are referred to as a collection of computing resources, and these nodes are organised to form cluster nodes. Each cluster contains at least one master node and compute node. It connects to various sources such as databases, BI tools, etc. and makes data available in real-time. You can use JDBC and ODBC to connect to third-party applications.   

2 Methods to Load Data from Stripe to Amazon Redshift

You can use any of the following methods to load data from Stripe to Amazon Redshift according to your need:

  1. Method 1: Stripe to Redshift Using Manual Method
    With the usage of Webhooks and Amazon S3, you can load Stripe data to the Amazon Redshift easily.  
  2. Method 2: Stripe to Redshift Using Hevo Data
    Hevo is a No-code Data Pipeline. It will automatically load your Stripe data into Amazon Redshift without writing any line of code. 

Let’s discuss each of them in detail.

Transfer Data from sources like Stripe to your target destination for Free!

Method 1: Stripe to Redshift Using Manual Method

Stripe supports REST API to access, store, and retrieve the data in Stripe. To know more about their API, refer to this documentation. They support JSON for their responses. 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. Stripe REST APIs support requests only over HTTPS because of security reasons. 

Stripe API supports ten core resources – balance, customers, charge, file uploads, refunds, token, transfers, transfer reversals, events, and dispute. 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 to Redshift, you need to follow these steps:

  1. Data Extraction from Stripe
  2. Prepare Stripe Data for Amazon Redshift
  3. Load Data into Amazon Redshift

1. Data Extraction from Stripe

For this section, let’s assume that you want to perform a churn analysis for your organization. You need to request a Stripe object that contains customer data to know when they have canceled their subscription. Use 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": 500,
      "amount_refunded": 0,
      "application_fee": null,
      "balance_transaction": "txn_17KGyT2eZvKYlo2CoIQ1KPB1",
      "captured": true,
      "created": 1452627963,
      "currency": "usd",
      "customer": null,
      "description": "thedude@grepinnovation.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": xxxx,
  "cancel_at_period_end": false,
  "canceled_at": xxxx,
  "current_period_end": 1455306419,
  "current_period_start": 1452628019,
  "customer": "cus_7hy0yQ55razJrh",
  "discount": xxxx,
  "ended_at": xxxx,
  "metadata": {
  },
  "plan": {
    "id": "gold2132",
    "object": "plan",
    "amount": 2000,
    "created": 1386249594,
    "currency": "usd",
    "interval": "month",
    "interval_count": 1,
    "livemode": false,
    "metadata": {
    },
    "name": "Gold ",
    "statement_descriptor": null,
    "trial_period_days": null
  },
  "quantity": 1,
  "start": 1452628019,
  "status": "active",
  "tax_percent": null,
  "trial_end": null,
  "trial_start": null
}

To stream your Stripe data, you will use Webhooks for it. You can register some events with Webhooks, and every time any event happens, Stripe will push the data in Webhooks.

2. Prepare Stripe Data for Amazon Redshift

Amazon Redshift is built around industry-standard SQL and supports some added functionality to manage large datasets and perform high-performance analysis. To load data into Redshift, you need to follow its relational database model. You need to map your data into tables and columns with suitable data types. 

Data types supported by Amazon Redshift are as follows:

  1. SMALLINT
  2. INTEGER
  3. BIGINT
  4. DECIMAL
  5. REAL
  6. DOUBLE PRECISION
  7. BOOLEAN
  8. CHAR
  9. VARCHAR
  10. DATE
  11. TIMESTAMP

As data coming from Stripe is in the form of JSON, you need to carefully map your data with appropriate data type before loading it into Redshift. Refer to this documentation to have an idea about the design of your database. 

After finalising your database design, select any of the following data sources that are supported by Redshift as data sources.

  1. Amazon S3
  2. Amazon Kinesis Firehose
  3. Amazon DynamoDB

3. Load Data into Amazon Redshift

Before pulling the data in Amazon Redshift, you need to pull it from a source that is supported by Amazon Redshift. 

To load data into Amazon S3, you need to use AWS REST API. You need to create an Amazon S3 bucket by executing an HTTP PUT request on the AWS REST API endpoints for Amazon S3. You can use CURL or Postman for it. Refer to this documentation for the API reference of Bucket operations. 

After creating the Bucket, you can start loading the data into Amazon S3 using the same AWS REST API but by operating on the HTTP endpoints or use the library of your preference for object operations. 

Amazon Redshift supports two methods for loading data – INSERT command and COPY Command. 

You can use either a JDBC or ODBC connection to connect to your client and then perform an INSERT operation as follows: 

 insert into category_stage values
(12, 'Concerts', 'Comedy', 'All stand-up comedy performances');

You can perform bulk uploads of flat files on S3 using the COPY command. Like the INSERT command, you need to connect your Amazon Redshift instance using JDBC and ODBC. COPY command allows reading of multiple files and automatically distributing the workload to the cluster nodes and loading it in parallel. 

copy listing
from 's3://mybucket/data/listing/'
credentials 'aws_access_key_id=;aws_secret_access_key=';

Voila! You have successfully loaded data from Stripe to Redshift. 

Limitations of Using Manual Method

Stripe provides financial services to organizations. It’s important to track each service wisely. Marketers load data from Stripe to Redshift to analyse their customer activities, such as ROI, sales, churn rates, etc. In such cases, prior knowledge of APIs, Webhooks, and Amazon S3 becomes a necessity. Marketeers with no technical background can find it difficult to do so.

You can remove this issue by using a fully automated ETL tool, like Hevo. Hevo will ease the process of data loading from Stripe to Redshift. 

Method 2: Stripe to Redshift Using Hevo Data

Hevo is a No-code Data Pipeline. It supports pre-built data integrations from 100+ data sources, including Stripe. Hevo offers a fully managed solution for your data migration process to Amazon Redshift. It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully-automated solution to manage data in real-time and always have analysis-ready data at Redshift.

Now you can transfer data from Stripe to your target Destination for Free using Hevo!

The steps to load data from Stripe to Redshift using Hevo Data are as follow:

  1. Authenticate and connect Stripe as your data source.
  2. Connect Amazon Redshift as a destination to load your data. 
Get Started with Hevo for free

Let’s look at some salient features of Hevo:

  • Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
  • Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
  • Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within pipelines.
  • Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support call.
Sign up here for a 14-day Free Trial!

Conclusion

In this blog, you have learned about Stripe, Amazon Redshift and how to load data from Stripe to Redshift using two different methods. You also came across the limitations of the manual method. So, if you are a marketer who wants to load their data from Stripe to Redshift automatically, then try Hevo. 

Visit our Website to Explore Hevo

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

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

Share your experience of loading data from Stripe to Redshift in the comment section below. 

Free No-Code Data Pipeline for Stripe