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.

HardTechnical
34 practiced
A BI query needs to compute median order value per customer from a large fact table. The DB lacks a native median aggregate and computing exact medians is expensive. Propose approximate schema or precomputation strategies to support both approximate and exact medians on demand.
EasyTechnical
41 practiced
Describe Slowly Changing Dimension (SCD) types 0, 1, 2, and 3. For each type give a concrete use case where it is appropriate to use.
EasyTechnical
31 practiced
You are designing a transactional database for a small e-commerce application. Describe the core tables and their relationships (orders, customers, products, order_items, payments, shipments). What normalization level would you apply initially and why? Include primary keys and foreign keys in your description.
HardTechnical
50 practiced
You inherit a reporting schema where many dimension tables have nullable surrogate foreign keys to a central 'master' table. Query performance suffers from many LEFT JOINs producing repeated nulls. Propose schema and query-level optimizations to reduce join cost and simplify reporting queries.
MediumTechnical
35 practiced
A legacy schema stores orders with denormalized customer and product details directly in the orders table to simplify reads. The table has grown to billions of rows and writes are slowing. What step-by-step strategy would you propose to evaluate and refactor the schema for both performance and maintainability?

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.