Technical reference

- 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](https://github.com/ClickHouse/clickhouse-go/issues/1311) 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[​](#table-structure "Direct link to Destination tables")

The ClickHouse Cloud destination uses
[Replacing](/docs/engines/table-engines/mergetree-family/replacingmergetree) engine type of
[SharedMergeTree](/docs/cloud/reference/shared-merge-tree) family
(specifically, `SharedReplacingMergeTree`), versioned by the `_fivetran_synced` column.

Every column except primary (ordering) keys and Fivetran metadata columns is created
as [Nullable(T)](/docs/sql-reference/data-types/nullable), where `T` is a
ClickHouse Cloud type based on the [data types mapping](#type-mapping).

The table structure varies depending on the Fivetran
[sync mode](https://fivetran.com/docs/using-fivetran/features#deletedrowhandling)
configured for the connector: **soft delete** (default) or **history mode** (SCD Type 2).

### Soft delete mode[​](#soft-delete-mode "Direct link to Soft delete mode")

In soft delete mode, 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 `SharedReplacingMergeTree`.| `_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. | | | | | | | | | | | |

#### Single primary key in the source table[​](#single-pk "Direct link to 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[​](#multiple-pks "Direct link to 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[​](#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.
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)[​](#history-mode "Direct link to History mode (SCD Type 2)")

When [history mode](https://fivetran.com/docs/using-fivetran/features#historymode) is enabled,
the destination preserves every version of each record rather than overwriting previous values.
This implements [Slowly Changing Dimension Type 2](https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row) (SCD Type 2),
maintaining a complete audit trail of all changes.

In history mode, 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 `SharedReplacingMergeTree`.| `_fivetran_start` `DateTime64(9, 'UTC')` Timestamp when this version of the record became active. Part of the table's sorting key.|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `_fivetran_end` `Nullable(DateTime64(9, 'UTC'))` Timestamp when this version was superseded. Set to `2262-04-11 23:47:16` for currently active records.| `_fivetran_active` `Nullable(Bool)` Whether this is the currently active version of the record.|  |  |  | | --- | --- | --- | | `_fivetran_id` `String` Auto-generated unique identifier. Only present when the source table has no primary keys. | | | | | | | | | | | | | | | | | |

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[​](#history-single-pk "Direct link to 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: