Most teams end up with data scattered across several systems and no single place to point a BI tool at. This walkthrough builds a three-layer data warehouse (Bronze, Silver, Gold) end to end, using the public Olist Brazilian e-commerce dataset, and finishes with real dashboards in Apache Superset.
The dataset
The Olist dataset (public on Kaggle, covering 2016 to 2018) has nine tables:
Table Content Record Count
customers Location & identity data 99,441
orders Transaction lifecycle timestamps 99,441
order_items Individual line items 112,650
order_payments Payment transaction records 103,886
order_reviews Customer ratings 99,224
products Catalog inventory 32,951
sellers Merchant information 3,095
geolocation Postal code mappings 1,000,163
category_translation Language localization 71
The three layers
Everything lives in one PostgreSQL instance (olist_DWH), split across three schemas:
Bronze (bronze_olist): raw data, loaded exactly as it arrives from MySQL, with ingested_at and source_system columns appended for lineage. No transformation happens here on purpose.
Silver (silver_olist): light standardization, mainly TRIM() on text columns. No rows are filtered out at this stage; the goal is a clean copy of everything, not a curated subset.
Gold (gold_olist): a proper fact/dimension star schema, built specifically for analytical queries and BI tools.
Step 1: Get the source data into MySQL
Create the database and import the nine CSV files, using MySQL Workbench's Table Data Import Wizard for the smaller tables. For the larger ones (geolocation is roughly a million rows), LOAD DATA INFILE is significantly faster than the GUI importer.
Step 2: Bronze ingestion, MySQL to PostgreSQL
A small Python script using SQLAlchemy and pandas handles the move:
python
import mysql.connector
import pandas as pd
from sqlalchemy import create_engine
MYSQL_CONFIG = {
"host": "localhost",
"port": 3306,
"user": "root",
"password": "your_password",
"database": "olist_prd"
}
PG_CONN = "postgresql+psycopg2://postgres:your_password@localhost:5432/olist_DWH"
TABLES = [
"category_translation", "customers", "geolocation",
"order_items", "order_payments", "order_reviews",
"orders", "products", "sellers"
]
mysql_conn = mysql.connector.connect(**MYSQL_CONFIG)
pg_engine = create_engine(PG_CONN)
for table in TABLES:
print(f"Ingesting: {table}")
df = pd.read_sql(f"SELECT * FROM {table}", mysql_conn)
df["ingested_at"] = pd.Timestamp.now()
df["source_system"] = "mysql_olist_prd"
df.to_sql(table, pg_engine, schema='bronze_olist',
if_exists='replace', index=False)
print(f" -> {len(df):,} rows loaded")
mysql_conn.close()
print("Bronze ingestion complete.")
Step 3: Silver layer standardization
Just TRIM() on text columns, nothing more aggressive than that:
sql
INSERT INTO silver_olist.customers
SELECT
TRIM(customer_id),
TRIM(customer_unique_id),
customer_zip_code_prefix,
TRIM(customer_city),
TRIM(customer_state),
ingested_at,
source_system
FROM bronze_olist.customers;
INSERT INTO silver_olist.order_items
SELECT
TRIM(order_id),
order_item_id,
TRIM(product_id),
TRIM(seller_id),
TRIM(shipping_limit_date),
price,
freight_value,
ingested_at,
source_system
FROM bronze_olist.order_items;
The principle worth keeping in mind here: every row from Bronze survives into Silver. Filtering happens later, deliberately, in Gold, not accidentally at this stage.
Step 4: Gold layer, the actual star schema
Dimension tables
dim_seller (3,095 rows):
sql
CREATE TABLE IF NOT EXISTS gold_olist.dim_seller
(
seller_sk SERIAL PRIMARY KEY,
seller_id TEXT,
seller_zip_code_prefix BIGINT,
seller_city TEXT,
seller_state TEXT
);
INSERT INTO gold_olist.dim_seller
(seller_id, seller_zip_code_prefix, seller_city, seller_state)
SELECT seller_id, seller_zip_code_prefix, seller_city, seller_state
FROM silver_olist.sellers;
dim_payment_type (4 rows):
sql
CREATE TABLE IF NOT EXISTS gold_olist.dim_payment_type
(
payment_type_sk SERIAL PRIMARY KEY,
payment_type TEXT
);
INSERT INTO gold_olist.dim_payment_type (payment_type)
SELECT DISTINCT payment_type
FROM silver_olist.order_payments
ORDER BY payment_type;
dim_date is generated, not sourced from the transaction data at all. That's intentional: it gives you a complete calendar (including days with zero orders) rather than one shaped by whatever happened to have activity.
dim_customer and dim_product round things out, covering who bought and what they bought (including category name translations).
Fact tables
fact_order_items is the primary fact table, about 112,650 rows, one row per line item:
sql
CREATE TABLE IF NOT EXISTS gold_olist.fact_order_items
(
order_item_sk SERIAL PRIMARY KEY,
order_id TEXT,
order_item_id BIGINT,
date_sk INT,
customer_sk INT,
product_sk INT,
seller_sk INT,
order_status TEXT,
delivery_status TEXT,
price DOUBLE PRECISION,
freight_value DOUBLE PRECISION,
total_item_value DOUBLE PRECISION,
review_score BIGINT,
actual_delivery_days INT
);
INSERT INTO gold_olist.fact_order_items
(order_id, order_item_id, date_sk, customer_sk, product_sk,
seller_sk, order_status, delivery_status, price, freight_value,
total_item_value, review_score, actual_delivery_days)
SELECT
oi.order_id,
oi.order_item_id,
TO_CHAR(TO_TIMESTAMP(o.order_purchase_timestamp,
'YYYY-MM-DD HH24:MI:SS'), 'YYYYMMDD')::INT AS date_sk,
dc.customer_sk,
dp.product_sk,
ds.seller_sk,
o.order_status,
CASE
WHEN o.order_status IN ('cancelled','unavailable') THEN 'CANCELLED'
WHEN o.order_delivered_customer_date IS NULL THEN 'PENDING'
WHEN o.order_delivered_customer_date > o.order_estimated_delivery_date THEN 'LATE'
ELSE 'ON_TIME'
END AS delivery_status,
oi.price,
oi.freight_value,
ROUND((oi.price + oi.freight_value)::NUMERIC, 2) AS total_item_value,
orv.review_score,
CASE WHEN o.order_delivered_customer_date IS NOT NULL
THEN DATE_PART('day',
TO_TIMESTAMP(o.order_delivered_customer_date,
'YYYY-MM-DD HH24:MI:SS') -
TO_TIMESTAMP(o.order_purchase_timestamp,
'YYYY-MM-DD HH24:MI:SS'))::INT
ELSE NULL END AS actual_delivery_days
FROM silver_olist.order_items oi
JOIN silver_olist.orders o ON oi.order_id = o.order_id
JOIN silver_olist.customers c ON o.customer_id = c.customer_id
JOIN gold_olist.dim_customer dc ON c.customer_id = dc.customer_id
JOIN gold_olist.dim_product dp ON oi.product_id = dp.product_id
JOIN gold_olist.dim_seller ds ON oi.seller_id = ds.seller_id
LEFT JOIN silver_olist.order_reviews orv ON oi.order_id = orv.order_id;
fact_order_payments sits alongside it, about 103,886 rows, one row per payment record (an order can have more than one payment):
sql
CREATE TABLE IF NOT EXISTS gold_olist.fact_order_payments
(
payment_sk SERIAL PRIMARY KEY,
order_id TEXT,
date_sk INT,
customer_sk INT,
payment_type_sk INT,
payment_sequential BIGINT,
payment_installments BIGINT,
payment_value DOUBLE PRECISION
);
INSERT INTO gold_olist.fact_order_payments
(order_id, date_sk, customer_sk, payment_type_sk,
payment_sequential, payment_installments, payment_value)
SELECT
op.order_id,
TO_CHAR(TO_TIMESTAMP(o.order_purchase_timestamp,
'YYYY-MM-DD HH24:MI:SS'), 'YYYYMMDD')::INT AS date_sk,
dc.customer_sk,
dpt.payment_type_sk,
op.payment_sequential,
op.payment_installments,
op.payment_value
FROM silver_olist.order_payments op
JOIN silver_olist.orders o ON op.order_id = o.order_id
JOIN silver_olist.customers c ON o.customer_id = c.customer_id
JOIN gold_olist.dim_customer dc ON c.customer_id = dc.customer_id
JOIN gold_olist.dim_payment_type dpt ON op.payment_type = dpt.payment_type;
Why the design choices matter
Surrogate keys (the SERIAL integers) decouple the warehouse from source-system IDs. That decoupling is what makes slowly changing dimensions and clean indexing possible later; tying fact tables directly to source primary keys works fine until the source system changes something, and then it doesn't.
Grain matters more than it sounds like it should: deciding upfront that fact_order_items is "one row per line item" (not per order, not per shipment) is what keeps every downstream aggregation correct. Get the grain wrong and every SUM() built on top of it quietly lies.
Conformed dimensions (the same dim_date, dim_customer, and dim_product used across both fact tables) are what let you join revenue and payment data together consistently, rather than ending up with two versions of "customer" that don't quite agree.
Wiring up the BI layer
Apache Superset connects straight to the gold_olist schema and gives you, with no further transformation needed:
Total revenue as a big-number tile with a trendline, monthly revenue trend as a line chart, revenue by product category as a bar chart, top 10 categories as a horizontal bar chart, orders by state as a map of Brazil, and average delivery days by state as a heatmap table.
Worth doing before you trust any of it: check that the numbers coming out of Gold match the raw MySQL source. If Total Revenue in Superset doesn't equal SUM(price + freight_value) computed directly against the original tables, something's wrong upstream in Silver or Gold, not in Superset.
Takeaways
Bronze exists to preserve raw ingestion, untouched. Silver standardises lightly and keeps every row. Gold is where the real modelling decisions (grain, surrogate keys, conformed dimensions) actually happen. Get those three boundaries right, and the BI tool on top becomes almost mechanical to wire up.
Full implementation, including the DDL for every table: (https://github.com/aravindrajamani/Mastering-Fact-Dimension-Tables-with-Medallion-Architecture-for-BI-Reports) For detailed explanation visit (https://www.geopits.com/blog/medallion-architecture-explained-a-hands-on-guide-to-star-schema-for-bi-reporting)
Top comments (0)