Technical reference

Setup details

User and role management

Consider not using the default user; instead, create a dedicated one to use it with this Fivetran destination only. The following commands, executed with the default user, will create a new fivetran_user with the required privileges.

CREATE USER fivetran_user IDENTIFIED BY '<password>'; -- use a secure password generator

GRANT CURRENT GRANTS ON *.* TO fivetran_user;

Additionally, you can revoke access to certain databases from the fivetran_user. For example, by executing the following statement, we restrict access to the default database:

REVOKE ALL ON default.* FROM fivetran_user;

You can execute these statements in the ClickHouse SQL console.

Advanced configuration

The ClickHouse Cloud destination supports an optional JSON configuration file for advanced use cases. This file allows you to fine-tune destination behavior by overriding the default settings that control batch sizes, parallelism, connection pools, and request timeouts.

The file must be valid JSON and conform to the schema described below.

If you need to modify the configuration after the initial setup, you can edit the destination configurations in the Fivetran dashboard and upload an updated file.

The configuration file has a top-level section:

{
  "destination_configurations": { ... }
}

Inside of it you can specify the following configurations that control the internal behavior of the ClickHouse destination connector itself. These configurations affect how the connector processes data before sending it to ClickHouse.

All fields are optional. If a field is not specified, the default value is used. If a value is outside the allowed range, the destination will report an error during sync. Unknown fields are silently ignored (a warning is logged) and do not cause errors, which allows forward compatibility when new settings are added.

Example:

{
  "destination_configurations": {
    "write_batch_size": 50000,
    "select_batch_size": 200
  }
}

Type transformation mapping

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

Date and time value ranges

Fivetran sources can send date and time values in the range 0001-01-01, 9999-12-31. ClickHouse Cloud date types have narrower ranges, so values outside the supported range are silently clamped to the nearest boundary:

• The INSTANT upper bound is 2262-04-11 23:47:16 because DateTime64(9) stores nanoseconds since epoch as int64, and 2^63 - 1 nanoseconds corresponds to this date. ClickHouse itself supports DateTime64 with precision <= 9 up to 2299-12-31 23:59:59.
• The LOCALDATETIME upper bound is also limited to 2262-04-11 23:47:16 due to a known bug in the Go ClickHouse driver, where time.Time.UnixNano() is called for all DateTime64 precisions before scaling, causing int64 overflow for dates beyond 2262 even at precision 0.

Destination tables

The ClickHouse Cloud destination uses Replacing engine type of SharedMergeTree family (specifically, SharedReplacingMergeTree), versioned by the _fivetran_synced column.

Every column except primary (ordering) keys and Fivetran metadata columns is created as Nullable(T), where T is a ClickHouse Cloud type based on the data types mapping.

The table structure varies depending on the Fivetran sync mode configured for the connector: soft delete (default) or history mode (SCD Type 2).

Soft delete mode

In soft delete mode, every destination table includes the following metadata columns:

Single primary key in the source table

For example, source table users has a primary key column id (INT) and a regular column name (STRING). The destination table will be defined as follows:

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

In this case, the id column is chosen as a table sorting key.

Multiple primary keys in the source table

If the source table has multiple primary keys, they are used in order of their appearance in the Fivetran source table definition.

For example, there is a source table items with primary key columns id (INT) and name (STRING), plus an additional regular column description (STRING). The destination table will be defined as follows:

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

In this case, id and name columns are chosen as table sorting keys.

No primary keys in the source table

If the source table has no primary keys, a unique identifier will be added by Fivetran as a _fivetran_id column. Consider an events table that only has the event (STRING) and timestamp (LOCALDATETIME) columns in the source. The destination table in that case is as follows:

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

Since _fivetran_id is unique and there are no other primary key options, it is used as a table sorting key.

History mode (SCD Type 2)

When history mode is enabled, the destination preserves every version of each record rather than overwriting previous values. This implements Slowly Changing Dimension Type 2 (SCD Type 2), maintaining a complete audit trail of all changes.

In history mode, every destination table includes the following metadata columns:

The _fivetran_start column is always included in the ORDER BY clause as the last element of the compound sorting key. This allows multiple versions of the same record (with different start times) to coexist in the table.

When a record is updated:

• The previous version’s _fivetran_end is set to the new version’s _fivetran_start minus one nanosecond, and _fivetran_active is set to false.
• The new version is inserted with _fivetran_active set to true and _fivetran_end set to 2262-04-11 23:47:16.000000000 (the maximum DateTime64(9) value).

Single primary key in the source table

For example, source table users has a primary key column id (INT) and regular columns name (STRING) and status (STRING). The destination table in history mode will be defined as follows:

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

In this case, id and _fivetran_start form the compound sorting key.

After a few syncs, the table might contain the following data:

Record id=1 has two versions: the original (name 1, inactive) and the updated one (name 11, active). Record id=2 has only one version, which is currently active.

Multiple primary keys in the source table

If the source table has multiple primary keys, they are all included in the ORDER BY together with _fivetran_start as the last element.

For example, there is a source table items with primary key columns id (INT) and name (STRING), plus an additional regular column description (STRING). The destination table in history mode will be defined as follows:

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

In this case, id, name, and _fivetran_start form the compound sorting key.

No primary keys in the source table

If the source table has no primary keys, a unique identifier will be added by Fivetran as a _fivetran_id column, and _fivetran_start is appended to the sorting key. Consider an events table that only has the event (STRING) and timestamp (LOCALDATETIME) columns in the source. The destination table in history mode is as follows:

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

Since _fivetran_id and _fivetran_start form the compound sorting key.

Selecting the latest version of the data without duplicates

SharedReplacingMergeTree performs background data deduplication only during merges at an unknown time. However, selecting the latest version of the data without duplicates ad-hoc is possible with the FINAL keyword:

SELECT *
FROM example FINAL
LIMIT 1000 

Check out the optimizing reading queries” section in the troubleshooting guide for query optimization tips.

Retries on network failures

The ClickHouse Cloud destination retries transient network errors using the exponential backoff algorithm. This is safe even when the destination inserts the data, as any potential duplicates are handled by the SharedReplacingMergeTree table engine.