4 ms·

But CH is capable of the same “data warehousing” features that snowflake is. Which leaves snowflake as a slower, less capable, less open, and more expensive alt

But CH is capable of the same “data warehousing” features that snowflake is. Which leaves snowflake as a slower, less capable, less open, and more expensive alternative. Which brings me to the next point: I’m convinced the delineation between “data warehouse” and “olap” is largely a marketing move designed to segment the market along made up boundaries.

sv123 3y ago
We use both MS SQL and Snowflake heavily. There are clearly instances where having row based storage is appropriate, and also instances where columnar storage outperforms. All based on your workload and not just marketing.
- berkle4455 3y ago
  I don't think anyone disagrees with you. Clickhouse and Snowflake are both OLAP; neither are row-based (OLTP).
- FridgeSeal 3y ago
  MSSQL is an OLTP based db (going to preclude discussion of its fancy column index stuff it’s capable of). OLTP db’s definitely, definitely have a different role. I’m talking about the false difference between the likes of ClickHouse and Snowflake, where they’re both column oriented already. I’m asserting that the fundamental differences between “classic” column db’s and “data warehouses” is far less fundamental than the marketing would have us believe. Some of the db’s in this space have slightly different architectures and trade offs, and some deliberately operate at different scales, but they are built for, and operate in, basically the same purpose.
- fuy 3y ago
  There's columnar tables in SQL Server, though. Have you tried it? Would be interesting to compare to Snowflake.
  - FridgeSeal 3y ago
    As useful and powerful as the columnar tables in mssql are, they’re not on the same level as a full columnar db.
glogla 3y ago
Snowflake and ClickHouse are very different in their focus. Snowflake is focused on enterprise customers. It has a lot of features focused on that, like very granular security and governance and data marketplace. There's also some non-enterprise features that ClickHouse lacks, like the ability to execute Python in database (so you can bring ML in). But the biggest difference is that Snowflake is storage segregated architecture. Scaling Snowflake is done by running "alter warehouse resize" or something. You can also dedicate specific compute slices to specific users and scale them up and down as needed. And this is all managed for you. If you want to run ClickHouse at scale, you have to run your own k8s, figure out how to manage persistent storage, figure out how to replicate your data, manage cluster replicated tables, etc. Once you outgrow single instance, things get exponentially more difficult - both for the admins and for the users. Also, while ClickHouse can do joins and is getting better and better optimizer as we speak, and is probably faster than Snowflake for the same money on "single big table analytics" kind of workload, I would expect it to perform much worse in traditional analytics queries, like you would find in TPC-DS.
- riku_iki 3y ago
  > you have to run your own k8s, figure out how to manage persistent storage, figure out how to replicate your dat I think they have option of standalone cluster, where all of this kinda easy to configure.
  - 5Qn8mNbc2FNCiVV 3y ago
    Self managing storage is never easy to configure, especially not when it's storage that you want to access in a timely manner
    - glogla 3y ago
      Yeah, that would be my answer as well. I actually forgot to mention that - Snowflake and the like store data away from compute so no matter how you misconfigure clusters (though Snowflake isn't really that configurable) the data is safe. Messing up database that stores data locally means the data is gone - and that makes all operations like resizes and upgrades much more scary. But of course the local storage is much faster. Tradeoffs. I know ClickHouse Cloud uses S3 as well, but I don't know much about it, so I don't want to comment on it.
ownagefool 3y ago
I think it's more borne of the lack of scaling capabilities in the traditional sql databases, and I guess a lack of capability in summarising data. In reality, you can probably scale something like vitess pretty far, and then by adding your own summary tables on top, you're probably good for most usecases. I'm not an expert on this level of the stack though, so I'm probably missing a whole bunch of context.

sv123 3y ago

We use both MS SQL and Snowflake heavily. There are clearly instances where having row based storage is appropriate, and also instances where columnar storage outperforms. All based on your workload and not just marketing.

berkle4455 3y ago
I don't think anyone disagrees with you. Clickhouse and Snowflake are both OLAP; neither are row-based (OLTP).
FridgeSeal 3y ago
MSSQL is an OLTP based db (going to preclude discussion of its fancy column index stuff it’s capable of). OLTP db’s definitely, definitely have a different role. I’m talking about the false difference between the likes of ClickHouse and Snowflake, where they’re both column oriented already. I’m asserting that the fundamental differences between “classic” column db’s and “data warehouses” is far less fundamental than the marketing would have us believe. Some of the db’s in this space have slightly different architectures and trade offs, and some deliberately operate at different scales, but they are built for, and operate in, basically the same purpose.
fuy 3y ago
There's columnar tables in SQL Server, though. Have you tried it? Would be interesting to compare to Snowflake.
- FridgeSeal 3y ago
  As useful and powerful as the columnar tables in mssql are, they’re not on the same level as a full columnar db.

glogla 3y ago

Snowflake and ClickHouse are very different in their focus. Snowflake is focused on enterprise customers. It has a lot of features focused on that, like very granular security and governance and data marketplace. There's also some non-enterprise features that ClickHouse lacks, like the ability to execute Python in database (so you can bring ML in). But the biggest difference is that Snowflake is storage segregated architecture. Scaling Snowflake is done by running "alter warehouse resize" or something. You can also dedicate specific compute slices to specific users and scale them up and down as needed. And this is all managed for you. If you want to run ClickHouse at scale, you have to run your own k8s, figure out how to manage persistent storage, figure out how to replicate your data, manage cluster replicated tables, etc. Once you outgrow single instance, things get exponentially more difficult - both for the admins and for the users. Also, while ClickHouse can do joins and is getting better and better optimizer as we speak, and is probably faster than Snowflake for the same money on "single big table analytics" kind of workload, I would expect it to perform much worse in traditional analytics queries, like you would find in TPC-DS.

riku_iki 3y ago
> you have to run your own k8s, figure out how to manage persistent storage, figure out how to replicate your dat I think they have option of standalone cluster, where all of this kinda easy to configure.
- 5Qn8mNbc2FNCiVV 3y ago
  Self managing storage is never easy to configure, especially not when it's storage that you want to access in a timely manner
  - glogla 3y ago
    Yeah, that would be my answer as well. I actually forgot to mention that - Snowflake and the like store data away from compute so no matter how you misconfigure clusters (though Snowflake isn't really that configurable) the data is safe. Messing up database that stores data locally means the data is gone - and that makes all operations like resizes and upgrades much more scary. But of course the local storage is much faster. Tradeoffs. I know ClickHouse Cloud uses S3 as well, but I don't know much about it, so I don't want to comment on it.

ownagefool 3y ago

I think it's more borne of the lack of scaling capabilities in the traditional sql databases, and I guess a lack of capability in summarising data. In reality, you can probably scale something like vitess pretty far, and then by adding your own summary tables on top, you're probably good for most usecases. I'm not an expert on this level of the stack though, so I'm probably missing a whole bunch of context.