- Problem
- The Olist dataset spans orders, customers, sellers, products, payments, reviews, geolocation, and marketing leads across separate tables in SQLite. Any substantive question cuts across most of them, so the relational structure has to be sound and the data has to live somewhere that supports real analytical work before analysis can begin.
- Approach
- A custom Python pipeline migrates the relational SQLite source into SQL Server and Azure SQL, handling entity ordering, foreign key dependencies, type conversion, validation, and source-to-target parity testing so the migration can be verified rather than assumed. On top of that, T-SQL analysis covers sales performance and customer behavior, and a JavaScript geospatial application renders seller-to-buyer trade routes with selectable metrics.
- Result
- A queryable marketplace database with verified parity to its source, plus an interactive map of more than 35,000 trade routes across over 1,600 cities, filterable by order count, value, distance, and freight intensity.
Parity testing as part of the migration
A migration that moves rows without verifying them produces a database nobody can trust, and the failure is silent: queries return results, they are just wrong. Comparing row counts, key relationships, and value distributions between source and target turns “the migration ran” into “the migration is correct.”
Geography as a first-class dimension
Seller and customer location is the difference between a marketplace analysis and a logistics one. Treating each seller-to-buyer pair as a trade route, rather than reducing both endpoints to a region label, exposes the distance and freight structure that a purely categorical analysis flattens away. Freight intensity against distance is where the interesting outliers live.
Analyses on this site are for general information only. They are not investment, financial, legal, or tax advice, and not an offer or solicitation to buy or sell any instrument. Past results do not indicate future outcomes. Views are the author's own.