kxmaie/Egypt-Travel-Weather-Data-Pipeline

An automated End-to-End Data Pipeline that integrates Weather APIs, TomTom POIs, and hospitality datasets (Hotels/Restaurants) into Snowflake using Airflow. Implementing a Medallion Architecture (Bronze-Silver-Gold) with SQL Server as a source and Docker for orchestration and dbt for transformation # Weather & hospitality data pipeline End-to-end ELT built on **Astronomer Airflow**: ingest APIs and CSV files into **Microsoft SQL Server** (bronze/silver), load into **Snowflake**, then model with **dbt** (staging in `silver_layer`). ## Architecture ```text Open-Meteo (weather) ──┐ TomTom (landmarks) ───┼──► SQL Server (Bronze → Silver) ──► Snowflake RAW_DATA ──► dbt staging ──► dbt marts (gold) CSV (hotels / restaurants) ─┘ ``` Airflow DAGs (scheduled every 20 minutes): 1. **`ingestion_dag`** — weather, landmarks, restaurants CSV, hotels CSV 2. **`transformation_dag`** — builds silver tables from bronze (Python + pandas), after `ExternalTaskSensor` on ingestion 3. **`loader_dag`** — copies tables to Snowflake with `write_pandas`, after sensor on transformation `ExternalTaskSensor` uses `execution_delta` to align logical dates between DAGs; tune it if you change schedules. ## Repository layout | Path | Role | |------|------| | `dags/` | Airflow DAG definitions | | `include/ingestion/` | API + CSV load into SQL Server | | `include/transformations/` | Bronze → silver transforms | | `include/loaders/` | SQL Server → Snowflake | | `include/dbt_project/` | dbt: `models/staging` (silver), `models/marts` (gold), tests | | `include/*.csv` | Sample static files (paths in DAGs assume Astro `/usr/local/airflow/include/`) | ## Prerequisites - Docker and Astro CLI for local Airflow - Reachable **SQL Server** and **Snowflake** accounts - **dbt** with Snowflake adapter (for running models outside Astro if you want) ## Airflow connections Configure in the Airflow UI or `airflow_settings.yaml` (local): | Connection ID | Type | Purpose | |---------------|------|---------| | `sql_server_conn` | Microsoft SQL Server | Bronze/silver tables | | `conn_snowflake` | Snowflake | `RENTAL_WEATHER_DB.RAW_DATA` loads | **TomTom API key** (landmarks task): set Airflow Variable `TOMTOM_API_KEY` or environment variable `TOMTOM_API_KEY`. For local Astro, use `airflow_settings.yaml` …