Star vs Snowflake Schemas | Dataford Interview Questions

Can you describe your experience with data warehousing concepts, including star and snowflake schemas?

For the practical portion, use the provided warehouse tables to demonstrate how transaction facts can be analysed with customer and product dimensions. Return only combinations whose completed transaction value exceeds 1,000.

Output

One row per customer segment and product category
Columns: customer_segment, product_category, total_value
Sort by total_value descending, then customer_segment and product_category ascending, with null categories last

Schema

fact_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer dimension key
product_id	INT	Product dimension key
transaction_date	DATE	Transaction posting date
status	VARCHAR(20)	Transaction processing status
amount	DECIMAL(12,2)	Transaction value

dim_customer

Column	Type	Description
customer_idPK	INT	Unique customer dimension key
customer_name	VARCHAR(100)	Customer name
customer_segment	VARCHAR(40)	Customer segment

dim_product

Column	Type	Description
product_idPK	INT	Unique product dimension key
product_name	VARCHAR(100)	Product name
product_category	VARCHAR(50)	Product category

Your question is Star vs Snowflake Schemas. 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