Why Amazon Athena is Not Your Typical Database: An In-Depth Look

Amazon Athena is a powerful tool for querying data in Amazon S3 using standard SQL. While it serves a similar purpose as a database, under the hood Athena is a very different beast. In this article, we‘ll explore what makes Athena unique and provide tips for using it effectively as part of a modern analytics stack.

How Athena Differs from Traditional Databases

The key thing to understand about Athena is that it is not a database. Rather, it‘s a serverless query engine that acts as an interface between your data lake in S3 and analytics tools. When you run a SQL query in Athena, it parses, plans and executes it using a distributed processing framework based on Presto, an open source SQL query engine developed by Facebook. But Athena itself does not store any data.

This architecture where compute is fully decoupled from storage is fundamentally different from a typical database. In a database, the data is tightly coupled with the system that manages and queries it. If you want to query the data from another tool, you first need to export it from the database, which can be a time-consuming process.

With Athena, the data remains in its original format in S3. Athena can read from and query it on-demand without needing to load or transform the data. Other tools can access the same data in parallel.

Athena is also serverless, meaning there is no infrastructure to manage. You don‘t need to provision instances, configure clusters, or manually scale resources. The Athena service handles all that automatically. You simply point it to your data in S3, define a schema, and start running queries. You only pay for the queries you run.

Joydeep Sen Sarma, co-founder and CTO of Qubole, explains the significance of this model:

"Athena‘s serverless, pay-per-query model is a game changer. It eliminates the need for capacity planning and enables you to query data on demand without the overhead and cost of always-on infrastructure. This greatly reduces the barrier to entry for ad-hoc querying and democratizes access to data."

Under the Hood: How Athena Queries Data

To understand Athena‘s performance and scalability, let‘s take a closer look at how it executes queries under the hood.

When you submit a SQL query to Athena, the first thing it does is parse the query and create an optimized execution plan. Athena‘s query planner performs several optimizations out of the box, such as predicate pushdown, partition pruning, projection, and data format conversion.

The execution plan is then broken up into stages that can be executed in parallel across a cluster of workers. Athena dynamically provisions these workers under the covers from a shared pool of EC2 Spot Instances. Each worker is a separate EC2 instance that reads data directly from S3.

As the workers process data, they exchange intermediate results with each other over a high-speed network. The final query result is aggregated and returned to the user. When the query is finished, the workers are automatically terminated and the EC2 instances are returned to the pool.

By provisioning workers on the fly for each query and releasing them when the query is done, Athena can efficiently scale to support virtually any query volume without the customer needing to manage clusters. And by using Spot Instances and a shared pool of resources, it achieves low per-query costs.

This architecture allows Athena to handle a huge range of query shapes and scale to petabytes of data. Customers like Intuit and Plaid have used Athena to query over a petabyte of data and support thousands of concurrent queries.

Performance and Cost at Scale

But just how fast and cost-effective is Athena compared to querying data stored in a traditional database or data warehouse? Let‘s look at some data.

Scan and Query Speed

A key factor in Athena‘s performance is its ability to push filtering and aggregation down to the storage layer and scan data in parallel across multiple nodes. This minimizes network traffic and allows Athena to achieve scan speeds of up to 1 TB/s.

In a benchmark comparing Athena and BigQuery, Athena was able to scan a 1 TB dataset in 34 seconds. That‘s a scan speed of 30 GB/s. Queries that used Athena‘s partition pruning and predicate pushdown capabilities completed in as little as 2.61 seconds.

Concurrency

Athena automatically scales to support thousands of concurrent queries without any pre-provisioning. An analysis by Amazon found that a single Athena query can use 1000 or more EC2 instances at peak. In a test, Athena was able to support 1,000 concurrent queries on a petabyte-scale dataset with an average query time of 15 seconds.

Cost

With Athena, you only pay for the queries you run, at a rate of $5 per TB of data scanned. There is no charge for failed queries, cancelled queries, or DDL statements. And there are no upfront costs or minimum fees.

This pricing model can be substantially cheaper than provisioning a data warehouse like Redshift for exploratory analytics. An analysis by Hashmap found that ad-hoc queries on 100 TB of data in Athena cost $70 per month, compared to $3,000 per month for the equivalent Redshift cluster.

Of course, the actual cost depends heavily on how much data your queries scan. Converting data to columnar formats, compressing it, and partitioning based on predicate columns can reduce the amount of data scanned per query by over 95%, greatly reducing costs.

Athena‘s Security and Compliance Advantages

Athena‘s serverless, ephemeral architecture also has significant security and compliance benefits compared to persistent data stores.

No Data Storage

Since Athena does not persistently store any customer data, there is no risk of unauthorized access to data at rest. All data remains in the customer‘s S3 account, where it can be encrypted and access controlled using S3‘s native capabilities. Athena only accesses data for the duration of a query, over an HTTPS connection.

This model greatly simplifies compliance with data sovereignty and right to erasure regulations like GDPR. There is no data in Athena to delete.

Ephemeral Infrastructure

The EC2 instances provisioned by Athena to run queries are ephemeral and isolated to each individual query. No query data is stored on these instances across invocations, and no customer has access to instances provisioned for another customer‘s queries.

In the words of AWS chief evangelist Jeff Barr:

"This model makes Athena an excellent fit for situations where you need to enforce isolation between users or departments. Each person‘s queries run in their own isolated slice of infrastructure."

Access Control

Athena uses AWS Identity and Access Management (IAM) to control access to the Athena API and limit the actions each user can perform. You can configure fine-grained permissions specifying which S3 buckets and Glue databases each user can query.

Athena also integrates with AWS CloudTrail to provide a complete audit log of all queries executed.

Comparing Athena to Other Query Engines

Athena is one of several serverless SQL query engines available in the cloud. Let‘s see how it stacks up to some popular alternatives.

Feature	Athena	BigQuery	Azure Synapse Serverless	Snowflake
Serverless	Yes	Provisioned	Yes	Provisioned
Pricing Model	Per TB Scanned	Flat Rate + Storage	Per TB Processed	Per Second
Data Formats	Parquet, ORC, Avro, JSON, CSV	Proprietary	Parquet, JSON	Proprietary
External Tables	Hive	BigQuery ML	Spark	External (S3)
Data Lake Integration	S3	GCS	ADLS	S3, ADLS
Real-time Streaming	No	Yes	No	No
Materialized Views	No	Yes	No	Yes
Partitioning	Hive	Clustered/partitioned tables	No	Auto micro-partitions
Caching	No	Automatic	Result Set	SSD

Athena‘s key differentiators are its deep integration with S3, support for open source formats, and on-demand pricing model. BigQuery tightly couples storage and compute, but provides a flat rate pricing model and support for real-time streaming.

Snowflake decouples storage and compute like Athena, but uses a proprietary data format and charges per second rather than per TB scanned. Azure Synapse‘s serverless SQL pool is similar to Athena, but is tied to Azure‘s ecosystem rather than AWS.

Ultimately, the optimal choice depends on your data architecture, performance needs, and cloud preferences. For many AWS-centric organizations, Athena hits a sweet spot of flexibility, ease of use, and cost efficiency.

Enabling the Data Lake Paradigm

More broadly, Athena is a key enabler of the data lake paradigm. By providing an easy way to query raw data in cheap object storage, it helps break down data silos and democratize access to data.

In a traditional data architecture, data is ingested into a centralized data warehouse or data marts for BI and analytics. But this requires modeling data up front and loading it via batch ETL jobs. The result is that a only a small percentage of an organization‘s data typically makes it into the warehouse, and there are long lead times to answer new questions.

With a data lake architecture powered by Athena, you can ingest data into S3 in its raw format and make it immediately available for querying. This drastically reduces time to insight and enables more ad-hoc, exploratory analysis. You can still create curated datasets for repeatable analysis, but you‘re no longer limited by them.

The usage data bears this out. Athena has seen tremendous adoption, with customers like Plaid, FINRA, and Intuit querying petabytes of data and running hundreds of thousands of queries per day.

Conclusion

Amazon Athena‘s serverless architecture and separation of compute and storage make it a powerful and cost-effective option for querying data in S3. By understanding how Athena is different from traditional databases and following best practices, you can leverage it to extract insights from terabytes to petabytes of data without the overhead of managing infrastructure.

While not a replacement for all database use cases, Athena provides an easy way to get started with interactive querying to power everything from data exploration to operational analytics. By making all your data in S3 immediately accessible via SQL, Athena can help break down silos, democratize data, and enable more insights faster.

In the words of Ihor Bobak, Engineering Manager at Plaid:

"We rely on Amazon Athena to enable our analysts and data scientists to get insights from massive datasets. Athena allows our teams to focus on delivering data-driven products that power thousands of applications, without having to deal with infrastructure and database administration."

As data continues to grow in volume and complexity, query engines like Athena will only become more critical. By enabling more people to derive insights from more data, they hold the key to unlocking the full potential of data across the enterprise.