feat(clickhouse): MergeTree model configuration — engine, order_by, primary_key, ttl, settings
- Dominant language
- Rust
- Stars
- 13.8k
- Forks
- 2.6k
- Avg merge
- 21h 31m
- Merged PRs (30d)
- 56
Description
## Summary
ClickHouse tables require a table engine declaration at `CREATE TABLE` time — most commonly `MergeTree()` or one of its variants. Without this, ClickHouse rejects the DDL. The dbt-clickhouse Python adapter exposes this via model config; dbt-fusion's ClickHouse adapter needs the same.
## User-facing config (target behavior)
```yaml
models:
my_project:
+engine: "MergeTree()" # default
+order_by: ["event_date", "id"]
+primary_key: ["id"]
+ttl: "event_date + INTERVAL 90 DAY"
+settings:
index_granularity: 8192
```
Or inline in a model:
```sql
{{ config(
engine="ReplacingMergeTree(updated_at)",
order_by=["id"],
primary_key=["id"]
) }}
select ...
```
## Work items
### dbt-schemas / model config
- [ ] Add optional fields to `ProjectModelConfig` (or a ClickHouse-specific sub-config):
- `engine: Option` — default `"MergeTree()"`
- `order_by: Option>`
- `primary_key: Option>`
- `ttl: Option`
- `settings: Option>`
- `sharding_key: Option` (for Distributed engine)
### DDL generation
- [ ] Pass `engine`, `order_by`, `primary_key`, `ttl`, `settings` through to ClickHouse `CREATE TABLE` DDL in the `table.sql` Jinja macro
- [ ] Ensure `ORDER BY` is required when `engine` is a `*MergeTree` variant (ClickHouse enforces this)
- [ ] `ReplacingMergeTree(ver_col)` — pass optional version column
- [ ] `AggregatingMergeTree()` — no extra args, but validate `ORDER BY` is set
### Incremental models
- [ ] `ReplacingMergeTree` deduplication key / version column wiring for incremental materializations
- [ ] `AggregatingMergeTree` pre-aggregated incremental support (if in scope)
## References
- Python adapter: `dbt-clickhouse` — `ClickhouseConfig` model config
- Implementation tracked in `dbt-labs/fs` issue #9517
- Depends on ClickHouse adapter Parts 0–8 merging first (tracked in `dbt-labs/fs` #9962)
Contributor guide
Assessment
This issue has not been assessed yet.