Data Modeling
What to Know
Data modeling covers how data is structured for storage and analysis. Candidates should know normalization (1NF atomic values, 2NF no partial dependency on part of a composite key, 3NF no transitive dependency) and denormalization for performance. Understand dimensional modeling: fact tables hold measures and foreign keys, dimension tables hold descriptive attributes, and star versus snowflake schemas differ in whether dimensions are normalized. Know slowly changing dimensions: Type 1 overwrites, Type 2 adds a new row with validity dates and a current flag, Type 3 adds a previous-value column. Also grasp grain (what one row represents), primary and foreign keys, surrogate versus natural keys, and bridge tables for many-to-many relationships.
Traps
Questions often test grain confusion: mixing line-item and order-level measures in one fact table double-counts. Another trap is assuming a star schema is always better; snowflake reduces redundancy but adds joins. Type 2 dimensions require filtering on the current flag or date range, or reports will double-count history. Watch for natural keys that change (email, phone) versus stable surrogate keys. Referential integrity traps include orphan foreign keys and nullable keys in fact tables. Also know that conformed dimensions let you compare across fact tables consistently.
Worked Example
A retailer tracks product price changes. To preserve history, use a Type 2 dimension: each price change inserts a new product row with start date, end date, and is_current flag, while the fact table references the surrogate key valid at transaction time. Rule of thumb: if analysts need to report on values as they were at the event, use Type 2; if only the latest value matters, Type 1 is simpler. Always state the grain of a fact table before adding measures.