Introduction

At The Data Refinery, we make use of Fivetran, a data integration platform, to facilitate seamless data pipeline setup and maintenance across various sources - so we like to think we know what we're doing! Within this framework, we utilize Fivetran webhooks to monitor the status of a connection in real-time. We are primarily interested in tracking the status of a connection so we (or our users) can react to any issues and maintain a live history of connection events.

Webhooks differ slightly from standard APIs (application programming interface). With a standard API we request the data we would like, and the data is returned to us. While a webhook sends data to a destination when a change has occurred.

This difference between an API and a webhook can be demonstrated by a request to Fivetran for an update on connector statuses. Using the Fivetran API connector endpoint, Fivetran would gather the required information and then return the response to us. While using a webhook, data will be sent automatically whenever there are any changes made.

We have pulled together this blog post to help other developers looking to get started with Fivetran webhooks, as we found the documentation created by Fivetran to be lacking in some parts.

Setup

To start making use of Fivetran webhooks the following prerequisites are required:

• A Fivetran account  
• Active Fivetran connectors
• A suitable API with a public POST endpoint
• Knowledge of the curl command or an API interaction tool

Webhook Overview

When creating a webhook in Fivetran, you can set it up either at the account level or the group level. At the account level, notifications apply globally to all connectors within the account. An account in Fivetran represents an entire organization or user account, encompassing all connectors and data integration processes associated with it. Therefore, setting up a webhook at the account level means receiving notifications that pertain to any activity or status changes across all connectors managed within the account. This contrasts with setting up a webhook at the group level, where notifications are specific to a selected group of connectors that share similar characteristics or belong to a common category. While the specific configurations may vary, the process for creating an account-wide webhook is similar to that of creating a group webhook. This approach will look to create a group webhook.

We can specify when we would like Fivetran to send us a request dependant on the events we provide when creating the webhook. Each event has been documented in detail here

Webhook Flow

Creating a Webhook

Creation and configuration of the webhook can be done using Fivetrans Webhook Management REST API.

Targeting a Group

To interact with the webhook API a Fivetran group id is required, this can be obtained via the Fivetran UI or by grabbing the id from the REST API group endpoint.  

curl -X GET https://api.fivetran.com/v1/groups \ 
  -H "Content-Type: application/json" \ 
  -H "Authorization: {fivetran-bearer-token}"
  

**Note: Your Fivetran bearer token can be generated using this guide.

Creating the hook

Making a POST request, we can now create a new Fivetran webhook. You will also need to provide the URL to your POST API endpoint, Fivetran will make a call to this endpoint when creating the webhook so ensure it is running and will return a 2** response.  

We also need to provide a secret string; this will be used later during authentication so keep a note of it (to create an account level webhook we would instead POST to https://api.fivetran.com/v1/webhooks/account) We can now create our webhook using:

curl -X POST https://api.fivetran.com/v1/webhooks/group/{fivetran-group-id} \ 
  -H "Content-Type: application/json" \ 
  -H "Authorization: {fivetran-bearer-token}" \ 
  -d '{"url": "{your-api-url}",\ 
       "events": ["sync_end"],\  
       "active": true,\  
       "secret": "{secret}"}'
   

There are 4 important properties that must be set when creating a webhook:

• URL: This is the endpoint that you want the webhook to call when an event occurs.
• Events: A list of events that are to be subscribed to (here we have chosen sync_end event but more than one can be selected)
• Active: Is the webhook in an active state
• Secret: Your secret key that is used to hash and decrypt the webhook payload

The successful response should look something like:

{ 
  "url": "{your-api-url}", 
  "events": [ 
    "sync_end" 
  ], 
  "active": true, 
  "secret": **** 
}

Authenticating a Webhook

The next step is to implement authentication. Fivetran handles authentication by sending us a SHA-256 HMAC request header called X-Fivetran-Signature-256 that we can use to validate the request. A SHA-256 HMAC is a hash function that takes an input, scrambles the plaintext using a secret to augment the scrambled output.  

Fivetran uses the secret we added when creating the webhook to hash the request body. So, to validate the request we need to generate our own hash of the request body and ensure it matches what Fivetran sent us. Below is an example using Flask:

import hashlib 
import hmac 
from typing import Optional 

from flask import Flask, request  

app = Flask(__name__) 

PASSWORD = "" 
 

def authenticate(fivetran_request: request) -> str: 
    # Create our own hash using the request body and our password 
    request_body = fivetran_request.data.decode("UTF-8") 
    valid_hash: str = ( 
        hmac.new( 
            bytes(PASSWORD, "latin-1"), 
            msg=bytes(request_body, "latin-1"), 
            digestmod=hashlib.sha256, 
        ) 
        .hexdigest() 
        .upper() 
    ) 

    fivetran_hash: str = fivetran_request.headers.get("X-Fivetran-Signature-256") 
  
    if fivetran_hash != valid_hash: 
        return "Valid Request" 

    return "Invalid Request"
    

Handling a Webhook

After authenticating we will need to handle the request from Fivetran. An example request can be seen below:

{ 
  "event" : "sync_end", 
  "created" : "2021-08-18T11:38:34.386Z", 
  "connector_type" : "asana", 
  "connector_id" : "mystified_presiding", 
  "destination_group_id" : "deck_enjoy", 
  "data" : { 
    "status" : "SUCCESSFUL" 
  } 
}

A description of each of the fields can be seen here.

Handling this request, we can react to the various event types and data coming from Fivetran. The below example simply prints a message whenever we receive a sync end event. More complex logic could be implemented to handle the information in various ways to suit your requirements.  

if request.json.get("event") == "sync_end": 
    print("Sync end event has occurred!")
    

Testing

Fivetran does provide a test webhook endpoint, however the approach outlined by Fivetran would require Ngrok and mean updating our webhook URL to match. Also, with the free version of Ngrok we would be limited to two hours of usage before having to update the URL.  

Instead, I find it more convenient to directly call our code locally. We can populate our request body to match what we expect Fivetran to send us and generate our own X-Fivetran-Signature-256 header. We can pass whatever we want into the request body, but the request body would ideally match Fivetrans request.  

Using the below code we can generate our own X-Fivetran-Signature-256 header and request body, ensuring the password field is the same as the one we used to create the webhook:

import hashlib 
import hmac 
import json 
 
if __name__ == "__main__": 
    password = "" 
    request = { 
        "event": "sync_end", 
        "created": "2021-09-23T14:50:34.386Z", 
        "connector_type": "shopify", 
        "connector_id": "clang_taught", 
        "destination_group_id": "revive_turtle", 
        "data": { 
            "status": "FAILURE_WITH_TASK", 
            "reason": "ErrorExceptionFromJava: something failed", 
        }, 
    } 
    request_string = json.dumps(request, indent=4) 
    print(f"body: {request_string}") 
    valid_hash: str = ( 
        hmac.new( 
            bytes(password, "latin-1"), 
            msg=bytes(request_string, "latin-1"), 
            digestmod=hashlib.sha256, 
        ) 
        .hexdigest() 
        .upper() 
    ) 
    print(f"header: {valid_hash}")
    

Using the header and body we can test the code locally:

curl -X POST -H 'X-Fivetran-Signature-256: header' -H 'Content-Type: application/json' -d '{ 
  "event" : "sync_end", 
  "created" : "2021-08-18T11:38:34.386Z", 
  "connector_type" : "asana", 
  "connector_id" : "mystified_presiding", 
  "destination_group_id" : "deck_enjoy", 
  "data" : { 
    "status" : "SUCCESSFUL" 
  } 
}' 'http://127.0.0.1:8000'

Conclusion

This was just a very simple example of what can be done with Fivetran webhooks, there are a wide range of possibilities with this new feature. I would recommend reading the Fivetran webhook documentation for more information on the topic.  

If there are features/events you would require that are not currently available, they can be requested by completing the Fivetran webhook event support form.  

A full working example can be found here.

‍