Back to Projects

Data Engineering

From Flat File to Data Warehouse

An ETL pipeline and star schema for 161K drug reviews

  • ETL
  • SQL & SQLite
  • Data Modeling

See the work

GitHub
01

The Problem

Product catalogues and review systems usually live in separate places, and someone has to reconcile them before anyone can run a query. I took the same Drugs.com data from my NLP project and rebuilt it the way it would arrive in practice.

02

Strategy & Solution

  • Extracted a drug master (8,490 drug-condition pairs) and a review transaction table (161,297 records).
  • Cleaned text, standardised drug names, and added sentiment score, review length and a usefulness ratio.
  • Loaded a star schema (dim_drug and fact_reviews) into SQLite, dropping ~900 orphan reviews as a data-quality check.
03

Impact

  • Confirmed sentiment rises steadily with star rating, from -0.47 at 1 star to +0.15 at 10.
  • Flagged conditions like Postherpetic Neuralgia that rate 7.3 but read strongly negative.
  • Laid out a path to BigQuery or Snowflake with a date dimension and Airflow scheduling.

Interested in working together?

Get in touch