Build Connector Status Dashboards With the Fivetran REST API

Learn how to combine AWS Lambda and the new Fivetran REST API to build connector status dashboards.
Build Connector Status Dashboards With the Fivetran REST API

Fivetran is currently in beta with a REST API. We built this API so that enterprising data engineers at your organization could programmatically manage users, groups and connectors, and automate their workflows.

There is an analytics use case for the API, too. By using an AWS Lambda function to request a list of all connectors within a group, you can easily assemble a table with the status of every connector syncing to that group’s warehouse. If you sync it every five minutes and connect the data to Looker, you can have a near real-time visualization of every connector’s status without entering your Fivetran dashboard.

Per the code below, the data from this API endpoint is split into three tables:

  1. Items – every connector
  2. Tasks – tasks, each of which is attributed to a connector
  3. Warnings – warnings, each of which is attributed to a connector

You can, of course, designate your tables however you like. One basic view you can create with the data is:

All connectors with current status and last successful sync:

CREATE VIEW connector_status 
  WITH max_date 
       AS (SELECT service               AS Connection_Type, 
                  Max(_fivetran_synced) AS max 
           FROM   items 
           GROUP  BY 1) 
  SELECT SCHEMA       AS Connector, 
         sync_state   AS Status, 
         update_state AS Status_Detail, 
         succeeded_at AS Last_Success 
  FROM   items 
         join max_date 
           ON items.service = max_date.connection_type 
              AND items._fivetran_synced = max_date.max 
  ORDER  BY connector ASC; 

The following is a plug-and-play Python script that you can use as an AWS Lambda function. You can find it in our GitHub repo, too.

import datetime
from botocore.vendored import requests
import time

def lambda_handler(request, context):

   # Fivetran API URL
   api_key = request['secrets']['api_key']
   group_id = request['secrets']['group_id']
   api_secret = request['secrets']['api_secret']

   url = '{group_id}/connectors'.format(group_id=group_id)

   # Make a request to the endpoint using the correct auth values

   auth_values = (api_key, api_secret)
   response = requests.request('GET', url, auth=auth_values)

   timestamp = datetime.datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S')

   # Convert JSON to dict
   response_json = response.json()
   item_list = response_json['data']['items']

   # These will be put into 'insert' in the response
   result_items = []
   result_tasks = []
   result_warnings = []

   # Iterate through the items returned by the API
   for entry in item_list:
       entry_item = {}
       for k, v in entry.items():
           if k != 'status':
               entry_item[k] = v

               result_tasks += [{'id': entry['id'], 'message': task['message'], 'code': task['code']} for task in
               result_warnings += [{'id': entry['id'], 'message': task['message'], 'code': task['code']} for task in

               for sub_key in ['sync_state', 'setup_state', 'is_historical_sync', 'update_state']:
                   entry_item[sub_key] = v[sub_key]


   result = {}
   result['state'] = {timestamp: timestamp}
   result['insert'] = {'items': result_items,
                       'tasks': result_tasks,
                       'warnings': result_warnings}

   return result

The API key and API secret can be found via your dashboard. Go to the upper left menu, select “Manage Account,” and enter the “Settings” tab. You can retrieve the names of your groups with a GET request to the groups API.

The steps to configuring your Lambda connector are fairly similar to those of our Redshift migration script. You will need to:

  1. Set up your Lambda function on AWS via the AWS dashboard
    1. Make sure you have the appropriate IAM policies and roles
    2. Create the function
    3. Copy and paste or upload the Python code
    4. Make sure to configure a test event in the following JSON format with your API key, API secret and group ID, and test it.
  "secrets": {
    "api_key": "s4Mpl3APIk3Y",
    "api_secret": "53cr374p1",
    "group_id": "coding_crisis"
  1. Set up your connector via the Fivetran dashboard
    1. Add a connector of the type AWS Lambda
    2. Get the Role ARN from the IAM role that you specified earlier on AWS
    3. Enter the name of the Lambda Function
    4. Enter your API key, API secret and group ID into “Secrets”
    5. Save and test
    6. Set the sync frequency to your preference
{"api_key": "s4Mpl3APIk3Y", "api_secret": "53cr374p1",  "group_id": "coding_crisis"}

You should be able to see the function’s output in your connector group’s data warehouse.

Feel free to contact Charles ( or Stephen ( with any additional questions. If you don’t currently use Fivetran but are interested in what you see, feel free to request a demo here.

Start analyzing your data
in minutes, not months

Launch any Fivetran connector instantly.
We have detected that you are using an adblocking plugin in your browser. We don't show ads, but we rely on advertising services, so it might restrict you from completing important functions or seeing important content. Please make sure you whitelist our website in your adblocking plugin.
Fivetran uses cookies to enhance your user experience and improve the quality of our website. Unless you disable cookies, you consent to the placement and use of cookies as described in our Cookie Policy by continuing to use this website.
Adblock Detection