Star vs Snowflake Benefits | Dataford Interview Questions

What are the benefits of using star schemas versus Snowflake schemas for analytics?

For the practical portion, write a query that produces a category-level analytics result from dimensional sales data. The result should retain categories without qualifying sales so the modeling and reporting implications can be discussed.

Output

One row per category, including categories with no qualifying sales.
Columns: category_name, total_units, and total_revenue.
Include sales from 2025-01-01 through 2025-03-31, inclusive. Sort by total_revenue descending, then category_name ascending.

Schema

dim_category

Column	Type	Description
category_idPK	INT	Unique category identifier
category_name	VARCHAR(100)	Analytics category name
category_group	VARCHAR(100)	Higher-level category grouping

dim_product

Column	Type	Description
product_idPK	INT	Unique product identifier
product_name	VARCHAR(150)	Product or service offering name
category_id	INT	Associated category identifier
product_status	VARCHAR(30)	Product lifecycle status

fact_sales

Column	Type	Description
sale_idPK	INT	Unique sales transaction identifier
product_id	INT	Sold product identifier
sale_date	DATE	Date of the sale
quantity	INT	Units sold
net_amount	NUMERIC(12,2)	Net sale amount after adjustments

Your question is Star vs Snowflake Benefits. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem