← Back to blog AWS • DATA ENGINEERING

Building an ETL Pipeline with AWS Glue & Athena

September 2, 2026 · 8 min read

Building an analytical data pipeline does not require starting with a large data platform. A practical approach is to begin with application data, store it in Amazon S3, transform it using AWS Glue, catalog the datasets, and query the results using Amazon Athena.

This architecture provides a simple foundation for learning modern cloud-based data engineering while also introducing concepts that are commonly used in production systems.

1. The application and source database

Consider a movie recommendation application that contains transactional data such as users, movies, carts, orders, and wishlists.

The application database is designed primarily for transactional operations. Users can browse movies, add them to a cart, place orders, and maintain wishlists.

As analytical requirements grow, running large analytical queries directly against the transactional database can become undesirable. A separate analytical pipeline provides better isolation between application workloads and reporting workloads.

2. Why separate analytics from transactions?

A transactional database is optimized for application operations such as inserts, updates, and small lookups. Analytical workloads are different. They often scan large amounts of data, join multiple datasets, and calculate aggregations.

Instead of making the application database responsible for both workloads, the analytical pipeline creates a separate data layer.

3. Store raw data in Amazon S3

Amazon S3 provides the storage layer for the analytical pipeline. The original source data can be stored in a raw zone before any major transformation is performed.

Keeping a raw copy is useful because transformations can be reproduced without repeatedly querying the transactional database.

A simple S3 structure can separate raw and processed datasets:

s3://movie-analytics/
│
├── raw/
│   ├── users/
│   ├── movies/
│   ├── orders/
│   ├── order_movies/
│   ├── cart/
│   └── wishlist/
│
└── processed/
    └── movie_sales/

4. Catalog the datasets with AWS Glue Data Catalog

Files stored in S3 are not automatically available as tables in Athena. Athena needs metadata describing the datasets, including their schema, location, and file format.

AWS Glue Data Catalog provides this metadata layer.

AWS Glue Crawlers can inspect datasets in S3, identify their structure, and create tables in the Glue Data Catalog.

5. AWS Glue ETL

Once the raw datasets are available, AWS Glue can perform the ETL operations required for analytics.

The Glue job can read multiple datasets, join related tables, clean columns, rename fields, calculate derived values, and write the processed result back to S3.

6. Building a movie sales dataset

One useful analytical dataset is a movie sales fact table. It can combine order information with movie information to create a dataset suitable for reporting.

For example, an order-movie relationship can be joined with movie information to produce fields such as:

user_id
order_id
order_movie_id
movie_id
movie_title
genre
price
revenue

The revenue field can be derived from the transactional information. For example:

revenue = quantity × price

7. Joining multiple datasets in Glue

The movie analytics pipeline requires data from multiple related tables. For example, an order contains customer information while an order-movie record identifies the movie associated with the order.

The logical flow can look like this:

8. Handling overlapping column names

When multiple datasets are joined, common column names can create ambiguity. For example, several tables may contain fields such as id, user_id, or movie_id.

AWS Glue Studio can warn about overlapping fields when multiple parent nodes contain columns with the same names.

A practical solution is to explicitly select and rename the fields required by the analytical dataset.

SELECT
    user_id,
    order_id,
    order_movie_id,
    movie_id,
    movie_title,
    genre,
    price,
    price AS revenue
FROM myDataSource;

Explicit column selection also makes the final dataset easier to understand and prevents unnecessary fields from flowing through the pipeline.

9. Troubleshooting Glue SQL transformations

During development, Spark SQL transformations can fail when the expected schema is not available to the SQL transformation.

An error such as:

UNRESOLVED_COLUMN
column `user_id` cannot be resolved

usually indicates that the column is not available in the input node under the name being referenced.

Common causes include incorrect node connections, duplicate field names, schema mismatches, or referencing a field using a name that does not exist in the current transformation input.

The solution is to inspect the schema entering the transformation and explicitly map or select the required columns.

10. Store processed data in S3

After transformation, the Glue job writes the analytical dataset back to Amazon S3.

The processed layer contains data that is ready for analytical queries. Columnar formats such as Parquet are useful because they can reduce the amount of data scanned by analytical queries.

11. Query the processed data with Athena

Amazon Athena provides serverless SQL querying over data stored in S3. There is no database server to provision for the query layer.

Once the processed dataset is registered in the Glue Data Catalog, Athena can query it using standard SQL.

12. Example Athena queries

Once the processed movie dataset is available, analytical questions can be answered directly using SQL.

Find highly rated movies

SELECT *
FROM "movie_analytics"."processed_movie"
WHERE imdb_rating > 8.5
LIMIT 10;

Calculate total revenue

SELECT SUM(revenue) AS total_revenue
FROM "movie_analytics"."movie_sales";

Revenue by movie

SELECT
    movie_title,
    SUM(revenue) AS total_revenue
FROM "movie_analytics"."movie_sales"
GROUP BY movie_title
ORDER BY total_revenue DESC;

Revenue by genre

SELECT
    genre,
    SUM(revenue) AS total_revenue
FROM "movie_analytics"."movie_sales"
GROUP BY genre
ORDER BY total_revenue DESC;

13. Incremental ETL with checkpoints

Processing the complete dataset every time a Glue job runs is inefficient. As the source database grows, the pipeline should process only new or changed records.

A checkpoint table can maintain the last successfully processed identifier or timestamp.

For example, a checkpoint can contain:

table_name
last_processed_id
updated_at

If the previous successful run processed records up to ID 5000, the next run can begin from records greater than 5000 instead of scanning the entire table again.

14. The complete pipeline

Combining the components results in a simple cloud-based analytical architecture:

15. From raw data to analytical facts

As the pipeline matures, the processed data can be organized into fact and dimension tables.

For the movie analytics use case, a possible model is:

This structure makes analytical queries easier to maintain and provides a foundation for dashboards and reporting systems.

16. What was learned from the implementation

The pipeline demonstrates several important AWS data engineering concepts:

• Separating transactional and analytical workloads
• Using Amazon S3 as an analytical storage layer
• Using Glue Crawlers to discover schemas
• Using Glue Data Catalog as a metadata layer
• Building ETL jobs with AWS Glue
• Joining multiple datasets in Glue
• Handling overlapping column names
• Debugging Spark SQL schema errors
• Writing processed datasets to S3
• Querying S3 data with Amazon Athena
• Implementing checkpoint-based incremental processing

17. What comes next?

The basic pipeline provides the foundation for a more production-oriented architecture. The next stages can introduce:

• S3 partitioning by date
• Incremental Glue processing
• Data quality validation
• Glue job monitoring
• AWS Lambda-based job triggering
• Workflow orchestration
• Fact and dimension tables
• Athena performance optimization
• Dashboards and visualization
• Movie recommendation analytics

Conclusion

AWS Glue and Athena provide a relatively simple way to build a cloud analytics pipeline without managing a dedicated analytics server.

The architecture separates application transactions from analytical processing, stores raw and processed data in S3, uses Glue for metadata and ETL, and provides SQL-based analytics through Athena.

Starting with a small movie analytics use case makes it possible to understand each component individually before progressing toward a production-grade data platform.

← Explore more articles