They use DeltaLake + Spark 3.0, and are mostly careful to partition well.
Their datasets are small. Most tables are ~50GB, the odd table up to ~2TB. The clusters typically are nothing shabby for this size, defaults to ~[4-12]x32GB.
The queries that I have seen are typically not written well. Think view-on-view-on-view (there's a BigCo policy against them materialising data..), and where the filter is applied in the last step. The stuff of horrors, but something I've seen in more-than-one-BigCo.
But we have compared some of those same queries on BigQuery vs. Databricks, and, I don't know if BigQuery's execution optimiser is better? Or if the BigQuery storage is better organising the data? Or if BigQuery is simply throwing more resource their way?
> Think view-on-view-on-view (there's a BigCo policy against them materialising data..), and where the filter is applied in the last step. The stuff of horrors, but something I've seen in more-than-one-BigCo.
That sounds wonderful (really). I was contracting for a BigCo where they materialised things all the time, and they would regularly end up running queries over multiple materialisations from different points in time, which invariably means that you always get wrong answers.
I very much wished to put a stop to use of any materilised views, but didn't have the buy-in to make the policy.
Was going to say. Most of the times all it takes is to have a proper data model.
For analytics I favour de-nomarlized schemas and, if necessary, nested fields. Queries are much easier to write (fewer joins), much faster, no need to incrementally materialize (sigh), fewer backfills and no messy field definitions.
What you often see instead is highly-normalized data models with an un-trackable amount of materialized views (usually on top each others) and some complicated tools/solutions to try to deal with all that mess. The cost of a bad design.
In my experience Athena on AWS beats Spark by an order of magnitude in terms of performance and price. Without the annoyance of having to start/stop/run a cluster. Snowflake is even faster than that since its storage is more optimized.
And Athena is an old fork of Presto, provided as a service; modern Presto e.g. Starburst is much faster than Athena, and cheaper if you use it much as Athena has usage fees not hosting fees.
