Reshape the shop database into a star schema

applied · 60 min · Objective 1.1

Task

Build a small data warehouse from the transactional database: a fact table of sales and three dimension tables for customers, products and dates. Then prove the warehouse still says what the source says, by row count and by total.

Steps

  1. Create a new database lab/star/star.db, leaving data/shop.db untouched.
  2. Create dim_customer (customer_id, region, segment) and dim_product (product_id, name, category, unit_price) from the source tables.
  3. Create dim_date with one row for every day of 2025, with columns date_key (YYYY-MM-DD), month (1-12) and weekday (0-6).
  4. Create fact_sales with one row per order: order_id, date_key, customer_id, product_id, quantity and net_amount, where net_amount is quantity times unit price times (1 minus discount_pct / 100).
  5. Write lab/star/check.txt with the lines fact rows: N, date rows: N and net total: N, the total rounded to two decimal places.

Verify

These checks run in a POSIX shell with Python 3: Terminal on macOS or Linux, and on Windows Git Bash (it comes with Git for Windows) or WSL. On Windows, python3 is often a Microsoft Store placeholder that prints “Python was not found”; run alias python3=py in Git Bash first.

grep -E '^(fact rows|date rows|net total): ' lab/star/check.txt
python3 -c "import sqlite3; c=sqlite3.connect('lab/star/star.db'); print(round(c.execute('SELECT SUM(net_amount) FROM fact_sales').fetchone()[0], 2))"
python3 -c "import sqlite3; c=sqlite3.connect('lab/star/star.db'); print(c.execute('SELECT COUNT(*) FROM fact_sales f JOIN dim_customer c ON f.customer_id = c.customer_id').fetchone()[0], 'fact rows join a customer')"

The fact table has 2000 rows, the date dimension 365, and the net total is 353816.74 both in the check file and straight from the database. Every fact row joins a customer. The date dimension has a row for every day even though orders fell on only 364 of them: a dimension describes the calendar, not the activity.

Notes

The total includes the three orders with negative quantities. They are left in on purpose here: a warehouse load should not silently drop rows, and the reconciling lesson shows how the same data yields three different "revenue" figures.

This is an independent study companion for CompTIA Data+ DA0-002 and is not produced by or endorsed by CompTIA.