Overview

This project focuses on designing and implementing a comprehensive data pipeline and analytics platform for a simulated metro system in Prague. The core objective is to integrate and process multiple evolving data sources—from metro ridership and station information to rider feedback and sensor data—into a centralized data lake and warehouse. These datasets will be used to build clear, actionable dashboards that empower metro operations and city planners to make informed, data-driven decisions. Throughout the semester, the project grows in complexity as it incorporates new data types and addresses progressively deeper business questions related to ridership patterns, fare group behavior, train performance, and system efficiency. The technical stack includes batch data processing using Airflow orchestrated pipelines, PostgreSQL for metadata management, and Grafana for dashboard visualization, all maintained under strict version control and robust infrastructure.

Skills: PostgreSQL, SQL, pandas, MongoDB, psycopg2, MinIO, Grafana, Apache Airflow

Data Process

Data Ingestion & Access

The project involves accessing continuously updated relational databases containing core metro data such as stations, routes, passengers, and tap-in/tap-out events. Access is secured through SSH tunneling and an infrastructure setup that facilitates smooth data extraction. Daily snapshots are captured to enable batch processing, ensuring the data remains current without relying on real-time updates.

Data Transformation & Integration

Airflow DAGs orchestrate the extraction, cleaning, and transformation of raw data into a unified and coherent data lake and warehouse. Evolving data dimensions, including changing rider fare types and the addition of new stations, are managed carefully to maintain data integrity. The data is enriched by establishing connections between related entities such as rides, routes, and fare groups, supporting multidimensional analysis.

Data Analysis & Visualization

Polished, user-friendly dashboards in Grafana provide answers to key business questions concerning ridership demand, travel times, and demographic usage patterns. These dashboards offer clear insights to guide operational decisions like train allocation, schedule adjustments, and targeted service improvements. Ongoing maintenance ensures that dashboard accuracy and functionality remain intact as new data and business questions develop over time.

Results

This project deepened my practical understanding of data engineering and analytics at scale. I gained hands-on experience designing modular, scalable data pipelines using industry-standard tools like Apache Airflow, PostgreSQL, and MinIO. I learned how to structure and orchestrate batch workflows that accommodate evolving schemas and dependencies, ensuring our data warehouse remains accurate and reliable over time. By building pipelines that adapt to incoming changes—such as new fare groups or added stations—I developed a stronger appreciation for maintaining data integrity in a dynamic environment. From a collaboration perspective, I learned the importance of version control, structured team communication, and clear division of responsibilities. As the dataset evolved and the complexity of our business questions increased, our team had to remain agile, frequently refining DAG logic, SQL queries, and dashboard parameters. This taught me to think not just like a data engineer or analyst, but like a systems architect—considering long-term scalability, maintainability, and usability. Ultimately, this project helped bridge the gap between technical implementation and stakeholder impact, reinforcing the importance of transforming raw data into actionable insights for real-world decision-making.