How to Pre-Create Schema Configuration for Database Connections Before the Initial Sync

Use this tutorial to configure your connection's schema by selecting which tables and columns to include or exclude before the first sync runs. This approach uses the pre-create schema config endpoint, which is only available between connection creation and the initial sync.

This workflow applies to database connectors and the following connectors, which behave similarly to databases:

For other connectors, use the How to Set Up a Schema Before the Initial Sync tutorial instead.

Prerequisites

You have a scoped API key and secret.
You know the connector service for the connection you want to create.
You have jq installed.

Instructions

Complete the following steps:

Set your environment variables.
Create paused connection.
Retrieve pre-create schema configuration.
Modify schema configuration to select tables and columns.
Apply schema configuration.
Unpause connection.

Set your environment variables

export FIVETRAN_API_KEY="<your_scoped_api_key>"
export FIVETRAN_API_SECRET="<your_scoped_api_secret>"
export FIVETRAN_BASE_URL="https://api.fivetran.com/v1"
export FIVETRAN_AUTH_HEADER=$(printf "%s" "$FIVETRAN_API_KEY:$FIVETRAN_API_SECRET" | base64 | tr -d '\n')

# Required for connection creation.
export GROUP_ID="<your_group_id>"
export CONNECTION_SERVICE="<your_connection_service>"
# Example: postgres_rds

Create paused connection

Create a connection in the paused state. Creating the connection in the paused state prevents the initial sync from starting immediately, which gives you time to configure the schema before any data is replicated.

Use the Create a Connection endpoint.

CREATE_CONNECTION_RESPONSE=$(curl --silent --show-error \
  --request POST \
  --url "$FIVETRAN_BASE_URL/connections" \
  --header "Accept: application/json" \
  --header "Authorization: Basic $FIVETRAN_AUTH_HEADER" \
  --header "Content-Type: application/json" \
  --data @- <<JSON
{
  "service": "$CONNECTION_SERVICE",
  "group_id": "$GROUP_ID",
  "paused": true,
  "config": {
    "schema_prefix": "<your_schema_prefix>",
    "host": "<your_host>",
    "port": "<connector_port>",
    "user": "<your_user>",
    "password": "<your_password>",
    "database": "<your_database>"
  }
}
JSON
)

echo "$CREATE_CONNECTION_RESPONSE"
export CONNECTION_ID=$(echo "$CREATE_CONNECTION_RESPONSE" | jq -r '.data.id')
echo "CONNECTION_ID=$CONNECTION_ID"

The config object is connector-specific. Review the request schema for your connector in Create a Connection.

Retrieve pre-create schema configuration

Retrieve the list of available schemas, tables, and columns from the source before the first sync. This endpoint is only available while the connection has not yet completed its initial sync.

Use the Retrieve a Connection Schema Config endpoint.

GET_SCHEMA_RESPONSE=$(curl --silent --show-error \
  --request GET \
  --url "$FIVETRAN_BASE_URL/connections/$CONNECTION_ID/schemas" \
  --header "Accept: application/json" \
  --header "Authorization: Basic $FIVETRAN_AUTH_HEADER")

echo "$GET_SCHEMA_RESPONSE" | jq

The response contains the available schemas and tables. Review the output to identify which schemas, tables, and columns you want to include or exclude from the sync.

If the source does not return column information by default, use the Retrieve Source Table Columns Config endpoint to inspect individual tables:

curl --silent --show-error \
  --request GET \
  --url "$FIVETRAN_BASE_URL/connections/$CONNECTION_ID/schemas/<schemaName>/tables/<tableName>/columns" \
  --header "Accept: application/json" \
  --header "Authorization: Basic $FIVETRAN_AUTH_HEADER" | jq

Modify schema configuration

Review the schema returned in the previous step. Decide which schemas, tables, and columns to include in the sync. Use "enabled": true for objects you want to sync and "enabled": false for objects you want to exclude.

Set schema_change_handling to control how new schemas, tables, and columns that appear in the source after the initial sync are handled:

ALLOW_ALL - new schemas, tables, and columns are automatically included in syncs.
ALLOW_COLUMNS - new columns are automatically included, but new schemas and tables are excluded.
BLOCK_ALL - all new schemas, tables, and columns are excluded from syncs.

If you enable specific columns, you must also enable the table that contains those columns and the schema that contains that table.

Apply schema configuration

Use the Pre-Create a Connection Schema Config endpoint to apply your selection before the first sync.

curl --silent --show-error \
  --request POST \
  --url "$FIVETRAN_BASE_URL/connections/$CONNECTION_ID/schemas" \
  --header "Accept: application/json" \
  --header "Authorization: Basic $FIVETRAN_AUTH_HEADER" \
  --header "Content-Type: application/json" \
  --data @- <<JSON | jq
{
  "schema_change_handling": "BLOCK_ALL",
  "schemas": {
    "public": {
      "enabled": true,
      "tables": {
        "orders": {
          "enabled": true,
          "columns": {
            "id": { "enabled": true },
            "created_at": { "enabled": true },
            "status": { "enabled": true },
            "internal_notes": { "enabled": false }
          }
        },
        "audit_log": {
          "enabled": false
        }
      }
    }
  }
}
JSON

This example enables the ORDERS table but excludes the internal_notes column, and fully excludes the AUDIT_LOG table.

The pre-create schema config endpoint POST is distinct from the standard update endpoint PATCH. Use POST only before the initial sync. After the connection has completed its first sync, use PATCH /v1/connections/{connectionId}/schemas to update the schema config.

Unpause connection

After applying your schema configuration, unpause the connection to start the initial sync.

Use the Update a Connection endpoint.

curl --silent --show-error \
  --request PATCH \
  --url "$FIVETRAN_BASE_URL/connections/$CONNECTION_ID" \
  --header "Accept: application/json" \
  --header "Authorization: Basic $FIVETRAN_AUTH_HEADER" \
  --header "Content-Type: application/json" \
  --data '{"paused": false}' | jq

The connection will start its initial sync and replicate only the schemas, tables, and columns you configured.

Endpoints used in this workflow

POST /v1/connections
GET /v1/connections/{connectionId}/schemas
POST /v1/connections/{connectionId}/schemas
PATCH /v1/connections/{connectionId}

Notes

If you want to prevent any new tables or columns from being added automatically after the initial sync, set schema_change_handling to BLOCK_ALL.