This project analyses a Toy Store e-commerce dataset supplied as a set of CSV files (orders, order_items, order_item_refunds, products, website_sessions, and website_pageviews). The goal was to load the raw data into an SQL environment, use SQL to answer a defined set of business questions, and then build an interactive Power BI dashboard on top of the same data model so the findings could be explored visually and filtered by date.
The work spanned three stages: (1) setting up a local SQL environment in Visual Studio Code using SQLite, (2) writing and validating SQL queries to answer specific business questions, and (3) building a relational data model and dashboard in Power BI Desktop.
The dataset was originally provided as six separate CSV files. Rather than working with SQL Server (which requires a server instance, authentication, and additional overhead), SQLite was chosen as the database engine, since it runs entirely off a single local file with no server setup required.
A Python script was used to load each CSV into its own table inside a single SQLite database file (toystory.db):
import pandas as pd import sqlite3 import glob, os conn = sqlite3.connect('toystory.db') for file in glob.glob('*.csv'): table_name = os.path.splitext(os.path.basename(file))[0] df = pd.read_csv(file) df.to_sql(table_name, conn, if_exists='replace', index=False) print(f"Loaded {table_name}: {len(df)} rows") conn.close()
This produced six tables inside toystory.db: orders, order_items, order_item_refunds, products, website_sessions, and website_pageviews.
The SQLTools extension (with its SQLite driver) was installed in VS Code to provide a proper query editor and results pane. A notable setup issue arose here: the extension's automatic Node.js runtime detection hung indefinitely on first connection. This was resolved by:
Once resolved, an SQLite (Node) connection was created pointing directly at toystory.db, giving a working query editor connected to all six tables.
Before building any joins, each table's primary key was checked for duplicates using a GROUP BY / HAVING pattern, for example:
SELECT order_id, COUNT() AS count FROM orders GROUP BY order_id HAVING COUNT() > 1;
A key finding during this check was distinguishing expected repetition from genuine duplication. For example, website_session_id repeats naturally within website_pageviews (one session can have many pageviews) because it is a foreign key there, not a primary key. Only primary keys were expected to be unique; foreign key repetition was correctly left alone.
Null values in website_sessions (utm_source, utm_campaign, utm_content) were also investigated. These were determined to be meaningful, not missing data: they are null whenever a visit did not arrive through a tracked marketing link (i.e. direct traffic or organic search), so they were left in place rather than removed.