Using Fivetran webhooks to collect and store events

Creating a tracking solution has never been easier. There are numerous (free) tools available to collect and store event data. In this blog post I explain how to collect data using Fivetran webhooks and storing it in ElephantSQL, a managed PostgreSQL database.

In this blog post, we will:

• Set up a free trial of Fivetran
• Configure a webhook connector in Fivetran
• Set up a free managed PostgreSQL database on ElephantSQL
• Send data to the webhook
• Query our collected data

Create a free Fivetran trial account

First, a little information on Fivetran. Fivetran is an integration tool that is well-suited within an enterprise context but can also be used for smaller-scale projects. Fivetran has three main components: connectors, transformations, and destinations. Connectors: extract data from applications such as Salesforce, LinkedIn, or Shopify but also from database technologies such as Postgres or Google BigQuery. Transformations: manipulate the data before writing it away. Destinations: load the data in a database such as Snowflake or Redshift.

You can create a free trial account on Fivetran by going to the signup page at Fivetran.com. It’s easy, no credit card is required and you’re free to experiment for two weeks with all of Fivetran’s features.

Create a Fivetran webhook connector

Within your project, create a new connector. There are many, but you might want to scroll down to the pink/purple icon with Webhooks next to it. Give a name to your schema that you wille be storing in the destination. Also name a table.

You can choose to keep your data “packed” as a JSON object or to have Fivetran unnest it and letting it guess about the data types. I prefer to keep it packed and do the processing myself afterward.

Once this is set up, you’ll have access to a webhook connector within your project.

The Webhook URL is what you’ll be sending data to with a POST request. More on this below.

Create a managed PostgreSQL database on ElephantSQL

What is PostgreSQL? It is a free and open-source relational database system. If you’re familiar with MySQL: there are a lot of overlapping features. However, Postgres tends to be more in compliance with SQL standards. It’s also object-centric and has some advanced features like function overloading and table inheritance. Anyone can install PostgreSQL for free on their machine, but there are some managed services for those who aren’t database engineers.

ElephantSQL offers a fully managed service on many cloud environments and within many regions. This means the whole infrastructure, configuration, backups, and security is managed by a professional team of database experts. You can rent your own database for just a couple of bucks a month or apply for a free plan. Which we’ll do here.

Head over to ElephantSQL and click “Get a managed database today”. Select the FREE plan, or pick one of your choice if you plan on using this service in the future. Create a new account by providing your contact details or log on through GitHub or Google.

Next, create a new instance. It doesn’t really matter on which cloud environment you’ll put it. However, it never hurts to select one close to your region.

Create requests to the Fivetran webhook in Google Colab

Sending data to the webhook is straightforward and can be done from anywhere. An easy example is to do it through a simple Python snippet — which you can run within Google Colab, for example.

In the following snippet, I create an object that contains an event name and a data object. That object contains a key (“name”) and its value is “roel”. Once I run this code, this object will be sent as a POST request to Fivetran.

Make sure to change the webhook URL to your own webhook.

import requests
import json

url = 'https://webhooks.fivetran.com/webhooks/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
myobj = {'event': 'colab', 'first_name':'roel', 'second_name': 'peters'}

x = requests.post(url, data = json.dumps(myobj), headers={'Content-Type': 'application/json'})

It’s of essence that you realize that Fivetran does not sync to the PostgreSQL database in real-time. It is done in batch. The better your plan, the more sync you can have per day. Syncing can be done from your Fivetran dashboard, by clicking on the sync icon in the top right corner.

Query the data in the PostgreSQL database

In ElephantSQL, you will find an SQL browser that comes out of the box. In other words: you don’t need a query tool like HeidiSQL to run your queries.

To get access to our data, we simply have to run the following query. This will return the first_name and last_name field we inserted from the Google Colab notebook.

SELECT
	_created as TIMESTAMP,
	data -> 'first_name' as FIRSTNAME,
	data -> 'last_name' as LASTNAME
FROM 
	"webhooks"."fivetran" 
ORDER BY _created DESC 
LIMIT 100

Congratulations, you now know how to insert data into a PostgreSQL database through a Fivetran webhook connector!