How teja798/book-sales-analysis works
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 Python loader · Books.csv Books CSV · Customers.csv Customers CSV · Orders.csv Orders CSV
Components 12
Raw data
Source CSV files that feed the pipeline.
- Books CSV · Raw book dataset.
Book sales/Books.csv - Customers CSV · Raw customer dataset.
Book sales/Customers.csv - Orders CSV · Raw orders dataset.
Book sales/Orders.csv
ETL
Python ingestion and relational loading boundary.
- Python loader · Discovers CSV files and loads them into the database.
path to tables.py - Pandas · Tabular transformation before load.
- SQLAlchemy · Database connectivity and insert abstraction.
Storage
Canonical relational persistence and SQL layer.
- MySQL · Canonical analytical database for modeled data.
- Books table · Relational representation of book entities.
- Customers table · Relational representation of customer entities.
- Orders table · Relational orders data linked to books and customers.
- SQL script · Schema or query artifact supporting database setup or analysis.
Book sales.sql
Reporting
Power BI analytics consumption layer.
- Power BI report · Dashboard and analytics consumption layer.
Book sales analysis.pbix
Connections 14
- 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.