# avega17/ice-melt_ducklake architecture

> Architecture diagram of the GitHub repository avega17/ice-melt_ducklake, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-06-19). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/avega17/ice-melt_ducklake
- Repository: https://github.com/avega17/ice-melt_ducklake

## Overview

##### Project Type & Purpose
- This is a research‐oriented modern data lakehouse pipeline (“ice-mELT DuckLake”) for Earth-Observation (EO) photovoltaic (PV) segmentation and energy-forecasting.
- It ingests vector (PV polygons/points) and raster (satellite imagery, irradiance) data, stores them in open formats, catalogs metadata in SQL, transforms via ELT (dbt & Hamilton), and serves through query engines and a documentation/dashboard site.

##### Key Architectural Layers & Components
A. External Data Sources
- DOI PV datasets (via datahugger & STAC)
- Public STAC catalogs for imagery/irradiance
- Overture Maps (admin boundaries, building footprints)
- Google Solar API / NREL NSRDB
B. Ingestion Layer
- Scripts & pipelines (doi_dataset_pipeline.py, raw_pv_doi_ingest.py)
- DLT or custom Python utilities (`utils/ingestion_utils.py`, `utils/fetch_and_preprocess.py`)
- Hamilton DAG orchestrators (`db/validate_pv_ingest.py`, `test_hamilton_pipeline.py`)
C. Raw Data Storage
- Local filesystem (or S3-compatible) buckets under `datasets/raw/...`
- Formats: GeoParquet (vector), COG/GeoTIFF (raster), Zarr/Icechunk (tensors)
D. Metadata & Catalog
- DuckLake metadata store (PostgreSQL or Neon/Supabase) via SQL tables
- Optional Apache Iceberg catalog for ACID table management
- STAC metadata catalog (pgstac, profiles.yml)
E. ELT/Transform Layer
- dbt project (`dbt_project.yml` + `models/` folders)
- staging: raw standardization (`models/staging`)
- prepared: spatial processing, H3 indexing (`models/prepared`)
- curated: final analytical tables (`models/curated`)
- Python/SQL hybrid transformations (dbt Python models, Ibis, Xarray)
- Hamilton for programmatic DAGs
F. Analytical Engines
- DuckDB for local SQL and spatial extensions (dbt-duckdb)
- MotherDuck/DuckDB cloud scaling
- PostgreSQL for transactional metadata queries
G. Consumption & Visualization
- Documentation & dashboard (`dashboard/` folder, Next.js or static site)
- Jupyter Notebooks (`notebooks/`) for EDA
- Future ML/forecasting models (in `utils/torch_datasets.py`, planned code)

##### Relationships & Data Flow
- Ingestion Layer writes Raw Data → Raw Data Storage
- Raw Data triggers metadata registration → Metadata & Catalog
- ELT Layer reads Raw Data + Metadata → writes Curated Tables → stored in DuckLake/Iceberg
- Analytical Engines query Curated Tables ←→ Metadata Store
- Outputs feed Dashboard & Notebooks

##### Architectural Patterns & Design Principles
- Modern ELT (dbt + Hamilton) over raw ingestion
- Data Lakehouse: separation of storage (object) vs metadata (SQL)
- Open formats: Parquet, GeoParquet, Zarr, Icechunk, COG
- Spatial indexing with H3 for performance
- Polyglot engine support via Ibis abstraction
- Free-tier/cloud-native cost optimization

##### Technologies & Frameworks (callouts)
- Ingestion: Python, DLT (if used), datahugger, Rustac/pgstac
- Storage: S3/R2, local FS, DuckLake (SQL), Apache Iceberg
- Catalog: PostgreSQL (Neon/Supabase), pgstac
- Transform: dbt (SQL + Python), Hamilton, Ibis, Xarray, Tensorstore
- Query Engines: DuckDB, MotherDuck, PostgreSQL
- Visualization: Next.js/MDX (`dashboard/`), Jupyter

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% External Data Sources
    subgraph "External Data Sources"
        direction TB
        DOI["DOI PV Datasets\n(datahugger & STAC)"]:::external
        STAC["Public STAC Catalogs\n(imagery/irradiance)"]:::external
        Overture["Overture Maps\n(admin boundaries)"]:::external
        SolarAPI["Google Solar API\n/NREL NSRDB"]:::external
    end

    %% Ingestion Layer
    subgraph "Ingestion Layer" 
        direction TB
        RawIngest["raw_pv_doi_ingest.py"]:::ingestion
        Validate["db/validate_pv_ingest.py"]:::ingestion
        TestHam["test_hamilton_pipeline.py"]:::ingestion
        IngestUtils["utils/ingestion_utils.py"]:::ingestion
        FetchPrep["utils/fetch_and_preprocess.py"]:::ingestion
    end

    %% Raw Data Storage
    subgraph "Raw Data Storage"
        direction TB
        GeoParquet["Raw GeoParquet Storage\n(vector)"]:::storage
        GeoTIFF["Raw COG/GeoTIFF Storage\n(raster)"]:::storage
        Zarr["Raw Zarr/Icechunk Storage\n(tensors)"]:::storage
    end

    %% Metadata & Catalog
    subgraph "Metadata & Catalog"
        direction TB
        MetaStore["DuckLake Metadata Store\n(PostgreSQL)"]:::catalog
        Iceberg["Apache Iceberg Catalog\n(Optional)"]:::catalog
        STACMeta["STAC Metadata Catalog\n(pgstac)"]:::catalog
        SpatioJSON["spatio-temporal_metadata.json"]:::catalog
        SourcesYML["models/sources.yml"]:::catalog
        SourcesHamYML["models/sources_hamilton.yml"]:::catalog
        DuckDBMacros["macros/duckdb_spatial_utils.sql"]:::catalog
    end

    %% ELT / Transform Layer
    subgraph "ELT / Transform Layer"
        direction TB
        DBTConfig["dbt_project.yml"]:::transform
        subgraph "dbt Models"
            direction TB
            Staging["Staging Models\n(models/staging)"]:::transform
            Prepared["Prepared Models\n(models/prepared)"]:::transform
            Curated["Curated Models\n(models/curated)"]:::transform
        end
        SchemaYML["models/schema.yml"]:::transform
        OverviewMD["models/dbt_layers_overview.md"]:::transform
    end

    %% Analytical Engines
    subgraph "Analytical Engines"
        direction TB
        DuckDBEngine["DuckDB Engine\n(db-duckdb)"]:::query
        MotherDuck["MotherDuck Cloud\n(Scaling)"]:::query
        PGTransact["PostgreSQL\n(Transactional)"]:::query
    end

    %% Consumption & Visualization
    subgraph "Consumption & Visualization"
        direction TB
        Dashboard["dashboard/"]:::presentation
        Notebooks["notebooks/fetch-pv-datasets-ESDA.ipynb"]:::presentation
        VizUtils["utils/visualizations.py"]:::presentation
        MLModels["utils/torch_datasets.py"]:::presentation
    end

    %% Connections
    DOI -->|"STAC API"| RawIngest
    STAC -->|"STAC API"| IngestUtils
    Overture -->|"STAC API"| RawIngest
    SolarAPI -->|"REST API"| FetchPrep

    RawIngest -->|"write raw data"| GeoParquet
    Validate -->|"validate & write"| GeoParquet
    TestHam -->|"orchestrate"| GeoTIFF
    IngestUtils -->|"utils"| Zarr
    FetchPrep -->|"preprocess"| GeoTIFF

    GeoParquet -->|"metadata registration"| MetaStore
    GeoTIFF -->|"metadata registration"| MetaStore
    Zarr -->|"metadata registration"| MetaStore

    GeoParquet -->|"read raw"| Staging
    GeoTIFF -->|"read raw"| Staging
    Zarr -->|"read raw"| Staging
    MetaStore -->|"metadata read"| Staging

    Staging -->|"dbt run"| Prepared
    Prepared -->|"dbt run"| Curated
    Curated -->|"store curated"| Iceberg
    Curated -->|"store curated"| GeoParquet

    Curated -->|"query"| DuckDBEngine
    Curated -->|"query"| PGTransact
    MetaStore <-->|"catalog lookup"| PGTransact
    DuckDBEngine -->|"analytics"| Dashboard
    PGTransact -->|"metadata queries"| Dashboard
    DuckDBEngine -->|"analytics"| Notebooks
    PGTransact -->|"metadata queries"| Notebooks
    Notebooks -->|"future ML data"| MLModels

    %% Click Events
    click DOI "https://github.com/avega17/ice-melt_ducklake/blob/main/doi_manifest.json"
    click DOI "https://github.com/avega17/ice-melt_ducklake/blob/main/raw_pv_doi_ingest.py"
    click STAC "https://github.com/avega17/ice-melt_ducklake/blob/main/utils/st_context_processing.py"
    click Overture "https://github.com/avega17/ice-melt_ducklake/blob/main/models/prepared/prep_overture_countries.sql"
    click SolarAPI "https://github.com/avega17/ice-melt_ducklake/blob/main/utils/fetch_and_preprocess.py"

    click RawIngest "https://github.com/avega17/ice-melt_ducklake/blob/main/raw_pv_doi_ingest.py"
    click Validate "https://github.com/avega17/ice-melt_ducklake/blob/main/db/validate_pv_ingest.py"
    click TestHam "https://github.com/avega17/ice-melt_ducklake/blob/main/test_hamilton_pipeline.py"
    click IngestUtils "https://github.com/avega17/ice-melt_ducklake/blob/main/utils/ingestion_utils.py"
    click FetchPrep "https://github.com/avega17/ice-melt_ducklake/blob/main/utils/fetch_and_preprocess.py"

    click SpatioJSON "https://github.com/avega17/ice-melt_ducklake/blob/main/spatio-temporal_metadata.json"
    click SourcesYML "https://github.com/avega17/ice-melt_ducklake/blob/main/models/sources.yml"
    click SourcesHamYML "https://github.com/avega17/ice-melt_ducklake/blob/main/models/sources_hamilton.yml"
    click DuckDBMacros "https://github.com/avega17/ice-melt_ducklake/blob/main/macros/duckdb_spatial_utils.sql"

    click DBTConfig "https://github.com/avega17/ice-melt_ducklake/blob/main/dbt_project.yml"
    click Staging "https://github.com/avega17/ice-melt_ducklake/blob/main/models/staging/stg_doi_datasets.py"
    click Prepared "https://github.com/avega17/ice-melt_ducklake/blob/main/models/prepared/prep_country_h3_decomposition.sql"
    click Prepared "https://github.com/avega17/ice-melt_ducklake/blob/main/models/prepared/prep_country_pv_statistics.sql"
    click Prepared "https://github.com/avega17/ice-melt_ducklake/blob/main/models/prepared/prep_pv_datasets_unified.py"
    click Prepared "https://github.com/avega17/ice-melt_ducklake/blob/main/models/prepared/prep_pv_with_admin_boundaries.sql"
    click Prepared "https://github.com/avega17/ice-melt_ducklake/blob/main/models/prepared/efficient_spatial_join_architecture.md"
    click Curated "https://github.com/avega17/ice-melt_ducklake/blob/main/models/curated/curated_pv_dataset_summary_draft.sql"
    click SchemaYML "https://github.com/avega17/ice-melt_ducklake/blob/main/models/schema.yml"
    click OverviewMD "https://github.com/avega17/ice-melt_ducklake/blob/main/models/dbt_layers_overview.md"

    click Dashboard "https://github.com/avega17/ice-melt_ducklake/tree/main/dashboard/"
    click Notebooks "https://github.com/avega17/ice-melt_ducklake/blob/main/notebooks/fetch-pv-datasets-ESDA.ipynb"
    click VizUtils "https://github.com/avega17/ice-melt_ducklake/blob/main/utils/visualizations.py"
    click MLModels "https://github.com/avega17/ice-melt_ducklake/blob/main/utils/torch_datasets.py"

    %% Styles
    classDef external fill:#dddddd,stroke:#888888,color:#000000
    classDef ingestion fill:#cce5ff,stroke:#3399ff,color:#000000
    classDef storage fill:#d4edda,stroke:#28a745,color:#000000
    classDef catalog fill:#e2d9f3,stroke:#7f3fbf,color:#000000
    classDef transform fill:#ffe5b4,stroke:#ff9933,color:#000000
    classDef query fill:#b2ebf2,stroke:#00acc1,color:#000000
    classDef presentation fill:#f8d7da,stroke:#dc3545,color:#000000
```
