Technical reference

```
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:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| id name status \_fivetran\_start \_fivetran\_end \_fivetran\_active|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1 name 1 TODO 2025-11-10 20:57:00.000000000 2025-11-11 20:56:59.999000000 false|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1 name 11 TODO 2025-11-11 20:57:00.000000000 2262-04-11 23:47:16.000000000 true|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | 2 name 2 TODO 2025-11-10 20:57:00.000000000 2262-04-11 23:47:16.000000000 true | | | | | | | | | | | | | | | | | | | | | | | |

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[​](#history-multiple-pks "Direct link to 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[​](#history-no-pks "Direct link to 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[​](#selecting-latest-version "Direct link to Selecting the latest version of the data without duplicates")

`SharedReplacingMergeTree` performs background data deduplication
[only during merges at an unknown time](/docs/engines/table-engines/mergetree-family/replacingmergetree).
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](/docs/integrations/fivetran/troubleshooting#optimizing-reading-queries)" section in the troubleshooting guide for query optimization tips.

## Retries on network failures[​](#retries-on-network-failures "Direct link to 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.

- [Setup details](#setup-details)
  - [User and role management](#user-and-role-management)- [Advanced configuration](#advanced-configuration)- [Type transformation mapping](#type-mapping)
    - [Date and time value ranges](#date-and-time-value-ranges)- [Destination tables](#table-structure)
      - [Soft delete mode](#soft-delete-mode)- [History mode (SCD Type 2)](#history-mode)- [Selecting the latest version of the data without duplicates](#selecting-latest-version)- [Retries on network failures](#retries-on-network-failures)

Was this page helpful?