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.

EasyTechnical

0 practiced

Explain differences between OLTP and OLAP systems focusing on data modeling and schema design: normalization level, expected query types, typical storage formats, and performance goals. Give two concise examples of schema choices appropriate for each (OLTP and OLAP) and explain why.

EasyTechnical

0 practiced

You're working with an OLTP transactional schema for an online storefront. Analysts report that a frequently run 'orders-by-customer' report is slow because it joins many normalized tables. Explain when and why you would denormalize parts of the schema for reporting. Provide two concrete denormalization patterns, the trade-offs (consistency, storage, maintainability), and how you'd keep denormalized data correct.

HardTechnical

0 practiced

Analyze the following simplified slow query and execution plan snippet and recommend schema, index, or partitioning changes to improve performance:

Query:

SELECT u.user_id, COUNT(*) AS events
FROM events e
JOIN users u ON e.user_id = u.id
WHERE e.event_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY u.user_id
HAVING COUNT(*) > 1000;

Plan excerpt: Seq Scan on events (cost huge), Hash Join, Aggregate. No index on event_date or user_id.

Explain specific changes and why they help.

EasyTechnical

0 practiced

Define fact and dimension tables in dimensional modeling. Design a small star schema for a sales reporting use case with one fact table and at least three dimension tables. Explicitly state the grain of the fact table and give sample columns for each table.

EasyTechnical

0 practiced

Explain the difference between star and snowflake schemas. Describe a scenario where snowflaking a dimension (normalizing it) is beneficial and another scenario where a denormalized star schema is preferable.

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.