Data Modeling and Storage: grain, keys, history, layout
What this domain tests
Data Modeling and Storage tests your ability to choose a schema and physical layout that answer the questions the business will ask, without corrupting grain or losing history. Questions cover normalization, dimensional models, keys, slowly changing dimensions, indexing, partitioning, and storage format tradeoffs.
Core rules
- Define the grain first: what one row represents. Every fact table must have a single, stated grain.
- Use surrogate keys in warehouse dimensions; keep natural and source keys for lineage and deduplication.
- Normalize for operational integrity (3NF), denormalize for analytical read performance (star schema).
- Model history deliberately: Type 1 overwrites, Type 2 adds a row with valid_from/valid_to and a current flag, Type 3 keeps a prior-value column.
- Store dates as date types, timestamps as UTC, money as decimal with currency, and IDs as strings when leading zeros matter.
- Partition large fact tables by event date; cluster or sort by common filters; index foreign keys and high-selectivity predicates.
- Choose columnar formats (Parquet, ORC) for scan-heavy analytics and row stores for high-concurrency point reads.
Worked example
You model web events. The fact table grain is one event per event_id. Dimensions are date, user, page, and campaign. The user dimension is Type 2 so segment changes are historized; the fact stores the user_sk valid at event time. You partition by event_date and cluster by user_sk. Reasoning: event_id gives a deterministic deduplication key; Type 2 preserves the segment the user had when the event occurred, avoiding retroactive attribution errors; partitioning by date makes backfills and retention deletes cheap; clustering by user reduces scanned bytes for cohort queries. A late-arriving event is inserted into its event_date partition and reconciled by event_id, not by overwriting the latest row.
Traps that cost marks
- Mixing grains, such as order-level and line-item rows in one fact table.
- Reusing a natural key after source-system reuse, which silently reassigns history.
- Storing dates as strings and breaking range filters.
- Over-normalizing an analytics schema into dozens of joins.
- Forgetting late-arriving facts or Type 2 effective dates.