How I build this: a public, fully-worked pipeline on the Olist e-commerce dataset

The exact same pipeline I build for real clients (medallion architecture, SCD2 versioning, data quality checks at every layer, Airflow orchestration), just run on a public dataset instead of confidential client data, so there's nothing to take on faith. It's the clearest way to see how I actually work.

Why this exists

Client work is necessarily confidential. No client names, no real revenue figures, nothing that could identify a business without sign-off. This demo exists to show the same engineering with nothing to anonymize: the Brazilian E-Commerce Public Dataset by Olist (~100k orders, September 2016 to October 2018), via Kaggle, licensed CC BY-NC-SA 4.0. Non-commercial use only, and that’s respected throughout since this is a portfolio demo, not a product.

The chart on the homepage (monthly order volume, revenue, and on-time delivery rate) is real output from this pipeline’s Gold tables, not a mock-up.

The architecture

A batch pipeline, orchestrated by Airflow, moving data through a Medallion model:

raw CSVs (8 source files)
    -> schema enforcement, type casting, Parquet conversion
Bronze   (type-safe, untransformed Parquet)
    -> DQ: schema, nulls, primary-key uniqueness
Silver   (dimensional model: SCD2 dimensions + fact upserts)
    -> DQ: range checks, SCD rules, referential integrity
Gold     (business-ready aggregation tables)
    -> DQ: non-negative values, non-empty tables, quarantine-growth checks

Silver schema: 3 SCD Type 2 dimensions (dim_customers, dim_sellers, dim_products) and 4 upserted fact tables (fact_orders, fact_order_items, fact_payments, fact_reviews). Every dimension carries full SCD2 metadata (effective_date, end_date, is_current, is_deleted), so historical state is queryable, not just current state.

Gold layer: four business-ready tables: gold_revenue_by_month_state, gold_delivery_performance, gold_review_score_by_category, and gold_seller_performance. They feed a 3-tab Metabase dashboard covering sales, logistics, and customer satisfaction.

Data quality, not just data movement

Every layer boundary has an explicit gate: Bronze checks schema conformance, null rates, and primary-key uniqueness before anything moves downstream. Silver checks range validity, SCD-versioning correctness, and referential integrity. Violations get quarantined to their own files, never silently dropped, so a bad record stays auditable and recoverable. Gold checks that derived aggregates stay internally consistent (non-negative values, row counts that reconcile across layers) before the dashboard ever sees them.

Deliberate, documented trade-offs

A few decisions worth calling out, because they show engineering judgment rather than just “SCD2 by default everywhere”:

This dataset is a one-time historical snapshot, not a live feed, so ingestion does a full pull of all 8 CSVs on every run rather than using the incremental watermark mechanism the underlying template supports. A real live source dropped into the same template would use the incremental path instead. This is a considered choice for a static dataset, not a limitation of the engine.
Revenue excludes freight and excludes canceled/unavailable orders. Freight is a pass-through logistics cost to the carrier, not merchant revenue, and orders that never became real transactions shouldn’t drag down a “revenue per order” average.
Reviews are attributed at the order level, matching how Olist actually records them (once per order, not per line item). It’s a documented approximation, not an invented precision the source data doesn’t support.

The point

This is the exact same engine (the SCD2/upsert logic, FK quarantine, data quality checks, Airflow DAG structure, Docker/Terraform/CI setup) used for real client engagements like the anonymized events-industry case study. Only the source connectors and the Silver/Gold business logic change per client. The governance and orchestration underneath don’t get rebuilt from scratch every time.