Retail sales data warehouse with a dashboard

The problem

A shop keeps every sale in one big spreadsheet. Nobody can answer simple questions such as which product earns the most, which country buys the most, or how sales change by month without redoing the work each time. You will load the file, clean it, model it as a star schema, and build a dashboard on top.

How the data moves

1. Raw CSV
2. Staging table
3. Clean table
4. Star schema
5. Dashboard

The data

The Online Retail II dataset from the UCI Machine Learning Repository is a common choice: about a million rows of real invoices from a UK shop. Any sales data with an invoice, product, quantity, price, customer and date works. Check the licence before you share a copy.

Week by week

1. Week 1Load the raw file into a staging table. Count rows, find NULLs and look at five random rows.
2. Week 2Clean it: remove cancelled invoices, fix negative quantities, trim text, and write down every rule you applied.
3. Week 3Design the star schema: one fact table of sales and dimension tables for product, customer and date. Say what one row of the fact table means.
4. Week 4Write the load script so it can run twice without duplicating rows. Add checks: row counts match, revenue matches the raw total.
5. Week 5Build the dashboard (revenue by month, top products, revenue by country) and write the README.

Starter code

A tiny star schema you can run now. Run it first, then grow it into the real project.

CREATE TABLE raw_sales (invoice VARCHAR, sku VARCHAR, product VARCHAR, qty INTEGER,
  unit_price DOUBLE, customer_id INTEGER, sold_on DATE, country VARCHAR);
INSERT INTO raw_sales VALUES
('A1','P10','Mug',2,150.0,1,DATE '2024-03-01','India'),
('A1','P11','Plate',1,300.0,1,DATE '2024-03-01','India'),
('A2','P10','Mug',1,150.0,2,DATE '2024-03-02','India'),
('A3','P12','Lamp',1,900.0,3,DATE '2024-03-02','UK'),
('A4','P11','Plate',3,300.0,2,DATE '2024-03-03','India');

CREATE TABLE dim_product AS
SELECT ROW_NUMBER() OVER (ORDER BY sku) AS product_key, sku, product
FROM (SELECT DISTINCT sku, product FROM raw_sales);

CREATE TABLE fact_sales AS
SELECT r.invoice, p.product_key,
       CAST(strftime(r.sold_on, '%Y%m%d') AS INTEGER) AS date_key,
       r.customer_id, r.qty, r.qty * r.unit_price AS revenue
FROM raw_sales r JOIN dim_product p ON p.sku = r.sku;

SELECT p.product, SUM(f.qty) AS units, SUM(f.revenue) AS revenue
FROM fact_sales f JOIN dim_product p USING (product_key)
GROUP BY p.product ORDER BY revenue DESC;

product | units | revenue
Plate   | 4     | 1200.0
Lamp    | 1     | 900.0
Mug     | 3     | 450.0

Replace the 5 sample rows with the real file and add dim_customer and dim_date the same way.

What to hand in

• A GitHub repository with the SQL, the load script and a README
• An entity diagram of the star schema
• A dashboard screenshot with three charts
• A short list of data quality checks and their results

Viva and interview questions

Practise saying each answer out loud. Short and honest beats long and vague.

How to stand out

• Add a slowly changing dimension for customers so you can show history
• Schedule the load with Airflow or a simple cron job
• Add a data quality report that runs after every load

Put it on your CV

Built a retail data warehouse (star schema) from 1M+ raw invoice rows using SQL and Python, with repeatable loads and row-count checks, and a dashboard showing revenue by month, product and country.

README outline

1. Problem and users
2. Architecture diagram
3. How to run it in 3 commands
4. Schema and grain
5. Data quality checks
6. What I would improve

Learn the pieces

• Star schema: facts, dimensions, grain
• Capstone: model a star from orders
• Deduplication and incremental upserts