Well, that’s not really correct is it. ClickHouse for one definitely has them as Snowflake the last time I used it.
This is a lot of work to go through to avoid using the right tool for the job. Just use something like ClickHouse or even DuckDB and reap the benefits of better performance with less caveats.
But, as things grow, you tend to run into problems with analytical loads adversely impacting, and even knocking over, the production system by locking resources and causing timeouts. Especially if you're allowing analysts to run ad-hoc analytical queries.
Long story short, yes, resiliency is expensive, but it's not always more expensive than not having resiliency.
Use a single database for transational and analytical workloads.
Replicate your transational database as is for analytical workloads.
Remodel your data and replicate to the exact same technology stack.
Remodel your data and replicate into specialized tools for analytics.
I have never seen anybody that actually needs the last one. But the largest environments I've looked are government databases with a few thousands of people working on (there are bigger envs out there).
Synchronising the data can be done in stages as well. Daily loads are pretty straightforward and usually small/straightforward enough to get going quickly and maintain. As your needs increase you up the frequency or start employing more sophisticated methods.
Snowflake does not have indexes, and ClickHouse indexes are what they call "data skipping indexes". BigQuery, Redshift, Netezza, and Vertica also do not have support for indexes.
That’s still an index though isn’t it? Might work slightly differently, but the purpose is still the same.
What I think you meant to say was that Snowflake does not offer user-manageable indexes, not that they don't use indexes at all.
Primary key is range index - to quickly locate records. Secondary indexes are data skipping indexes - to quickly skip blocks of data.
On top of that BRIN (block range) indexes are usually used to capture the value of sorting by pruning I/O. I don't see these mentioned here -- they seem like a good open-source version of this idea.
To my mind the big differences for data warehouses are the following:
(a) Table scans are relatively cheap thanks to columnar structure and compression. It's much more important to tune compression than indexes. If you can reduce stored data size by 10^3, you don't need an index. That's exactly the opposite of row stores like MySQL.
(b) Data warehouses don't use indexes to maintain referential integrity, because it's not something they really care about in the first place.
My DW experience is with ClickHouse, but I think it illustrates a lot of the principles.