NOAA GHCN Climatology Data Pipeline

This project demonstrates end-to-end data pipeline using the ELT model (extract - load - transform), that downloads climatology data from AWS S3 Bucket, uploads it to MinIo and transforms it with modern tech stacks like Dbt, Duckdb, Pyspark in order to be visualized with Apache Superset.

---

Datasets

The datasets are ingested from NOAA’s Global Historical Climatology Network of which mainly CSV format is used in this project. It contains daily observations over global land areas. Each file corresponds to a year from 1763 to present and is named as such & is updated daily.

Here we used:

• ghcnd-countries - metadata about countries name & code
• ghcnd-stations - metadata of stations
• year - daily records of weather parameters

Tech Stack

• Python – for scripting and Airflow custom operators
• Airflow – DAGs orchestration
• Minio - s3-compatible object storage as data lake
• Spark - data tranformation parallely with repartition
• DBT – data transformation & modeling
• dbt test - validation & data quality
• DuckDB - lightweight query engine with DBT
• PostgreSQL – final data warehouse for transformed data
• Superset – dashboard & visualization
• Docker - airflow,minio,spark,dbt,superset infrastructure

---

Project Structure

├── airflow
│   ├── config # Airflow configs 
│   ├── dags # Airflow DAGs
│   │   ├── current_year_ingest.py
│   │   ├── dbt_duckdb.py
│   │   |── last_five_years_ingest.py
|   |   |__station_countries_ingest.py
|   |   |___transformation.py
|   |__jobs
|   |   |___spark-eda.ipynb # Spark transform scripts 
│   ├── docker-compose.yaml # Service orchestration
│   ├── Dockerfile # Custom airflow image 
│   ├── requirements.txt
│   └── .env # Environment variables
├── dbt_noaa_climatology # DBT transformation project
│   ├── dbt_project.yml
│   ├── macros
│   ├── models # Dbt transformations
│   │   ├── final
│   │   ├── schema.yml
│   │   └── staging
│   ├── profiles.yml
│   └── tests
├── dashboards # Pipeline architecture, dashboards
├── superset
│   ├── dockerfile # Custom superset image

---

Pipeline Architecture

1. We use docker to containerize the application and airflow to orchestrate dags.
2. NOAA_GHCN data of .csv format is ingested from S3-bucket, & load it into the datalake - MinIO.
3. After obtaining the data, we perform EDA first in .jupyter then prepare to save dataframes.
4. From MinIO, load data into spark for transformations & write into data warehouse - PostgreSQL.
5. Transform as needed using dbt-duckdb through duckdb as query-engine between dbt & MinIO.
   • staging: Raw & dataschema formatted tables
   • final: Ready-to-query analytical tables
6. Visualize the silver & gold data using superset

---

Setup Instruction

1. Clone the Repo

git clone https://github.com/yourusername/climatology.git
cd climatology

2. Configure Airflow Connections

Get airflow docker compose with:

curl -LfO 'https://airflow.apache.org/docs/apache-airflow/3.0.3/docker-compose.yaml'

Initialize folders & environment:

mkdir -p ./dags ./logs ./plugins ./config
echo -e "AIRFLOW_UID=$(id -u)" > .env

Ensure .env file has all credentials if needed.

---

3. DBT Setup

Ensure your DBT project is placed at:

/opt/dbt_climatology

Edit profiles.yml accordingly to connect to your external DuckDB instance.

---

4. Run docker Pipeline

To build image & run:

docker compose up build --no-cache
docker compose up

To shut down & clean up:

docker compose down --volumes --remove-orphans

Docker services

• Airflow init apiserver, scheduler, dag-processor, trigger, CLI
• Minio server & client
• Jupyter Notebook Integrated with Spark
• Spark Master & workers
• Superset UI

Example .env file:

# .env
AIRFLOW_UID=1000

POSTGRES_HOST = localhost
POSTGRES_PORT = 5432
POSTGRES_DB = db
POSTGRES_USER = postgres
POSTGRES_PASSWORD = postgres

AIRFLOW_CONN_POSTGRES_DEFAULT=postgresql://postgres:postgres@host:port/db

JDBC_URL=jdbc:postgresql://host:port/db

SUPERSET_SECRET_KEY='your-secret-key'
SUPERSET_DATABASE_URI=postgresql+psycopg2://{POSTGRES_USER}:{POSTGRES_PASSWORD}@{POSTGRES_HOST}:{POSTGRES_PORT}/{POSTGRES_DB?options=-csearch_path%3d{SCHEMA_NAME}
 
MINIO_ENDPOINT=9000
MINIO_ACCESS_KEY=admin
MINIO_SECRET_KEY=admin
MINIO_BUCKET=db

You can replace all above info like host,user,password with your own.

---

User Interfaces

• http://localhost:8080 - Airflow
• http://localhost:9090 - Spark master (Submit your job scripts in spark/)
• http://localhost:8888 - Jupyter Notebook
• http://localhost:9001 - MinIO
• http://localhost:8088 - Superset

---

Visualization

---

Future Actions

• Deploy using one of cloud services like AWS, Azure, Google Cloud.
• Testing environment & data quality with Great Expectations