I have used both across a few different use cases and conclude:

Advantages of Redshift Spectrum:

• Allows creation of Redshift tables
• Able to join Redshift tables with Redshift spectrum tables efficiently

If you do not need those things then you should consider Athena as well

Athena differences from Redshift spectrum:

• Billing. This is the major difference and depending on your use case you may find one much cheaper than the other
• Performance. I found Athena slightly faster.
• SQL syntax and features. Athena is derived from presto and is a bit different to Redshift which has its roots in postgres.
• Connectivity. Its easy enough to connect to Athena using API,JDBC or ODBC but many more products offer "standard out of the box" connection to Redshift

Also, for either solution, make sure you use the AWS Glue metadata, rather than Athena as there are fewer limitations.

Redshift Spectrum is dogshit. Literally. Kudos to AWS for making it work. I can’t imagine the smell in the data centers. More seriously this is a typical move of AWS to graft half-assed shit on existing technologies just to say it’s possible: yes you can query S3. You can, that’s it. It’s not optimised for it, it has trouble handling large rows, it has trouble handling different schemas, lots of trouble.

I would go through the Redshift Spectrum best practices blog [here][1] and plan to run some tests. It is hard to quantify such metrics as every customer workload is different. Regarding your questions: 1/ Depends on a variety of factors as noted in the best practices blog. Such as parquet file format, Snappy compression, proper partitioning on S3 to help with query access patterns/filters, type of queries such as ORDER BY, DISTINCT which cannot be pushed down to Spectrum compute layer etc. Amazon Redshift Spectrum owns managed compute layer independent of your Redshift cluster. The number of Redshift Spectrum compute nodes that a query uses depends on the Redshift node type and the overall workload. Based on the demands of your queries and Redshift cluster configuration, Redshift Spectrum scales automatically in an intelligent fashion. 2/ Same as #1 3/ Regarding query syntax difference between Athena and Redshift Spectrum, yes. Athena's query engine is Apache Presto and hence, it follows query syntax of Apache Presto. I would refer to Presto documentation [here][2] under "SQL Language" and "SQL Statement Syntax". As far as Spectrum goes, you will find that Spectrum follows pretty much the same syntax as Redshift except things like you cannot do DML operations on Spectrum tables due to the external table. For the second part of your question, I would make sure that customer is aware when to use Athena versus Spectrum. They are not meant to replace each other but rather meant for different workloads. Athena is more like rent-a-car for adhoc/on-demand data explorations as and when needed without needing to spin up a cluster etc. Whereas Redshift Spectrum is more like a secondary car and Redshift is your primary car. A common pattern for Redshift Spectrum is to run queries that span both the frequently accessed “hot” data stored locally in Amazon Redshift and the “warm/cold” data stored cost-effectively in Amazon S3. This pattern serves to separate compute and storage, enabling independent scaling of both to match the use case without having to pay disproportionately for value. Athena and Redshift Spectrum query optimizers are completely different. There are also differences such as you can get the same rich compliance standards of Amazon Redshift. [1]: https://aws.amazon.com/blogs/big-data/10-best-practices-for-amazon-redshift-spectrum/ [2]: http://prestodb.github.io/docs/current/