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

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.

MediumTechnical

39 practiced

Explain columnar vs row-oriented storage models. For which types of analytical queries is columnar storage significantly better, and why?

HardTechnical

43 practiced

A BI team reports that joins between a large fact table and a high-cardinality dimension table are causing memory pressure on the analytic cluster. Propose schema-level and engine-level mitigations to reduce memory usage for large joins.

HardSystem Design

32 practiced

A global company needs to shard its customer table across regions. Propose a logical sharding key and schema-level strategies for joins with orders that reference customers across shards. Discuss handling cross-shard transactions and referential integrity.

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.

Join thousands of developers preparing for their dream job.