Powered by Fivetran Detailed Guide

If you are planning to onboard data from multiple end users, you have several options for how to set up your destination(s) based on your and your end users' needs:

• Send data to a single destination database shared by multiple end users:

  • With a single destination group and one schema per connector and end user combination

    Expand to show the diagram

    

  • With a destination group per each end user and one schema per connector and end user combination

    Expand to show the diagram

    

  IMPORTANT: Schema names must be unique in a single shared destination database.

• Send data to a separate destination database for each end user (either your destination database or your end user's destination database):

  • With a destination group per each end user, and one schema per connector

    Expand to show the diagram

    

  • With a destination group per connector type and one schema per end user

    Expand to show the diagram

    

    IMPORTANT: Schema names must be unique in the destination with one schema per connector.

Which data architecture is right for you depends on a number of factors including, first and foremost, your existing data environment.

The following table offers a high-level comparison of the primary approaches:

Single Destination vs. Multiple Destinations

NOTE: If you've already finished creating a destination during your initial setup, you can skip this section. If not, continue to step 1.

1. From the Fivetran dashboard, go to the Destinations page.

2. In the top right corner, click Add Destination.

   

3. Enter a name for your destination, then click Add.

   

4. Select your destination type.

   

5. Complete the form using the setup guide on the right for instructions related to your specific destination. When you're finished, click Save & Test.

   

6. Once connection tests pass successfully, click View Destination.

7. You've created a new destination! If necessary, repeat steps 1 - 7 to create additional destinations within your Fivetran account.

TIP: To toggle between destinations, open the top left drop-down menu and select the desired destination

NOTE: Before creating a destination using the API, you must first create a group (shown in the next section).

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 contains 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 displays 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 does not contain the setup_tests field and the setup_status field always contains incomplete.

There are several ways groups can be implemented to help you stay organized. To understand your options, consider the following scenarios:

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 destination 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 that 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 destinations works well for scenarios where each end user is given/has their own warehouse in a specific destination platform account.

Since a group is created automatically when a new destination is added, to create a group you simply need to create a destination.

Create a new group in your Fivetran 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.

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.

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, go to the Destinations page.

2. In the Destinations list, click on the destination name.

3. Find the group ID.

   

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 must still be completed using the API. The only action the end user takes within the Connect Card is providing authentication information.

What do Connect 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 Connect Cards work?

Implementing Connect Cards

To use Connect Card, complete the following steps:

Create a connector using the Create a Connector API endpoint with the connect_card_config section in the request, and get the connect_card.uri from the API response

TIP: For Powered by Fivetran use cases, since you ingest data from your end users at scale, you should create a connector using the API. Using the API directly (without Connect Cards) requires you to manage the OAuth refresh token experience.

This creates a new connector within a specified group in your Fivetran account and generates Connect Card URI.

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"
    },
    "connect_card_config": {
        "redirect_uri": "http://test_domain.com"
    }
}

Payload parameters

Response

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"
        },
        "connect_card": {
            "token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJkIjp7ImxvZ2luIjp0cnVlLCJ1c2VyIjoiX2FjY291bnR3b3J0aHkiLCJhY2NvdW50IjoiX21vb25iZWFtX2FjYyIsImdyb3VwIjoiX21vb25iZWFtIiwiY29ubmVjdG9yIjoiY29iYWx0X2VsZXZhdGlvbiIsIm1ldGhvZCI6IlBiZkNhcmQiLCJpZGVudGl0eSI6ZmFsc2V9LCJpYXQiOjE2Njc4MzA2MzZ9.YUMGUbzxW96xsKJLo4bTorqzx8Q19GTrUi3WFRFM8BU",
            "uri": "https://fivetran.com/connect-card/setup?redirect_uri=http://test_domain.com&auth=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJkIjp7ImxvZ2luIjp0cnVlLCJ1c2VyIjoiX2FjY291bnR3b3J0aHkiLCJhY2NvdW50IjoiX21vb25iZWFtX2FjYyIsImdyb3VwIjoiX21vb25iZWFtIiwiY29ubmVjdG9yIjoiY29iYWx0X2VsZXZhdGlvbiIsIm1ldGhvZCI6IlBiZkNhcmQiLCJpZGVudGl0eSI6ZmFsc2V9LCJpYXQiOjE2Njc4MzA2MzZ9.YUMGUbzxW96xsKJLo4bTorqzx8Q19GTrUi3WFRFM8BU"
        },
        "connect_card_config": {
            "redirect_uri": "http://test_domain.com"
        }
    }
}

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 greatly speeds up creating connectors through the API.

OPTIONAL: Set schema configuration using the Modify a Connector Schema Config API endpoint

This allows 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, 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

(Optional) If you want to create a Connect Card for existing connector, use the connector ID to create a Connect Card URL

The Connect Card URI is valid for one day.

Request

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

{
    "connect_card_config": {
      "redirect_uri": "http://test_domain.com"
    }
}

Payload

Response

HTTP 200 OK

{
    "code": "Success",
    "message": "Connector Connect Card has been created",
    "data": {
        "connector_id": "speak_inexpensive",
        "connect_card": {
          "token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJkIjp7ImxvZ2luIjp0cnVlLCJ1c2VyIjoiX2FjY291bnR3b3J0aHkiLCJhY2NvdW50IjoiX21vb25iZWFtX2FjYyIsImdyb3VwIjoiX21vb25iZWFtIiwiY29ubmVjdG9yIjoiY29iYWx0X2VsZXZhdGlvbiIsIm1ldGhvZCI6IlBiZkNhcmQiLCJpZGVudGl0eSI6ZmFsc2V9LCJpYXQiOjE2Njc4MzA2MzZ9.YUMGUbzxW96xsKJLo4bTorqzx8Q19GTrUi3WFRFM8BU",
          "uri": "https://fivetran.com/connect-card/setup?redirect_uri=http://test_domain.com&auth=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJkIjp7ImxvZ2luIjp0cnVlLCJ1c2VyIjoiX2FjY291bnR3b3J0aHkiLCJhY2NvdW50IjoiX21vb25iZWFtX2FjYyIsImdyb3VwIjoiX21vb25iZWFtIiwiY29ubmVjdG9yIjoiY29iYWx0X2VsZXZhdGlvbiIsIm1ldGhvZCI6IlBiZkNhcmQiLCJpZGVudGl0eSI6ZmFsc2V9LCJpYXQiOjE2Njc4MzA2MzZ9.YUMGUbzxW96xsKJLo4bTorqzx8Q19GTrUi3WFRFM8BU"
        },
        "connect_card_config": {
          "redirect_uri": "http://test_domain.com"
        }
    }
}

(Optional) Add your logo to the Connect Card

You can add your logo to the Connect Card to give your end user a cohesive experience:

To brand your Connect Card, do the following:

1. In the Fivetran dashboard, go to Account Settings > General > Powered by Fivetran.

   

2. Enter your Company Name.

3. Upload your Logo.

   

   TIP: If you want to change your logo in the future, return to the Powered by Fivetran tab. Click Remove under the logo thumbnail and upload your new logo.

4. Click Save.

Redirect your end user to the Connect Card URI in your user interface

It has one of the following formats depending on whether you are hiding the setup guide:

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

Your end user sees 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 end user is redirected to redirect_uri specified in step 5 with the query parameter redirect_uri={redirect_uri}. If the tests fail, the end user can attempt to re-authorize 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 following diagram illustrates the Connect Card end user workflow 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 must 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 allows a user to define exactly what data is brought in from their data source.

If you have multiple end users with multiple data sources, chances are they 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.

Data Design

It is important that you understand what data you want to sync through Fivetran, as this has a secondary 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 secondary effects to load under usage depending on which copy you choose. The incremental sync strategy you choose - or indeed, already have enabled - also determines which copy you proceed with.

Change Log Configuration

The incremental sync method you choose to implement or have already implemented impacts both the Fivetran configuration and the secondary effect to your database.

Connector Setup: Number of Connectors

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

To help you get familiar with the API, particularly as it relates to Powered by Fivetran, complete the following exercise:

Open items

Open your preferred REST API Client or Command Line Interface (CLI) + cURL (for this exercise, we 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 want to interact with using the API.

List all of the connectors associated with your group ID

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 has 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. See our Connector Config documentation 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 is 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