Custom Data Type Mapping Private Preview

Custom data type mapping lets you control which data type a column uses in the destination. By default, Fivetran automatically maps source data types to destination types. With custom data type mapping, you can override this default on a per-column basis and specify a target data type for individual columns.

For example, you can map an INTEGER source column to STRING to make it compatible with downstream tools that expect text values.

How to configure custom data type mapping

To set a target data type for a column:

1. In the Fivetran dashboard, navigate to the Connections page and click the desired connection.
2. On the connection details page, go to the Schema tab.
3. Expand a schema, then expand a table to view its columns.
4. In the Data type column, click the data type cell for the column you want to configure.
5. In the drop-down menu, select the target data type. The source type is labeled (Default).
6. Click Save changes.

Columns with a user-selected target type are shown with a blue background in the Data type column to distinguish them from columns using the default type.

To revert a column to its default type, click the data type cell, select the (Default) option in the drop-down menu, and click Save changes.

How custom data type mapping works

After you save a target data type for a column, Fivetran converts all values in that column to the specified type during each subsequent sync. Any type mapping change requires a re-sync to recreate the destination column with the new type. We warn you about this when you click Save changes. If the connector supports table-level re-syncs, only the affected tables are re-synced. Otherwise, the full connection is re-synced.

Null coercion

If a source value can't be converted to the specified target type, Fivetran writes null to the destination column instead. The sync doesn't fail.

Each time this occurs, we generate a Type coercion to null warning. You can view these warnings on the Alerts page in your Fivetran dashboard, under Warnings, or in the Events section on the affected connection's page.

We also emit a warning log event of type value_coerced_as_null for each affected column. You can query these events using the Fivetran Platform Connector or forward them to your logging service using External Logs. Each event includes the source data type, the target data type, and the schema, table, and column where the coercion occurred.

All mapped columns are nullable in the destination regardless of their nullable status in the source.

Unsupported type mappings

If Fivetran detects that the configured target type is not supported for a column's source type and destination combination, we emit a warning log event of type unsupported_type_mappings. Each event includes the schema name, table name, and a list of affected columns with their source and target data types. You can query these events using the Fivetran Platform Connector or External Logs.

Using custom data type mapping with type locking

Custom data type mapping and type locking can be used together on the same connection:

• Columns with a configured target type use that type, regardless of whether type locking is enabled.
• Columns without a configured target type follow type locking rules when type locking is enabled, or Fivetran's default type promotion logic when type locking is disabled.

Supported type mappings

The target types available for a column depend on the column's source data type and your destination. The following tables list all supported source-to-target type combinations by destination.

Redshift, PostgreSQL, Oracle, and Apache Kafka

BigQuery

Azure Synapse, SQL Server, and MySQL (destination)

Databricks and Managed Data Lake Service

Snowflake

Partner-Built destinations

ClickHouse Cloud, Convex, Materialize, Milvus, MotherDuck, SingleStore, SurrealDB, and Teradata Vantage use the Partner SDK and support the following mappings:

Limitations

Custom data type mapping is only available for database, file, and event connectors. Application connectors do not support this feature.

Custom data type mapping can't be configured for the following column types:

• Key columns: Primary key, delete key, and pricing key columns are not eligible for type mapping.
• Hashed or encrypted columns: Columns with column hashing or encryption enabled can't be remapped.
• Fivetran system columns: Columns generated by Fivetran (such as _fivetran_synced and _fivetran_deleted) can't be remapped.
• Columns with an unknown source type can't be remapped.

Columns that are not eligible for type mapping display their source type in gray and do not have an active drop-down menu.

The available target types depend on the source data type and the destination. Not all type combinations are supported for all destinations. When you click the data type cell for an eligible column, the dropdown shows only the types that are supported for that column and destination combination.