How I Built a Data Pipeline From Scratch Using Python

Building a data pipeline from scratch can seem daunting, especially when the data comes from multiple sources in various formats and you have a stakeholder meeting the next morning. However, with the right Python development approach, it becomes a series of manageable, testable decisions. A data pipeline is essentially a sequence of steps that extract data from sources, transform it into a usable format, and load it into a destination such as a database, data warehouse, or dashboard.

The classic pattern is Extract → Transform → Load (ETL). While there are off-the-shelf tools like Airbyte, Fivetran, or AWS Glue that handle much of this, they often come with trade-offs like high cost at scale, limited customization, vendor lock-in, and the constant gap between the tool's capabilities and your actual data.

When you build your own pipeline in Python, you have complete control over every decision and gain a deep understanding of your data flow end-to-end. This is valuable not just for cost savings but also for ensuring your data is handled exactly as needed.

Initially, I created a single Python script that handled everything: connecting to the API, parsing JSON, running transformations, and inserting data into PostgreSQL. However, this approach had its flaws. When the API started rate-limiting me, a schema change broke the parser, and the entire system failed silently in the middle of the night. This taught me that a pipeline is not just a script but a system. The architecture I adopted after several iterations consists of several isolated stages:

1. **Source(s) → Extractor Module**: The extractor's sole responsibility is to pull data and save it as-is, without any transformations. For REST API sources, I utilized the `requests` library with built-in retry logic to handle rate limits effectively. The function `create_session_with_retries()` establishes a session with retry logic, and `extract_from_api()` utilizes this session to make GET requests to the API and return the JSON response.

2. **Raw Storage**: Before any transformation occurs, the raw data is serialized to disk or stored in a staging layer. During development, I used local JSON files and later moved to S3-compatible object storage in production. The function `save_raw()` saves the raw data in JSON format with a timestamped filename, ensuring an audit trail that allows you to replay data from the raw source if needed.

3. **Transformation**: This is where most of the Python development happens. Transformations include cleaning null values, standardizing date formats, type casting, deduplication, applying business logic, and joining across sources. I used pandas for most transformations, and pyarrow for handling larger datasets where memory constraints are a concern. A typical transformer function might standardize column names, convert types, drop duplicates, filter out certain records, and handle null values with intent.

4. **Validation**: Validation is a critical step that ensures bad data does not land in your production database. I used pandera, a library for defining and enforcing data schemas, to validate the transformed data. This step catches any issues before the data is loaded into the destination system.

By separating these stages, each failure is contained within its stage, preserving the raw data and preventing the need to re-extract everything from the source. This separation of concerns makes debugging manageable and allows for easier maintenance and updates to the pipeline.