# data project  
## Purpose  
This project focuses on exploring a collection of datasets gathered from multiple sources. The goal is to construct a streamlined data pipeline that simplifies the process of transforming, analyzing, and storing the information.

A detailed breakdown of the methodology, tools, and implementation steps can be found in the Capstone project template, which serves as a guide for the overall design and logic behind this project.

Within the `data analysis` directory, you’ll find visual reports created in Power BI based on the final curated dataset.

The `etl.py` script is responsible for automating the ETL workflow and pushing the processed data to AWS S3.

## Tech Overview  
This solution integrates several data engineering tools and platforms, including:

- **Apache Spark** – utilized for its powerful data processing engine capable of handling large datasets efficiently with support for batch and stream processing
- **Pandas** – chosen for its flexibility and powerful data manipulation features
- **Matplotlib** – used to generate insightful visualizations from the transformed data
- **AWS S3** – selected as the destination for storing the final tables due to its scalability and ease of integration with analytics platforms

## Datasets Used  
The project integrates multiple datasets to form a unified analytical structure:

- **i94 Immigration Data** – Historical immigration information from the U.S. National Tourism and Trade Office. This dataset functions as the main fact table. Source: https://travel.trade.gov/research/reports/i94/historical/2016.html  
- **Global Temperature Data** – Long-term weather data spanning multiple cities from 1700 to 2013. This serves as a reference point for understanding climate trends. Source: https://www.kaggle.com/berkeleyearth/climate-change-earth-surface-temperature-data  
- **U.S. Demographics** – Provides statistics about city populations, including metrics like household size and racial distribution. Source: https://public.opendatasoft.com/explore/dataset/us-cities-demographics/export/  
- **Airport Codes** – Contains mappings of airport codes to locations, used for linking travel data with cities. Source: https://datahub.io/core/airport-codes#data  

## Data Architecture  
To effectively model the data, a star schema approach was implemented. This allowed for optimized analytical querying and logical separation between facts and dimensions.

![AltText](images/tables_schema.png)

The general process followed includes:

- Reading and cleaning the source datasets
- Creating staging tables to hold preprocessed data
- Transforming and loading data into fact and dimension tables within a star schema
- Saving results in Parquet format and storing them in an S3 bucket

For a visual walkthrough of this pipeline, refer to the diagram below:

![AltText](images/ETL_DIAGRAM1.png)

## Insights & Visualizations  
Power BI was used to perform exploratory analysis on the processed data. Using S3 as a data source, various dashboards and interactive visuals were created to uncover patterns and answer key business questions.

Examples include:

![AltText](images/map.gif)

![AltText](images/Animation.gif)

## Automation  
The `etl.py` script is designed to manage the ETL process end-to-end. It's particularly useful for recurring data updates, making the system easy to maintain and extend over time.
