Star schema and data warehousing

Star schemas are designed for analytics. A central fact table (sales events with metrics) connects to dimension tables (who, what, when, where). This denormalized design trades storage for query speed, which is exactly what dashboards need.

Star schema: fact + dimensions

A star schema has fully denormalized dimensions (one table per dimension). A snowflake schema normalizes dimensions into sub-tables. Star schemas are simpler and faster to query; snowflake schemas save storage but require more JOINs. Most modern warehouses prefer star schemas.

star_schema.sql
sql
-- Create dimension tables
CREATE TABLE dim_date AS
SELECT DISTINCT
  order_purchase_timestamp::DATE AS date_id,
  EXTRACT(YEAR FROM order_purchase_timestamp) AS year,
  EXTRACT(QUARTER FROM order_purchase_timestamp) AS quarter,
  EXTRACT(MONTH FROM order_purchase_timestamp) AS month,
  EXTRACT(DOW FROM order_purchase_timestamp) AS day_of_week,
  TO_CHAR(order_purchase_timestamp, 'Day') AS day_name
FROM orders;

CREATE TABLE dim_customer AS
SELECT DISTINCT
  customer_id,
  customer_city,
  customer_state
FROM customers;

Dimension tables are denormalized: all attributes in one table, no JOINs needed. dim_date is especially useful for time-based analysis.

With dimensions in place, the fact table ties everything together. It holds measurable values (price, freight) and foreign keys pointing to each dimension.

fact_table.sql
sql
-- Create and populate fact table
CREATE TABLE fact_sales AS
SELECT
  oi.order_id,
  oi.order_item_id,
  o.customer_id,
  oi.product_id,
  oi.seller_id,
  o.order_purchase_timestamp::DATE AS date_id,
  oi.price,
  oi.freight_value,
  o.order_status
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id;

-- Query the star schema: revenue by quarter by category
SELECT
  d.year,
  d.quarter,
  p.product_category_name,
  SUM(f.price) AS revenue
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_product p ON f.product_id = p.product_id
GROUP BY d.year, d.quarter, p.product_category_name
ORDER BY d.year, d.quarter, revenue DESC;

Fact tables contain metrics (price, freight) and foreign keys to dimensions. Queries JOIN fact to dimensions for slicing and dicing.

In a star schema, dimension tables are denormalized (flat). In a snowflake schema, dimensions are further normalized into sub-dimensions. Star is simpler and faster for queries. Snowflake saves storage but requires more JOINs. Most data warehouses prefer star schemas.

Matching exercise: Star schema concepts

Loading practice…

Quiz: Quiz

Loading practice…