Shubham Kumar Jha

1. Why This Project Matters

This project demonstrates a complete end-to-end machine learning workflow — from raw data through a structured preprocessing pipeline, model comparison, hyperparameter tuning, and evaluation, to a FastAPI backend and a live Render deployment. It moves well beyond a notebook exercise into a deployable application.

2. Problem Statement

Predict the room type of a New York City Airbnb listing — Entire home/apt, Private room, Shared room, or Hotel room — from available listing attributes. This is a multi-class classification problem with real class-imbalance challenges.

3. Dataset

NYC Airbnb Open Data (2019) — Kaggle. The dataset contains NYC Airbnb listings with both numerical and categorical features describing location, availability, price, and host characteristics.

4. Preprocessing Pipeline

A ColumnTransformer pipeline was built using Scikit-Learn, applying separate transformations to numerical and categorical features:

5. Class Imbalance Handling

class_weight="balanced" was applied to the classifier to address the uneven distribution of room types. SMOTE was considered but avoided to prevent data leakage through the preprocessing pipeline.

6. Model Comparison (Cross-Validation)

Note: The above are cross-validation estimates, not final test results.

7. Hyperparameter Tuning

RandomizedSearchCV was applied to the Random Forest to search over: n_estimators, max_depth, and min_samples_split. The best parameters from the search were used for the final model.

8. Final Evaluation (Test Set)

9. API & Application Architecture

The FastAPI backend receives listing features, runs them through the fitted preprocessing pipeline, and returns the predicted room type with class probabilities.

10. Engineering Takeaways

• The Scikit-Learn ColumnTransformer ensures that training and inference preprocessing are identical — preventing data leakage and prediction drift.
• Using class_weight="balanced" improved Macro F1 by handling minority classes without synthetic oversampling.
• RandomizedSearchCV is efficient for moderate search spaces — balancing compute cost against thorough hyperparameter exploration.
• FastAPI provided a clean, typed interface between the ML model and the frontend with minimal boilerplate.

Key Results

• 85.6% test accuracy and 0.742 Macro F1 on the held-out test set.
• Random Forest outperformed Logistic Regression by over 20 percentage points in accuracy.
• Application successfully deployed and accessible via Render.

1. Why This Project Matters

This project demonstrates an end-to-end data analytics pipeline — moving from raw CSVs through a relational PostgreSQL schema, SQL-based semantic layer, and into an interactive Power BI dashboard. It covers data modeling, relational integrity, SQL engineering, DAX, and BI design in a single coherent project.

2. Business Problem

The Brazilian Olist marketplace generates rich transactional data across orders, products, sellers, customers, reviews, and payments. The goal was to transform these raw relational tables into a structured analytical layer and a 9-page interactive dashboard answering key e-commerce business questions.

3. Dataset & Schema

4. Architecture

5. PostgreSQL Architecture

All 9 raw CSV tables were loaded into PostgreSQL with primary key and foreign key constraints enforcing referential integrity across the schema. Indexes were added on key join and filter columns to support DirectQuery performance.

6. SQL Semantic Layer — 5 BI Views

A fact/dimension pattern was used to build 5 BI views on top of the raw schema:

7. DAX Layer — 26 Measures

26 DAX measures cover the full BI surface area including:

• Core KPIs: Revenue, Freight, GMV, Orders, Customers, Items Sold, AOV, Items per Order
• Delivery metrics: Delivered Orders, Late Orders, On-time %, Average Delivery Days, Late %
• Time intelligence: Revenue YTD, Revenue Last Year, YoY %, Rolling 30-Day Revenue
• Review metrics: Average Rating, Review Count
• Payment & Seller metrics
• Drillthrough title measure for the Drillthrough page

8. Dashboard — 9 Pages

9. Key Results

10. Engineering Decisions

11. Known Limitations

• Geolocation data — the geolocation CSV is loaded into PostgreSQL but is currently not used in any BI view. Geographic mapping is a planned next step.
• Order-item grain consideration — the main fact view (bi_fact_sales) operates at order-item grain. A dedicated bi_payments_order view aggregates payments to order level, but review and order-level metrics joined to the main fact can involve grain duplication considerations that should be accounted for in DAX.
• Local setup — the dashboard uses local PostgreSQL connected via Power BI Desktop DirectQuery. Power BI Service publishing and scheduled refresh are not currently configured.

Engineering Takeaways

• Building a proper SQL semantic layer before connecting Power BI avoids spaghetti DAX and keeps business logic in the database where it belongs.
• PK/FK constraints and indexes are not optional — they are the foundation that makes DirectQuery performant.
• Documenting grain considerations explicitly is essential for accurate metric interpretation in any fact/dimension design.