Amazon AthenaAuthorCloudBrum editorial teamLast reviewed28 Oct 2026Reading time~12 minLevelBeginner to advanced

• Query files where they sitThere’s nothing to load. Point a table at an S3 prefix and run SQL against it. The same data can be read by Athena, a Spark job and a data science notebook at the same time.
• Pay by the bytes scannedOn-demand queries cost about $5 per terabyte scanned, with a 10 MB minimum per query. Statements that only change the catalog are free, and so are queries that fail before reading any data.
• Tables from the Glue Data CatalogA table is a definition of columns, a format and a location. Athena keeps it in the Glue Data Catalog, which other AWS analytics services share. Partition projection lets Athena work out partition paths without storing every one.
• Standard SQL with a few extrasThe engine is based on Trino. You get joins, window functions, JSON and array functions, CREATE TABLE AS SELECT to turn raw data into tidy Parquet, and INSERT INTO for Iceberg tables.
• WorkgroupsA workgroup separates one team’s queries from another’s. It can force a result location and encryption, cap the data a query may scan, publish metrics and keep the history apart.
• Federated queriesConnectors, which run as Lambda functions, let a query read from sources such as relational databases, DynamoDB or other clouds and join them with S3 data. They add their own Lambda and data transfer costs.
• Spark when SQL isn’t enoughAthena can run Apache Spark notebooks for Python-heavy work on the same data, billed by DPU-hours. It’s a separate cost model from the SQL queries.
• Provisioned capacityFor heavy, predictable use you can reserve capacity in DPUs, which are billed by the hour instead of by the bytes scanned.

Query result location

The S3 prefix where Athena writes the result of every query as a file. It needs its own bucket or prefix, a lifecycle rule and encryption.

Table

A schema, a format and an S3 location. Dropping an external table removes the definition only, not the files.

Database

A group of tables in the Glue Data Catalog, similar to a schema in other systems.

Partition

A folder in the path such as order_date=2026-10-28. Queries that filter on the partition column read only the matching folders.

Partition projection

A table property that tells Athena how partition values are built, such as a date range, so it doesn’t need a catalog entry for each partition.

Columnar format

Parquet and ORC store data by column, with compression and statistics. A query reads only the columns it names.

Bytes scanned

The amount of data read from S3 for a query, after compression. It’s the number your on-demand bill is based on.

Workgroup

A named setting for a set of users or applications: result location, encryption, scan limit, engine version and metrics.

CTAS

CREATE TABLE AS SELECT. It runs a query and writes the result as new files in a format and layout you choose, and registers a table for them.

Iceberg table

A table format that adds transactions, schema changes and row-level updates on top of files in S3.

Federated query

A query that reaches outside S3 through a data source connector, usually a Lambda function.

DPU

Data processing unit, the measure of capacity for provisioned queries and Spark sessions, billed per hour.

Query result reuse

A setting that lets Athena return a previous result for an identical query within a time you choose, so it scans nothing and charges nothing.

Lake Formation

An AWS service for access rules on catalog tables, down to columns and rows, which Athena respects.

Query data in S3 with the AWS CLI

1. Put the data in a sensible layoutStore files as Parquet if you can, under a folder structure such as orders/order_date=2026-10-28/. Compress them and keep each file between roughly 128 MB and 1 GB, since thousands of tiny files slow queries down.
2. Create a bucket for resultsMake a separate S3 bucket or prefix for query results, turn on encryption and block public access, and add a lifecycle rule that deletes old results.
3. Create a workgroupGive the team a workgroup that enforces the result location and encryption, and sets a scan limit for each query so one bad query can’t cost a fortune.
4. Create the database and the tableRun CREATE DATABASE and CREATE EXTERNAL TABLE with the columns, the format and the location. For date partitions, add partition projection so you don’t have to register each new day.
5. Run a query and read the resultStart the query with aws athena start-query-execution, poll its state until it succeeds, and fetch the rows. Athena also leaves the result as a file in your result location.
6. Check what it costRead DataScannedInBytes from the query execution. Multiply by the rate per terabyte to see the cost of that one query, and compare it with what you expected.
7. Convert and compactUse CTAS to turn raw CSV or JSON into partitioned, compressed Parquet. Queries on the new table scan a fraction of the data.
8. Control accessGrant the least privilege on the workgroup, the catalog and the S3 prefixes, and use Lake Formation where you need column or row rules.
9. Watch usagePublish the workgroup metrics to CloudWatch, set an alarm on data scanned per day, and review the most expensive queries each week.

• Store data as Parquet or ORC with compression. Queries read only the columns they need, and skip blocks using the file’s statistics.
• Partition by the column you filter on most, often a date, and keep the number of partitions reasonable. Too many tiny partitions are as bad as none.
• Use partition projection for date-like partitions, so planning stays fast as the number of folders grows.
• Aim for files of 128 MB to 1 GB. Compact many small files with CTAS or Iceberg maintenance.
• Select only the columns you need and put the biggest table first or last as the engine’s join guidance says, with the smaller one broadcast.
• Filter with plain predicates on partition columns. Wrapping a partition column in a function can stop pruning.
• Turn on query result reuse for dashboards that repeat the same query within a short window.
• For heavy repeated workloads, test provisioned capacity and compare it with the on-demand bill.

FinOps is the habit of managing cloud spend together with engineering. These are the FinOps checks that matter most for Amazon Athena.

• Remember the meter: bytes scanned. The cheapest query is one that reads little, so format, partitions and column lists are your levers.
• Convert CSV and JSON to compressed Parquet. It often cuts the scan by an order of magnitude.
• Set a scan limit per query and a daily alarm on each workgroup, so a mistake is stopped or noticed quickly.
• Use query result reuse and views for repeated questions.
• Add lifecycle rules on the result prefix and delete scratch tables created by CTAS.
• Consider provisioned capacity only for large, steady workloads, and compare it with on-demand for a month first.
• Don’t forget the extras: Glue Data Catalog storage and requests, S3 requests and storage, Lambda for federated connectors and KMS calls.
• Use Iceberg or compacted tables to avoid paying repeatedly to scan files that changed only slightly.
• Tag workgroups by team, and set a budget alert on Athena and S3.
• Teach analysts to check DataScannedInBytes, which every query reports, before they run something on the whole lake.

• Give users and applications least-privilege IAM on the workgroup, the Glue catalog and only the S3 prefixes they need.
• Use Lake Formation for column-level and row-level rules where different teams share tables.
• Encrypt the data in S3 and the query results, with SSE-S3 or KMS keys, and enforce the setting at the workgroup.
• Block public access on data and result buckets, and add bucket policies that limit access to named roles and your VPC endpoint.
• Use a VPC endpoint for Athena and S3 so traffic stays on the AWS network.
• Keep federated connectors in a private network with narrow roles, and rotate any stored credentials in Secrets Manager.
• Turn on CloudTrail for Athena and S3 data events, so you can see who ran what over which data.
• Use separate workgroups for sensitive and general data, with different result locations and keys.
• Avoid putting secrets or personal data in query strings, table properties or named queries.
• Review access with IAM Access Analyzer and AWS Config rules for public buckets and unencrypted results.

AWS CloudTrail records who started queries and changed workgroups, CloudWatch holds the workgroup metrics, AWS Config and Security Hub check the surrounding S3 and encryption settings, and AWS Artifact holds compliance reports. AWS runs the query engine and the infrastructure behind it, while you own the data, its layout, the access rules, the result files and the bill that follows from how people query.

• Scan less: partitions and columnar files cut the compute done per answer.
• Compact small files, which reduces the requests and the processing needed per query.
• Reuse results for repeated queries instead of recomputing them.
• Delete scratch tables and old results that nobody reads.
• Compress data, which means less storage and less data moved.
• Choose a Region with a cleaner grid for batch analysis that isn’t tied to a place, as far as data rules allow.
• AWS’s Customer Carbon Footprint Tool gives account-level emission estimates. Check whether Athena usage is itemised before you quote a figure.

Purchase options

What you actually pay for

• Bytes scanned by each on-demand query, rounded up with a 10 MB minimum
• Provisioned capacity or Spark DPU-hours, if you use them
• S3 storage and requests for the data and the query results
• Glue Data Catalog storage and requests beyond the free allowance
• Lambda, KMS and data transfer for federated queries and encryption

Order data stored as Parquet in S3, partitioned by day, queried from a workgroup that enforces an encrypted result location and a 10 GiB limit per query. The CLI tab creates the workgroup, the table with partition projection and a seven-day revenue query, then compacts October with CTAS. Terraform builds the workgroup, the database and the table. The Python tab runs a query and prints the bytes scanned and the cost at the list rate, and the C# tab runs a query and reads the rows. Replace the bucket names and the Region with your own. Queries cost money by the bytes they scan, so start with one partition.

# Placeholders: replace the bucket names and the Region with your own.
REGION=ap-south-1
LAKE=orders-lake-example
RESULTS=orders-athena-results-example
WG=orders-analytics

aws s3api create-bucket --bucket $RESULTS --region $REGION --create-bucket-configuration LocationConstraint=$REGION
aws s3api put-public-access-block --bucket $RESULTS --public-access-block-configuration BlockPublicAcls=true,IgnorePublicAcls=true,BlockPublicPolicy=true,RestrictPublicBuckets=true
aws s3api put-bucket-lifecycle-configuration --bucket $RESULTS --lifecycle-configuration '{"Rules":[{"ID":"expire-results","Status":"Enabled","Filter":{"Prefix":""},"Expiration":{"Days":30}}]}'

# The workgroup: results go to one encrypted place, and no single query may scan more than 10 GiB.
aws athena create-work-group --name $WG --region $REGION --configuration '{
  "ResultConfiguration": {"OutputLocation": "s3://'$RESULTS'/analytics/", "EncryptionConfiguration": {"EncryptionOption": "SSE_S3"}},
  "EnforceWorkGroupConfiguration": true,
  "PublishCloudWatchMetricsEnabled": true,
  "BytesScannedCutoffPerQuery": 10737418240,
  "EngineVersion": {"SelectedEngineVersion": "Athena engine version 3"}}'

# Small helper: run a statement in the workgroup, wait for it, and print how much data it scanned.
run() {
  local id state
  id=$(aws athena start-query-execution --region $REGION --work-group $WG --query-string "$1" --query QueryExecutionId --output text)
  while :; do
    state=$(aws athena get-query-execution --region $REGION --query-execution-id "$id" --query QueryExecution.Status.State --output text)
    case $state in SUCCEEDED) break ;; FAILED|CANCELLED) aws athena get-query-execution --region $REGION --query-execution-id "$id" --query QueryExecution.Status.StateChangeReason --output text; return 1 ;; esac
    sleep 2
  done
  aws athena get-query-execution --region $REGION --query-execution-id "$id" --query 'QueryExecution.Statistics.DataScannedInBytes' --output text | sed 's/^/bytes scanned: /' >&2
  echo "$id"
}

run "CREATE DATABASE IF NOT EXISTS orders_lake"

# Parquet files under orders/order_date=YYYY-MM-DD/. Projection works the partitions out, so there is nothing to register each day.
run "CREATE EXTERNAL TABLE IF NOT EXISTS orders_lake.orders (
  order_id string, customer_id string, total double, currency string, status string)
PARTITIONED BY (order_date string)
STORED AS PARQUET
LOCATION 's3://$LAKE/orders/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.order_date.type' = 'date',
  'projection.order_date.format' = 'yyyy-MM-dd',
  'projection.order_date.range' = '2026-01-01,NOW',
  'storage.location.template' = 's3://$LAKE/orders/order_date=\${order_date}/')"

# Revenue per day for the last seven days. The filter is on the partition column, so only seven folders are read.
QID=$(run "SELECT order_date, count(*) AS orders, round(sum(total), 2) AS revenue
FROM orders_lake.orders
WHERE order_date >= date_format(date_add('day', -7, current_date), '%Y-%m-%d') AND status = 'PAID'
GROUP BY order_date ORDER BY order_date")
aws athena get-query-results --region $REGION --query-execution-id "$QID" --query 'ResultSet.Rows[*].Data[*].VarCharValue' --output text

# Compact October into fewer, compressed files.
run "CREATE TABLE orders_lake.orders_2026_10
WITH (format = 'PARQUET', parquet_compression = 'ZSTD', external_location = 's3://$LAKE/curated/orders_2026_10/')
AS SELECT order_id, customer_id, total, currency, status, order_date
FROM orders_lake.orders WHERE order_date BETWEEN '2026-10-01' AND '2026-10-31'"

# Placeholders: the bucket names are examples, S3 bucket names are global.
variable "lake_bucket" {
  type    = string
  default = "orders-lake-example"
}

variable "results_bucket" {
  type    = string
  default = "orders-athena-results-example"
}

resource "aws_s3_bucket" "results" {
  bucket = var.results_bucket
}

resource "aws_s3_bucket_public_access_block" "results" {
  bucket                  = aws_s3_bucket.results.id
  block_public_acls       = true
  ignore_public_acls      = true
  block_public_policy     = true
  restrict_public_buckets = true
}

resource "aws_s3_bucket_server_side_encryption_configuration" "results" {
  bucket = aws_s3_bucket.results.id
  rule {
    apply_server_side_encryption_by_default {
      sse_algorithm = "AES256"
    }
  }
}

resource "aws_s3_bucket_lifecycle_configuration" "results" {
  bucket = aws_s3_bucket.results.id
  rule {
    id     = "expire-results"
    status = "Enabled"
    filter {}
    expiration {
      days = 30
    }
  }
}

resource "aws_athena_workgroup" "orders" {
  name          = "orders-analytics"
  force_destroy = false

  configuration {
    enforce_workgroup_configuration    = true
    publish_cloudwatch_metrics_enabled = true
    bytes_scanned_cutoff_per_query     = 10737418240

    engine_version {
      selected_engine_version = "Athena engine version 3"
    }

    result_configuration {
      output_location = "s3://${aws_s3_bucket.results.bucket}/analytics/"
      encryption_configuration {
        encryption_option = "SSE_S3"
      }
    }
  }
}

resource "aws_athena_database" "orders_lake" {
  name   = "orders_lake"
  bucket = aws_s3_bucket.results.id
}

resource "aws_glue_catalog_table" "orders" {
  name          = "orders"
  database_name = aws_athena_database.orders_lake.name
  table_type    = "EXTERNAL_TABLE"

  parameters = {
    "classification"                 = "parquet"
    "projection.enabled"             = "true"
    "projection.order_date.type"     = "date"
    "projection.order_date.format"   = "yyyy-MM-dd"
    "projection.order_date.range"    = "2026-01-01,NOW"
    "storage.location.template"      = "s3://${var.lake_bucket}/orders/order_date=$${order_date}/"
  }

  partition_keys {
    name = "order_date"
    type = "string"
  }

  storage_descriptor {
    location      = "s3://${var.lake_bucket}/orders/"
    input_format  = "org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat"
    output_format = "org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat"

    ser_de_info {
      serialization_library = "org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe"
    }

    columns {
      name = "order_id"
      type = "string"
    }
    columns {
      name = "customer_id"
      type = "string"
    }
    columns {
      name = "total"
      type = "double"
    }
    columns {
      name = "currency"
      type = "string"
    }
    columns {
      name = "status"
      type = "string"
    }
  }
}

# Runs a query in a workgroup, waits for it and reports what it scanned and what that cost at the list rate.
import sys
import time

import boto3

athena = boto3.client("athena")
RATE_PER_TB = 5.00        # USD, the on-demand list price at the time of writing; check your Region's page


def run(sql, workgroup="orders-analytics", timeout=300):
    qid = athena.start_query_execution(QueryString=sql, WorkGroup=workgroup)["QueryExecutionId"]
    deadline = time.monotonic() + timeout
    while time.monotonic() < deadline:
        q = athena.get_query_execution(QueryExecutionId=qid)["QueryExecution"]
        state = q["Status"]["State"]
        if state == "SUCCEEDED":
            return qid, q["Statistics"].get("DataScannedInBytes", 0)
        if state in ("FAILED", "CANCELLED"):
            raise RuntimeError("%s: %s" % (state, q["Status"].get("StateChangeReason", "no reason given")))
        time.sleep(1.5)
    athena.stop_query_execution(QueryExecutionId=qid)
    raise TimeoutError("query still running after %d seconds, stopped" % timeout)


def rows(qid):
    for page in athena.get_paginator("get_query_results").paginate(QueryExecutionId=qid):
        for r in page["ResultSet"]["Rows"]:
            yield [c.get("VarCharValue") for c in r["Data"]]


if __name__ == "__main__":
    sql = sys.argv[1] if len(sys.argv) > 1 else "SELECT order_date, count(*) FROM orders_lake.orders WHERE order_date = '2026-10-27' GROUP BY 1"
    qid, scanned = run(sql)
    for row in rows(qid):
        print(row)
    # Athena rounds each query up to at least 10 MB
    billed = max(scanned, 10 * 1024 * 1024)
    print("scanned %.1f MB, about $%.4f at $%.2f per TB" % (scanned / 1e6, billed / 1e12 * RATE_PER_TB, RATE_PER_TB))

// dotnet add package AWSSDK.Athena
// Runs one query in the orders-analytics workgroup and prints the rows. The first row of the result is the header.
using Amazon.Athena;
using Amazon.Athena.Model;

const string workGroup = "orders-analytics";
string sql = args.Length > 0 ? args[0]
    : "SELECT status, count(*) AS orders FROM orders_lake.orders WHERE order_date = '2026-10-27' GROUP BY status";

using var athena = new AmazonAthenaClient();
var started = await athena.StartQueryExecutionAsync(new StartQueryExecutionRequest { QueryString = sql, WorkGroup = workGroup });

QueryExecutionState state;
long scanned;
do
{
    await Task.Delay(1500);
    var q = (await athena.GetQueryExecutionAsync(new GetQueryExecutionRequest { QueryExecutionId = started.QueryExecutionId })).QueryExecution;
    state = q.Status.State;
    scanned = q.Statistics?.DataScannedInBytes ?? 0;
    if (state == QueryExecutionState.FAILED || state == QueryExecutionState.CANCELLED)
        throw new InvalidOperationException($"{state}: {q.Status.StateChangeReason}");
} while (state != QueryExecutionState.SUCCEEDED);

string? token = null;
do
{
    var page = await athena.GetQueryResultsAsync(new GetQueryResultsRequest { QueryExecutionId = started.QueryExecutionId, NextToken = token });
    foreach (Row row in page.ResultSet.Rows)
        Console.WriteLine(string.Join(" | ", row.Data.Select(d => d.VarCharValue ?? "(null)")));
    token = page.NextToken;
} while (token != null);

Console.WriteLine($"Scanned {scanned / 1_000_000.0:F1} MB.");

Short walkthrough videos for this service are being prepared. They will appear here and load only when you press play. Until then, follow CloudBrum on YouTube for new videos.

If you need fast, repeated dashboard queries from many users, look at a warehouse such as Amazon Redshift, or BigQuery native tables, instead of scanning files each time. For heavy transforms use AWS Glue or EMR. The files themselves live in Amazon S3, and transactional data stays in a database such as Amazon RDS.