Connection Schema Config

The connection 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 connection doesn't have a schema config. Fivetran generates the schema config after the initial connection sync or after triggering the Reload a connection schema config endpoint. The config generated by default contains only schemas and tables.

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

• schema_change_handling = ALLOW_ALL - new schemas and tables that appear in the connection source are added to the config in the enabled state.
• schema_change_handling = ALLOW_COLUMNS - new schemas and tables that appear in the connection source are added to the config in the disabled state; new columns are enabled.
• schema_change_handling = BLOCK_ALL - new schemas, tables, and columns that appear in the connection 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 excluded from sync only in BLOCK_ALL mode. In ALLOW_ALL and ALLOW_COLUMNS mode 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 syncing, you will have to PATCH the connection config. The patched column is guaranteed to be returned to the API response no matter if the column is enabled or disabled.

Use cases

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

Event 1

You created a new connection and triggered the Reload a connection schema config endpoint. Suppose a connection source contains schema_1 and table_1 with column_1 and column_2.

The connection generates the following default config:

{
  "schema_change_handling": "ALLOW_ALL",
  "schemas": {
    "schema_1": {
      "enabled": true,
      "tables": {
        "table_1": {
          "enabled": true
        }
      }
    }
  }
}

Event 2

A new table, table_2 with column_1, column_2 appears in the source.

What happens after the next connection sync:

The table_2 is added to the config in the enabled state. column_1, column_2 of table_2 are not added to the config. Data for column_1, column_2 of table_1 is synced to the destination. Data for column_1, column_2 of table_2 is synced to the destination.

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

Event 3

PATCH the connection config to set the schema_change_handling = ALLOW_COLUMNS property

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

Event 4

A new table, table_3, with column_1, column_2 appears in the source.

What happens after the next connection sync:

table_3 is added to the config in the disabled state. column_1, column_2 of table_3 are not added to the config. Data for column_1, column_2 of table_1 is synced to the destination. Data for column_1, column_2 of table_2 is synced to the destination. Data for table_3 with all its columns is blocked and excluded from sync.

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

Event 5

PATCH the connection config to disable column_1 of table_1

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

Event 6

New column_3 of table_1 appears in the source.

What happens after the next connection sync:

column_3 of table_1 is not added to the config. Data for column_2, column_3 of table_1 is synced to the destination. Data for column_1 of table_1 is blocked and excluded from sync. Data for column_1, column_2 of table_2 is synced to the destination. Data for table_3 with all its columns is blocked and excluded from sync.

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