> ## Documentation Index
> Fetch the complete documentation index at: https://filament.getgalaxy.io/llms.txt
> Use this file to discover all available pages before exploring further.

# ClickHouse

> Write typed analytical tables to ClickHouse

The ClickHouse sink (**alpha**) writes each resource to a typed table on
self-hosted ClickHouse or ClickHouse Cloud. Choose replace when the old table
must remain visible until the new snapshot is ready. Append and upsert write
directly to the live table and can be visible before the run finishes.

## Configuration

| Field               | Scope      | Default   | Description                                                                               |
| ------------------- | ---------- | --------- | ----------------------------------------------------------------------------------------- |
| `connection_method` | Connection | `fields`  | `fields` builds the connection from the fields below. `url` uses `dsn` directly.          |
| `dsn`               | Connection | —         | ClickHouse connection URL. Required when `connection_method` is `url`. Secret.            |
| `host`              | Connection | —         | Server hostname. A pasted `http(s)://` endpoint is normalized. Required in `fields` mode. |
| `port`              | Connection | `9440`    | Server port (9440 is the secure native port)                                              |
| `protocol`          | Connection | `native`  | Wire protocol: `native` or `http`. Native connections use LZ4 compression.                |
| `username`          | Connection | `default` | ClickHouse username                                                                       |
| `password`          | Connection | —         | Required. Secret.                                                                         |
| `database_name`     | Connection | `default` | Database encoded in the connection. The pipeline `database` selects the destination.      |
| `secure`            | Connection | `true`    | Connect with TLS 1.2+ (required by ClickHouse Cloud)                                      |
| `database`          | Pipeline   | `default` | Destination database                                                                      |

A `database` left empty is defaulted by the server to the normalized source
connection name. The sink always authenticates against the `default` database and runs
`CREATE DATABASE IF NOT EXISTS` for the destination from there, so the
configured user needs access to `default` even when writing elsewhere. The
sink prefers batches of 10,000 rows.

## Supported write modes

| Write mode           | Destination table               | When data becomes visible                 |
| -------------------- | ------------------------------- | ----------------------------------------- |
| `full_replace`       | Staging table, then destination | At commit, per resource                   |
| `full_append`        | Live `MergeTree`                | During the run                            |
| `full_upsert`        | Live `ReplacingMergeTree`       | During the run; deduplication is eventual |
| `incremental_upsert` | Live `ReplacingMergeTree`       | During the run; deduplication is eventual |

CDC and incremental delete are not supported because ClickHouse does not offer
efficient row-level deletes for this sink. See
[replication modes](/pages/guides/concepts/replication-modes).

## Table management

`EnsureSchema` behavior depends on the write mode.

For **replace**, the run creates a staging table named
`__filament_stage_<hex8>` (run-scoped) and leaves the destination untouched
until `Commit`.

For **append and upsert**, the live table is created if missing:

```sql theme={"theme":{"light":"github-light-default","dark":"github-dark-default"}}
CREATE TABLE db.tbl (...) ENGINE = MergeTree() ORDER BY (pk...)
```

with `tuple()` as the sorting key for keyless resources. Upsert modes use
`ReplacingMergeTree()` under the `insert_order` version strategy, or
`ReplacingMergeTree(<cursor field>)` under the `cursor` strategy. The version
field must exist in the schema, be `NOT NULL`, and be a `timestamp` or
`timestamptz`.

A pre-existing live table is validated, and mismatches are hard errors:

* The engine must be `MergeTree` for append or `ReplacingMergeTree` for upsert
  (`Shared*` cloud variants are accepted).
* A `ReplacingMergeTree` version argument must match the resolved version
  policy exactly.
* The table's sorting key must equal the sorted source primary key.

Missing columns are added to live tables with `ADD COLUMN IF NOT EXISTS`.
Evolution is add-only.

### Type mapping

| Logical type                                             | ClickHouse type                                                  |
| -------------------------------------------------------- | ---------------------------------------------------------------- |
| `bool`                                                   | `Bool`                                                           |
| `int16` / `int32` / `int64`                              | `Int16` / `Int32` / `Int64`                                      |
| `float32` / `float64`                                    | `Float32` / `Float64`                                            |
| `decimal`                                                | `Decimal(p,s)` from the native declaration, else `Decimal(38,9)` |
| `string` / `bytes` / `time` / `json` / `array` / unknown | `String`                                                         |
| `date`                                                   | `Date32`                                                         |
| `timestamp`                                              | `DateTime64(6)`                                                  |
| `timestamptz`                                            | `DateTime64(6, 'UTC')`                                           |
| `uuid`                                                   | `UUID`                                                           |

Nullable fields are wrapped in `Nullable()`.

## Write mechanics and atomicity

The sink converts Arrow values to the destination's typed columns. It accepts
common textual forms for numbers, timestamps, and encoded bytes, but rejects a
missing or null value for a non-nullable column.

Append and upsert modes write directly to the live table, so partial data is
**visible mid-run**. Replace mode is invisible until `Commit`, which per staged
resource runs `RENAME TABLE` when the destination does not exist yet, or
`EXCHANGE TABLES` followed by `DROP TABLE` of the stage. The exchange is
atomic and requires the destination database to use the `Atomic` engine (the
default).

<Note>
  `ReplacingMergeTree` deduplicates at background merge time, not on insert.
  Until parts merge, a key can have multiple rows — query with `SELECT … FROM
    tbl FINAL` to read the current state.
</Note>

## Failure behavior

`Abort` drops the run's staging tables and nothing else. Failed replace runs
leave the destination exactly as it was. Failed append or upsert runs can
leave partial rows in live tables, which upsert re-runs converge through the
primary key.