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
Design a schema and approach for storing event attributes where events have a semi-structured set of key-value attributes that vary per event type. Discuss trade-offs between EAV (entity-attribute-value), JSONB/VARIANT columns, and separate typed tables in terms of queryability, indexing, and schema evolution.
MediumTechnical
56 practiced
Provide a step-by-step approach to evaluate and improve an index strategy on a production database with frequent schema changes. Include how you'd collect evidence, prioritize indexes, and safely deploy index changes.
EasyTechnical
44 practiced
What is database normalization aimed to prevent? List three common anomalies that normalization addresses and give a short example of each.
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.
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.

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.