An ETL pipeline that loads the Yelp open dataset (businesses, users, reviews, tips and check-ins) into a normalised PostgreSQL schema. The extract and load steps are written in Rust.
- Convert. The raw JSON is turned into CSV, with invalid records filtered out, dates normalised, and malformed values fixed. For example,
eliteyears written as"20,20"become2020. - Load. The CSV files are bulk-loaded with
COPY, and a cleanup pass removes orphaned rows, such as reviews by users or for businesses that don’t exist. - Validate. SQL constraint-validation scripts check each table after loading.
Index ablation
I ran the same analytical queries with and without the indexes:
| Query | With indexes | Without |
|---|---|---|
still_there |
0.043s | 0.181s |
top_reviews |
0.048s | 1.99s |
average_rating |
0.006s | 1.46s |
topBusiness_in_city |
1.81s | 1.99s |
high_fives |
0.869s | 2.20s |
The point lookups and per-business aggregates gained the most. average_rating went from 1.46s to 6ms. The city-wide ranking barely changed, because it scans most of the table anyway.