Oh wow, I missed that. I guess I heard about the CTE somewhere and understood dbt-clickhouse not having ephemeral materialization as CH still not supporting it.
I am still somewhat salty about not being able to do GROUP BY 1, 2, 3 :)
> You are right that ClickHouse requires attention and skills to get the best performance. However, that includes fixed, low-latency use cases like real-time marketing that Snowflake simply does not handle. ClickHouse and Snowflake aren't interchangeable for these use cases.
Yup, that was my point.
> GROUP BY 1, 2, 3
It is not ANSI SQL.
Nevertheless, it is already available in ClickHouse as well under `enable_positional_arguments` setting and we are considering making it enabled by default.
Ooooh.
Is there a way to turn unquoted column and table names case insensitive too? Thats like the second biggest annoyance.
Your examples are not entirely correct. ClickHouse introduced CTEs in early 2021. I use them constantly. ClickHouse SQL is turning into a superset of standard SQL at least for queries. Most SELECT syntax, including window functions, just works. ClickHouse does have updates; but they are asynchronous. This too is being fixed. Synchronous DELETEs will be available this year, UPDATE is next. One big issue for my money is distributed joins. They still require a lot of reasoning about data locality.
You are right that ClickHouse requires attention and skills to get the best performance. However, that includes fixed, low-latency use cases like real-time marketing that Snowflake simply does not handle. ClickHouse and Snowflake aren't interchangeable for these use cases.