ClickHouse Data Modeling for Postgres Users

> if the situation is different with clickhouse

Somewhat. Unlike postgres, clickhouse lets you throw more CPU resources at the problem. And there shouldn't be a need to scan the whole table.

OP here. It depends on the workload/query - number of columns in the query, filters, presence of aggregates, etc. Overall, I've seen ClickHouse perform well with window functions. This is a common strategy customers use to deduplicate data.

Adding to above, CH should take ownership of CH airbyte connector. There are so so many users waiting for it. Have a look at that issue/discussion in airbyte github repo.

ur absolutely right that a simple FINAL clause won't work as expected with a materialized view like this, since the FINAL clause relies on the underlying table's ordering to eliminate duplicates. With a materialized view, the ordering is based on the enabled, ts, and id columns, which might not be the same as the underlying table's ordering.

As for the other two deduplication methods, you're also correct that they might cause performance problems on large-ish tables. The DISTINCT ON method can be slow due to the need to sort the entire table, while the ROW_NUMBER() method can be resource-intensive due to the need to assign a unique row number to each row.

one possible solution to this problem is to use a combination of a materialized view and a secondary deduplication method. For example, you could create a materialized view that includes a ROW_NUMBER() or RANK() function to assign a unique identifier to each row, and then use a secondary query to eliminate duplicates based on this identifier.

you could consider using a different data modeling approach, such as using a separate table to store the deduplicated data, or using a data warehousing tool that supports more advanced deduplication techniques.