# teja798/book-sales-analysis architecture

> Architecture diagram of the GitHub repository teja798/book-sales-analysis, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-06-20). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/teja798/book-sales-analysis
- Repository: https://github.com/teja798/book-sales-analysis

## Overview

This repository is a small end-to-end analytics pipeline, not an application server. The architecture is centered on moving three raw CSV datasets into a relational database and then into a Power BI report.

1. **Source data layer**
   - The raw inputs are the three CSV files under the `Book sales/` folder: `Books.csv`, `Customers.csv`, and `Orders.csv`.
   - These represent the system’s upstream data sources and likely correspond to the core business entities in the model.

2. **Ingestion / staging boundary**
   - The Python side of the project is responsible for discovering and loading these files.
   - The README indicates use of `pathlib` and `glob` for automated file discovery, which suggests the ingestion step is file-driven rather than manually hardcoded per dataset.

3. **Transformation and load layer**
   - `SQLAlchemy` and `pandas` are used to move data from CSV into MySQL.
   - This is the main ETL boundary: raw flat files are converted into relational tables suitable for analysis.

4. **Relational storage layer**
   - MySQL is the persistence layer for the modeled data.
   - The important architectural point is that the data becomes structured around relationships between books, customers, and orders rather than remaining as separate files.

5. **Data model**
   - The project explicitly mentions relationship mapping among Orders, Books, and Customers.
   - From an architecture perspective, this is the core analytical model: one fact-like orders dataset connected to two dimension-like reference entities.

6. **SQL artifact**
   - `Book sales.sql` likely contains either schema definitions, load queries, or analysis queries used alongside the database.
   - In the diagram, treat it as the SQL layer that supports the database setup or downstream querying.

7. **Analytics / reporting layer**
   - `Book sales analysis.pbix` is the Power BI report file.
   - This is the presentation boundary where the modeled MySQL data is consumed for dashboarding and business intelligence.

8. **Tooling stack that affects architecture**
   - Python is the orchestration language for ingestion.
   - `pandas` handles tabular data manipulation before load.
   - `SQLAlchemy` abstracts database connectivity and inserts.
   - Power BI is the final consumer of the curated data.

9. **Data flow**
   - The flow is: CSV inputs → Python ingestion script → MySQL tables → Power BI dashboard.
   - There is no evidence of a live API or application runtime; the pipeline is batch-oriented and file/database driven.

10. **Repository layout signal**
   - The README describes a clean separation between data, scripts, and dashboard artifacts, even though the file tree shown is flatter than the documented structure.
   - For an architecture diagram, the key nodes are still the same: raw files, Python load job, MySQL, and Power BI.

11. **Primary boundary to emphasize**
   - The most important architectural boundary is between raw data preparation and analytics consumption.
   - Everything before MySQL is concerned with ingestion and modeling; everything after MySQL is reporting and visualization.

12. **What this repo is demonstrating**
   - This is a classic small-scale analytics stack: batch ETL, relational modeling, and BI reporting.
   - The design goal is traceability from raw CSVs to dashboard metrics, with the database acting as the canonical analytical store.

Main entry points: [`path to tables.py`](https://github.com/teja798/book-sales-analysis/blob/HEAD/path%20to%20tables.py) (Python loader), [`Book sales/Books.csv`](https://github.com/teja798/book-sales-analysis/blob/HEAD/Book%20sales/Books.csv) (Books CSV), [`Book sales/Customers.csv`](https://github.com/teja798/book-sales-analysis/blob/HEAD/Book%20sales/Customers.csv) (Customers CSV), [`Book sales/Orders.csv`](https://github.com/teja798/book-sales-analysis/blob/HEAD/Book%20sales/Orders.csv) (Orders CSV)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_raw_data["Raw data"]
  node_books_csv["Books CSV<br/>source file<br/>[Books.csv]"]
  node_customers_csv["Customers CSV<br/>source file<br/>[Customers.csv]"]
  node_orders_csv["Orders CSV<br/>source file<br/>[Orders.csv]"]
end

subgraph group_etl["ETL"]
  node_python_loader["Python loader<br/>etl script<br/>[path to tables.py]"]
  node_pandas{{"Pandas<br/>dataframe layer"}}
  node_sqlalchemy{{"SQLAlchemy<br/>db connector"}}
end

subgraph group_storage["Storage"]
  node_mysql[("MySQL<br/>relational store")]
  node_book_table[("Books table<br/>dimension table")]
  node_customer_table[("Customers table<br/>dimension table")]
  node_orders_table[("Orders table<br/>fact table")]
  node_sql_artifact["SQL script<br/>sql artifact<br/>[Book sales.sql]"]
end

subgraph group_reporting["Reporting"]
  node_powerbi["Power BI report"]
end

node_books_csv -->|"input"| node_python_loader
node_customers_csv -->|"input"| node_python_loader
node_orders_csv -->|"input"| node_python_loader
node_python_loader -->|"shape"| node_pandas
node_python_loader -->|"connect"| node_sqlalchemy
node_pandas -->|"load"| node_mysql
node_sqlalchemy -->|"write"| node_mysql
node_mysql -->|"contains"| node_book_table
node_mysql -->|"contains"| node_customer_table
node_mysql -->|"contains"| node_orders_table
node_sql_artifact -->|"supports"| node_mysql
node_book_table -.->|"relates"| node_orders_table
node_customer_table -.->|"relates"| node_orders_table
node_mysql -->|"serve"| node_powerbi

click node_books_csv "https://github.com/teja798/book-sales-analysis/blob/main/Book sales/Books.csv"
click node_customers_csv "https://github.com/teja798/book-sales-analysis/blob/main/Book sales/Customers.csv"
click node_orders_csv "https://github.com/teja798/book-sales-analysis/blob/main/Book sales/Orders.csv"
click node_python_loader "https://github.com/teja798/book-sales-analysis/blob/main/path to tables.py"
click node_sql_artifact "https://github.com/teja798/book-sales-analysis/blob/main/Book sales.sql"
click node_powerbi "https://github.com/teja798/book-sales-analysis/blob/main/Book sales analysis.pbix"

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_books_csv,node_customers_csv,node_orders_csv toneBlue
class node_python_loader,node_pandas,node_sqlalchemy toneAmber
class node_mysql,node_book_table,node_customer_table,node_orders_table,node_sql_artifact toneMint
class node_powerbi toneRose
```

## Components

### Raw data

Source CSV files that feed the pipeline.

- [Books CSV](https://github.com/teja798/book-sales-analysis/blob/HEAD/Book%20sales/Books.csv): source file · Raw book dataset. · `Book sales/Books.csv`
- [Customers CSV](https://github.com/teja798/book-sales-analysis/blob/HEAD/Book%20sales/Customers.csv): source file · Raw customer dataset. · `Book sales/Customers.csv`
- [Orders CSV](https://github.com/teja798/book-sales-analysis/blob/HEAD/Book%20sales/Orders.csv): source file · Raw orders dataset. · `Book sales/Orders.csv`

### ETL

Python ingestion and relational loading boundary.

- [Python loader](https://github.com/teja798/book-sales-analysis/blob/HEAD/path%20to%20tables.py): etl script · Discovers CSV files and loads them into the database. · `path to tables.py`
- **Pandas**: dataframe layer · Tabular transformation before load.
- **SQLAlchemy**: db connector · Database connectivity and insert abstraction.

### Storage

Canonical relational persistence and SQL layer.

- **MySQL**: relational store · Canonical analytical database for modeled data.
- **Books table**: dimension table · Relational representation of book entities.
- **Customers table**: dimension table · Relational representation of customer entities.
- **Orders table**: fact table · Relational orders data linked to books and customers.
- [SQL script](https://github.com/teja798/book-sales-analysis/blob/HEAD/Book%20sales.sql): sql artifact · Schema or query artifact supporting database setup or analysis. · `Book sales.sql`

### Reporting

Power BI analytics consumption layer.

- [Power BI report](https://github.com/teja798/book-sales-analysis/blob/HEAD/Book%20sales%20analysis.pbix): bi report · Dashboard and analytics consumption layer. · `Book sales analysis.pbix`

## Connections

- Books CSV → Python loader: input · Books CSV is discovered and loaded.
- Customers CSV → Python loader: input · Customers CSV is discovered and loaded.
- Orders CSV → Python loader: input · Orders CSV is discovered and loaded.
- Python loader → Pandas: shape · Python uses pandas for tabular preparation.
- Python loader → SQLAlchemy: connect · Python uses SQLAlchemy to reach MySQL.
- Pandas → MySQL: load · Prepared tables are written into MySQL.
- SQLAlchemy → MySQL: write · SQLAlchemy handles database writes.
- MySQL → Books table: contains · Books data is stored as a relational table.
- MySQL → Customers table: contains · Customers data is stored as a relational table.
- MySQL → Orders table: contains · Orders data is stored as a relational table.
- SQL script → MySQL: supports · SQL file supports schema setup or downstream querying.
- Books table → Orders table: relates · Orders links to books in the analytical model.
- Customers table → Orders table: relates · Orders links to customers in the analytical model.
- MySQL → Power BI report: serve · Power BI consumes curated relational data.