I have used both. My biggest aha moment with BQ was realising it isn’t “sql to query a database”, but “map reduce as a sql”. Redshift really feels like a DB: I’m always worrying about the number of the runtime complexity of my sql, the number of nodes in my fleet, how my joins work, it just feels like a big big DB. BQ is less like that. I’m thinking in terms of memory complexity of my operation : N, log N, etc. Always having as many nodes available as it can be parallelised to. You’re writing a map reduce job on your data, and it’s... it feels like the more appropriate boundary for abstraction.
This is a bit hand wavy, but there is really no better way to get a feel for it than to try it. And think of it less as a db, and more as a map reduce cluster :) if that helps.
Your comment is a little bit perplexing to me because I don't really recognize much difference between composing relational operations and map / reduce operations. SQL `select` is map, `group by` is reduce, `where` is a flatMap to zero or one items, and `join` is a kind of specialized flatMap to zero or more items. You have to consider IO quantity and throughput (both disk and memory) to get decent performance either way.
For example, if you're writing straight-up SQL, the fewer columns you project out before you start sorting the better off you are, it's better to join stuff after you've done your sort than the reverse because it means physically less data shuffling.
In redshift, I control the size of nodes. That means I can brute force my way through an expensive operation by increasing the memory capacity of the nodes. In BigQuery, you play by the rules or you’re out. It forces you to think in map reduce, because there really is nothing else under the hood.
Also, you get immediate parallelisation across O(n) nodes. Again; not in redshift.
These are crucially different to regular DBs. They’re both semantically sql, nobody denies that, but they describe different underlying models.
E.g.: in BigQuery, you can’t sort your entire column, even if you do other stuff afterwards. That makes sense in a map reduce system, but not in a “normal” DB.
I'd argue that AWS's more directly comparable service to BQ is Athena.. Athena is AWS's managed Presto service, but it's positioned as 'serverless' as you don't need to plan capacity in advance, and very 'coincidentally' priced at the exact same $5/TB that BQ is priced at. Has anyone compared Athena vs BQ performance? I work at an AWS shop so we've been kicking the tires with Athena and thus far are pretty impressed, but I do wonder if the exact same workload would perform better on BQ, which has been around much longer and is truly built from scratch for running as a 'serverless' scalable query engine.
This is a bit hand wavy, but there is really no better way to get a feel for it than to try it. And think of it less as a db, and more as a map reduce cluster :) if that helps.