Troubleshooting & best practices

**Error message:**

```
user is missing the required grants on *.*: ALTER, CREATE DATABASE, CREATE TABLE, INSERT, SELECT
```

**Cause:** The connector checks grants with:

```
SELECT access_type, database, table, column FROM system.grants WHERE user_name = 'my_user'
```

This only returns direct grants. Privileges assigned via a ClickHouse role have `user_name = NULL` and `role_name = 'my_role'`, so they are invisible to this check.

**Solution:**

**Grant privileges directly** to the Fivetran user:

```
GRANT CURRENT GRANTS ON *.* TO fivetran_user;
```

---

## Best practices[​](#best-practices "Direct link to Best practices")

### Dedicated ClickHouse service for Fivetran[​](#dedicated-service "Direct link to Dedicated ClickHouse service for Fivetran")

In case of high ingestion load, consider using ClickHouse Cloud's [compute-compute separation](/docs/cloud/reference/warehouses) to create a dedicated service for Fivetran write workloads. This isolates ingestion from analytical queries and prevents resource contention.

For example, the following architecture can be used:

- **Service A (writer)**: Fivetran destination + other ingestion tools (ClickPipes, Kafka connectors)
- **Service B (reader)**: BI tools, dashboards, ad-hoc queries

### Optimizing reading queries[​](#optimizing-reading-queries "Direct link to Optimizing reading queries")

ClickHouse uses `SharedReplacingMergeTree` for Fivetran destination tables, which is the version of the [`ReplacingMergeTree` table engine](/docs/guides/replacing-merge-tree) in ClickHouse Cloud. Duplicate rows with the same primary key are normal — deduplication happens asynchronously during background merges. At read time, you need to be careful to avoid returning duplicate rows, as some rows may not have been deduplicated yet.

Using the `FINAL` keyword is the simplest way to avoid duplicate rows, as it forces a merge of any not-yet-deduplicated rows at read time:

```
SELECT * FROM schema.table FINAL WHERE ...
```

There are ways to optimize this `FINAL` operation — for example, by filtering on key columns using a `WHERE` condition. For more details, see the [FINAL performance](/docs/guides/replacing-merge-tree#final-performance) section of the ReplacingMergeTree guide.

If those optimizations are not sufficient, you have additional options that avoid using `FINAL` while still handling duplicates correctly:

- If you want to query a numeric column that is always incrementing, [you can use `max(the_column)`](/docs/guides/developer/deduplication#avoiding-final).
- If you need to retrieve the latest value for some columns for a particular key, you can use [`argMax(the_column, _fivetran_id)`](https://clickhouse.com/blog/10-best-practice-tips#perfecting_replacingmergetree).

### Primary key and ORDER BY optimization[​](#primary-key-optimization "Direct link to Primary key and ORDER BY optimization")

Fivetran replicates the source table's primary key as the ClickHouse `ORDER BY` clause. When the source has no PK, `_fivetran_id` (a UUID) becomes the sorting key, which can lead to poor query performance because ClickHouse builds its [sparse primary index](/docs/guides/best-practices/sparse-primary-indexes) from the `ORDER BY` columns.

**Recommendations in this case if any other optimization is not sufficient:**

1. **Treat Fivetran tables as raw staging tables.** Do not query them directly for analytics.
2. **If queries are still not performant enough**, use a [Refreshable Materialized View](/docs/materialized-view/refreshable-materialized-view) to create a copy of the table with an `ORDER BY` optimized for your query patterns. Unlike incremental materialized views, refreshable materialized views re-run the full query on a schedule, which correctly handles the `UPDATE` and `DELETE` operations that Fivetran issues during syncs:

   ```
   CREATE MATERIALIZED VIEW schema.table_optimized
   REFRESH EVERY 1 HOUR
   ENGINE = ReplacingMergeTree()
   ORDER BY (user_id, event_date)
   AS SELECT * FROM schema.table_raw FINAL;
   ```

   Note

   Avoid incremental (non-refreshable) materialized views for Fivetran-managed tables. Because Fivetran issues `UPDATE` and `DELETE` operations to keep data in sync, incremental materialized views will not reflect these changes and will contain stale or incorrect data.

### Don't manually modify Fivetran-managed tables[​](#dont-modify-tables "Direct link to Don't manually modify Fivetran-managed tables")

Avoid manual DDL changes (e.g., `ALTER TABLE ... MODIFY COLUMN`) to tables managed by Fivetran. The connector expects the schema it created. Manual changes can cause [type mapping errors](#uint64-type-error) and schema mismatch failures.

Use materialized views for custom transformations.

## Debugging operations[​](#debugging "Direct link to Debugging operations")

When diagnosing failures:

- Check the ClickHouse `system.query_log` for server-side issues.
- Request Fivetran for help with client-side issues.

For connector bugs, [create a GitHub issue](https://github.com/ClickHouse/clickhouse-fivetran-destination/issues) or contact [ClickHouse Support](/docs/about-us/support).

### Debugging Fivetran syncs[​](#debugging-fivetran-syncs "Direct link to Debugging Fivetran syncs")

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

#### Check recent ClickHouse errors related to Fivetran[​](#check-errors "Direct link to Check recent ClickHouse errors related to Fivetran")

```
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 recent Fivetran user activity[​](#check-activity "Direct link to 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;
```

- [Common errors](#common-errors)
  - [Grants test failed or operations are failing related to permissions](#grants-test-failed)- [Error while waiting for all mutations to be completed](#mutations-not-completed)- [Column mismatch error](#column-mismatch-error)- [AST is too big (code 168)](#ast-too-big)- [Memory limit exceeded / OOM (code 241)](#memory-limit-exceeded)- [Unexpected EOF / Connection error](#unexpected-eof)- [Can't map type UInt64](#uint64-type-error)- [No primary keys for table](#no-primary-keys)- [Role-based grants failing](#role-based-grants)- [Best practices](#best-practices)
    - [Dedicated ClickHouse service for Fivetran](#dedicated-service)- [Optimizing reading queries](#optimizing-reading-queries)- [Primary key and ORDER BY optimization](#primary-key-optimization)- [Don't manually modify Fivetran-managed tables](#dont-modify-tables)- [Debugging operations](#debugging)
      - [Debugging Fivetran syncs](#debugging-fivetran-syncs)

Was this page helpful?