🌦️ Weather ELT and Analytics Project

Welcome to the Weather ELT and Analytics Project repository! 🚀
This project demonstrates a complete end-to-end ELT pipeline that ingests live weather data, stores it in a data warehouse, transforms it using modern data engineering tools, and tracks historical changes using Slowly Changing Dimensions (SCD Type 2).

Designed as a portfolio-grade data engineering project, it showcases best practices in orchestration, transformation, and data modeling.

---

🏗️ Data Architecture

The data architecture for this project follows the Medallion Architecture pattern with Bronze, Silver, and Gold layers:

1. Bronze Layer: Stores raw weather data ingested from the OpenWeather API without modification.
2. Silver Layer: Applies data cleaning, type casting, and JSON flattening using dbt models.
3. Gold Layer: Tracks historical weather changes over time using SCD Type 2 implemented via dbt snapshots.

📖 Project Overview

This project covers the following key areas:

1. Data Ingestion

   • Extracts live weather data from the OpenWeather API
   • Orchestrated using Apache Airflow

2. ELT Pipeline Design

   • Raw data loaded directly into PostgreSQL
   • Transformations handled downstream using dbt

3. Data Transformation

   • Silver layer staging models using dbt views
   • Gold layer historical tracking using dbt snapshots (SCD Type 2)

4. Data Warehousing

   • PostgreSQL used as the analytical data warehouse
   • Schema-based separation for Bronze, Silver, and Gold layers

---

🚀 Project Requirements

Building the Data Warehouse (Data Engineering)

Objective

Build a modern ELT pipeline that ingests live API data, structures it for analytics, and tracks historical changes efficiently.

Specifications

• Data Source: OpenWeather API (live weather data)
• Orchestration: Apache Airflow
• Storage: PostgreSQL Data Warehouse
• Transformation: dbt (models + snapshots)
• Historical Tracking: SCD Type 2 using dbt snapshots
• Containerization: Docker for local reproducibility

---

🧱 Medallion Layers

🟫 Bronze Layer (Raw)

• Stores raw JSON API responses
• One row per API call
• No transformations applied
• Table: raw.weather_api

🥈 Silver Layer (Cleaned / Staging)

• JSON flattening
• Type casting & data standardization
• One row per API call
• dbt view
• Model: silver.stg_weather

🥇 Gold Layer (Historical / Analytics)

• Slowly Changing Dimension (SCD Type 2)
• Tracks weather changes over time
• Maintains current and historical records
• Implemented using dbt snapshots
• Table: gold.weather_scd

---

🔄 ELT Pipeline Flow

flowchart LR
    API[OpenWeather API]
    PY[Python Extract]
    AF[Apache Airflow]
    PG[(PostgreSQL Data Warehouse)]

    API --> PY
    PY --> AF
    AF --> PG

    PG --> B[Bronze<br/>raw.weather_api]
    B -->|dbt model| S[Silver<br/>stg_weather]
    S -->|dbt snapshot<br/>SCD Type 2| G[Gold<br/>weather_scd]

Loading

---

📂 Project Structure

WEATHER_ELT_CLEAN/
│
├── dags/
│   └── bronze/
│       └── extract_raw_data.py
│
├── weather_dbt/
│   ├── models/
│   │   ├── silver/
│   │   │   └── stg_weather.sql
│   │   └── gold/
│   │       └── (future marts / facts)
│   │
│   ├── snapshots/
│   │   └── weather_scd.sql
│   │
│   ├── dbt_project.yml
│   └── README.md
│
├── Dockerfile
├── airflow_settings.yaml
├── requirements.txt
├── packages.txt
└── README.md

---

⚙️ How to Run the Project

Follow the steps below to run the full Weather ELT pipeline locally.

1️⃣ Start all services using Docker:

docker compose up -d

Wait until all containers are up and running.

2️⃣ Trigger Airflow DAG (Data Ingestion):

• Open the Airflow Web UI: http://localhost:8080

• Locate and trigger the DAG: openweather_raw_dag

This step ingests data from the OpenWeather API into PostgreSQL (Bronze layer).

3️⃣ Run dbt models (Silver layer):

dbt run

This step cleans and transforms raw data and creates: silver.stg_weather

4️⃣ Run dbt snapshots (Gold layer – SCD Type 2):

dbt snapshot

This step tracks historical changes and creates: gold.weather_scd

After completing these steps:

• Bronze contains raw weather data
• Silver contains cleaned and structured data
• Gold contains historical data with SCD Type 2 tracking

The Weather ELT pipeline is now fully operational.

This structure separates ingestion, transformation, and historical tracking logic clearly.

---

🧠 Key Concepts Used

• ELT Architecture (Extract, Load, Transform)
• Apache Airflow for orchestration
• PostgreSQL as a data warehouse
• dbt models for data transformation
• dbt snapshots for SCD Type 2
• Medallion Architecture (Bronze / Silver / Gold)
• Dockerized local environment

---

✅ Data Layers Explained

• Bronze Layer
  Raw weather data ingested from the API without modification.

• Silver Layer
  Cleaned and structured data created using dbt models.

• Gold Layer
  Historical data with full change tracking using SCD Type 2 snapshots.

---

🚀 Future Improvements

• Add support for multiple cities
• Trigger dbt jobs directly from Airflow
• Add data quality and freshness checks
• Build dashboards using a BI tool (Metabase / Power BI)
• Add CI pipeline for dbt tests

---

🛡️ License

This project is licensed under the MIT License. You are free to use, modify, and share this project with proper attribution.

🌟 About Me

Hi there! I'm Ziad Ashraf Ibrahim,I’m software instructor with 2+ years of experience teaching Python, Java, data engineering, Arduino, and robotics. Skilled in simplifying complex concepts for kids and preparing students for competitions like MakeX, Robofest, and Sumo robotics. Experienced in IoT and data engineering projects such as EcoFarm, integrating IoT sensors, data pipelines, and cloud computing. Strong background in data engineering practices including ETL, SQL, data modeling, and pipeline development.!

Let's stay in touch! Feel free to connect with me on the following platforms: