Analyzing Data: Athena, Redshift and Glue

The application we finished designing in part twenty-two produces a lot of data: orders, logs and events. Then someone asks: "Which city grew the most this year?"

You could run that on your production database and slow it down for your customers. Instead, AWS has a set of services built for analysis, and this part is a guide to them.

Two kinds of work #

The questions an application asks are different from the questions a business asks:

The secret of analytics services is storing data by column. To add up one column, there's no need to read the rest:

Amazon Athena: SQL on S3 #

Athena lets you query files in S3 directly with SQL. There's nothing to run: you describe the files as a table, and the data stays where it is.

You pay about $5 per TB of data scanned, with a 10 MB minimum per query. Creating tables and failed queries cost nothing. It suits occasional questions and analyzing logs, such as those from a load balancer or S3, and it's also where AWS points you now that S3 Select is closed to new customers.

Read less, pay less #

Since the price depends on what's read, reading less means both a lower bill and a faster answer:

• Columnar formats: convert files to Parquet or ORC, so a query reads only its columns.
• Partitions: split the data into paths such as year=2025/, so a query skips the rest.
• Compression: the smaller the data, the less gets scanned.
• Larger files: thousands of tiny files slow things down, so merge them.

Federated queries #

Your data isn't all in S3? Federated Query lets Athena reach other sources, such as DynamoDB or RDS, through a connector that runs as a Lambda function in your account. One query can even join them with your files.

Amazon Redshift: the warehouse #

When reports become heavy and frequent, with many joins and many users, you need a data warehouse. Redshift is based on PostgreSQL, stores data by column, and splits a query across many nodes in parallel.

Getting data in #

Things worth knowing #

• Redshift Spectrum: query files in S3 from Redshift without loading them, and join them with your tables.
• Snapshots: automatic and incremental, and you can copy them to another Region to recover from a disaster.
• Multi-AZ: available on provisioned RA3 clusters, with nodes in two Availability Zones behind one endpoint.

Athena or Redshift? #

Start with Athena, and move to Redshift when your queries become frequent enough for it to pay off.

AWS Glue: preparing the data #

Analysis assumes the data is clean and organized, which is rarely the case. Glue is a serverless set of tools for that:

A common pattern: a file lands in S3, an event starts a Glue job, and it converts the file to Parquet in another folder. Note that Glue Ray jobs closed to new customers on April 30, 2026, while Spark jobs carry on as usual.

You may also read about AWS Data Pipeline, an older service for moving and processing data on a schedule. It's closed to new customers and in maintenance mode, so use Glue or Step Functions for new work.

Lake Formation: permissions for the lake #

S3 with Parquet files and a catalog is what's called a data lake. As it grows, managing access through bucket policies gets hard, because S3 knows about files, not columns.

Lake Formation gives you one place to grant access by database, table, column and even row: the sales team sees orders without card numbers, for example. These permissions apply through services like Athena, Redshift and EMR.

Search, big jobs and dashboards #

OpenSearch #

A database is good at finding by key. OpenSearch is good at finding by any word in any field, and at analyzing logs. It comes as managed clusters where you control the sizes, or as Serverless for changing load.

It's usually a copy of your data, not the source. Two common patterns:

• Search over a table: DynamoDB Streams, then Lambda, then OpenSearch. You search there, then fetch the full item from the table.
• Log analysis: CloudWatch Logs, then Firehose, then OpenSearch.

Amazon EMR #

Do you have existing Spark or Hadoop jobs? EMR runs them, and you can choose to run it on EC2, on EKS or as Serverless. On EC2 there are three kinds of nodes:

Quick Sight #

Amazon Quick Sight builds dashboards from Athena, Redshift, RDS, S3 and others. Its name was QuickSight, and since October 2025 it has been part of a broader suite called Amazon Quick, though the API still uses the old name.

• SPICE: an in-memory engine that makes dashboards fast, without burdening the source on every view.
• An analysis or a dashboard: you edit in an analysis, and publish it as a read-only dashboard.
• Row-level security: each manager sees only their own region's data.

Streams: Flink and Kafka #

Kinesis and Firehose deliver the data. What if you want to analyze it as it arrives, such as spotting a suspicious pattern within seconds?

Managed Service for Apache Flink (formerly Kinesis Data Analytics) runs Flink applications that read from a Kinesis stream or from MSK, without you managing the cluster.

Amazon MSK is a managed Apache Kafka, the most popular streaming platform in the open-source world. It comes as provisioned clusters with Standard or Express brokers, or as Serverless.

How does the data arrive? #

Choosing a way in starts with how often the data arrives, and how large it is:

Secure each way in the same manner: least-privilege IAM roles, encryption in transit and at rest, and private paths such as VPC endpoints instead of the public internet where you can.

The complete picture #

Here's how the pieces meet in a typical pipeline:

Hands-on: Parquet cuts what a query reads #

We'll generate a file of 200,000 orders and put the same question to it twice. From CloudShell:

BUCKET=series-data-$RANDOM$RANDOM
aws s3 mb s3://$BUCKET

awk 'BEGIN {
  srand(7)
  split("Riyadh Jeddah Dammam Abha Tabuk", city, " ")
  for (i = 1; i <= 200000; i++)
    printf "%d,%s,%d,%.2f\n", i, city[int(rand()*5)+1], 2024+int(rand()*3), rand()*500
}' > sales.csv

head -3 sales.csv
aws s3 cp sales.csv s3://$BUCKET/raw/sales.csv

This function runs a query, waits for it, then prints its state, the bytes it scanned and its result:

run_sql() {
  ID=$(aws athena start-query-execution --query-string "$1" \
    --result-configuration OutputLocation=s3://$BUCKET/results/ \
    --query QueryExecutionId --output text)
  while true; do
    STATE=$(aws athena get-query-execution --query-execution-id $ID \
      --query QueryExecution.Status.State --output text)
    [ "$STATE" = QUEUED ] || [ "$STATE" = RUNNING ] || break
    sleep 1
  done
  aws athena get-query-execution --query-execution-id $ID \
    --query "QueryExecution.[Status.State,Statistics.DataScannedInBytes]" \
    --output text
  aws athena get-query-results --query-execution-id $ID \
    --query "ResultSet.Rows[*].Data[*].VarCharValue" --output text
}

Describe the file as a table. Nothing moves, and the data stays in S3:

run_sql "CREATE DATABASE series_data"

run_sql "CREATE EXTERNAL TABLE series_data.sales_csv (
  order_id INT, city STRING, year INT, amount DOUBLE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION 's3://$BUCKET/raw/'"

Ask the question:

run_sql "SELECT city, ROUND(SUM(amount)) AS total
FROM series_data.sales_csv WHERE year = 2025
GROUP BY city ORDER BY total DESC"

You get the total for each city, and the first line shows the whole file was scanned, a little over 5 MB, even though the question concerns one year and two columns. Now create a partitioned Parquet copy with one query:

run_sql "CREATE TABLE series_data.sales_parquet WITH (
  format = 'PARQUET',
  external_location = 's3://$BUCKET/parquet/',
  partitioned_by = ARRAY['year']
) AS SELECT order_id, city, amount, year FROM series_data.sales_csv"

aws s3 ls s3://$BUCKET/parquet/

You'll see a folder for each year. Ask the same question of the new table:

run_sql "SELECT city, ROUND(SUM(amount)) AS total
FROM series_data.sales_parquet WHERE year = 2025
GROUP BY city ORDER BY total DESC"

The numbers match, but the scanned bytes are a small fraction of the first figure. On terabytes, that difference is your bill.

run_sql "DROP TABLE series_data.sales_parquet"
run_sql "DROP TABLE series_data.sales_csv"
run_sql "DROP DATABASE series_data"
aws s3 rb s3://$BUCKET --force

Summary #

• Separate analysis from your production database, and store data by column.
• Athena queries S3 with no servers, and Parquet and partitions lower its cost.
• Redshift is for heavy, repeated reports, and Serverless is an easy start.
• Glue catalogs and converts your data, and Lake Formation controls who sees which column.
• OpenSearch is for search, EMR for Spark jobs, Quick Sight for dashboards, and Flink and MSK for streams.

Test yourself #

Before you move on, try these questions to check you've got this part. Each one is a practical scenario, and the explanation appears once you answer.

You now understand the data you hold. Can you get more out of it, such as reading documents, understanding speech or generating text? In part twenty-four we go through AWS's ready-made AI services.