How to Set Sync Modes for Tables Using the REST API

Use this tutorial to configure the sync mode for individual tables in a database connection. Sync modes control how Fivetran handles row updates and deletions when replicating data to your destination.

Sync mode configuration applies primarily to database connectors. The sync_mode field is only present in the schema config for connections that support switching sync modes.

Prerequisites

You have a scoped API key and secret.
You have an existing connection ID for a database connection.
You have jq installed.

Sync mode values

Value	Description	When to use
`LIVE`	Only the current state of each row is stored in the destination. Rows are updated in place when the source changes.	Default for most tables. Use when you only need current data and don't require history.
`SOFT_DELETE`	Deleted rows are retained in the destination and marked with a `_fivetran_deleted` BOOLEAN column set to `true`.	Use when you need to detect and preserve deleted records without storing full row history.
`HISTORY`	All versions of each row are stored as separate rows in the destination, preserving a complete audit trail of every change.	Use for slowly changing dimensions, audit trails, or any table where you need to query past states.

Instructions

Run the following commands:

Set your environment variables.
Retrieve connection schema config.
Set sync mode for table.

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')

# Your existing connection ID.
export CONNECTION_ID="<your_connection_id>"

Retrieve connection schema config

Use the Retrieve a Connection Schema Config endpoint to see the current table configuration, including which sync modes are currently set.

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

The response includes a sync_mode field on each table that supports it. If sync_mode is not present on a table, that table does not support sync mode switching for the connection type.

Example response excerpt:

{
  "code": "Success",
  "data": {
    "schema_change_handling": "ALLOW_ALL",
    "schemas": {
      "public": {
        "name_in_destination": "public",
        "enabled": true,
        "tables": {
          "customers": {
            "name_in_destination": "customers",
            "enabled": true,
            "sync_mode": "LIVE"
          },
          "orders": {
            "name_in_destination": "orders",
            "enabled": true,
            "sync_mode": "LIVE"
          }
        }
      }
    }
  }
}

Set sync mode for table

Use the Update a Connection Schema Config endpoint to set the sync_mode for one or more tables in a single PATCH request.

Set table to History mode

Use HISTORY when you need a complete record of every state a row has been in. Each update to a source row creates a new row in the destination rather than overwriting the existing one.

curl --silent --show-error \
  --request PATCH \
  --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
{
  "schemas": {
    "public": {
      "tables": {
        "customers": {
          "sync_mode": "HISTORY"
        }
      }
    }
  }
}
JSON

Set table to Soft Delete mode

Use SOFT_DELETE when you want deleted rows to remain visible in the destination. We add a _fivetran_deleted BOOLEAN column to the destination table, and set it to true for rows deleted from the source.

curl --silent --show-error \
  --request PATCH \
  --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
{
  "schemas": {
    "public": {
      "tables": {
        "orders": {
          "sync_mode": "SOFT_DELETE"
        }
      }
    }
  }
}
JSON

Set table to Live mode

Use LIVE to return a table to the default behavior, where only the current state of each row is stored and rows are updated in place.

curl --silent --show-error \
  --request PATCH \
  --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
{
  "schemas": {
    "public": {
      "tables": {
        "customers": {
          "sync_mode": "LIVE"
        }
      }
    }
  }
}
JSON

Set sync modes for multiple tables at once

You can configure multiple tables in a single request by including all of them in the schemas object.

curl --silent --show-error \
  --request PATCH \
  --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
{
  "schemas": {
    "public": {
      "tables": {
        "customers": {
          "sync_mode": "HISTORY"
        },
        "orders": {
          "sync_mode": "SOFT_DELETE"
        },
        "products": {
          "sync_mode": "LIVE"
        }
      }
    }
  }
}
JSON

Endpoints used in this workflow

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

Notes

Sync mode availability depends on the connector type. The sync_mode field only appears on tables for connections that support sync mode switching, primarily database connectors.
Changing a table's sync mode from LIVE to HISTORY or SOFT_DELETE triggers a re-sync of that table to rebuild the destination table in the new format.
You can set sync modes for individual tables using the Update a Connection Table Config endpoint as an alternative to the full schema PATCH.
The _fivetran_deleted column added by SOFT_DELETE mode is a Fivetran system column and cannot be disabled.