# aswina1636/gmail-data_warehouse-pipeline architecture

> Architecture diagram of the GitHub repository aswina1636/gmail-data_warehouse-pipeline, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-10-03). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/aswina1636/gmail-data_warehouse-pipeline
- Repository: https://github.com/aswina1636/gmail-data_warehouse-pipeline

## Overview

This repository is an email ETL pipeline that turns a Gmail Excel export into a PostgreSQL warehouse for BI. The main workflow is orchestrated by the Prefect flow: it prepares the schema, extracts and loads raw rows, transforms them into deduplicated and classified staging rows, builds marts, and validates row counts. README documents a star-schema mart; its SQL implementation is present in the tree but was not excerpted, so that subsystem is represented without unsupported internal detail.

Main entry points: [`flow.py`](https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/flow.py) (Prefect pipeline), [`sql/02_build_marts.sql`](https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/sql/02_build_marts.sql) (Star-schema marts), [`src/load/load_postgres.py`](https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/src/load/load_postgres.py) (Raw loader), [`src/extract/extract_emails.py`](https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/src/extract/extract_emails.py) (Excel extraction)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_orchestration["Orchestration"]
  node_flow["Prefect pipeline<br/>[flow.py]"]
  node_config["Runtime config<br/>[config.py]"]
  node_validate["Run validation<br/>[flow.py]"]
end

subgraph group_ingest["Ingestion"]
  node_xlsx["Email export"]
  node_extract["Excel extraction<br/>[extract_emails.py]"]
end

subgraph group_warehouse["Warehouse layers"]
  node_rawload["Raw loader<br/>[load_postgres.py]"]
  node_raw[("Raw emails")]
  node_transform["Clean and classify<br/>[clean.py]"]
  node_stagingload["Staging loader<br/>[load_postgres.py]"]
  node_staging[("Staging emails")]
  node_schema["Warehouse schema<br/>[01_schema.sql]"]
end

subgraph group_analytics["Analytics modeling"]
  node_marts[("Star-schema marts<br/>[02_build_marts.sql]")]
end

node_operator(("Pipeline operator"))
node_bi(("BI consumers"))

node_operator -->|"starts"| node_flow
node_flow -->|"reads settings"| node_config
node_flow -->|"applies DDL"| node_schema
node_flow -->|"calls extraction"| node_extract
node_xlsx -->|"provides workbook"| node_extract
node_flow -->|"loads raw"| node_rawload
node_rawload -->|"writes rows"| node_raw
node_flow -->|"calls transform"| node_transform
node_flow -->|"loads staging"| node_stagingload
node_stagingload -->|"writes rows"| node_staging
node_flow -->|"executes mart SQL"| node_marts
node_flow -->|"checks counts"| node_validate
node_validate -->|"counts rows"| node_raw
node_validate -->|"counts rows"| node_staging
node_validate -->|"checks fact rows"| node_marts
node_bi -->|"queries marts"| node_marts

click node_flow "https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/flow.py"
click node_config "https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/src/config.py"
click node_extract "https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/src/extract/extract_emails.py"
click node_rawload "https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/src/load/load_postgres.py"
click node_transform "https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/src/transform/clean.py"
click node_stagingload "https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/src/load/load_postgres.py"
click node_schema "https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/sql/01_schema.sql"
click node_marts "https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/sql/02_build_marts.sql"
click node_validate "https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/flow.py"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_flow,node_config,node_validate toneBlue
class node_xlsx,node_extract toneAmber
class node_rawload,node_raw,node_transform,node_stagingload,node_staging,node_schema toneMint
class node_marts toneRose
class node_operator,node_bi toneIndigo
```

## Components

### Orchestration

- [Prefect pipeline](https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/flow.py): `flow.py`
- [Runtime config](https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/src/config.py): `src/config.py`
- [Run validation](https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/flow.py): `flow.py`

### Ingestion

- **Email export**
- [Excel extraction](https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/src/extract/extract_emails.py): `src/extract/extract_emails.py`

### Warehouse layers

- [Raw loader](https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/src/load/load_postgres.py): `src/load/load_postgres.py`
- **Raw emails**
- [Clean and classify](https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/src/transform/clean.py): `src/transform/clean.py`
- [Staging loader](https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/src/load/load_postgres.py): `src/load/load_postgres.py`
- **Staging emails**
- [Warehouse schema](https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/sql/01_schema.sql): `sql/01_schema.sql`

### Analytics modeling

- [Star-schema marts](https://github.com/aswina1636/gmail-data_warehouse-pipeline/blob/main/sql/02_build_marts.sql): `sql/02_build_marts.sql`

### Other components

- **Pipeline operator**
- **BI consumers**

## Connections

- Pipeline operator → Prefect pipeline: starts (evidence: `README.md`)
- Prefect pipeline → Runtime config: reads settings (evidence: `flow.py`)
- Prefect pipeline → Warehouse schema: applies DDL (evidence: `flow.py`)
- Prefect pipeline → Excel extraction: calls extraction (evidence: `flow.py`)
- Email export → Excel extraction: provides workbook (evidence: `README.md`)
- Prefect pipeline → Raw loader: loads raw (evidence: `flow.py`)
- Raw loader → Raw emails: writes rows (evidence: `src/load/load_postgres.py`)
- Prefect pipeline → Clean and classify: calls transform (evidence: `flow.py`)
- Prefect pipeline → Staging loader: loads staging (evidence: `flow.py`)
- Staging loader → Staging emails: writes rows (evidence: `src/load/load_postgres.py`)
- Prefect pipeline → Star-schema marts: executes mart SQL (evidence: `flow.py`)
- Prefect pipeline → Run validation: checks counts (evidence: `flow.py`)
- Run validation → Raw emails: counts rows (evidence: `flow.py`)
- Run validation → Staging emails: counts rows (evidence: `flow.py`)
- Run validation → Star-schema marts: checks fact rows (evidence: `flow.py`)
- BI consumers → Star-schema marts: queries marts (evidence: `README.md`)
