Connector Management Overview

REST API supports the following connector management actions:

• Retrieve source metadata
• Retrieve connector configuration metadata
• Create a connector
• Retrieve connector details
• Modify a connector
• Sync connector data
• Re-sync Connector Data (Historical Sync)
• Re-sync connector table data
• Run connector setup tests
• Delete a connector
• Retrieve a connector schema config
• Retrieve source table columns config
• Reload a connector schema config
• Modify a connector schema config
• Modify a connector database schema config
• Modify a connector table config
• Modify a connector column config
• Use Case: How to select only the columns you want to sync and block new tables and columns for new connectors
• Use Case: How to enable sync only for columns that you need
• Use case: How to set a custom sync frequency
• Use case: How to set a time for your daily syncs
• Use case: How to trigger syncs manually only when you need them

Retrieving the exact names of schemas, tables, and columnslink

In order for the request to succeed, you must specify the correct parameters. When you call endpoints that require passing schema, table, or column names as path or payload parameters, specify the names exactly as they appear in the source.

IMPORTANT: Do not rely on the example requests and responses in our REST API documentation or on how the names appear in your destination. Fivetran transforms schema, table, and column names according to our naming conventions.

We recommend that you retrieve the exact schema, table, and column names of your connector before you call endpoints that require passing schema, table, or column as path or payload parameters.

You can use either of the following methods:

• In your Fivetran dashboard: Go to the Schema tab of your connector and use the schema, table, or column names displayed there. See the following example:

  

• Using Fivetran REST API: Call the Retrieve a Connector Schema Config endpoint.

NOTE: The connector schemas define what tables and columns present in the source your connector syncs. Most Fivetran REST API endpoint requests and responses use the connector schema names as path or payload parameters. On the other hand, the destination schema that we use as a name of your connector defines how the data is stored in your destination. You specify the destination schema name either in the connector setup form or as a value of the schema payload parameter in the Create a Connector endpoint request when creating a connector.

Retrieve source metadatalink

Returns all available source types within your Fivetran account. This endpoint makes it easier to display Fivetran connectors within your application because it provides metadata including the proper source name (‘Facebook Ad Account’ instead of facebook_ad_account), the source icon, and links to Fivetran resources. As we update source names and icons, that metadata will automatically update within this endpoint.

Requestlink

GET https://api.fivetran.com/v1/metadata/connector-types

Responselink

HTTP 200 OK

{
    "code": "Success",
    "data": {
        "items": [
            {
                "id": "facebook_ad_account",
                "name": "Facebook Ad Account",
                "type": "Marketing",
                "description": "Facebook Ad Account provides attribute data on Facebook Ad Accounts",
                "icon_url": "https://fivetran.com/integrations/facebook/resources/facebook.svg",
                "link_to_docs": "https://fivetran.com/docs/applications/facebook-ad-account",
                "link_to_erd": "https://fivetran.com/docs/applications/facebook-ad-account#schemainformation"
            }
        ],
        "next_cursor": "eyJza2lwIjoxMDB9"
    }
}

Retrieve connector configuration metadata BETAlink

Returns metadata of configuration parameters and authorization parameters for a specified connector type.

Requestlink

GET api.fivetran.com/v1/metadata/connector-types/{connector_type}

Responselink

HTTP 200 OK

{
    "code": "Success",
    "data": {
      "id": "connector_type",
      "name": "Service Display Name",
      "type": "Marketing",
      "description": "Service detailed description",
      "icon_url": "https://fivetran.com/integrations/link/to/icon.svg",
      "link_to_docs": "https://fivetran.com/docs/service-docs",
      "link_to_erd": "https://fivetran.com/docs/link/to/service#schemainformation",
      "auth": {
        "type": "object",
        "properties": {
          "access_token": {
            "type": "string",
            "description": "The long-lived Access token carries the information necessary to access API resources."
          },
          ...
        }
      }
      "config": {
        "type": "object",
        "properties": {
          "schema": {
            "type": "string",
            "description": "Destination schema. Schema is permanent and cannot be changed after connection creation"
          },
          "sync_mode": {
            "description": "",
            "one_of": [
              {
                "type": "string",
                "description": "Automatically sync all existing and new client accounts",
                "const": "AllAccounts"
              },
              {
                "type": "string",
                "description": "Select client accounts for sync",
                "const": "SpecificAccounts"
              },
              {
                "type": "string",
                "description": "Select manager accounts whose clients you want to sync",
                "const": "ManagerAccounts"
              }
            ]
          },
          ...
        },
        "required": [
          "schema",
          ...
        ]
      }
    }
}

Schema format is compatible with JSON-Schema Draft-06.

Create a Connectorlink

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

Requestlink

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

{
    "service": "criteo",
    "group_id": "projected_sickle",
    "trust_certificates": true,
    "run_setup_tests": true,
    "paused": true,
    "pause_after_trial": true,
    "sync_frequency": 1440,
    "daily_sync_time": "03:00",
    "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"
    }
}

Responselink

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",
        "paused": true,
        "pause_after_trial": true,
        "connected_by": "interment_burdensome",
        "created_at": "2018-12-01T15:43:29.013729Z",
        "succeeded_at": null,
        "failed_at": null,
        "sync_frequency": 1440,
        "daily_sync_time": "03:00",
        "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"
        }
    }
}

If run_setup_tests is set to FALSE in the request, the response will not contain the setup_tests field.

See how to create a new connector based on an existing connector’s parameters.

Note that the config parameter can be presented in v2 configuration format. For more information, see our Retrieve Connector Details endpoint documentation.

Retrieve Connector Detailslink

Returns a connector object if a valid identifier was provided.

Currently, we support two formats for the connector configuration response payload. The first is the default legacy format of the configuration. The second one is a new configuration format (v2) we introduced in January 2021.

IMPORTANT: All accounts created after September 9, 2021 receive the connector configuration response in the v2 format by default.

To retrieve the connector configuration info in the v2 format, you should add version=2 to the Accept request header: --header 'Accept: application/json;version=2'

The v2 config format is consistent with the configuration payload parameter used for Modifying a connector.

The connector configuration response depends on the connector type.

Below are examples of the legacy and new v2 connector configuration request and response that have different Accept header values, respectively. These examples are based on the Google Ads connector:

Legacy request:

GET https://api.fivetran.com/v1/connectors/{connector_id} --header 'Accept: application/json'

Legacy response:

{
    "code": "Success",
    "data": {
        ...
        "config": {
            "sync_mode": "Selected Accounts",
            "latest_version": "4",
            "configuration": "Custom Report",
            "accounts": "0987654321",
            "report_type": "AD_PERFORMANCE_REPORT",
            "service_version": "4",
            "customer_id": "123-456-7890",
            "fields": "PolicySummary, AdType, Date",
            "conversion_window_size": 30,
            "historical_sync_timeframe": "12 months"
        }
    }
}

New v2 request:

GET https://api.fivetran.com/v1/connectors/{connector_id} --header 'Accept: application/json;version=2'

New v2 response:

{
    "code": "Success",
    "data": {
        ...
        "config": {
            "sync_mode": "SpecificAccounts",
            "customer_id": "123-456-7890",
            "accounts": [
                "0987654321"
            ],
            "conversion_window_size": 30,
            "report_type": "AD_PERFORMANCE_REPORT",
            "fields": [
                "PolicySummary",
                "AdType",
                "Date"
            ]
        },
        "source_sync_details": {
            "accounts": [
                "0987654321"
            ]
        }
    }
}

The exact set of fields we return when the source_sync_details parameter is specified depends on the connector type. The source_sync_details section is not included in the response if the parameter is omitted in the request. The source_sync_details section in the above example contains the information about synced accounts.

We return sync details for the following connectors in V2 configuration mode:

• Asana
• Facebook Ads
• Facebook Ad Insights
• Google Ads
• Google Analytics
• Google Analytics (MCF)
• Google Campaign Manager 360
• Google Sheets
• Microsoft Advertising
• Salesforce

Requestlink

GET https://api.fivetran.com/v1/connectors/{connector_id}

Responselink

HTTP 200 OK

{
    "code": "Success",
    "data": {
        "id": "connector_id",
        "group_id": "group_id",
        "service": "adwords",
        "service_version": 4,
        "schema": "adwords.schema",
        "paused": true,
        "pause_after_trial": true,
        "connected_by": "monitoring_assuring",
        "created_at": "2020-03-11T15:03:55.743708Z",
        "succeeded_at": "2020-03-17T12:31:40.870504Z",
        "failed_at": "2021-01-15T10:55:00.056497Z",
        "sync_frequency": 360,
        "schedule_type": "auto",
        "status": {
            "setup_state": "broken",
            "sync_state": "scheduled",
            "update_state": "delayed",
            "is_historical_sync": false,
            "tasks": [
                {
                    "code": "reconnect",
                    "message": "Reconnect"
                }
            ],
            "warnings": []
        },
        "config": {
            "sync_mode": "SpecificAccounts",
            "customer_id": "XXX-XXX-XXXX",
            "accounts": [
                "1234567890"
            ],
            "conversion_window_size": 30,
            "report_type": "AD_PERFORMANCE_REPORT",
            "fields": [
                "PolicySummary",
                "AdType",
                "Date"
            ]
        },
        "source_sync_details": {
            "accounts": [
                "1234567890"
            ]
        }
    }
}

Modify a Connectorlink

Updates the information for an existing connector within your Fivetran account.

Requestlink

PATCH https://api.fivetran.com/v1/connectors/{connector_id}

{
    "paused": false,
    "pause_after_trial": true,
    "sync_frequency": 60,
    "trust_certificates": true,
    "run_setup_tests": true,
    "config": {
        "username": "newuser",
        "password": "newpassword"
    },
    "auth": {
        "client_access": {
            "client_id": "my_client_id",
            "client_secret": "my_client_secret"
        },
        "refresh_token": "my_refresh_token"
    },
    "schedule_type": "manual"
}

Responselink

HTTP 200 OK

{
    "code": "Success",
    "message": "Connector has been updated",
    "data": {
        "id": "speak_inexpensive",
        "group_id": "projected_sickle",
        "service": "criteo",
        "service_version": 0,
        "schema": "criteo",
        "paused": true,
        "pause_after_trial": true,
        "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": "newuser",
            "password": "******",
            "api_token": "******",
            "service_version": "0"
        }
    }
}

If the run_setup_tests parameter is set to FALSE in the request, then the response will not contain the setup_tests field.

Note that the config parameter can be presented in v2 configuration format. For more information, see our Retrieve Connector Details endpoint documentation.

Sync Connector Datalink

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.

Requestlink

POST https://api.fivetran.com/v1/connectors/{connector_id}/sync

{
  "force": true
}

Responselink

HTTP 200 OK

{
    "code": "Success",
    "message": "Sync has been successfully triggered for connector with id 'connector_id1'"
}

Legacy Requestlink

POST https://api.fivetran.com/v1/connectors/{connector_id}/force

NOTE: We renamed this endpoint and now use sync as an action term to make our API contract match the terms on the Fivetran dashboard. This legacy endpoint is available for backward compatibility.

Responselink

HTTP 200 OK

{
    "code": "Success",
    "message": "Sync has been successfully triggered for connector with id 'connector_id1'"
}

Re-sync Connector Data (Historical Sync)link

Triggers a full historical sync of a connector or multiple schema tables within a connector.

Requestlink

POST https://api.fivetran.com/v1/connectors/{connector_id}/resync

{
    "scope": {
        "schema1": ["table1", "table2"],
        "schema2": ["table3", "table4", "table5"],
        ...,
        "schemaN": ["table1", "table2", ..., "tableM"]
    }
}

Path parameterslink

Responselink

HTTP 200 OK

{
    "code": "Success",
    "message": "Re-sync has been triggered successfully"
}

Re-sync Connector Table Datalink

Triggers a historical sync of all data for multiple schema tables within a connector. This action does not override the standard sync frequency you defined in the Fivetran dashboard.

If the connector is paused, the table sync will be scheduled to be performed when the connector is re-enabled. If there is a data sync already in progress, we will try to complete it. If it fails, the request will be declined and the HTTP 409 Conflict error will be returned.

NOTE: If you want to trigger a historical sync for the entire connector, use the Re-sync Connector Data (Historical Sync) endpoint instead.

Requestlink

POST https://api.fivetran.com/v1/connectors/{connector_id}/schemas/tables/resync

{
    "schema1": ["table1", "table2"],
    "schema2": ["table3", "table4", "table5"],
     ...,
    "schemaN": ["table1", "table2", ..., "tableM"]
}

Responselink

HTTP 200 OK

{
    "code": "Success",
    "message": "Re-sync has been triggered successfully"
}

Run connector setup testslink

Runs the setup tests for an existing connector within your Fivetran account.

Requestlink

POST https://api.fivetran.com/v1/connectors/{connector_id}/test

{
    "trust_certificates": true,
    "trust_fingerprints": true
}

Responselink

HTTP 200 OK

{
    "code": "Success",
    "message": "Setup tests were completed",
    "data": {
        "id": "speak_inexpensive",
        "group_id": "projected_sickle",
        "service": "criteo",
        "service_version": 0,
        "schema": "criteo",
        "paused": false,
        "pause_after_trial": true,
        "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": "newuser",
            "password": "******",
            "api_token": "******",
            "service_version": "0"
        }
    }
}

Note that the config parameter can be presented in v2 configuration format. For more information, see our Retrieve Connector Details endpoint documentation.

Delete a Connectorlink

Deletes a connector from your Fivetran account.

Requestlink

DELETE https://api.fivetran.com/v1/connectors/{connector_id}

Responselink

HTTP 200 OK

{
    "code": "Success",
    "message": "Connector with id 'speak_inexpensive' has been deleted"
}

Retrieve a Connector Schema Configlink

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

Requestlink

GET https://api.fivetran.com/v1/connectors/{connector_id}/schemas

Responselink

HTTP 200 OK

{
    "code": "Success",
    "data": {
        "enable_new_by_default": false,
        "schemas": {
            "schema_1": {
                "name_in_destination": "schema_1",
                "enabled": true,
                "tables": {
                    "table_1": {
                        "name_in_destination": "table_1",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": true
                        },
                        "columns": {
                            "column_1": {
                                "name_in_destination": "column_1",
                                "enabled": true,
                                "hashed": false,
                                "enabled_patch_settings": {
                                    "allowed": false,
                                    "reason_code": "SYSTEM_COLUMN",
                                    "reason": "The column does not support exclusion as it is a Primary Key"
                                }
                            },
                            "column_2": {
                                "name_in_destination": "column_2",
                                "enabled": true,
                                "hashed": false,
                                "enabled_patch_settings": {
                                    "allowed": true
                                }
                            },
                            "column_3": {
                                "name_in_destination": "column_3",
                                "enabled": true,
                                "hashed": true,
                                "enabled_patch_settings": {
                                    "allowed": true
                                }
                            }
                        }
                    },
                    "table_2": {
                        "name_in_destination": "table_2",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": false,
                            "reason_code": "SYSTEM_TABLE"
                        }
                    }
                }
            },
            "schema_2": {
                "name_in_destination": "schema_2",
                "enabled": true,
                "tables": {
                    "table_1": {
                        "name_in_destination": "table_1",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": true
                        },
                        "columns": {
                            "name_in_destination": "column_1",
                            "column_1": {
                                "enabled": false
                            }
                        }
                    },
                    "table_2": {
                        "name_in_destination": "table_2",
                        "enabled": false,
                        "enabled_patch_settings": {
                            "allowed": false,
                            "reason_code": "OTHER",
                            "reason": "Permission denied"
                        }
                    }
                }
            }
        }
    }
}

Connector Schema Configlink

The connector schema config stores the settings for schema, table, and column objects, and allows you to enable or disable it. If a schema, table, or column is enabled, Fivetran syncs the data for those objects to your destination.

A newly created connector doesn’t have a schema config. Fivetran generates the schema config after the initial connector sync or after triggering the Reload a connector schema config endpoint. The config generated by default contains only schemas and tables.

There are the following possible modes depending on the enable_new_by_default flag:

• enable_new_by_default = true - (allow all) new schemas and tables that appear in the connector source are added to the config in the enabled state.
• enable_new_by_default = false - (allow columns) new schemas and tables that appear in the connector source are added to the config in the disabled state.

Information about schemas and tables is always present in the config, but if the source produces new columns, they are not excluded from sync by default for both modes described above. All columns under enabled schemas and tables are considered to be enabled even though they are not present in the config.

If you need to block a particular column from sync you will have to PATCH the connector config. The patched column is guaranteed to be returned to the API response no matter column is enabled or disabled.

Let’s have a look at examples of different use-cases:

{
  "enable_new_by_default": true,
  "schemas": {
    "schema_1": {
      "enabled": true,
      "hashed": false,
      "tables": {
        "table_1": {
          "enabled": true
        }
      }
    }
  }
}

{
  "enable_new_by_default": true,
  "schemas": {
    "schema_1": {
      "enabled": true,
      "tables": {
        "table_1": {
          "enabled": true
        },
        "table_2": {
          "enabled": true
        }
      }
    }
  }
}

{
  "enable_new_by_default": false,
  "schemas": {
    "schema_1": {
      "enabled": true
      "tables": {
        "table_1": {
          "enabled": true
        },
        "table_2": {
          "enabled": true
        }
      }
    }
  }
}

{
  "enable_new_by_default": false,
  "schemas": {
    "schema_1": {
      "enabled": true,
      "tables": {
        "table_1": {
          "enabled": true
        },
        "table_2": {
          "enabled": true
        },
        "table_3": {
          "enabled": false
        }
      }
    }
  }
}

{
  "enable_new_by_default": false,
  "schemas": {
    "schema_1": {
      "enabled": true,
      "hashed": false,
      "tables": {
        "table_1": {
          "enabled": true,
          "columns": {
            "column_1": {
              "enabled": false,
              "hashed": false
            }
          }
        },
        "table_2": {
          "enabled": true
        },
        "table_3": {
          "enabled": false
        }
      }
    }
  }
}

{
  "enable_new_by_default": false,
  "schemas": {
    "schema_1": {
      "enabled": true,
      "hashed": false,
      "tables": {
        "table_1": {
          "enabled": true,
          "columns": {
            "column_1": {
              "enabled": false,
              "hashed": false
            }
          }
        },
        "table_2": {
          "enabled": true
        },
        "table_3": {
          "enabled": false
        }
      }
    }
  }
}

Retrieve Source Table Columns Configlink

Returns the source table columns config for an existing connector within your Fivetran account.

Requestlink

GET https://api.fivetran.com/v1/connectors/{connector_id}/schemas/{schema}/tables/{table}/columns

Responselink

HTTP 200 OK

{
    "code": "Success",
    "columns": {
        "column_1": {
            "name_in_destination": "column_1",
            "enabled": true,
            "hashed": false,
            "enabled_patch_settings": {
                "allowed": false,
                "reason_code": "SYSTEM_COLUMN",
                "reason": "The column does not support exclusion as it is a Primary Key"
            }
        },
        "column_2": {
            "name_in_destination": "column_2",
            "enabled": true,
            "hashed": false,
            "enabled_patch_settings": {
                "allowed": true
            }
        },
        "column_3": {
            "name_in_destination": "column_3",
            "enabled": false,
            "hashed": false,
            "enabled_patch_settings": {
                "allowed": true
            }
        }
    }
}

The response contains an array of columns with the Fivetran sync settings for each column.

Reload a Connector Schema Configlink

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

We advise that you use this method if the connector was recently created or its schema is outdated.

Requestlink

POST https://api.fivetran.com/v1/connectors/{connector_id}/schemas/reload

{
    "exclude_mode":"PRESERVE"
}

Responselink

HTTP 200 OK

{
    "code": "Success",
    "data": {
        "enable_new_by_default": false,
        "schemas": {
            "schema_1": {
                "name_in_destination": "schema_1",
                "enabled": true,
                "tables": {
                    "table_1": {
                        "name_in_destination": "table_1",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": true
                        },
                        "columns": {
                            "column_1": {
                                "name_in_destination": "column_1",
                                "enabled": true,
                                "hashed": false,
                                "enabled_patch_settings": {
                                    "allowed": false,
                                    "reason_code": "SYSTEM_COLUMN",
                                    "reason": "The column does not support exclusion as it is a Primary Key"
                                }
                            },
                            "column_2": {
                                "name_in_destination": "column_2",
                                "enabled": true,
                                "hashed": false,
                                "enabled_patch_settings": {
                                    "allowed": true
                                }
                            },
                            "column_3": {
                                "name_in_destination": "column_3",
                                "enabled": true,
                                "hashed": true,
                                "enabled_patch_settings": {
                                    "allowed": true
                                }
                            }
                        }
                    },
                    "table_2": {
                        "name_in_destination": "table_2",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": false,
                            "reason_code": "SYSTEM_TABLE"
                        }
                    }
                }
            },
            "schema_2": {
                "name_in_destination": "schema_2",
                "enabled": true,
                "tables": {
                    "table_1": {
                        "name_in_destination": "table_1",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": true
                        },
                        "columns": {
                            "column_1": {
                                "name_in_destination": "column_1",
                                "enabled": false
                            }
                        }
                    },
                    "table_2": {
                        "name_in_destination": "table_2",
                        "enabled": false,
                        "enabled_patch_settings": {
                            "allowed": false,
                            "reason_code": "OTHER",
                            "reason": "Permission denied"
                        }
                    }
                }
            }
        }
    }
}

Modify a Connector Schema Configlink

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

Requestlink

PATCH https://api.fivetran.com/v1/connectors/{connector_id}/schemas

{
    "enable_new_by_default": true,
    "schemas": {
        "schema_1": {
            "enabled": true,
            "tables": {
                "table_1": {
                    "enabled": true
                },
                "table_2": {
                    "enabled": false,
                    "columns": {
                        "column_2": {
                            "enabled": true,
                            "hashed": false
                        },
                        "column_3": {
                            "hashed": true
                        }
                    }
                }
            }
        },
        "schema_2": {
            "enabled": false
        }
    }
}

Responselink

HTTP 200 OK

{
    "code": "Success",
    "data": {
        "enable_new_by_default": true,
        "schemas": {
            "schema_1": {
                "name_in_destination": "schema_1",
                "enabled": true,
                "tables": {
                    "table_1": {
                        "name_in_destination": "table_1",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": true
                        }
                    },
                    "table_2": {
                        "name_in_destination": "table_2",
                        "enabled": false,
                        "enabled_patch_settings": {
                            "allowed": true
                        },
                        "columns": {
                            "column_1": {
                                "name_in_destination": "column_1",
                                "enabled": true,
                                "hashed": false,
                                "enabled_patch_settings": {
                                    "allowed": false,
                                    "reason_code": "SYSTEM_COLUMN",
                                    "reason": "The column does not support exclusion as it is a Primary Key"
                                }
                            },
                            "column_2": {
                                "name_in_destination": "column_2",
                                "enabled": true,
                                "hashed": false,
                                "enabled_patch_settings": {
                                    "allowed": true
                                }
                            },
                            "column_3": {
                                "name_in_destination": "column_3",
                                "enabled": true,
                                "hashed": true,
                                "enabled_patch_settings": {
                                    "allowed": true
                                }
                            }
                        }
                    }
                }
            },
            "schema_2": {
                "name_in_destination": "schema_2",
                "enabled": false,
                "tables": {
                    "table_1": {
                        "name_in_destination": "table_1",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": true
                        }
                    },
                    "table_2": {
                        "name_in_destination": "table_2",
                        "enabled": false,
                        "enabled_patch_settings": {
                            "allowed": false,
                            "reason_code": "SYSTEM_TABLE"
                        }
                    }
                }
            }
        }
    }
}

The response contains only the difference between your selections and the schema config. The schema config includes schemas, tables, and columns. If you have not specified any schema configurations that differ from the default, the response will consist of only the top schema level. For more information, see also the Connector Schema config section.

Modify a Connector Database Schema Configlink

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

Requestlink

PATCH https://api.fivetran.com/v1/connectors/{connector_id}/schemas/{schema}

{
        "enabled": true,
        "tables": {
            "table_1": {
                "enabled": true
            },
            "table_2": {
                "enabled": false,
                "columns": {
                    "column_2": {
                        "enabled": true,
                        "hashed": false
                    },
                    "column_3": {
                        "hashed": true
                    }
                }
            }
        }
}

Responselink

HTTP 200 OK

{
    "code": "Success",
    "data": {
        "enable_new_by_default": true,
        "schemas": {
            "schema_1": {
                "name_in_destination": "schema_1",
                "enabled": true,
                "tables": {
                    "table_1": {
                        "name_in_destination": "table_1",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": true
                        }
                    },
                    "table_2": {
                        "name_in_destination": "table_2",
                        "enabled": false,
                        "enabled_patch_settings": {
                            "allowed": true
                        },
                        "columns": {
                            "column_1": {
                                "name_in_destination": "column_1",
                                "enabled": true,
                                "hashed": false,
                                "enabled_patch_settings": {
                                    "allowed": false,
                                    "reason_code": "SYSTEM_COLUMN",
                                    "reason": "The column does not support exclusion as it is a Primary Key"
                                }
                            },
                            "column_2": {
                                "name_in_destination": "column_2",
                                "enabled": true,
                                "hashed": false,
                                "enabled_patch_settings": {
                                    "allowed": true
                                }
                            },
                            "column_3": {
                                "name_in_destination": "column_3",
                                "enabled": true,
                                "hashed": true,
                                "enabled_patch_settings": {
                                    "allowed": true
                                }
                            }
                        }
                    }
                }
            },
            "schema_2": {
                "name_in_destination": "schema_2",
                "enabled": false,
                "tables": {
                    "table_1": {
                        "name_in_destination": "table_1",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": true
                        }
                    },
                    "table_2": {
                        "name_in_destination": "table_2",
                        "enabled": false,
                        "enabled_patch_settings": {
                            "allowed": false,
                            "reason_code": "SYSTEM_TABLE"
                        }
                    }
                }
            }
        }
    }
}

The response contains only the difference between your selections and the schema config. The schema config includes schemas, tables, and columns. If you have not specified any schema configurations that differ from the default, the response will consist of only the top schema level. For more information, see also the Connector Schema config section.

Modify a Connector Table Configlink

Updates the table config within your database schema for an existing connector within your Fivetran account.

Requestlink

PATCH https://api.fivetran.com/v1/connectors/{connector_id}/schemas/{schema}/tables/{table}

{
    "enabled": false,
    "columns": {
        "column_2": {
            "enabled": true,
            "hashed": false
        },
        "column_3": {
            "hashed": true
        }
    }
}

Responselink

HTTP 200 OK

{
    "code": "Success",
    "data": {
        "enable_new_by_default": true,
        "schemas": {
            "schema_1": {
                "name_in_destination": "schema_1",
                "enabled": true,
                "tables": {
                    "table_1": {
                        "name_in_destination": "table_1",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": true
                        }
                    },
                    "table_2": {
                        "name_in_destination": "table_2",
                        "enabled": false,
                        "enabled_patch_settings": {
                            "allowed": true
                        },
                        "columns": {
                            "column_1": {
                                "name_in_destination": "column_1",
                                "enabled": true,
                                "hashed": false,
                                "enabled_patch_settings": {
                                    "allowed": false,
                                    "reason_code": "SYSTEM_COLUMN",
                                    "reason": "The column does not support exclusion as it is a Primary Key"
                                }
                            },
                            "column_2": {
                                "name_in_destination": "column_2",
                                "enabled": true,
                                "hashed": false,
                                "enabled_patch_settings": {
                                    "allowed": true
                                }
                            },
                            "column_3": {
                                "name_in_destination": "column_3",
                                "enabled": true,
                                "hashed": true,
                                "enabled_patch_settings": {
                                    "allowed": true
                                }
                            }
                        }
                    }
                }
            },
            "schema_2": {
                "name_in_destination": "schema_2",
                "enabled": false,
                "tables": {
                    "table_1": {
                        "name_in_destination": "table_1",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": true
                        }
                    },
                    "table_2": {
                        "name_in_destination": "table_2",
                        "enabled": false,
                        "enabled_patch_settings": {
                            "allowed": false,
                            "reason_code": "SYSTEM_TABLE"
                        }
                    }
                }
            }
        }
    }
}

The response contains only the difference between your selections and the schema config. The schema config includes schemas, tables, and columns. If you have not specified any schema configurations that differ from the default, the response will consist of only the top schema level. For more information, see also the Connector Schema config section.

Modify a Connector Column Configlink

Updates the column config within your table for an existing connector within your Fivetran account.

Requestlink

PATCH https://api.fivetran.com/v1/connectors/{connector_id}/schemas/{schema}/tables/{table}/columns/{column}

{
    "enabled": true,
    "hashed": false
}

Responselink

HTTP 200 OK

{
    "code": "Success",
    "data": {
        "enable_new_by_default": true,
        "schemas": {
            "schema_1": {
                "name_in_destination": "schema_1",
                "enabled": true,
                "tables": {
                    "table_1": {
                        "name_in_destination": "table_1",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": true
                        }
                    },
                    "table_2": {
                        "name_in_destination": "table_2",
                        "enabled": false,
                        "enabled_patch_settings": {
                            "allowed": true
                        },
                        "columns": {
                            "column_1": {
                                "name_in_destination": "column_1",
                                "enabled": true,
                                "hashed": false,
                                "enabled_patch_settings": {
                                    "allowed": false,
                                    "reason_code": "SYSTEM_COLUMN",
                                    "reason": "The column does not support exclusion as it is a Primary Key"
                                }
                            },
                            "column_2": {
                                "name_in_destination": "column_2",
                                "enabled": true,
                                "hashed": false,
                                "enabled_patch_settings": {
                                    "allowed": true
                                }
                            },
                            "column_3": {
                                "name_in_destination": "column_3",
                                "enabled": true,
                                "hashed": true,
                                "enabled_patch_settings": {
                                    "allowed": true
                                }
                            }
                        }
                    }
                }
            },
            "schema_2": {
                "name_in_destination": "schema_2",
                "enabled": false,
                "tables": {
                    "table_1": {
                        "name_in_destination": "table_1",
                        "enabled": true,
                        "enabled_patch_settings": {
                            "allowed": true
                        }
                    },
                    "table_2": {
                        "name_in_destination": "table_2",
                        "enabled": false,
                        "enabled_patch_settings": {
                            "allowed": false,
                            "reason_code": "SYSTEM_TABLE"
                        }
                    }
                }
            }
        }
    }
}

The response contains only the difference between your selections and the schema config. The schema config includes schemas, tables, and columns. If you have not specified any schema configurations that differ from the default, the response will consist of only the top schema level. For more information, see also the Connector Schema config section.

Use Case: How to select only the columns you want to sync and block new tables and columns for new connectorslink

Using our REST API, you can define a set of columns in newly created connectors that you want to sync and set up the Schema Change Handling in such a way that all new columns and tables that may appear in the source in the future are excluded from syncs.

1. Create a connector in the paused state, using the Create a Connector endpoint.

   Requestlink

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

   {
     "service": "postgres",
     "group_id": "your_group",
     "paused": "true",
     "config": {
       "schema_prefix": "your_prefix",
       "host": "localhost",
       "port": 7005,
       "user": "your_username",
       "password": "your_password",
       "database": "your_database",
       "update_method": "WAL",
       "replication_slot": "fivetran_replication_slot"
     }
   }
   
   

   NOTE: The actual values of the config section depend on the service you use.

   Responselink

   {
       "code": "Success",
       "message": "Connector has been created",
       "data": {
           "id": "culminating_dignitaries",
           "group_id": "your_group",
           "service": "postgres",
           "service_version": 3,
           "schema": "postgres_from_rest",
           "connected_by": "your_username",
           "created_at": "2021-07-29T09:51:03.338595Z",
           "succeeded_at": null,
           "failed_at": null,
           "paused": true,
           "pause_after_trial": false,
           "sync_frequency": 360,
           "schedule_type": "auto",
           "status": {
               "setup_state": "incomplete",
               "sync_state": "paused",
               "update_state": "on_schedule",
               "is_historical_sync": true,
               "tasks": [],
               "warnings": []
           },
           "Setup_tests": [
               {
                   "title": "Validating certificate",
                   "status": "FAILED",
                   "message": "No valid or non-expired trust anchors for this connection. Found 0 expired and 0 invalid certificates."
               },
               {
                   "title": "Connecting to SSH tunnel",
                   "status": "SKIPPED",
                   "message": ""
               },
               {
                   "title": "Connecting to host",
                   "status": "PASSED",
                   "message": ""
               }
           ],
           "config": {
               "database": "your_database",
               "password": "********",
               "latest_version": "3",
               "port": "7005",
               "host": "localhost",
               "service_version": "3",
               "incremental_update_method": "WAL",
               "replication_slot": "fivetran_replication_slot",
               "user": "your_username"
           }
       }
   }
   
   

2. Depending on the service type, you may need to do some additional configuration. For example, for PostgreSQL, you may need to approve certificates.

   Requestlink

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

   {
       "connector_id": "your_connector_id",
       "hash": "your_certificate_hash",
       "encoded_cert": "your_encoded_certificate"
   }
   
   

   Responselink

   {
       "code": "Success",
       "message": "The certificate has been approved"
   }
   

3. Reload the schema config using the Reload a Connector Schema Config endpoint and specify EXCLUDE as the payload. This excludes all schemas and tables from syncs.

   Requestlink

   POST https://api.fivetran.com/v1/connectors/{connector_id}/schemas/reload

   {
       "exclude_mode": "EXCLUDE"
   }
   

   Responselink

   {
       "code": "Success",
       "data": {
           "schemas": {
               "public": {
                   "name_in_destination": "public",
                   "enabled": false,
                   "tables": {
                       "test_rows": {
                           "name_in_destination": "test_rows",
                           "enabled": false,
                           "columns": {
                               "id": {
                                   "name_in_destination": "id",
                                   "enabled": false,
                                   "hashed": false,
                                   "enabled_patch_settings": {
                                       "allowed": true
                                   }
                               },
                               "name": {
                                   "name_in_destination": "name",
                                   "enabled": false,
                                   "hashed": false,
                                   "enabled_patch_settings": {
                                       "allowed": true
                                   }
                               }
                           },
                           "enabled_patch_settings": {
                               "allowed": true
                           }
                       }
                   }
               }
           },
           "schema_change_handling": "ALLOW_ALL"
       }
   }
   
   

4. Review the source schema that was returned in the response from the previous step. Identify the schemas, tables, and columns that you want to sync.

   NOTE: Some connectors do not provide the column information by default. If you don’t know which columns you are interested in, you can use the Retrieve Source Table Columns Config endpoint to get the column list.

5. Perform a request to Modify a Connector Schema Config endpoint. Don’t specify the value for enable_new_by_default in the payload because this parameter is not relevant to the Schema Change Handling feature. Use schema_change_handling instead.

   The possible values for the schema_change_handling parameter are as follows:

   • ALLOW_ALL - all new schemas, tables, and columns which appear in the source after the initial setup are included in syncs;
   • ALLOW_COLUMNS - all new schemas and tables which appear in the source after the initial setup are excluded from syncs, but new columns are included;
   • BLOCK_ALL - all new schemas, tables, and columns which appear in the source after the initial setup are excluded from syncs.

   In this use case, you need to use BLOCK_ALL. For all objects that you want to be synced, use "enabled": "true".

   IMPORTANT: If you enable some columns, you must enable both the table that contains these columns and the schema that contains this table.

   Requestlink

   PATCH https://api.fivetran.com/v1/connectors/{connector_id}/schemas

   {
       "schema_change_handling": "BLOCK_ALL",
       "schemas": {
           "public": {
               "enabled": "true",
               "tables": {
                   "test_rows": {
                   "enabled": "true",
                       "columns": {
                           "name": {
                               "enabled": "true"
                           }
                       }
                   }
               }
           }
       }
   }
   

   Responselink

   {
     "code": "Success",
     "data": {
       "schemas": {
         "public": {
           "name_in_destination": "public",
           "enabled": true,
           "tables": {
             "test_rows": {
               "name_in_destination": "test_rows",
               "enabled": true,
               "columns": {
                 "id": {
                   "name_in_destination": "id",
                   "enabled": false,
                   "hashed": false,
                   "enabled_patch_settings": {
                     "allowed": true
                   }
                 },
                 "name": {
                   "name_in_destination": "name",
                   "enabled": true,
                   "hashed": false,
                   "enabled_patch_settings": {
                     "allowed": true
                   }
                 }
               },
               "enabled_patch_settings": {
                 "allowed": true
               }
             }
           }
         }
       },
       "schema_change_handling": "BLOCK_ALL"
     }
   }
   
   

6. Unpause the connector, then set the sync frequency and other sync parameters.

   Requestlink

   PATCH https://api.fivetran.com/v1/connectors/{connector_id}

   {
       "paused": "false",
       "sync_frequency": "1440",
       "daily_sync_time": "10:00"
   }
   
   

   Responselink

   {
       "code": "Success",
       "message": "Connector has been updated",
       "data": {
           "id": "culminating_dignitaries",
           "group_id": "your_group",
           "service": "postgres",
           "service_version": 3,
           "schema": "postgres_from_rest",
           "connected_by": "your_username",
           "created_at": "2021-07-29T09:51:03.338595Z",
           "succeeded_at": null,
           "failed_at": null,
           "paused": false,
           "pause_after_trial": false,
           "sync_frequency": 1440,
           "daily_sync_time": "10:00",
           "schedule_type": "auto",
           "status": {
               "setup_state": "connected",
               "sync_state": "scheduled",
               "update_state": "on_schedule",
               "is_historical_sync": true,
               "tasks": [],
               "warnings": []
           }
       }
   }
   
   

The Schema Change Handling setup is complete.

Use Case: How to enable sync only for columns that you needlink

The connector standard config only stores the information on the difference between the schema config in the source and the changes made to it by the user. To clarify this, let’s have a look at this example:

Requestlink

GET https://api.fivetran.com/v1/connectors/connector_id_1/schemas/schema_1/tables/table_1/columns

Responselink

HTTP 200 OK

{
    "code": "Success",
    "columns": {
        "column_1": {
            "name_in_destination": "column_1",
            "enabled": false,
            "hashed": false,
            "enabled_patch_settings": {
                "allowed": true
            }
        }
    }
}

This example shows that we have only one disabled sync for column_1, but it does not necessarily mean we do not have other columns in the source. For example, the source might have column_2 and column_3 in this table. Syncing is enabled for all table columns by default, they will be synced in the next connector sync cycle.

Assume we want to enable sync only for column column_3. First of all, to exclude other unwanted columns you need to know all column names of your table:

1. We can use the Retrieve source table columns config endpoint for this purpose.

   Requestlink

   GET https://api.fivetran.com/v1/connectors/connector_id_1/schemas/schema_1/tables/table_1/columns

   Responselink

   HTTP 200 OK

   {
       "code": "Success",
       "columns": {
           "column_1": {
               "name_in_destination": "column_1",
               "enabled": false,
               "hashed": false,
               "enabled_patch_settings": {
                   "allowed": true
               }
           },
           "column_2": {
               "name_in_destination": "column_2",
               "enabled": true,
               "hashed": false,
               "enabled_patch_settings": {
                   "allowed": true
               }
           },
           "column_3": {
               "name_in_destination": "column_3",
               "enabled": true,
               "hashed": false,
               "enabled_patch_settings": {
                   "allowed": true
               }
           }
       }
   }
   

2. Once we know column names, we can explicitly exclude from the sync the columns we do not want to be synced and include the ones we need through the Modify a connector table config endpoint.

   Requestlink

   PATCH https://api.fivetran.com/v1/connectors/connector_id_1/schemas/schema_1/tables/table_1

   {
       "enabled": true,
       "columns": {
           "column_1": {
               "enabled": false,
               "hashed": false
           },
           "column_2": {
               "enabled": false,
               "hashed": false
           },
           "column_3": {
                "enabled": true,
                "hashed": false
           }
       }
   }
   

   Responselink

   HTTP 200 OK

   {
       "code": "Success",
       "data": {
           "enable_new_by_default": false,
           "schemas": {
               "schema_1": {
                   "name_in_destination": "schema_1",
                   "enabled": true,
                   "tables": {
                       "table_1": {
                           "name_in_destination": "table_1",
                           "enabled": true,
                           "enabled_patch_settings": {
                               "allowed": true
                           },
                           "columns": {
                               "column_1": {
                                   "name_in_destination": "column_1",
                                   "enabled": false,
                                   "hashed": false,
                                   "enabled_patch_settings": {
                                       "allowed": true
                                   }
                               },
                               "column_2": {
                                   "name_in_destination": "column_2",
                                   "enabled": false,
                                   "hashed": false,
                                   "enabled_patch_settings": {
                                       "allowed": true
                                   }
                               },
                               "column_3": {
                                   "name_in_destination": "column_3",
                                   "enabled": true,
                                   "hashed": false,
                                   "enabled_patch_settings": {
                                       "allowed": true
                                   }
                               }
                           }
                       }
                   }
               }
           }
       }
   }
   

Use case: How to set a custom sync frequencylink

You can override Fivetran’s default sync frequency for a connector. You set your custom sync frequency by specifying your chosen time interval between syncs in minutes as a value of the sync_frequency parameter in the Modify a Connector endpoint request.

Assume we want to set a custom frequency for connector connector_id_1 to be synced once every hour.

PATCH https://api.fivetran.com/v1/connectors/connector_id_1

{
    "sync_frequency": 60
}

To learn how the sync scheduling works in Fivetran, read our sync frequency and scheduling documentation.

Use case: How to set a time for your daily syncslink

You can choose what time your daily syncs start if you don’t want to use Fivetran’s default midnight starting time.

Assume we want to set a 03:00 starting time for connector connector_id_1.

There are two ways to do this:

1. Make sure that connector_id_1 already has its sync frequency set to 1440 (expressed in minutes, which makes 24 hours) and send the following request:

   PATCH https://api.fivetran.com/v1/connectors/connector_id_1

     {
         "daily_sync_time": "03:00"
     }
   

2. Set both the sync_frequency and daily_sync_time parameters in one request:

   PATCH https://api.fivetran.com/v1/connectors/connector_id_1

      {
          "sync_frequency": 1440,
          "daily_sync_time": "03:00"
      }
   

Use case: How to trigger syncs only when you need themlink

You can enable manual sync scheduling and trigger data syncs only when you want them. To enable manual sync scheduling for a connector, specify manual as a value of the schedule_type parameter using the Modify a Connector endpoint. The manual sync scheduling ignores the set sync_frequency.

IMPORTANT: You need to manually trigger your syncs after you make this change.

Assume we want to enable manual sync scheduling for connector connector_id_1 and trigger its sync.

1. Use the example request below to switch your connector’s schedule_type to manual:

   PATCH https://api.fivetran.com/v1/connectors/connector_id_1

   {
       "schedule_type": "manual",
       "run_setup_tests": false
   }
   

2. Next, use the Sync Connector Data endpoint to trigger a data sync according to your own schedule. Alternatively, you can trigger a sync in your Fivetran dashboard.

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

   NOTE: The payload for this request should be empty.