Modeling for Performance
Performance designed in, not debugged after
Fast models follow rules: star shape, low cardinality, filtered early, minimal columns, snapshots for point-in-time. Performance is designed in, not tuned after.
What you will learn
- Apply the model rules that dominate query speed
- Reduce cardinality and column count where it matters
- Use snapshots and partitioning for point-in-time workloads
What is this?
Modeling for performance means making the query engine's job easy at design time: star shapes prune joins, low-cardinality columns compress and scan fast, filters push as early as possible, and tables carry only needed columns. Snapshots (slow-changing state) serve point-in-time looks without heavy scans. Performance is a property of the model, not a last-minute knob.
Why do you need it?
- A good shape makes even naive queries fast; a bad shape makes tuning a treadmill.
- Columnar engines reward low cardinality and few columns.
- Point-in-time answers need snapshot discipline, not runtime tricks.
- Interviewers test 'what model would you build for speed', not 'what index'.
Mental model
A supermarket arranged for fast checkout
A performance-designed model
Fast checkout isn't a faster cashier — it's the store designed for it: well-labeled short aisles (star dims), barcodes (low-cardinality keys), and stock laid out so the scan doesn't backtrack. Same for models: the layout IS the performance.
How it works
- Star shape: few wide dims join to facts; no snowflake chains.
- Low cardinality: replace high-cardinality strings with keys; compress better.
- Column pruning: store only columns reports actually use.
- Early filtering: push date/region predicates to the source or partition level.
- Snapshots: daily/monthly state tables give point-in-time without replay.
The performance levers at model design time.
Examples
Design-time performance decisions
fact_orders (modeled, pruned): order_id, cust_key, prod_key, date_key, amount, qty -- string columns and IDs moved to dims / dropped dim_customer: cust_key (INT surrogate), region_key (SMALLINT), segment_key Measures always over SUM(amount); no giant ROW looks. Partition by month; snapshot table for inventory at month-end.Tiny, typed keys and few columns are cheap to compress and scan; partitions and snapshots localize point-in-time work.
In production
- A 2B-row fact serves a 5-second exec query because date partitions + star dims prune first.
- An inventory model keeps an end-of-month snapshot table, killing week-long replay scans.
- A customer model maps long IDs to compact keys, halving model size and doubling query speed.
Best practices
- Keep the model star-shaped and the facts 'string-free'.
- Reduce column cardinality: keys over free text, smallint over int where safe.
- Prune columns each release; add only what reports demand.
- Partition by the date you filter; keep a snapshot pattern for state.
Common mistakes
- High-cardinality text keys (emails, GUIDs) inside the fact.
- Loading 'everything just in case' till the model crawls.
- No partitioning: every query scans the full history.
- Point-in-time via replay, rebuilding state per query instead of snapshotting.
Debugging tips
- Slow scan → check the last filter; is date pruned? partition used?
- Compression poor → inspect cardinality; replace text with keys.
- Memory high → prune unused columns and reduce fact width.
Interview checkpoint
Hands-on lab
- Demonstrate the cost of high-cardinality text vs keys
- Show column pruning reduces scan cost
- Partition and snapshot for point-in-time speed
Prove the Performance Rules with Data
45 min · advanced
Quick quiz
Which design choice most directly shrinks query scan cost?
Point-in-time analytics is best served by reconstructing state per query.