Data Warehouse Design & Implementation
342 words
2 min read
Visual companion
Python
Type and operator map
Python Week 1: the first filter for runtime behavior
View
Revision summary
What this note is really saying
Short form
# Data Warehouse Design & Implementation ## 🎯 Learning Objectives - Design star schema and snowflake schema dimensional models - Implement slowly changing dimensions (SCD Types 0-6) - Design fact tables (transactional, periodic snapshot, accumulating) - Optimize data warehouse performance ## 📖 Core Content ### 8.1...
Data Warehouse Design & Implementation
🎯 Learning Objectives
- Design star schema and snowflake schema dimensional models
- Implement slowly changing dimensions (SCD Types 0-6)
- Design fact tables (transactional, periodic snapshot, accumulating)
- Optimize data warehouse performance
📖 Core Content
8.1 Dimensional Modeling
Kimball methodology (bottom-up):
- Select business process (e.g., sales)
- Declare grain (e.g., one row per transaction)
- Identify dimensions (who, what, where, when)
- Identify facts (measures) Fact table types:
| Type | Description | Example | Grain |
|---|---|---|---|
| Transactional | Each transaction row | Sales fact | Per line item |
| Periodic Snapshot | Regular summary | Monthly inventory | Per month per product |
| Accumulating Snapshot | Process tracking | Order fulfillment | Per order (multiple dates) |
8.2 Slowly Changing Dimensions
| SCD Type | Method | Use Case |
|---|---|---|
| 1 | Overwrite old value | No history needed |
| 2 | Add new row with effective dates | Full history |
| 3 | Add "old value" column | Limited history |
| 6 | Hybrid (1+2+3) | Combine approaches |
8.3 Performance Optimization
| Technique | Description | Impact |
|---|---|---|
| Indexing | Bitmap indexes on dimension keys | Fast join performance |
| Partitioning | Split large tables by date | Query pruning |
| Materialized views | Pre-compute aggregate queries | Instant aggregations |
| Columnar storage | Store by column, not row | Compression, scan speed |
| Data compression | Dictionary, run-length, delta | 5-10× storage reduction |
📝 Practice Questions
Q1: When would you use a snowflake schema instead of star schema?Snowflake normalizes dimension tables (e.g., Product table linked to Category table linked to Department table). Use when: (1) dimension tables are very wide (> 20 columns), (2) hierarchy management is important, (3) storage cost is a concern. Otherwise prefer star schema — simpler queries, faster performance. Q2: What is the grain of a fact table?Grain defines what each row represents. "Sales fact at daily product store level" means each row = sales of one product in one store on one day. Once designed, grain cannot change without reloading the fact table. Always declare the most atomic grain possible — you can always aggregate up, but can't break down. Q3: How does an accumulating snapshot fact table work?Used for processes with a defined start and end (order fulfillment, claim processing). One row per process with multiple date foreign keys (order_date, ship_date, delivery_date). Dates update as the process advances. Unlike transactional (append-only) and periodic (scheduled), accumulating snapshots update existing rows. Join Discord PreviousBI Tools