AWS Athena

Amazon Athena is a serverless, interactive query service that lets you analyze data directly in Amazon S3 using standard SQL. There is no infrastructure...

• What is Amazon Athena, and how does it function?

  Amazon Athena is a serverless, interactive query service that lets you analyze data directly in Amazon S3 using standard SQL. There is no infrastructure to provision or manage you simply point Athena at your data, define a schema, and start querying. Under the hood, Athena uses Presto (and now Trino in Athena engine v3) as its distributed SQL execution engine to run queries at scale. You pay only for the amount of data scanned per query, making it cost effective for ad hoc analytics on data lake

• How does Athena integrate with AWS Glue Data Catalog?

  Athena uses the AWS Glue Data Catalog as its default metastore to store database, table, and partition definitions. When you create a table in Athena, the metadata is written to the Glue Data Catalog and becomes accessible to other AWS services like EMR , Redshift Spectrum , and Glue ETL . This integration provides a single, centralized schema repository, making data discovery and governance easier across the analytics stack.

• What data formats does Athena support?

  Athena supports a wide range of data formats: Columnar : Parquet, ORC Row based : CSV, TSV, JSON, Avro Other : Apache logs, CloudTrail logs, custom delimited text It also handles compressed files using gzip , Snappy , LZ4 , and ZSTD . For best performance and cost efficiency, columnar formats like Parquet or ORC are recommended because Athena can skip irrelevant columns during scans.

• How can you optimize query performance in Athena?

  Key strategies to optimize Athena query performance: Use columnar formats like Parquet or ORC to enable column pruning Partition data on frequently filtered columns (e.g., year , month , day ) Compress data with Snappy or gzip to reduce bytes scanned Bucket data within partitions for more efficient joins Select only needed columns instead of SELECT Use CTAS (CREATE TABLE AS SELECT) to convert and optimize existing datasets Since Athena charges per TB scanned, these optimizations reduce both late

• How does Athena handle data partitioning?

  Partitioning in Athena organizes S3 data into path based segments (e.g., s3://bucket/table/year=2024/month=06/ ). When a query includes a partition filter in the WHERE clause, Athena prunes irrelevant partitions and scans only matching S3 prefixes. You can add partitions manually with ALTER TABLE ADD PARTITION or automatically via MSCK REPAIR TABLE or a Glue Crawler . Partition projection is another option that computes partition values at query time without needing to store them in the catalog,

• Explain the pricing model of Amazon Athena.

  Athena charges $5 per terabyte of data scanned by each query. DDL statements and failed queries incur no charge. You can significantly reduce costs by: Using columnar formats (Parquet/ORC) so only required columns are read Partitioning data to limit the S3 prefixes scanned Compressing files to shrink the bytes processed Additionally, workgroups let you set per query or per workgroup data scan limits to prevent runaway costs.

• How can you secure data queried by Athena?

  Athena provides multiple layers of security: IAM policies control who can run queries, access databases, and manage workgroups S3 encryption (SSE S3, SSE KMS, or CSE KMS) protects data at rest Query result encryption secures output stored in the results S3 bucket AWS Lake Formation enforces fine grained table and column level permissions VPC endpoints (interface endpoints via PrivateLink ) keep traffic within the AWS network TLS encrypts data in transit between Athena and S3

• Describe a scenario where using Athena is more beneficial than a traditional relational database.

  Athena is ideal for ad hoc querying of large datasets stored in S3 without provisioning or managing database infrastructure. A common scenario is a data lake architecture where raw logs, clickstream data, or IoT events land in S3 in Parquet format. Analysts can run exploratory SQL queries on terabytes of data without loading it into a warehouse. Athena is also excellent for log analysis (e.g., CloudTrail, ALB logs) and one off investigations where standing up a database would be overkill. For wo