dbt-labs / dbt-labs/dbt

feat(clickhouse): MergeTree model configuration — engine, order_by, primary_key, ttl, settings

Open
#14,583 1 comment 0 reactions 0 assignees View on GitHub
adapter:clickhouse area:adapters engine:v2
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.