Fivetran ClickHouse Destination - Technical Reference

Type transformation mapping

The Fivetran ClickHouse destination maps Fivetran data types to ClickHouse types as follows:

Fivetran type	ClickHouse type
BOOLEAN	Bool
SHORT	Int16
INT	Int32
LONG	Int64
BIGDECIMAL	Decimal(P, S)
FLOAT	Float32
DOUBLE	Float64
LOCALDATE	Date
LOCALDATETIME	DateTime
INSTANT	DateTime64(9, 'UTC')
STRING	String
BINARY	String *
XML	String *
JSON	String *

Note

* BINARY, XML, and JSON are stored as String because ClickHouse's String type can represent an arbitrary set of bytes. The destination adds a column comment to indicate the original data type. The ClickHouse JSON data type is not used as it was marked as obsolete and never recommended for production usage.

Destination table structure

All destination tables use SharedReplacingMergeTree versioned by the _fivetran_synced column. Every column except primary (ordering) keys and Fivetran metadata columns is created as Nullable(T).

Single primary key

For a source table users with primary key id (INT) and column name (STRING):

CREATE TABLE `users`
(
    `id`                Int32,
    `name`              Nullable(String),
    `_fivetran_synced`  DateTime64(9, 'UTC'),
    `_fivetran_deleted` Bool
) ENGINE = SharedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}', _fivetran_synced)
ORDER BY id
SETTINGS index_granularity = 8192

Multiple primary keys

For a source table items with primary keys id (INT) and name (STRING), plus column description (STRING):

CREATE TABLE `items`
(
    `id`                Int32,
    `name`              String,
    `description`       Nullable(String),
    `_fivetran_synced`  DateTime64(9, 'UTC'),
    `_fivetran_deleted` Bool
) ENGINE = SharedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}', _fivetran_synced)
ORDER BY (id, name)
SETTINGS index_granularity = 8192

Primary keys are used in order of their appearance in the Fivetran source table definition.

No primary keys

When the source table has no primary keys, Fivetran adds a _fivetran_id column as the sorting key:

CREATE TABLE events
(
    `event`             Nullable(String),
    `timestamp`         Nullable(DateTime),
    `_fivetran_id`      String,
    `_fivetran_synced`  DateTime64(9, 'UTC'),
    `_fivetran_deleted` Bool
) ENGINE = SharedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}', _fivetran_synced)
ORDER BY _fivetran_id
SETTINGS index_granularity = 8192

Data deduplication

SharedReplacingMergeTree performs background data deduplication only during merges at an unknown time. To query the latest version of data without duplicates, use the FINAL keyword with select_sequential_consistency:

SELECT *
FROM example FINAL
LIMIT 1000
SETTINGS select_sequential_consistency = 1;

See also Duplicate records with ReplacingMergeTree in the troubleshooting guide.

Fivetran metadata columns

Every destination table includes the following metadata columns:

Column	Type	Description
_fivetran_synced	DateTime64(9, 'UTC')	Timestamp when the record was synced by Fivetran. Used as the version column for ReplacingMergeTree.
_fivetran_deleted	Bool	Soft delete marker. Set to true when the source record is deleted.
_fivetran_id	String	Auto-generated unique identifier. Only present when the source table has no primary keys.

Ownership and support model

The ClickHouse Fivetran destination has a split ownership model:

• ClickHouse develops and maintains the destination connector code (GitHub).
• Fivetran hosts the connector and is responsible for data movement, pipeline scheduling, and source connectors.

When diagnosing sync failures:

• Check the ClickHouse system.query_log for server-side issues.
• Request Fivetran connector process logs for client-side issues.

For connector bugs, create a GitHub issue or contact ClickHouse Support.

Debugging Fivetran syncs

Use the following queries to diagnose sync failures on the ClickHouse side.

Check recent Fivetran errors

SELECT event_time, query, exception_code, exception
FROM system.query_log
WHERE client_name LIKE 'fivetran-destination%'
  AND exception_code > 0
ORDER BY event_time DESC
LIMIT 50;

Check replica health

SELECT database, table, total_replicas, active_replicas, replica_is_active
FROM system.replicas
WHERE database LIKE 'ft_%'
ORDER BY active_replicas ASC;

Identify orphaned replicas

Orphaned replicas from migrated or scaled services can block DDL operations. Identify them with:

SELECT DISTINCT arrayJoin(mapKeys(replica_is_active)) AS replica_name
FROM system.replicas
WHERE arrayJoin(mapValues(replica_is_active)) = 0;

To remove an orphaned replica:

SYSTEM DROP REPLICA '<old-replica-name>' FROM TABLE <db>.<table>;

Check recent Fivetran user activity

SELECT event_time, query_kind, query, exception_code, exception
FROM system.query_log
WHERE user = 'fivetran_user'
ORDER BY event_time DESC
LIMIT 100;