How do you design a data pipeline using S3, Glue, and Athena?

A well-designed data pipeline on AWS follows the medallion architecture pattern (bronze/silver/gold zones), with S3 as the storage layer, Glue as the ETL engine and metadata catalog, and Athena as the serverless query engine. Think of it like a water treatment plant: raw water (data) enters the intake (landing zone), goes through filtration stages (ETL transformations), and emerges as clean drinking water (analytics-ready tables).

The S3 layer is organized into three zones. The landing zone (s3://prod-analytics-data-lake-123456789012/landing/) receives raw data in its original format: CSV exports from RDS, JSON events from Kinesis Firehose, or Parquet files from third-party vendors. This zone has a lifecycle policy: move to Glacier Deep Archive after 90 days, delete after 365 days. The processed zone (s3://prod-analytics-data-lake-123456789012/processed/) contains transformed data in Apache Parquet format with Snappy compression, partitioned by date. Parquet is critical because Athena charges per byte scanned, and Parquet's columnar format means querying 5 columns of a 200-column table scans only 2.5% of the data. The curated zone (s3://prod-analytics-data-lake-123456789012/curated/) contains business-specific aggregations ready for dashboarding: daily revenue summaries, user cohort metrics, funnel conversion rates.

Glue Crawlers automatically infer schemas from files in S3 and register them in the Glue Data Catalog. However, in production you should avoid running crawlers on every file change. Instead, use the Glue API to manually register schemas (create_table) because crawlers can infer incorrect data types (e.g., treating ZIP codes as integers, stripping leading zeros). Crawlers are useful for initial discovery but should be replaced with explicit schema definitions for production tables.

Glue ETL jobs run Apache Spark under the hood but with AWS-managed infrastructure. For small datasets (under 1GB), use Python shell jobs (0.0625 DPU, costs roughly $0.44/hour) instead of Spark jobs (minimum 2 DPUs at $0.44/DPU/hour). For larger datasets, use Glue 4.0 with auto-scaling enabled, which adjusts DPU count based on actual workload. A critical optimization: enable job bookmarks, which track which data has already been processed. Without bookmarks, re-running a job reprocesses all historical data, inflating both cost and execution time.

Partitioning strategy determines Athena query performance. Partition by the most common query filter. For event data, partition by year/month/day (s3://bucket/events/year=2024/month=01/day=15/). For multi-tenant data, partition by tenant_id first, then date. Use Hive-style partitions (key=value in the path) so Athena automatically recognizes them. Each partition creates a separate metadata entry in the Glue Data Catalog, so avoid over-partitioning: 100,000+ partitions causes slow query planning. If you need hourly granularity, use Apache Iceberg hidden partitioning instead, which partitions data without polluting the S3 path structure.

Athena pricing is $5 per TB scanned, so query optimization is directly financial optimization. Always use columnar formats (Parquet or ORC), compress with Snappy or ZSTD, partition by the most filtered column, and use LIMIT clauses during development. Create Athena workgroups to enforce per-team query limits: the analytics team gets a 10GB per-query scan limit to prevent runaway queries that scan the entire data lake. Enable Athena query result reuse for repeated queries to avoid re-scanning data.

Orchestration ties everything together. Use Glue Workflows for simple linear pipelines (crawl, transform, validate) or AWS Step Functions for complex DAGs with conditional branching, error handling, and human approval steps. EventBridge rules trigger the pipeline when new files land in the S3 landing zone via s3:ObjectCreated events.

Production gotchas: Glue crawlers do not detect schema evolution (added/removed columns) well; use schema registry with explicit versioning. Athena has a 30-query concurrency limit per account per region (can be increased). S3 strongly consistent reads (since December 2020) eliminated eventual consistency bugs in pipelines, but older documentation still warns about them. Never use CSV for analytical workloads; the format lacks schema enforcement and columnar scan benefits.