Is Athena, Glue, and the Serverless Analytics Stack free to learn on DevOps Network?

Yes - this topic, like everything on DevOps Network, is 100% free with no paywall or sign-up gate.

What does the Athena, Glue, and the Serverless Analytics Stack topic cover?

Query raw S3 data with Athena using SQL, convert CSV to Parquet with Glue ETL, and build a governed data lake with Lake Formation for cost-optimised analytics.

Athena, Glue, and the Serverless Analytics Stack

What you will learn

What Athena is and how it queries S3 data directly without loading anything
The single most impactful optimisation for Athena — columnar format and why it cuts costs 90%
Partitioning, compression, and large file strategies that multiply Athena performance
Athena Federated Query for querying ElastiCache, DynamoDB, and RDS in one SQL statement
AWS Glue as a serverless ETL engine — what it does and when to use it
Glue Data Catalog — the central metadata store that powers Athena, Redshift Spectrum, and EMR
Glue Crawler — automatic schema discovery that removes manual table definitions
Glue Job Bookmarks, DataBrew, and Streaming ETL
Lake Formation — governed data lake with row-level and column-level security
The complete Big Data ingestion pipeline — IoT to dashboard, fully serverless

Why this matters

Swiggy generates millions of order events, delivery events, and user interactions every day. Storing them in a relational database for analytics is expensive and slow at scale. Storing them as JSON in S3 and querying with Athena costs a fraction of the price and scales to any volume. Razorpay analyses transaction patterns across billions of payment records — Redshift for the warehouse, Athena for the exploratory queries on raw logs, Glue for the ETL pipelines that transform raw data into query-ready Parquet. The entire stack is serverless. No Hadoop clusters to manage, no Spark infrastructure to maintain. Understanding this stack is what separates an engineer who can build production data pipelines from one who can only describe them.

Amazon Athena — Serverless SQL on S3

Athena is a serverless query engine that runs SQL directly on files in S3. No database to set up, no data to load, no server to provision.

◈ DIAGRAM

Raw files in S3 (CSV, JSON, Parquet, ORC)
    ↓
Amazon Athena (you write standard SQL here)
    ↓
Results appear in console, download as CSV, or flow to QuickSight

Data never moves. Athena reads files directly from S3 where they already live. Built on Presto.

Pricing: $5.00 per TB of data scanned

Remember
Athena never moves or loads data. It reads directly from S3 in place. Every optimisation technique exists to reduce how much data Athena must read per query — because less data scanned means lower cost and faster results.

Common use cases:

◈ DIAGRAM

Log analysis          → VPC Flow Logs, CloudTrail, ALB access logs
Business intelligence → revenue queries on raw S3 data
Ad-hoc analysis       → explore data without loading it into a database
Cost and Usage Reports → query your AWS bill with SQL
Data lake queries     → exploratory analysis before building a formal pipeline

The Single Most Important Athena Optimisation

Supported file formats and their performance:

Format	Performance	Why
CSV	Poor	Row-based — reads entire file even for one column query
JSON	Poor	Row-based — same issue as CSV
Parquet	Recommended	Columnar — skips irrelevant columns
ORC	Recommended	Columnar — better compression and indexing
Avro	Supported	General purpose — better than CSV

Why columnar format matters so much:

TEXT

CSV stores data row by row:
  row1: name, age, salary, city, department, hire_date...
  row2: name, age, salary, city, department, hire_date...
 
Query: SELECT AVG(salary) FROM employees
CSV must read: EVERY column of EVERY row just to get salary values
 
Parquet stores data column by column:
  column: salary values for ALL rows
  column: name values for ALL rows
 
Query: SELECT AVG(salary) FROM employees
Parquet reads: ONLY the salary column, skips all others
 
Result: up to 90% less data scanned, 90% cheaper, significantly faster

Converting CSV to Parquet with Glue:

Use a Glue ETL job to convert existing CSV or JSON to Parquet automatically. One-time conversion, then all future queries benefit.

◈ DIAGRAM

Before conversion:
  Query on 100 GB CSV file → 100 GB scanned → $0.50
 
After conversion to Parquet:
  Same query on Parquet → 10 GB scanned → $0.05
  90% cost reduction on every query going forward

Four Ways to Make Athena Fast and Cheap

1. Use columnar format:

Convert to Parquet or ORC using Glue. Most impactful change. Do this first.

2. Compress data:

Smaller files mean less data scanned. Parquet supports built-in compression.

TEXT

Supported: gzip, bzip2, lz4, snappy, zstd
Snappy: good balance of compression ratio and speed
gzip: better compression, slower decompression

3. Partition your data in S3:

Organise files into folders so Athena only reads partitions matching your query filter.

◈ DIAGRAM

Without partitioning:
  s3://logs/app.log.2024.01.01
  s3://logs/app.log.2024.01.02
  s3://logs/app.log.2024.01.03
  Query: WHERE date = '2024-01-15' → Athena scans ALL files
 
With partitioning:
  s3://logs/year=2024/month=01/day=15/file.parquet
  s3://logs/year=2024/month=01/day=16/file.parquet
  s3://logs/year=2024/month=02/day=01/file.parquet
  Query: WHERE year=2024 AND month=01 AND day=15
  → Athena reads ONLY the matching folder — skips everything else

4. Use large files:

Keep individual files above 128 MB. Many tiny files cause overhead because Athena opens each one with a separate API call.

TEXT

100 files × 1 MB each  = 100 MB total, 100 S3 API calls, overhead per file
1 file  × 100 MB       = 100 MB total, 1 S3 API call, maximum efficiency

Athena Federated Query

By default Athena only queries S3. Federated Query extends it to query almost any data source using Lambda-based Data Source Connectors.

TEXT

Supported sources via connectors:
  ElastiCache, DocumentDB, DynamoDB, RDS (MySQL, PostgreSQL)
  On-premises databases, HBase, Redis, JDBC sources
 
One SQL query spans multiple sources:
  SELECT orders.id, users.email, cache.session_count
  FROM s3_logs.orders orders
  JOIN rds_prod.users users ON orders.user_id = users.id
  JOIN elasticache.sessions cache ON users.id = cache.user_id

Results are stored back in S3. You query multiple systems in one SQL statement without building a custom ETL pipeline.

AWS Glue — Serverless ETL and Data Catalog

Glue is a fully serverless managed ETL service. ETL = Extract, Transform, Load. Pull data from a source, clean or convert it, push to a destination.

TEXT

No servers to manage
Runs Apache Spark under the hood
Pay only for the time your ETL job runs

Most common Glue pattern — CSV to Parquet:

◈ DIAGRAM

S3 bucket receives CSV file
    ↓
Glue ETL job triggered (EventBridge or on schedule)
    ↓
Glue reads CSV from input S3 bucket
    ↓
Converts to Parquet format
    ↓
Writes Parquet to output S3 bucket
    ↓
Athena queries Parquet at up to 90% lower cost

Python

## Glue ETL script — convert CSV to Parquet
import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
 
args = getResolvedOptions(sys.argv, ['JOB_NAME'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)
 
## Read CSV from S3
datasource = glueContext.create_dynamic_frame.from_catalog(
    database="devops_raw",
    table_name="orders_csv"
)
 
## Write as Parquet to output bucket
glueContext.write_dynamic_frame.from_options(
    frame=datasource,
    connection_type="s3",
    connection_options={"path": "s3://devops-analytics/orders_parquet/"},
    format="parquet"
)
 
job.commit()

Glue Data Catalog — Central Metadata Store

The Data Catalog stores table definitions — what columns exist, what types they are, where the data lives in S3. It is the single source of truth for data structure across your analytics stack.

◈ DIAGRAM

Data sources (S3, RDS, DynamoDB, JDBC)
    ↓
Glue Crawler scans sources and detects schema
    ↓
Writes table definitions to Data Catalog
    ↓ (all read from the same catalog)
Athena    → runs SQL queries using catalog table definitions
Redshift Spectrum → discovers S3 data through catalog
EMR       → uses catalog for data discovery
Glue ETL  → reads catalog to understand source schemas

Without the Data Catalog:

TEXT

Each time you want to query a new dataset in Athena:
  CREATE EXTERNAL TABLE orders (
    id STRING, user_id STRING, amount DOUBLE, ...
  )
  ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
  LOCATION 's3://my-bucket/orders/'
Manual work every time schema changes

With Glue Crawler:

TEXT

Point Crawler at your S3 bucket
Crawler scans the data and detects schema automatically
Catalog populated — Athena can query immediately
Schema changes detected on next crawler run

Glue Crawler — Automatic Schema Discovery

A Crawler scans your data sources, determines the schema, and writes table definitions to the Data Catalog.

TEXT

Supported sources:
  S3 (CSV, JSON, Parquet, ORC, Avro)
  RDS databases
  DynamoDB
  JDBC sources
  DocumentDB

Crawler workflow:

◈ DIAGRAM

Create a Crawler in Glue Console
    ↓
Set source: s3://devops-raw-data/orders/
    ↓
Set target: Glue Data Catalog database name
    ↓
Schedule: on-demand, hourly, daily, weekly
    ↓
Crawler runs → samples files → detects schema
    ↓
Creates or updates table definitions in catalog
    ↓
Athena can query the table immediately

Bash

## Create a Glue crawler via CLI
aws glue create-crawler \
  --name devops-orders-crawler \
  --role arn:aws:iam::123456789012:role/GlueRole \
  --database-name devops_analytics \
  --targets '{
    "S3Targets": [{
      "Path": "s3://devops-raw-data/orders/",
      "Exclusions": ["**/.DS_Store"]
    }]
  }' \
  --schedule "cron(0 1 * * ? *)" \
  --region ap-south-1
 
## Run the crawler immediately
aws glue start-crawler \
  --name devops-orders-crawler \
  --region ap-south-1
 
## Check crawler status
aws glue get-crawler \
  --name devops-orders-crawler \
  --query 'Crawler.{State:State,LastRun:LastCrawl.StartTime}' \
  --region ap-south-1

Advanced Glue Features

Glue Job Bookmarks — process only new data:

◈ DIAGRAM

Without bookmarks:
  Every job run → processes all data from the beginning
  100 GB already processed + 1 GB new = 101 GB processed every run
 
With bookmarks:
  First run → processes all 100 GB
  Second run → tracks where it stopped, processes only new 1 GB
  Incremental processing — only new data each run

Bash

## Enable bookmarks when creating a Glue job
aws glue create-job \
  --name devops-orders-etl \
  --role arn:aws:iam::123456789012:role/GlueRole \
  --command Name=glueetl,ScriptLocation=s3://devops-scripts/etl.py \
  --default-arguments '{"--job-bookmark-option": "job-bookmark-enable"}' \
  --region ap-south-1

Glue DataBrew — visual no-code data cleaning:

TEXT

250+ pre-built transformations
Remove duplicates, fix date formats, fill missing values, mask PII
Business analysts can use it without writing Spark or Python
Creates a recipe of transformations that can be scheduled

Glue Streaming ETL — real-time data transformation:

◈ DIAGRAM

Run ETL on streaming data instead of batch files
Built on Apache Spark Structured Streaming
Compatible with Kinesis Data Streams, Apache Kafka, MSK
 
IoT devices → Kinesis Data Streams → Glue Streaming ETL → S3 (Parquet)
Continuous transformation, no batch windows

AWS Lake Formation — Governed Data Lake

Setting up a data lake manually takes months — collecting from sources, cleaning, moving to S3, cataloging, managing access. Lake Formation automates all of this and adds centralized fine-grained access control.

Data Lake vs Data Warehouse:

TEXT

Data Warehouse (Redshift):
  Structured data only
  Must load and transform first
  Schema defined before storing
  Expensive at scale
 
Data Lake (S3 + Lake Formation):
  All types — CSV, JSON, logs, images, video
  Store raw, transform when reading
  Schema defined when reading (schema-on-read)
  Cheap at S3 pricing

How Lake Formation works:

◈ DIAGRAM

Data Sources (S3, RDS, Aurora, on-premises)
    ↓
Lake Formation (crawlers, ETL, Data Catalog)
    ↓
Data Lake stored in S3
    ↓
Centralized access control enforced (row + column level)
    ↓
Athena, Redshift Spectrum, EMR query through Lake Formation permissions

The main reason to use Lake Formation — centralized permissions:

Without Lake Formation you manage separate IAM policies per service. Athena has its own permissions. Redshift Spectrum has its own. EMR has its own. Difficult to control who sees which rows or columns consistently across all services.

With Lake Formation you define access rules once:

TEXT

Row-level security:
  Rahul can only see rows where region = 'ap-south-1'
  Priya can see all rows
 
Column-level security:
  Finance team sees salary column
  Other teams see all columns except salary
 
These rules are enforced automatically for Athena, Redshift Spectrum, and EMR.
One definition, applied everywhere.

Remember
Lake Formation is built on top of AWS Glue. It uses Glue internally for crawling, ETL, and the Data Catalog — but adds data lake management and centralized row/column security on top. Use Glue alone for ETL pipelines. Use Lake Formation when you need a full governed data lake with fine-grained access control across multiple analytics services.

The Big Data Ingestion Pipeline — Everything Connected

This is the architecture that ties everything in this topic together. Fully serverless from IoT to dashboard.

◈ DIAGRAM

IoT Devices (real-time sensor data)
    ↓
Amazon Kinesis Data Streams (real-time collection, replay capability)
    ↓
Amazon Data Firehose (near real-time delivery to S3)
    ↓ (optional Lambda transform inside Firehose)
S3 Ingestion Bucket (raw data landing zone, Parquet format)
    ↓
Glue Crawler (detects schema, updates Data Catalog)
    ↓
Amazon Athena (serverless SQL queries on ingestion bucket)
    ↓
S3 Reporting Bucket (query results stored here)
    ↓
Amazon QuickSight (interactive dashboards from reporting bucket)
    ↓
Amazon Redshift Serverless (complex BI queries needing indexes and joins)

No servers anywhere in this pipeline. IoT Core harvests device data. Kinesis collects in real time with replay. Firehose batches and delivers to S3. Glue keeps the catalog current. Athena queries without loading data. QuickSight builds dashboards. Redshift handles the heavy analytical queries that need optimised column storage.

Hands-on Lab — Query S3 with Athena and Create a Glue Crawler

Step 1 — Create a results bucket for Athena

◈ DIAGRAM

S3 → Create bucket
Name: devops-athena-results-youraccountid
Region: ap-south-1    Create bucket
 
Athena → Settings → Manage
Query result location: s3://devops-athena-results-youraccountid/
Save

Step 2 — Upload sample CSV data

TEXT

Create a file orders.csv on your machine:

TEXT

order_id,user_id,amount,city,status
ORD-001,user-101,450.00,Mumbai,delivered
ORD-002,user-102,1200.00,Pune,pending
ORD-003,user-101,89.00,Mumbai,delivered
ORD-004,user-103,2500.00,Bangalore,delivered
ORD-005,user-102,340.00,Pune,cancelled

◈ DIAGRAM

S3 → Create bucket → devops-raw-orders-yourname
Upload orders.csv to: devops-raw-orders-yourname/orders/orders.csv

Step 3 — Create database and table in Athena

◈ DIAGRAM

Athena → Query editor → paste and run:

SQL

CREATE DATABASE devops_analytics;
 
CREATE EXTERNAL TABLE devops_analytics.orders (
  order_id STRING,
  user_id  STRING,
  amount   DOUBLE,
  city     STRING,
  status   STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://devops-raw-orders-yourname/orders/'
TBLPROPERTIES ('skip.header.line.count'='1');

Step 4 — Run queries

◈ DIAGRAM

Athena → Query editor → run each:

SQL

-- Total orders by city
SELECT city, COUNT(*) as orders, SUM(amount) as revenue
FROM devops_analytics.orders
GROUP BY city ORDER BY revenue DESC;
 
-- Only delivered orders
SELECT * FROM devops_analytics.orders WHERE status = 'delivered';

TEXT

After each query: check Data scanned at the bottom.
This is what you are billed for — $5.00 per TB scanned.

Step 5 — Create a Glue Crawler

◈ DIAGRAM

AWS Glue → Crawlers → Create crawler
Name: devops-orders-crawler
Data source: S3 → s3://devops-raw-orders-yourname/orders/
IAM role: Create new role
Target database: devops_analytics
Schedule: On demand
Create crawler
 
Glue → Crawlers → devops-orders-crawler → Run crawler
Status: Running → Succeeded (takes 1-2 minutes)
 
Glue → Tables → orders table now appears with auto-detected schema.
Athena can query this table immediately without manual CREATE TABLE.

Step 6 — Cleanup

◈ DIAGRAM

Athena → run: DROP TABLE devops_analytics.orders; DROP DATABASE devops_analytics;
AWS Glue → Crawlers → devops-orders-crawler → Delete
S3 → empty and delete both buckets

Production Best Practices and Common Pitfalls

Always convert to Parquet before running Athena at scale — the 90% cost reduction is the highest ROI change you can make to any Athena workload
Partition your S3 data from day one — retrofitting partitions after data is already in S3 is painful and requires rewriting all existing data
Set up Glue Crawlers on a schedule — without them, new data arriving in S3 is invisible to Athena until the table is manually updated
Enable Job Bookmarks on every Glue ETL job — without them, re-running a job reprocesses all historical data, wasting time and money
Keep Athena result files in a separate dedicated bucket — never mix query results with source data
Use Glue DataBrew for one-off data cleaning tasks — it is far faster than writing a custom Spark script for data quality work
Use Lake Formation when multiple teams need different visibility into the same data lake — without it you are writing complex IAM policies per service per team

Quick Reference and Troubleshooting Commands

Task	Command
List Glue databases	`aws glue get-databases --region ap-south-1`
List Glue tables	`aws glue get-tables --database-name <db> --region ap-south-1`
Start Glue crawler	`aws glue start-crawler --name <name> --region ap-south-1`
Check crawler status	`aws glue get-crawler --name <name> --query 'Crawler.State' --region ap-south-1`
Start Glue job	`aws glue start-job-run --job-name <name> --region ap-south-1`
List job runs	`aws glue get-job-runs --job-name <name> --region ap-south-1`
List Athena query executions	`aws athena list-query-executions --region ap-south-1`
Get query results	`aws athena get-query-results --query-execution-id <id> --region ap-south-1`

Common problems and fixes:

Problem	Likely cause	Fix
Athena query scanning too much data	Using CSV instead of Parquet, no partitioning	Convert to Parquet with Glue, add S3 prefix partitioning
Athena table shows no data after adding files	Partitions not loaded	Run MSCK REPAIR TABLE or add partitions with ALTER TABLE ADD PARTITION
Glue crawler not detecting schema changes	Crawler not re-run after file format changed	Run crawler manually or adjust schedule frequency
Glue job reprocessing all data on re-run	Job Bookmarks not enabled	Enable bookmarks with --job-bookmark-option job-bookmark-enable
Athena result files mixing with source data	Wrong S3 result location configured	Set result location to a dedicated separate bucket

Common Mistake
Running Athena on CSV files in production at scale. A 1 TB CSV file where you only need one column still costs $5.00 and takes minutes. The same data in Parquet costs $0.50 and completes in seconds. Converting to Parquet is not an optimisation — it is a requirement for Athena at any meaningful scale.

Common Mistake
Skipping the Glue Data Catalog and manually creating Athena tables with CREATE EXTERNAL TABLE. When the underlying data schema changes, you must manually update the table definition. With a Glue Crawler on a schedule, schema changes are detected and the catalog updated automatically. Start with Crawlers from day one.

Tip
For debugging slow or expensive Athena queries, check the query execution plan using EXPLAIN. The most common culprits are: full table scan (no partition filter), scanning too many columns (use columnar format), and too many small files (merge them into files above 128 MB). Fixing any one of these typically reduces both cost and runtime by 50-90%.

Common Mistakes to Avoid

Common Mistake
Running Athena on CSV files at scale. A 1 TB CSV file where you only need one column still costs $5.00 and takes minutes. The same data in Parquet costs $0.50 and completes in seconds. Convert to Parquet with Glue — it is not optional at scale.

Common Mistake
Skipping partitioning and wondering why every query scans the full dataset. Add year=/month=/day= prefixes to your S3 paths from day one. Retrofitting partitions after data is already stored requires rewriting everything.

Tip
Always create an Athena result bucket separate from your data bucket. Mixing query results with source data makes data management and cost tracking much harder over time.