Made by Dimensional Transformers
From Data to Discovery: Shaping the Future with Engineering
Our project automates the ETL workflow for Terrific Totes, leveraging Python, AWS, database modeling, and CI/CD practices. It extracts data from a PostgreSQL database on AWS RDS, transforms it with Python Lambda functions, and loads it into a PostgreSQL data warehouse. With secure credential management, CloudWatch monitoring, and Terraform and GitHub Actions automation, this scalable solution ensures quality through rigorous testing and PEP8 compliance, delivering actionable insights via a Tableau dashboard.
The Team
David Sheffield
No bio provided
Dorota Sawicki
No bio provided
Hamza Nazar
No bio provided
Laura Pugsley
No bio provided
Rohail Zaheer
No bio provided
William Robb
No bio provided
Tech Stack
We used: Python, AWS, Postgres, Terraform, GitHub Actions, Parquet, Tableau, Pytest Terraform: Used for managing and deploying infrastructure. Python: Employed for programmatically interacting with and manipulating data. SQL: Utilized due to the database’s structure and requirements. Parquet: Chosen for its efficient data storage capabilities. GitHub Actions: Implemented to automate workflows and minimize manual intervention throughout the project. AWS: Provided the hosting environment for all cloud infrastructure. Pytest: For test driven development. Tableau: For effiecient data visualisation.
Challenges Faced
Statelessness of Lambda Functions: Lambda functions are stateless, so we needed a way to track the last updated date for data extraction. We solved this by storing the date in AWS Secrets Manager, allowing us to persist the value between invocations. On the first run, we reset the date to process all source data. Fact Table Load Order: We encountered an issue where the fact table was loading before the dimension tables, causing reference errors. To fix this, we implemented a sleep mechanism after loading the dimension tables, ensuring they were populated first and maintaining data integrity. Lambda Layer Dependency Issues: We faced challenges with Lambda layer compatibility for PyArrow and SQLAlchemy due to package size. For PyArrow, we used an AWS Managed Layer. For SQLAlchemy, we created a custom GitHub library to package and deploy the specific version as a Lambda layer, resolving the compatibility issues. GitHub: https://github.com/hamza8599/nc-final-project
Student Projects – Dimensional Transformers Data Project
A project made by Team Dimensional Transformers – From Data to Discovery: Shaping the Future with Engineering
Student Projects – Cloudy With A Chance of Terraform Data Project
A project made by Team Cloudy With A Chance of Terraform – Data journey – from raw to ready!
Student Projects – Baltica 2 Data Project
A project created by Team Baltica – Collaborative and Creative
Student Projects – Trippy
A project created by Team SouthCoders – Addiction to Travelling
Student Project – Trail Tales
A project created by Project Trail Tales – A community biased geo-posting app, to document and tag wildlife seen along a users journey through life
Student Projects – Time Treasures
A project made by Team Productivity Pirates – Organise your inner pirate
Student Projects – Pix Pursuit
A project made by Team Fantastic Phor – Picture hunting mobile app
Student Projects – MusiCAL
A project made by Team MusiCAL – Discover events that match your soundtrack
Student Projects – Mind My Plants
A project made by Team Mind my Plants – Leaf it to the professionals
Student Projects – Late Plate
A project made by Team arcadia-synergistic-cloud-based-ai-driven-data-analytic-cybersecurity-solutions – Book a restaurant quick!
Student Projects – InkWell
A project made by Team Project Ribs and Wings – Slow dms fast pen-pals
Student Projects – HisTOURy
A project made by Team splice-girls – Plan your own history tour!