Powered by Fivetran Quick Start Guide

Single Destination vs. Multiple Destinations

Request

POST https://api.fivetran.com/v1/destinations

{
    "group_id":"target_group_id",
    "service":"snowflake",
    "region":"GCP_US_EAST4",
    "time_zone_offset":"-5",
    "config":{
        "host":"your-account.snowflakecomputing.com",
        "port":443,
        "database":"fivetran",
        "user":"fivetran_user",
        "password":"123456"
    }
}

Payload parameters

Response

HTTP 201 Created

{
    "code":"Success",
    "message":"Destination has been created",
    "data":{
        "id":"decent_dropsy",
        "group_id":"decent_dropsy",
        "service":"snowflake",
        "region":"GCP_US_EAST4",
        "time_zone_offset":"-5",
        "setup_status":"connected",
        "setup_tests":[
            {
                "title":"Host Connection",
                "status":"PASSED",
                "message":""
            },
            {
                "title":"Database Connection",
                "status":"PASSED",
                "message":""
            },
            {
                "title":"Permission Test",
                "status":"PASSED",
                "message":""
            }
        ],
        "config":{
            "host":"your-account.snowflakecomputing.com",
            "port":443,
            "database":"fivetran",
            "user":"fivetran_user",
            "password":"******"
        }
    }
}

If the setup tests pass successfully the setup_status field will contain connected.

HTTP 201 Created

{
    "code":"Success",
    "message":"Destination has been created",
    "data":{
        "id":"decent_dropsy",
        "group_id":"decent_dropsy",
        "service":"snowflake",
        "region":"GCP_US_EAST4",
        "time_zone_offset":"-5",
        "setup_status":"incomplete",
        "setup_tests":[
            {
                "title":"Host Connection",
                "status":"FAILED",
                "message":"Host address is private and not accessible via the internet"
            }
        ],
        "config":{
            "host":"your-account.snowflakecomputing.com",
            "port":443,
            "database":"fivetran",
            "user":"fivetran_user",
            "password":"******"
        }
    }
}

If the tests fail the setup_status field will display incomplete.

HTTP 201 Created

{
    "code":"Success",
    "message":"Destination has been created",
    "data":{
        "id":"decent_dropsy",
        "group_id":"decent_dropsy",
        "service":"snowflake",
        "region":"GCP_US_EAST4",
        "time_zone_offset":"-5",
        "setup_status":"incomplete",
        "config":{
            "host":"your-account.snowflakecomputing.com",
            "port":443,
            "database":"fivetran",
            "user":"fivetran_user",
            "password":"******"
        }
    }
}

If run_setup_tests is set to false in the request the response will not contain the setup_tests field and the setup_status field will always contain incomplete.

Scenario 1: One group for everything

This is the simplest way to get started with Fivetran groups. Using this approach, all end-users are added to a single group. Often, organization is achieved by naming each created connector after the end-user it is associated with. For example, the connector for Acme Company’s Salesforce account would be acme_company_salesforce.

Scenario 2: Dedicated group per end-user

Organizing groups by end-user works well for scenarios where end-user segmentation is desired in the destination. For example, each end-user is given their own warehouse in the shared or specific destination platform account.

Scenario 3: Dedicated group per connector type

This scenario works best when you deliver standardized data products built specific to respective data sources which you can segment in Fivetran by the data source connector type.

Scenario 4: Dedicated group per end-user (separate destinations)

Organizing groups by end-user to their own destination warehouses works well for scenarios where each end-user is given/has their own warehouse in a specific destination platform account.

Request

POST https://api.fivetran.com/v1/groups

{
    "name": "Above_Average_Group_Name"
}

Payload parameters

Response

HTTP 200 OK

{
    "code": "Success",
    "message": "Group has been created",
    "data": {
        "id": "decent_dropsy",
        "name": "Above_Average_Group_Name",
        "created_at": "2020-05-25T15:26:47.306509Z"
    }
}

NOTE: Changes may have been made to this process since this guide was last updated. See the Fivetran API docs for the most-up-date instructions on Creating a Group using the API.

Capture group ID using the API

1. Open your preferred REST API Client or Command Line Interface (CLI) + cURL

2. Send request GET https://api.fivetran.com/v1/groups

   • Before sending, include an Authorization HTTP header in your request using your encoded API key from earlier. It should look like this: Basic {YOUR-API-KEY}

   

3. Locate your group id(s) in the response

   

   TIP: Group IDs are composed of two randomly generated strings separated by an underscore. We hope you find them as comical as we do.

   

4. Pick a group ID from the response and make note of it. You’ll need this in the next section.

   TIP: Group IDs are unique identifiers that allow you to access details about a particular group within the Fivetran system.

Capture group ID using the UI

TIP: As you’ll see below, locating a single group ID from the UI only requires a few clicks. However, if you have many groups in your account (and thus many group IDs) you may find it easier to accomplish this task using the API. This method will allow you to capture all group IDs in a single API request.

1. From the Fivetran Dashboard, click Destination in the left menu

   

2. Find the group ID associated with the destination you are currently viewing in the Fivetran dashboard.

   

What is a Connect Card?

A Connect Card is a Fivetran popup that is designed to appear when an end-user attempts to connect one of their data sources to your app or portal. This popup prompts the end-user to authorize a connection between their data source and Fivetran themselves, avoiding the need for any credential sharing between you and your end-user.

NOTE: Configuration of the connector will still be completed using the API. The only action the user will take within the Connect Card is providing authentication information.

What do Connector Cards look like?

The easiest way to understand Connect Cards is through the perspective of an end-user:

A simplified illustration of the Connect Card experience

How do Connector Cards work?

Implementing Connect Cards

To use Connect Card, complete the following steps:

Create a connector using the create a connector API endpoint and get the connector ID from the API response.

TIP: For Powered by Fivetran use cases, since you’ll be ingesting data from your end-users at scale, you’ll likely want to create a connector via the API. Using the API directly (without Connect Cards) will require you to manage the OAuth refresh token experience.

Creates a new connector within a specified group in your Fivetran account. Runs setup tests and returns testing results.

Request

POST https://api.fivetran.com/v1/connectors

{
    "service": "criteo",
    "group_id": "projected_sickle",
    "trust_certificates": true,
    "run_setup_tests": true,
    "paused": true,
    "config": {
        "schema": "criteo",
        "username": "myuser",
        "password": "mypassword",
        "app_token": "myapptoken"
    },
    "auth": {
        "client_access": {
            "client_id": "my_client_id",
            "client_secret": "my_client_secret"
        },
        "refresh_token": "my_refresh_token"
    }
}

Payload parameters

Responses

HTTP 201 Created

{
    "code": "Success",
    "message": "Connector has been created",
    "data": {
        "id": "speak_inexpensive",
        "group_id": "projected_sickle",
        "service": "criteo",
        "service_version": 0,
        "schema": "criteo",
        "connected_by": "interment_burdensome",
        "created_at": "2018-12-01T15:43:29.013729Z",
        "succeeded_at": null,
        "failed_at": null,
        "sync_frequency":60,
        "status": {
            "setup_state": "incomplete",
            "sync_state": "scheduled",
            "update_state": "on_schedule",
            "is_historical_sync": true,
            "tasks": [],
            "warnings": []
        },
        "setup_tests": [{
            "title": "Validate Login",
            "status": "FAILED",
            "message": "Invalid login credentials"
        }],
        "config": {
            "username": "myuser",
            "password": "******",
            "api_token": "******",
            "service_version": "0"
        }
    }
}

NOTE: The connector setup configuration (config field) is different for each connector type. To help get you started, see the Fivetran REST API docs for an example for your specific connector(s).

TIP: Since the connector is created without specifying authorization parameters, we strongly recommend setting the value of the run_setup_tests parameter to FALSE. This will greatly speed up creating connectors through the API.

OPTIONAL: Set schema configuration using the Modify a Connector Schema Config API endpoint. This will allow you to define exactly what data you bring in from your end-user’s data source

NOTE: For connectors that require a report to be configured (such as our Facebook Ad Insights Connector), review the source system documentation to understand the Measures & Dimensions to be configured to retrieve the required data to support those analytic models.

Best practices:

• Set everything you don’t want the end-user to customize or configure (e.g., tables to enable / disable, column hashing for PII, sync frequency, etc.)
• Set enable_new_by_default to FALSE. This will ensure new tables added to an end-user’s data source don’t automatically start flowing through to the warehouse
• For certain apps (like Salesforce), you can choose default schema configuration or leave it empty. Leaving it empty will allow an end-user to modify (block tables, columns, etc.) in the authorization step later

Create a Connect card token using the connector ID. The lifetime of this token is ONE DAY.

Request

POST https://api.fivetran.com/v1/connectors/{connector_id}/connect-card-token

Payload

The request body must be empty.

Response

HTTP 200 OK

Redirect your end-user to the Connect Card URL in your user interface, which can be formed in the following way:

https://fivetran.com/connect-card/setup?redirect_uri={redirect_uri}&auth={connect_card_token}

https://fivetran.com/connect-card/setup?redirect_uri={redirect_uri}&auth={connect_card_token}&hide_setup_guide=true

Query parameters

Your end-user will see the following page that allows them to authorize the connector:

NOTE: How to render API responses in the UI of your web app or portal is beyond the scope of this guide. For an introduction to key concepts in this subject area, check out this article.

After authorization, the end-user will click Save & Test.

If the tests pass, the page will be redirected to redirect_uri specified in step 4 with the query parameter redirect_uri={redirect_uri}. If the tests fail, the page will not be redirected and the end-user can attempt to reauthorize the connector.

After successful connector authorization.

You can update connector settings through the Fivetran dashboard or through the Modify a Connector public API endpoint.

TIP: Having trouble? Visit our documentation on Connect Cards for the latest information.

Example of an actual Connect Card Implementation

The Connect Card end-user flow in CaliberMind’s application

What is PBF Headless?

PBF Headless is our solution for software development teams that want to embed Fivetran in their app but don’t want to use Fivetran Connect Cards.

Instead, developers leverage the Fivetran API behind the scenes and build their own user experience on top of it. This allows you to leverage the powerful functionality of Fivetran while retaining complete control over the front-end experience of your application.

PBF Headless enables software development teams to control the entire experience on the front-end of their application while relying on the Fivetran API to deliver functionality on the back-end

When implementing PBF Headless, you will have to create the equivalent of Fivetran Connect Cards.

How does PBF Headless work?

Unlike Connect Cards, implementing PBF Headless requires you to build a user experience on top of the Fivetran API. This user experience must collect credentials from the user that are required to authenticate a connection between their data source and Fivetran.

Implementing PBF Headless

NOTE: Unlike using Connect Cards, implementing PBF Headless requires you to manage the OAuth token exchange process between the end-user’s application and Fivetran.

1. Confirm your desired connector(s) can be authorized using the Fivetran API

   

2. OPTIONAL: Take note of our supported authorization methods.

You can use several authorization methods using the API. Each method involves defining the auth section in the API request (e.g., client_id, client_secret, refresh_token).

{
"service": "salesforce",
"group_id": "tranquil_playmaker",
"config": {
"schema": "testsfdc1",
"username": "dude@fivetran.com.5t",
"password": "XaBXXXXXXXXXX_XXXXXXXX_XXXXXXXXXXX_XXX"
}
}

Method 1: providing the credentials using the login and password fields in the API request.

    "auth": {
        "client_access": {
            "client_id": "my_client_id",
            "client_secret": "my_client_secret"
        },
        "refresh_token": "my_refresh_token"
    }
}

Method 2: providing the credentials using the token field and related field(s) in the API request.

{
    "service": "airtable",
    "group_id": "target_group_id",
    "config": {
        "schema_prefix" : "test_airtable",
        "api_key" : "****"
    }
}

Method 3: providing the credentials using the key field and related field(s) in the API request.

3. Review the setup configuration for your target data source(s) and which authorization methods it supports (see the “Authorization” section after clicking on your data source).

   TIP: Many of our web application connectors include support for OAuth tokens. Using such a connector, a token can be passed from an end-user’s application to the Fivetran API to authorize a connection.

4. Pick the authorization method(s) you’d like to use for your implementation.

5. Design a user experience in your application that captures the data source credentials required to support the authorization method you selected in Step 3.

6. OPTIONAL: Design a schema configuration experience on top of the Modify a Connector Schema Config API endpoint. This experience would allow a user to define exactly what data is brought in from their data source.

Connect to your end-user’s data

If you have multiple end-users with multiple data sources, chances are they will have different security and networking requirements for how data needs to be ingested by 3rd-party sources such as Fivetran.

This is a quick guide for which connection type is appropriate for which scenario.

Direct Connection

Simple and most relevant to Cloud or Public-Facing subnets.

(Forward) SSH Tunnel

Most security with the least complexity; best suited to end-users with private subnets who can easily set up and maintain an SSH Host and can provide port access.

(Reverse) SSH Tunnel

Parity of security with a Forward SSH Tunnel, more complexity but control over the invocation of the SSH connection; best suited to end-users with private subnets who can easily set up and maintain an SSH Host & who cannot provide direct port access.

VPN Tunnel

The highest level of security, but also the highest level of complexity to set up and maintain; best-suited to end-users who cannot provide any access to their environment or who have complex network configurations that cannot be solved with only an SSH Host.

Prepare your end-users to prepare their data

Data Design

It is important that you understand what data you want to sync through Fivetran, as this will have a knock-on effect on the optimal configuration of Fivetran.

Database Connection: Master vs Read Replica

You should consider which copy of your database Fivetran connects to because there can be knock-on effects to load under usage depending on which copy you choose. The incremental update strategy you choose - or indeed, already have enabled - will also determine which copy you proceed with.

Change Log Configuration

The incremental update mechanism you choose to implement or have already implemented will have an impact on both the Fivetran configuration and the knock-on effect to your database.

Connector Setup: Number of Connectors

This concern is linked to the make-up of your database and will impact the throughput of your Fivetran data pipeline.

Open items

Open your preferred REST API Client or Command Line Interface (CLI) + cURL (for this exercise, we will use Postman).

Retrieve your group ID

Why start here? Connectors and users are often organized by groups. And many PBF implementations use a “one group per end-user” policy. For both scenarios, retrieving your group ID gives you the ability to specify which set of connectors or which similar end-user you’d like to interact with using the API.

1. Send GET https://api.fivetran.com/v1/groups request to the Fivetran API.

   NOTE: Before you submit the request, include an Authorization header with the following value: Basic {Your-Base-64-encoded-API-Key}. Don’t have your Base64 encoded API key? See instructions for obtaining it, here.

   

2. Find a group ID in the response that is associated with a group containing one or more connectors. Take note of that group ID.

   

List all of the connectors associated with your group ID

1. Send GET https://api.fivetran.com/v1/groups/{your_group_id}/connectors request to the Fivetran API. Make sure to incorporate the group ID you noted in the previous step.

   

2. The response will return a list of details about each of the connectors in the specified group.

   NOTE: You can display connector metadata from this response in the UI of your web app or analytics portal. This will give end-users information about their connectors that they’d likely be interested in—such as the status of each of their connectors and the last time their data was synced successfully.

   

3. Take note of the connector ID for one of the connectors listed.

   

List All Users

Returns a list of all users within your Fivetran account.

Invite a User

Invites a new user to your Fivetran account. The invited user will have access to the account only after accepting the invitation. Invited user details are still accessible through the API.

See all user management actions supported by the REST API

List All Groups

Returns a list of all groups within your Fivetran account.

List All Connectors within a Group

Returns a list of information about all connectors within a group in your Fivetran account

See all group management actions supported by the REST API

Create a Destination

Creates a new destination within a specified group in your Fivetran account.

See all destination management actions supported by the REST API

Retrieve Connector Details

Returns a connector object if a valid identifier was provided.

Create a Connector

Creates a new connector within a specified group in your Fivetran account. Runs setup tests and returns testing results.

NOTE: The connector setup configuration (config field) is different for each connector. Click here to find the specific setup configuration for your connector.

Sync Connector Data

Triggers a data sync for an existing connector within your Fivetran account without waiting for the next scheduled sync. This action does not override the standard sync frequency you defined in the Fivetran dashboard.

If there is a data sync already in progress, that sync is immediately stopped and restarted. If the connector is paused, the data sync will be scheduled to be performed when the connector is re-enabled.

Retrieve a Connector Schema Config

Returns the connector schema config for an existing connector within your Fivetran account.

Modify a Connector Schema Config

Updates the schema config for an existing connector within your Fivetran account (for a single schema for a connector with multiple schemas).

Modify a Connector Database Schema Config

Updates the database schema config for an existing connector within your Fivetran account (for a single schema within a connector with multiple schemas).

Create a new connector based on an existing connector’s parameters

You can create new connectors using the parameters of an existing connector.

See all connector management actions supported by the REST API