InterviewStack.io LogoInterviewStack.io

Data Modeling and Schema Design Questions

Focuses on designing efficient, maintainable data schemas for transactional and analytical systems. Candidates should demonstrate understanding of normalization principles and normal forms, when and why to denormalize for performance, and schema design patterns for different use cases. Expect dimensional modeling topics including fact and dimension tables, star and snowflake schemas, grain definition, slowly changing dimensions, and strategies for handling historical data. The topic also includes trade offs between online transaction processing and online analytical processing designs, query performance considerations, indexing and partitioning strategies, and the ability to evaluate and improve existing schemas to meet business requirements and scale.

MediumTechnical
54 practiced
A data pipeline writes to a warehouse where fact and dimension tables are stored in a columnar format. The team needs to support fast lookups of a small subset of rows (point selects) as well as large scans. What schema and physical design choices reduce latency for point selects without harming scan performance?
MediumTechnical
38 practiced
Given this simple schema for product reviews:
reviews(review_id, product_id, user_id, rating, comment, created_at)
A customer asks for a leaderboard of top 10 products by average rating in the last 30 days. Propose schema-level changes or indexes to make this query fast under heavy write load, explaining your choices.
HardTechnical
43 practiced
An analytical query scans a partitioned fact table but isn't benefiting from partition pruning. Given the query and partitioning scheme below, identify why pruning fails and propose fixes.
Partitioning: orders partitioned by RANGE(order_date) monthlyQuery: SELECT product_id, SUM(amount) FROM orders WHERE order_date >= '2023-01-15' AND order_date < '2023-02-10' GROUP BY product_id;
Assume order_date is stored as a string in 'YYYY-MM-DD' format.
HardTechnical
30 practiced
A large fact table uses a composite clustered primary key (customer_id, order_id, item_id). Queries show poor locality for heavy customer-centric analytics. Propose changes to physical schema and indexing to improve locality for customer analytics while preserving OLTP performance.
HardSystem Design
32 practiced
You are asked to design a schema for a real-time analytics dashboard that needs near-real-time metrics (within seconds) and supports ad-hoc drilldowns. Outline a hybrid architecture and schema choices to meet low-latency ingestion and flexible querying.

Unlock Full Question Bank

Get access to hundreds of Data Modeling and Schema Design interview questions and detailed answers.

Sign in to Continue

Join thousands of developers preparing for their dream job.