Sperrins Syndicate Final Project

present

Terrific Totes is a fictional company that operates an OLTP database and a data warehouse used for reporting and visualisations. The goal of this project was to develop applications that Extract, Transform, and Load (ETL) data from an OLTP database into a data lake and warehouse, hosted in AWS. Our solution is reliable, scalable, and fully managed using Infrastructure-as-Code.

The features of our project include:

**Automated Data Processing**
- EventBridge Scheduler: Triggers data ingestion every 5 minutes.
- Step Machine with JSON Payloads: Orchestrates the workflow.
- Three Lambda Functions (& Layers):
- One Python application ingests all tables from the OLTP database. It then implements incremental refresh so that only new and/or updated data is processed for optimisation of resources.
- Another Python application remodels the data into a predefined schema and stores it in the "processed" S3 bucket in Parquet format.
- A third Python application loads the transformed data into a data warehouse, with new and updated data reflected in the target database within 30 minutes. Fact tables retain a full history of how the data has evolved.
- CloudWatch Monitoring & SNS Alerts: Logs errors, tracks performance, and sends critical failure notifications via SNS.

**Secure Data Management**
- IAM Policies: Implements the principle of least privilege.
- Secrets Manager: Manages database credentials securely.

**Data Storage in S3**
- Raw Data Bucket: Stores ingested data in its original form, acts as a data lake.
- Processed Data Bucket: Holds transformed data in an immutable, well-structured format.

**Code Quality & Security**
- Python code is PEP8 compliant, thoroughly tested (with unit and integration tests), and checked for security vulnerabilities using pip-audit and bandit.