Data dictionary — the star schema

This page documents the tables produced by pipeline/prepare_star_schema.py into data/processed/. These are the tables Power BI loads.

Attribution (required by the data license)

Data: Brazilian E-Commerce Public Dataset by Olist, obtained via Kaggle (olistbr/brazilian-ecommerce), licensed CC BY-NC-SA 4.0. This project is non-commercial and does not redistribute the raw data.

Model shape

erDiagram
    dim_customers   ||--o{ fact_order_items : customer_id
    dim_customers   ||--o{ fact_orders      : customer_id
    dim_customers   ||--o{ fact_payments    : customer_id
    dim_customers   ||--o{ fact_reviews     : customer_id
    dim_date        ||--o{ fact_order_items : order_purchase_date
    dim_date        ||--o{ fact_orders      : order_purchase_date
    dim_products    ||--o{ fact_order_items : product_id
    dim_sellers     ||--o{ fact_order_items : seller_id

Each fact carries customer_id and order_purchase_date, so it links directly to dim_customers and dim_date — a deliberate denormalisation that keeps the model a clean star (no dimension-to-dimension "snowflake" hops).

Dimensions

dim_customers — one row per Olist customer_id

Column	Meaning
customer_id	Per-order customer key (facts join on this).
customer_unique_id	Stable person identifier across orders (use for distinct-customer counts).
customer_zip_prefix	First digits of the ZIP code.
customer_city, customer_state	Location labels.
customer_lat, customer_lng	Representative coordinates for maps.

dim_products — one row per product

Column	Meaning
product_id	Product key.
product_category	Category translated to English (unknown if missing).
product_name_length, product_description_length	Text lengths (source misspelling "lenght" fixed).
product_photos_qty	Number of listing photos.
product_weight_g, product_length_cm, product_height_cm, product_width_cm	Physical attributes.

dim_sellers — one row per seller

Column	Meaning
seller_id	Seller key.
seller_zip_prefix, seller_city, seller_state	Location.
seller_lat, seller_lng	Representative coordinates.

dim_date — one row per calendar day

Column	Meaning
date	The calendar date.
date_key	Integer key YYYYMMDD (e.g. 20180131).
year, quarter, quarter_label, month, month_name, year_month, day	Calendar parts.
day_of_week, day_name, is_weekend	Weekday helpers (Monday = 0).

Facts

fact_order_items — grain: one order line

The primary revenue fact. | Column | Meaning | |---|---| | order_id, order_item_id | Composite grain (degenerate keys). | | product_id, seller_id, customer_id | Dimension keys. | | order_status, order_purchase_date | Denormalised order context. | | shipping_limit_date | Seller's shipping deadline. | | item_price | Price of the item (revenue). | | freight | Shipping cost for the item. |

fact_orders — grain: one order

Delivery-performance columns computed once in the pipeline. | Column | Meaning | |---|---| | order_id, customer_id, order_status, order_purchase_date | Order identity/context. | | order_approved_at, order_delivered_customer_date, order_estimated_delivery_date | Key timestamps. | | delivery_days | Actual days from purchase to delivery. | | estimated_delivery_days | Promised delivery window in days. | | delivery_delay_days | Delivered − estimated (positive = late). | | is_delivered | Whether status is delivered. | | is_on_time | For delivered orders: delivered on/before estimate. |

fact_payments — grain: one payment record

Column	Meaning
order_id, payment_sequential	Grain (an order may have several payments).
payment_type	e.g. credit_card, boleto, voucher.
payment_installments	Number of installments.
payment_value	Amount of this payment.
customer_id, order_purchase_date	Denormalised context.

fact_reviews — grain: one review

Column	Meaning
review_id, order_id	Identity.
review_score	1–5 rating.
review_creation_date, review_answer_timestamp	Review timestamps.
customer_id, order_purchase_date	Denormalised context.