Advanced Python Data Engineering
Laboratory Manual
AI & Data – Advanced Python Module
This laboratory manual is designed for students enrolled in the Advanced Python for Data
course. The objective of this lab is to simulate a professional data engineering project similar
to what engineers encounter in real companies.
Students will design and implement a data processing platform capable of ingesting multiple
data formats, validating datasets, transforming them, storing them in PostgreSQL, converting
them into optimized analytical formats, and exposing analytics through a web API.
The final deliverable of this laboratory is a professional GitHub repository containing the full
implementation, automated tests, documentation, and instructions required to reproduce the
environment and execute the system.
The lab follows real engineering practices and introduces students to tools and frameworks
widely used in data engineering environments.
1. Project Overview
Modern companies ingest and process data coming from many sources:
• CSV exports from business tools
• Excel reports from finance departments
• JSON data from APIs
• XML feeds from enterprise systems
• log files generated by applications
The objective of this laboratory is to build a robust ingestion and processing system capable
of handling these heterogeneous formats and transforming them into a clean analytical dataset.
The system will perform the following operations:
1. Detect file formats automatically
2. Parse the data
3. Validate and clean records
4. Store the data in a relational database
5. Convert datasets into analytical formats
6. Expose metrics through an API
The final result should resemble a mini data platform.
2. Development Environment
Students must begin by creating a reproducible Python development environment.
Required tools:
• Python 3.11 or higher
• Git
• pip
• virtual environments (venv)
Students must initialize their project using the following process:
Create a project folder:
mkdir data-platform-lab
cd data-platform-lab
Create a virtual environment:
python -m venv .venv
Activate the environment:
Linux / macOS:
source .venv/bin/activate
Windows:
.venv\Scripts\activate
Upgrade pip:
python -m pip install --upgrade pip
Install required libraries.
3. Required Python Libraries
The project must use several libraries commonly used in data engineering.
Core data libraries:
• pandas
• numpy
File handling:
• openpyxl
• csv (standard library)
• json (standard library)
• [Link]
Data validation:
• pydantic
Database integration:
• psycopg2
• SQLAlchemy
Data serialization:
• pyarrow
• fastparquet
API development:
• fastapi
• uvicorn
Testing:
• pytest
Install dependencies:
pip install pandas numpy openpyxl psycopg2 sqlalchemy pydantic pyarrow fastapi uvicorn
pytest
Freeze dependencies:
pip freeze > [Link]
4. Project Architecture
Students must organize the project using a professional structure.
Example architecture:
data-platform-lab
│
├── src
│ ├── ingestion
│ │ ├── csv_loader.py
│ │ ├── json_loader.py
│ │ ├── xml_loader.py
│ │ └── excel_loader.py
│ │
│ ├── validation
│ │ └── [Link]
│ │
│ ├── database
│ │ └── [Link]
│ │
│ ├── storage
│ │ └── parquet_writer.py
│ │
│ ├── api
│ │ └── [Link]
│ │
│ └── pipeline
│ └── [Link]
│
├── datasets
│
├── tests
│
└── [Link]
Each module should have a clear responsibility.
5. Multi-Format File Processing
Students must implement ingestion modules capable of reading several file formats.
The supported formats are:
• CSV
• Excel
• JSON
• NDJSON
• XML
Example CSV ingestion:
import pandas as pd
df = pd.read_csv("[Link]")
Example JSON ingestion:
import json
with open("[Link]") as f:
data = [Link](f)
Example XML parsing:
import [Link] as ET
tree = [Link]("[Link]")
root = [Link]()
Each ingestion module must return structured data objects such as dictionaries or pandas
DataFrames.
6. Data Validation
Real data pipelines must enforce strict validation rules.
Students must implement a data validation layer using Pydantic.
Example validation model:
from pydantic import BaseModel
class Order(BaseModel):
product: str
quantity: int
price: float
Validation rules should include:
• price must be positive
• quantity must be greater than zero
• product name cannot be empty
• country must belong to a predefined list
Invalid records must be isolated and logged.
7. Database Integration
The pipeline must store processed data inside a PostgreSQL database.
Example schema:
Customers table:
customers
id
name
country
Orders table:
orders
id
customer_id
product
quantity
price
Students must write scripts capable of:
• inserting new records
• avoiding duplicate entries
• performing analytical queries
Example aggregation query:
Total revenue per country.
8. Data Lake Conversion
CSV files are inefficient for large analytical workloads.
Students must convert processed datasets into Parquet format.
Parquet is a column-oriented storage format optimized for analytics.
Example conversion:
df.to_parquet("[Link]")
Students must compare:
• CSV file size
• Parquet file size
• read performance
This experiment demonstrates why Parquet is widely used in modern data pipelines.
9. Analytics API
Students must expose data insights through a FastAPI service.
Example endpoint:
GET /revenue
Returns:
{
"total_revenue": 450000
}
Other endpoints:
GET /revenue/country
GET /top-products
GET /orders
The API must connect to PostgreSQL and return JSON responses.
10. Automated Testing
All modules must be tested using pytest.
Students must create tests for:
• ingestion modules
• validation layer
• database operations
• API endpoints
Example test:
def test_csv_loader():
data = load_csv("[Link]")
assert len(data) > 0
Minimum required coverage: 70%.
11. Logging and Error Handling
The pipeline must implement structured logging.
Example:
import logging
logger = [Link](__name__)
[Link]("Processing file")
Errors such as corrupted files must be handled gracefully.
12. Final Project Deliverable
Students must submit a GitHub repository containing:
• complete source code
• automated tests
• datasets used for experiments
• README documentation
• architecture diagram
The repository must allow a reviewer to reproduce the project by running:
pip install -r [Link]
python [Link]
The project should demonstrate the student's ability to design a clean, scalable, and reliable
data processing system.
13. Evaluation Criteria
Projects will be evaluated according to the following criteria:
Code organization and architecture – 25%
Correct implementation of data ingestion – 20%
Validation and data quality mechanisms – 15%
Database integration – 15%
API implementation – 10%
Testing and documentation – 15%