Reporting systems need a means of running SQL against data.

Traditionally, this meant that a Database was required, and all databases (at the time) consisted of both Storage and Compute. There was no capability to separate these two components because the database stored its data in a proprietary format and the Compute component was required access that data.

As data volumes increased, traditional databases struggled to provide fast performance. This led to a new class of Data Warehouse systems that specialise in querying tables with billions of rows and Terabytes of data. These systems typically use parallel infrastructure and columnar storage split across multiple storage nodes to provide fast performance. Examples are: Amazon Redshift, Snowflake.

The next evolution came from Presto (and can be traced back to Hadoop), which was the idea of completely separating the Compute and Storage components of databases. Optimized for querying, Presto could query data stored in cloud services (eg Amazon S3) without having to load the data into the database (known as a 'query engine'). This was not only a mind-blowing concept, but depending upon the data format (eg Snappy-compressed Parquet) could actually rival the speed of Data Warehouses. Plus, the fact that they are cloud-native, it was easy to scale Compute as needed for short periods of time.

The main thing to understand about Query Engines is that data is not 'loaded' into them. Rather, when a query runs, they go to the storage service, look at data stored in whatever format and then calculate the answer to the query. Data can be added by simply adding another file in the storage location.

Examples of Query Engines are: Presto, Amazon Athena, Amazon Redshift Spectrum

The downside of using Query Engines is that they are not good at inserting/updating data. This has been addressed by the Delta Lake file format that uses a combination of Parquet files and logs files to allow data to be inserted, updated and deleted. This is the main focus of Databricks.

Of course, if your data needs are small, it is quite acceptable to use a traditional database (eg PostgreSQL) as a Data Warehouse.

The best approach is to start with something small until it no longer meets your needs. Then, move to something more powerful. If Amazon Athena is meeting your needs, then there is no need to move to anything else.

(My apologies for not including Google and other services as examples. My knowledge is mostly limited to AWS services.)

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.