A simulated data pipeline for an African fashion marketplace. Pulling data from multiple systems, cleaning it, joining it, and turning it into numbers a business would actually use.
I interviewed for a Financial & Operational Analyst role at a real company called Losode, an African fashion e-commerce marketplace. They needed someone who could build their reporting systems from scratch. Instead of waiting around for feedback, I decided to build a project that simulates exactly that kind of work. I made up a fictional company, Loshodey, and built out a full pipeline around it.
Loshodey is a marketplace, not a single-store retailer, so the numbers work differently than a normal shop. Sellers list products, customers buy them, and Loshodey takes a commission on each sale. That means GMV (total value of everything sold) is not the same as revenue (what Loshodey actually earns). Getting that distinction right shapes almost every model in this project.
Two systems, on purpose, because they represent two different real-world patterns:
- Shopify (Admin API) - orders and products. This is how most real order platforms work, so I pulled data the same way a real integration would: authenticate, hit the API, handle pagination.
- Postgres (running in Docker) - sellers, seller payouts, and logistics costs. This represents the kind of internal database a finance or ops team would query directly, no API in between.
40 sellers, a dev Shopify store with 101 products, 58 test orders (122 line items across them), and a year of payout and shipping history generated to reconcile with the sales data.
Shopify + Postgres -> Python (extract/load) -> BigQuery (raw) -> dbt (staging, intermediate, marts) -> Power BI
This is ELT, not ETL. Data lands in BigQuery raw and untouched first, then gets transformed inside the warehouse using dbt. Nothing gets cleaned before it's loaded.
Staging cleans each raw table on its own. No joins across sources here, just fixing types, renaming columns, and resolving grain problems.
Intermediate is where sources actually meet. This is where a Shopify order line gets matched to a real seller in Postgres, and where GMV and net revenue get calculated using the right commission rate.
Marts are the finished tables, each one built to answer a specific business question. Seven of them, covering GMV trends, take rate by seller tier, cash flow and gross profit, seller performance, delivery performance, order status, and vendor match quality.
Power BI connects only to the marts, never the raw or staging tables. Import mode, two dashboard pages (Finance and Ops/Sales) plus a page of summary KPI cards.
This is the part I think matters most, because catching these is the real job, not just writing SQL that runs without errors.
Fan-out in the Shopify orders table. The raw orders data comes back flattened, one row per line item. So an order with 3 products in it shows up as 3 rows, and the order's total price gets copied onto all 3 of them. I only caught this because I checked row counts (58 orders but 122 rows) and then ran a SUM() on the total price column, which came out wrong because it counted the same order total multiple times. Fixed by splitting staging into two models: one deduped to order grain, one left alone at line-item grain.
A calculation running without an error is not proof it's correct. The fan-out issue passed a clean dbt run with zero errors. It only showed up once I actually queried the row count and compared it to what I expected. That became a habit for the rest of the project: check the actual numbers after every join, don't just trust "PASS."
Two sellers with the same name. While previewing the raw sellers table I noticed "Nia Couture" showed up twice, with two different seller IDs and two different categories (menswear and footwear). Since the only way to match a Shopify order to a seller is by matching the vendor name to the seller name (there's no shared ID between the two systems), this meant any order from "Nia Couture" could have matched the wrong seller. I traced it back to the script that generates the seed data, which built seller names by randomly combining a first name and a business suffix with no check for duplicates. Fixed the generator, regenerated the data, reloaded everything, and added a dbt test (unique on seller_name) so this can never happen silently again.
A date gap that broke the commission rate logic. I wanted the model to use the actual commission rate that applied at the time of each order, not just the seller's current rate, in case a seller's rate changed over time. But the test orders were all dated after the payout history ended, so there was no matching historical rate for any order. Rather than force a match that didn't exist, the model falls back to the seller's current rate and flags which one it used with a commission_rate_source column, so nothing is hidden.
Two real findings came out of this that I didn't expect going in:
- Gross profit is negative. Total commission earned across all sellers was about $147,700, but total shipping cost was about $228,900. Shipping alone costs more than Loshodey earns in commission. At this take rate and these shipping costs, the marketplace loses money on logistics before any other cost is even considered.
- Carrier choice doesn't affect delivery time. I expected some carriers to be faster than others. Instead, delivery days were almost identical across all five carriers, and only changed based on destination type (domestic, regional, international). Carrier turned out to be close to meaningless in this dataset.
I'm including both of these as is, not smoothing them over, because a real analysis should surface what the data actually says, even when it's not a flattering result.
Shopify Admin API, PostgreSQL (Docker), Python (requests, psycopg2), Google BigQuery, dbt Core, Power BI.