Update a Connection Schema Config

Updates the schema config for an existing connection within your Fivetran account.

NOTE: For backward compatibility, the response may contain the 'enable_new_by_default' boolean field. It defines whether new schemas and tables discovered in the source are synced. The value is 'true' if you specify 'ALLOW_ALL' as a value of 'schema_change_handling'. In the future API versions, we may remove this field.

The response contains all known schemas and tables. Also, it contains columns whose state has ever been set by the user. For more information, see also the Connection Schema config tutorial.

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

Request

schemas

object

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.

property name *

object

additional property

enabled

booleanrequired

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

example:

true

tables

object

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.

property name *

object

additional property

enabled

booleanrequired

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

example:

true

columns

object

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.

property name *

object

additional property

enabled

booleanrequired

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

example:

true

hashed

boolean

The boolean value specifying whether a column should be hashed

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.

enum:

"Unknown""Boolean""String""Short""Int""Long""Float""Double""BigDecimal""Instant""LocalDateTime""LocalDate""LocalTime""Json""Binary""Xml"

sync_mode

string

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

enum:

"SOFT_DELETE""HISTORY""LIVE"

row_filter

object

The row filter for the table. A row filter restricts the rows synced to your destination to those that satisfy all of its clauses. This field uses three-state semantics:
- Absent (the row_filter key is omitted from the request): The table's existing row filter is left unchanged.
- null (the row_filter key is present with a JSON null value): The table's existing row filter, if any, is deleted.
- Object: The provided object creates or replaces (upserts) the table's existing row filter.
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_us_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 created on or after 2024 for US and Canadian customers

column_clauses

array

The list of column clauses that make up the row filter. A row is synced only if it satisfies every clause (the clauses are combined with a logical AND). The list must contain 1 to 10 clauses.

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"

schema_change_handling

string

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"

is_type_locked

boolean

The boolean value specifying whether the connection's destination data types are locked to the types determined from the source. If enabled before or immediately after the initial sync, the type lock captures the types determined at the initial sync; if enabled later, it captures the types determined on the first sync after enabling. Re-enabling an already-enabled lock does not change previously locked types.

Http + 1.1

PATCH

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

PATCH /v1/connections/4107c213907114059a5544ad8fa66c52/schemas HTTP/1.1
Accept: application/json
Authorization: Basic 
Content-Type: application/json
Host: api.fivetran.com
Content-Length: 335

{
  "schemas": {
    "schema_name": {
      "enabled": true,
      "tables": {
        "table_name": {
          "enabled": true,
          "columns": {
            "column_name": {
              "enabled": true
            }
          }
        }
      }
    }
  },
  "schema_change_handling": "BLOCK_ALL",
  "is_type_locked": false
}

Responses

200

Successful response

`200`

code

stringrequired

Response status code

example:

Success

message

string

Response status text

example:

Schema configuration has been modified

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

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

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

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

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_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

enum:

"ALLOW_ALL""ALLOW_COLUMNS""BLOCK_ALL"

{
  "code": "Success",
  "message": "Schema configuration has been modified",
  "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_filtering_supported": true,
    "schema_change_handling": "ALLOW_ALL"
  }
}