Building ShopFlow: A Production-Style E-Commerce ETL Pipeline with Python, Pandas, PostgreSQL, and REST APIs
When I started learning Data Engineering, I thought building an ETL pipeline was a relatively straightforward process. Extract data from a source, clean it, and load it into a database. Once those three steps worked, the pipeline was complete.
That understanding changed when I started building ShopFlow, my production-style e-commerce ETL project.
The objective was simple: retrieve e-commerce data from REST APIs, transform it into structured information, and load it into PostgreSQL for analytical querying.
But as I designed the workflow, I realized that a successful API response was only the beginning. A reliable pipeline also needed to handle pagination, HTTP failures, unexpected responses, inconsistent data, duplicate records, and empty datasets.
This project became an opportunity to move beyond writing scripts that work under ideal conditions and start thinking about how data pipelines should behave when things go wrong.
The First Challenge: Getting Data Is Not the Same as Building an Ingestion Pipeline
The first stage of ShopFlow involved retrieving JSON records from REST APIs using Python's Requests library.
Initially, the extraction process appeared simple. Send a request, receive a response, parse the JSON, and continue processing.
However, real-world APIs introduce additional challenges. Data may be distributed across multiple pages, requests can fail temporarily, and a successful HTTP response does not always guarantee that the returned data has the expected structure.
I implemented paginated API ingestion to handle records distributed across multiple responses.
Instead of treating each API response as an isolated operation, I approached extraction as a workflow that needed to retrieve data consistently before passing it to the transformation stage.
This introduced one of my first important lessons: a data pipeline should not assume that the source will always behave exactly as expected.
The Second Challenge: What Happens When an API Request Fails?
One of the most interesting parts of ShopFlow was designing its error-handling behavior.
A basic ingestion script might stop immediately when an HTTP request fails. But some failures are temporary, and retrying the request may allow the pipeline to recover.
To address this, I implemented HTTP status checks, exception handling, and configurable retry logic.
The pipeline uses a default configuration of three retry attempts and handles five retryable HTTP status codes.
The important design decision was recognizing that not every failure should be treated identically.
A temporary server-side problem may justify another attempt, while a malformed response requires a different handling approach.
This made me think more carefully about the distinction between a script that executes successfully once and a workflow designed to handle predictable failure conditions.
Reliability is not about assuming that failures will never happen. It is about deciding how the system should respond when they do.
The Third Challenge: Raw JSON Is Not Analytics-Ready Data
After extracting the records, the next challenge was transforming them into a consistent format.
API responses are designed for exchanging information, but analytical databases require structured, predictable data.
I used Python and Pandas to build the transformation stage, focusing on missing-value handling, type normalization, and data cleaning.
I also included deduplication logic to address repeated records.
These transformations helped prepare the extracted information for database loading.
This stage reinforced another important principle: data quality should be addressed before records become part of the analytical layer.
Without consistent data types and appropriate validation, even a correctly executed SQL query can produce misleading results.
The transformation stage therefore became more than a cleaning step. It was an essential part of ensuring that the downstream data could be analyzed meaningfully.
The Fourth Challenge: Why I Chose a Star Schema
Once the data was prepared, I needed to decide how to store it in PostgreSQL.
One option was to load everything into a single large table. That might have been simpler initially, but I wanted to explore how analytical databases organize information.
I chose a star-schema design consisting of three dimension tables and one fact table.
The fact table provides the central structure for measurable transactional information, while the dimension tables provide descriptive context for analysis.
This approach introduced me to dimensional modeling and helped me understand why database design matters in Data Engineering.
Rather than storing data only for retrieval, I wanted the database to support analytical queries in a structured way.
The experience showed me that the loading stage is not simply about inserting records. It also involves deciding how information should be organized for its intended use.
The Fifth Challenge: How Do I Know the Pipeline Actually Works?
At this point, ShopFlow had an ingestion workflow, transformation logic, and a PostgreSQL analytical model.
But there was still an important question.
How could I verify that the pipeline behaved correctly under different conditions?
A successful execution with one dataset was not enough to answer that.
I developed five unit test cases covering important parts of the workflow:
- API pagination.
- Malformed API responses.
- Data cleaning.
- Deduplication.
- Empty datasets.
These tests helped me evaluate behavior beyond the normal execution path.
Testing an empty dataset, for example, is useful because a pipeline should have a defined response when no records are available.
Similarly, testing malformed responses helps identify assumptions about the structure of incoming data.
This was one of the most valuable aspects of the project because it encouraged me to consider failure scenarios while designing the workflow rather than only after encountering problems.
The Final Stage: Turning the Pipeline into an Analytical Foundation
The final objective was not merely to transfer information from an API into PostgreSQL.
I wanted the resulting database to support analytical use cases.
To demonstrate this, I authored three SQL analytical queries using the structured data model.
This completed the connection between ingestion, transformation, storage, and analysis.
The overall workflow followed a clear sequence:
REST API → Paginated Ingestion → Error Handling and Retries → Pandas Transformation → PostgreSQL Star Schema → SQL Analytics
Each component had a specific responsibility, and each introduced different engineering considerations.
Looking at the complete workflow helped me understand how individual Python functions, database tables, and SQL queries contribute to a larger data system.
Why I Call ShopFlow Production-Style, Not Production-Grade
One decision I made while presenting this project was to describe it as production-style rather than production-grade.

ShopFlow incorporates several engineering practices commonly associated with reliable data pipelines, including retry handling, exception management, validation, deduplication, unit testing, and dimensional modeling.
However, implementing these practices in a project is not the same as operating a pipeline in a live production environment.
A production deployment would require additional consideration of monitoring, orchestration, operational recovery, security, and performance under real workloads.
I believe making this distinction is important because engineering maturity also involves understanding the limitations of what has been built.
What ShopFlow Taught Me About Data Engineering
Before building ShopFlow, I primarily understood ETL as three operations: extract, transform, and load.
After working on the project, I began to see ETL as a connected set of engineering decisions.
Extraction requires more than sending HTTP requests. Transformation requires more than removing missing values. Loading requires more than inserting rows. Testing requires more than checking whether the program runs.
Every stage contributes to the reliability and usefulness of the final data.
The project strengthened my practical understanding of Python, Pandas, REST API integration, PostgreSQL, dimensional modeling, SQL analytics, and software testing.
More importantly, it changed the way I approach technical projects.
Instead of asking only, "Does this code work?", I now ask, "What assumptions does this code make, and what happens when those assumptions are not satisfied?"
That shift in thinking has been one of the most valuable outcomes of building ShopFlow.
Final Thoughts
ShopFlow started as an e-commerce ETL project, but it became a practical exercise in designing a more dependable data workflow.
The biggest lesson I took away is that a working pipeline moves data, while a thoughtfully engineered pipeline also considers data quality, failure handling, structure, and verification.
I am continuing to develop my skills in Data Engineering by building projects that combine practical implementation with stronger engineering fundamentals.
If you have worked on ETL pipelines, I would be interested to hear your perspective:
What was the first unexpected challenge that changed how you approached Data Engineering?





