Retrieve a Connection Schema Config

Returns the top-level schema configuration for an existing connection within your Fivetran account. The response includes global flags, every schema, each table, and only the columns that were explicitly overridden.

Use this endpoint to read the current data-selection tree for a connection, to back up the schema before making edits, or to copy the configuration to another connection.

NOTE: To restore a backed-up schema or copy the configuration to another connection, use the Update a Connection Schema Config endpoint.

For more information, see the Connection Schema config tutorial.

NOTE: Unedited columns (those following table defaults) are omitted from the response. For a read-only cataloging workflow, walk this response from schemas to tables, inspect each table's supports_columns_config field, and call the Retrieve Source Table Columns Config endpoint only for tables where that field is true.

For the NetSuite SuiteAnalytics, and Salesforce and Salesforce Sandbox connectors, the 'schemas' map field contains a single entry with the 'netsuite' or 'salesforce' key, respectively. For the 'schema.name_in_destination` name field, these connectors always return the destination schema name you set in the connection setup form.

For more information on using this API endpoint with the the Oracle Fusion Cloud Applications connectors, see the Schema information documentation.

IMPORTANT: This endpoint does not apply to Magic Folder connectors.

Request schema

Path parameters

connectionId

stringrequired

The unique identifier of the connection. Retrieve it from the id field in the List All Connections response, or from the id field returned when you Create a Connection.

example:

4107c213907114059a5544ad8fa66c52

Header parameters

Authorization

stringrequired

HTTP: basicAuth

HTTP AuthorizationScheme: basic

string

default:

application/json

example:

application/json

Http + 1.1

GET

https://api.fivetran.com/v1/connections/4107c213907114059a5544ad8fa66c52/schemas

GET /v1/connections/4107c213907114059a5544ad8fa66c52/schemas HTTP/1.1
Accept: application/json
Authorization: Basic 
Host: api.fivetran.com

Responses

200

Successful response

`200`

code

stringrequired

Response status code

example:

Success

message

string

Response status text

example:

Schema configuration retrieved successfully

data

objectrequired

enable_new_by_default

boolean

The boolean value specifying whether to enable new schemas, tables, and columns by default

example:

true

schemas

objectrequired

The set of schemas within your connection schema config. Each key is the schema name as stored in the connection schema config. Schema names are case-sensitive; an incorrect case results in an HTTP 404 error.

schema

object

name_in_destination

stringrequired

The schema name within your destination in accordance with Fivetran conventional rules

example:

schema_name_in_destination

enabled

booleanrequired

The boolean value specifying whether the sync for the table into the destination is enabled.

example:

true

tables

objectrequired

The set of tables within your database schema config. Each key is the table name as stored in the connection schema config. Table names are case-sensitive; an incorrect case results in an HTTP 404 error.

table

object

sync_mode

string

This field appears in the response if the connection supports switching sync modes for tables

enum:

"SOFT_DELETE""HISTORY""LIVE"

name_in_destination

stringrequired

The schema name within your destination in accordance with Fivetran conventional rules

example:

table_name_in_destination

enabled

booleanrequired

The boolean value specifying whether the sync for the table into the destination is enabled.

example:

true

columns

objectrequired

The set of columns within your table schema config. Each key is the column name as stored in the connection schema config. Column names are case-sensitive; an incorrect case results in an HTTP 404 error. The columns object in the response contains the columns whose enabled value has been set by the user after the initial sync.

column

object

name_in_destination

stringrequired

The schema name within your destination in accordance with Fivetran conventional rules

example:

column_name_in_destination

enabled

booleanrequired

The boolean value specifying whether the sync for the table into the destination is enabled.

example:

true

hashed

booleanrequired

The boolean value specifying whether a column should be hashed

enabled_patch_settings

objectrequired

allowed

booleanrequired

The boolean value specifying whether the enabled property can be updated

reason

string

The additional message indicating the reason why the enabled property cannot be updated. This field appears in the response only when the reason_code value is OTHER

example:

...

reason_code

string

The reason code indicating the reason why the enabled property cannot be updated:
SYSTEM_TABLE - the table is a system table
DELETED - the table was deleted in the source
OTHER - the table was excluded by the system for some reason
This field appears in the response when the allowed property value is FALSE

enum:

"SYSTEM_COLUMN""DELETED""OTHER"

is_primary_key

boolean

target_data_type

string

The target data type for this column in the destination. Overrides Fivetran's default type mapping. Available target types depend on the column's source data type and destination. Set to null to remove an existing mapping and revert to the default.

example:

String

enabled_patch_settings

objectrequired

allowed

booleanrequired

The boolean value specifying whether the enabled property can be updated

reason

string

The additional message indicating the reason why the enabled property cannot be updated. This field appears in the response only when the reason_code value is OTHER

example:

...

reason_code

string

enum:

"SYSTEM_TABLE""DELETED""OTHER"

supports_columns_config

booleanrequired

A boolean value that specifies if the table supports a column-level configuration.

example:

true

supports_history_mode

booleanrequired

The boolean value that specifies if the table supports history mode.

example:

true

row_filter

object

The row filter configured for the table. Omitted when no row filter is configured for the table. The clauses are combined according to the operator field; OR is returned explicitly, while the default AND is represented by an omitted operator field. Row filtering must be available for the connection; see the row_filtering_supported response field.

name

string

A human-readable name for the row filter. Required when a row filter object is provided (upsert).

example:

active_orders

description

string

A human-readable description for the row filter. Required when a row filter object is provided (upsert).

example:

Sync only active orders

operator

string

The logical operator applied to the column clauses. AND means a row is synced only when it satisfies every clause; OR means a row is synced when it satisfies at least one clause. If omitted, defaults to AND. Only valid when column_clauses contains 2 or more clauses; setting it explicitly on a single-clause filter returns an error. For a single-clause filter, omit this field.

enum:

"AND""OR"

column_clauses

array

The list of column clauses that make up the row filter. Clauses are combined according to the operator field (defaults to AND when omitted). The list must contain 1 to 10 clauses. If operator is explicitly set to AND or OR, the list must contain at least 2 clauses; for a single-clause filter, omit operator rather than setting it explicitly.

column

string

The name of the source column the clause applies to. Maximum 500 characters.

maxLength:

500

example:

status

operator

stringrequired

The operator applied to the column. The operator must be compatible with column_type, and the number of values required depends on the operator:
- EQUALS, NOT_EQUALS - valid for LONG, FLOAT, DOUBLE, BIGDECIMAL, DATE, DATETIME, and STRING; require exactly 1 value.
- LESS, LESS_OR_EQUALS, GREATER, GREATER_OR_EQUALS - valid for LONG, FLOAT, DOUBLE, BIGDECIMAL, DATE, DATETIME, and STRING; require exactly 1 value.
- STARTS_WITH, CONTAINS - valid for STRING only; require exactly 1 value.
- IN - valid for LONG, STRING, FLOAT, and DOUBLE only; require at least 1 value.
- IS_NULL, IS_NOT_NULL - valid for all types; require no values.

enum:

"EQUALS""NOT_EQUALS""LESS""LESS_OR_EQUALS""GREATER""GREATER_OR_EQUALS""STARTS_WITH""CONTAINS""IN""IS_NULL""IS_NOT_NULL"

values

arrayrequired

The list of values the operator compares against. Each value is a string of at most 1,000 characters; the API parses it according to column_type. The number of values must match the operator: exactly 1 for the comparison and string operators, at least 1 for IN, and 0 for IS_NULL and IS_NOT_NULL. For the IN operator on a STRING column, the combined length of all values must not exceed 10,000 characters.

example:

[ "ACTIVE" ]

type:

string

column_type

string

The data type of the column. It determines which operators are valid and the required format of each value:
- LONG - integer types (short, int, long).
- FLOAT - single-precision floating-point number.
- DOUBLE - double-precision floating-point number.
- BIGDECIMAL - arbitrary-precision decimal number; each value is capped at 50 characters.
- STRING - text.
- BOOLEAN - accepts only the string values true or false (lowercase).
- DATE - a date with no time component, in yyyy-MM-dd format (for example, 2024-01-01).
- DATETIME - a UTC timestamp ending in Z, in yyyy-MM-ddTHH:mm:ssZ format (for example, 2024-01-01T00:00:00Z). Offsets other than Z are not accepted.

enum:

"LONG""FLOAT""DOUBLE""BIGDECIMAL""STRING""BOOLEAN""DATE""DATETIME"

parent_table

string

The name of the table that this table is grouped under, if any. This field appears in the response only for tables that belong to a group (for example, a core table's child tables).

example:

core_table_name

row_filtering_supported

boolean

A boolean value that specifies whether row filtering is available for the tables in this connection. It is true only when the row filtering feature is enabled for the connection, the connector type supports row filtering. It is false when the connector type does not support row filtering. This field is omitted from the response when the row filtering feature is not enabled for the connection.

example:

true

schema_change_handling

stringrequired

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

enum:

"ALLOW_ALL""ALLOW_COLUMNS""BLOCK_ALL"

{
  "code": "Success",
  "message": "Schema configuration retrieved successfully",
  "data": {
    "enable_new_by_default": true,
    "schemas": {
      "schema": {
        "name_in_destination": "schema_name_in_destination",
        "enabled": true,
        "tables": {
          "table": {
            "sync_mode": "SOFT_DELETE",
            "name_in_destination": "table_name_in_destination",
            "enabled": true,
            "columns": {
              "column": {
                "name_in_destination": "column_name_in_destination",
                "enabled": true,
                "hashed": false,
                "enabled_patch_settings": {
                  "allowed": false,
                  "reason": "...",
                  "reason_code": "SYSTEM_COLUMN"
                },
                "is_primary_key": true,
                "target_data_type": "String"
              }
            },
            "enabled_patch_settings": {
              "allowed": false,
              "reason": "...",
              "reason_code": "SYSTEM_TABLE"
            },
            "supports_columns_config": true,
            "supports_history_mode": true,
            "row_filter": {
              "name": "active_orders",
              "description": "Sync only active orders",
              "operator": "AND",
              "column_clauses": [
                {
                  "column": "status",
                  "operator": "EQUALS",
                  "values": [
                    "ACTIVE"
                  ],
                  "column_type": "STRING"
                }
              ]
            },
            "parent_table": "core_table_name"
          }
        }
      }
    },
    "row_filtering_supported": true,
    "schema_change_handling": "ALLOW_ALL"
  }
}