How do you build an ETL pipeline that transforms data, stores it in Amazon S3, and queries it using Amazon Athena?

An ETL pipeline that transforms data, stores it in S3, and queries it with Athena typically follows these steps: 1) Ingest raw data into an S3 bucket or stream it via Kinesis. 2) Use AWS Glue or EMR to run a Spark job that cleans, normalizes, and enriches the data. 3) Write the transformed data back to S3 in a columnar format such as Parquet or ORC, partitioned by date or other keys for efficient querying. 4) Create an Athena table that points to the S3 location, defining the schema and compression. 5) Run SQL queries in Athena to analyze the data, optionally materializing results into another S3 bucket or exporting to Redshift. 6) Schedule the pipeline using Glue triggers or Step Functions, and monitor with CloudWatch. To handle incremental data, Glue job bookmarks track processed records, ensuring only new data is processed each run. The Glue Data Catalog stores table metadata, making it reusable across Athena and other services. Security is enforced via IAM roles that grant least‑privilege access to S3, Glue, and Athena. Data at rest can be encrypted with SSE‑S3 or SSE‑KMS, and transit encryption is enabled by default. Cost savings come from using Athena’s pay‑per‑query model and storing data in compressed columnar formats, which reduce query time and storage costs. This architecture leverages serverless components, reduces operational overhead, and provides a cost‑effective analytics solution.