Data Warehouse Design

Data Warehouse Design

When to Use

Use this skill when:

• User asks to design a data warehouse or analytical data store
• User wants to create a dimensional model with fact and dimension tables
• User needs to decide between star schema and snowflake schema
• User wants to restructure an existing analytics schema for better query performance
• User asks about grain, slowly changing dimensions, or conformed dimensions

Do NOT use when:

• User wants to design an operational (OLTP) database schema (use data-schema-design)
• User wants to design the ETL pipelines that load the warehouse (use etl-pipeline-design)
• User wants a dashboard layout for warehouse data (use bi-dashboard-spec)
• User wants to set up streaming data ingestion (use streaming-data-architecture)

Process

1. Identify the business process to model. Ask the user:

   • What business process generates the data? (sales transactions, website visits, support tickets, manufacturing runs)
   • What are the key business questions the warehouse must answer?
   • Who queries the warehouse? (analysts, BI tools, ML pipelines)
   • What is the expected query pattern? (time-series aggregation, dimensional slicing, ad-hoc exploration)
   • What source systems feed the warehouse?

2. Define the grain. The grain is the most critical decision:

   • Grain = the level of detail each row in a fact table represents
   • Ask: "What does one row represent?" (one transaction, one daily snapshot, one click event, one line item)
   • The grain must be the most atomic level available -- you can always aggregate up, but you cannot disaggregate
   • Common grain patterns:
     • Transaction grain: One row per business event (order placed, payment received)
     • Periodic snapshot grain: One row per entity per time period (daily account balance, monthly revenue by product)
     • Accumulating snapshot grain: One row per entity lifecycle (order lifecycle from placed to shipped to delivered)

3. Identify fact tables. For each measurable business process:

   • Name the fact table (fact_[process_name])
   • State the grain explicitly
   • List the measures (numeric values that can be aggregated: revenue, quantity, duration, count)
   • Classify each measure:
     • Additive: Can be summed across all dimensions (revenue, quantity)
     • Semi-additive: Can be summed across some dimensions but not time (account balance)
     • Non-additive: Cannot be summed (ratios, percentages) -- store the components and calculate at query time

4. Identify dimension tables. For each descriptive context:

   • Name the dimension table (dim_[entity_name])
   • List the attributes (descriptive columns used for filtering and grouping)
   • Identify the natural key (business identifier) and surrogate key
   • Determine the SCD (Slowly Changing Dimension) type:
     • Type 1: Overwrite the old value (no history; simple; use for corrections)
     • Type 2: Add a new row with valid_from/valid_to dates (full history; use for attributes that change meaningfully)
     • Type 3: Add a column for the previous value (limited history; use for one-level comparison)

5. Choose the schema type.

   • Star schema: Fact table in the center, dimension tables radiating out. Each dimension is fully denormalized (one table per dimension, no sub-dimensions). Best for: simple queries, fast performance, most BI tools.
   • Snowflake schema: Dimensions are normalized into sub-dimensions (dim_product joins to dim_category joins to dim_department). Best for: storage efficiency, consistent hierarchies, complex dimension structures.
   • Default recommendation: Star schema unless there is a strong reason for snowflake (very large shared dimensions, strict storage constraints, or mandatory normalization policy).

6. Design conformed dimensions. Dimensions shared across multiple fact tables:

   • dim_date (always conformed -- every fact table references the same date dimension)
   • dim_customer (if multiple processes involve the same customers)
   • dim_product (if multiple processes involve the same products)
   • Conformed dimensions ensure consistent filtering and aggregation across fact tables

7. Produce the warehouse schema specification.

Output Format

## Data Warehouse Design: [Warehouse Name]

### Overview
- **Business process(es):** [What is being modeled]
- **Schema type:** [Star / Snowflake]
- **Primary consumers:** [Who queries this warehouse]
- **Source systems:** [What feeds it]

### Fact Tables

#### fact_[name]
- **Grain:** [One row = one _____]
- **Estimated volume:** [Rows per day, total rows]
- **Retention:** [How long data is kept]

| Column | Type | Measure Type | Description |
|--------|------|-------------|-------------|
| [surrogate_key] | BIGINT | -- | Degenerate or surrogate FK |
| [dim_key_1] | BIGINT | FK -> dim_[name].id | [Dimension reference] |
| [dim_key_2] | BIGINT | FK -> dim_[name].id | [Dimension reference] |
| [date_key] | INT | FK -> dim_date.date_key | [Date dimension reference] |
| [measure_1] | DECIMAL | Additive | [Description] |
| [measure_2] | DECIMAL | Additive | [Description] |
| [measure_3] | DECIMAL | Non-additive | [Description -- store components] |

### Dimension Tables

#### dim_[name]
- **SCD Type:** [1 / 2 / 3]
- **Estimated rows:** [Count]

| Column | Type | Description |
|--------|------|-------------|
| id | BIGINT | Surrogate key (auto-increment) |
| natural_key | [type] | Business identifier |
| [attribute_1] | [type] | [Description] |
| [attribute_2] | [type] | [Description] |
| valid_from | DATE | [SCD Type 2 only] Row effective start |
| valid_to | DATE | [SCD Type 2 only] Row effective end (9999-12-31 for current) |
| is_current | BOOLEAN | [SCD Type 2 only] Convenience flag |

#### dim_date (Conformed)
| Column | Type | Description |
|--------|------|-------------|
| date_key | INT | YYYYMMDD format integer key |
| full_date | DATE | Calendar date |
| day_of_week | VARCHAR(10) | Monday, Tuesday, etc. |
| month_name | VARCHAR(10) | January, February, etc. |
| quarter | VARCHAR(6) | Q1-2026, Q2-2026, etc. |
| fiscal_year | INT | Fiscal year (if different from calendar) |
| is_weekend | BOOLEAN | Saturday or Sunday |
| is_holiday | BOOLEAN | Company-defined holidays |

### Entity-Relationship Diagram

                +-------------+
                | dim_date    |
                +------+------+
                       |

+-------------+ +-------+-------+ +-------------+ | dim_[name1] +----+ fact_[name] +----+ dim_[name2] | +-------------+ +-------+-------+ +-------------+ | +------+------+ | dim_[name3] | +-------------+

### Design Decisions

| Decision | Choice | Rationale |
|----------|--------|-----------|
| Schema type | [Star/Snowflake] | [Why] |
| Grain | [Stated grain] | [Why this level of detail] |
| SCD strategy for dim_[name] | [Type 1/2/3] | [Why] |
| [Other decisions] | [Choice] | [Rationale] |

### Query Patterns

| Question | Query Approach | Tables Joined |
|----------|---------------|---------------|
| [Business question 1] | [GROUP BY dims, SUM measures] | [fact + dims] |
| [Business question 2] | [Filter + aggregate] | [Tables] |

Rules

1. NEVER create a fact table without explicitly stating the grain -- the grain determines everything else about the table
2. Every fact table must have at least one dimension foreign key and at least one measure -- a fact table with no measures is a dimension; a table with no dimension keys is a log
3. ALWAYS create a conformed dim_date dimension that every fact table references -- date is the most common query filter and must be consistent
4. Non-additive measures (ratios, percentages) must NOT be stored directly -- store the numerator and denominator as separate additive measures and calculate the ratio at query time
5. Surrogate keys (auto-increment integers) must be used for all dimension primary keys -- natural keys change, are inconsistent across sources, and are often strings that join slowly
6. SCD Type 2 dimensions must include valid_from, valid_to, and is_current columns -- without valid_to, point-in-time queries are impossible
7. NEVER mix different grains in the same fact table -- if you need both transaction-level and daily snapshot, create two fact tables
8. Star schema is the default recommendation unless the user has a documented reason for snowflake -- star is faster to query and easier to understand
9. Every dimension attribute used as a filter in the top 5 most common queries must be indexed
10. The warehouse design must include estimated row counts and growth rates -- without volume estimates, performance implications cannot be assessed

Edge Cases

• Multiple business processes sharing dimensions: Use conformed dimensions (same dim_customer, dim_product across all fact tables). Do not create process-specific copies of shared dimensions. This enables cross-process analysis (e.g., "customers who bought X and then contacted support").

• Very high cardinality dimension (millions of unique values): Consider a "junk dimension" for low-cardinality flag combinations, and a "mini-dimension" for frequently changing attributes. Example: dim_customer has 5M rows and the "age_band" attribute changes annually -- extract age_band into a mini-dimension to avoid Type 2 explosion.

• Real-time or near-real-time warehouse: Design a lambda architecture with two layers: a batch layer (standard dimensional model, updated daily) and a speed layer (append-only recent events, merged with batch at query time). Document the merge logic.

• No clear business process (user just has "a bunch of tables"): Start from the business questions, not the data. Ask: "What is the most important question you need to answer?" Then identify which tables contain the answer. Design the fact table from the answer backward to the source.

• Late-arriving facts or dimensions: Design the ETL to handle records that arrive after the expected period. For late facts: load them into the correct time period (not the load date). For late dimensions: if SCD Type 2, create a "placeholder" row and update when the dimension arrives.

Example

Input: "Design a data warehouse for our e-commerce business. We need to analyze sales by product, customer, time, and promotion. We also want to track inventory levels."

Output:

Data Warehouse Design: E-Commerce Analytics Warehouse

Overview

• Business processes: Sales transactions, Inventory snapshots
• Schema type: Star schema
• Primary consumers: BI analysts (Tableau), Finance team (monthly reports), Merchandising team (inventory planning)
• Source systems: Shopify (orders), ERP (inventory), CRM (customer profiles), Promotions engine

Fact Tables

fact_sales

• Grain: One row = one order line item (one product in one order)
• Estimated volume: 5,000 rows/day (~1.8M/year), retained indefinitely
• Retention: Permanent (partitioned by order_date_key, yearly)

fact_inventory_snapshot

• Grain: One row = one product per warehouse per day (daily periodic snapshot)
• Estimated volume: 10,000 rows/day (2,000 products x 5 warehouses), retained for 2 years
• Retention: 2 years rolling

Dimension Tables

dim_customer (SCD Type 2)

• SCD Type: Type 2 (track address and segment changes)
• Estimated rows: 100,000 (with ~10% having multiple historical rows)

dim_product (SCD Type 1)

• SCD Type: Type 1 (overwrite; product attributes are corrected, not tracked historically)
• Estimated rows: 2,000

dim_promotion (SCD Type 1)

• Estimated rows: 500

dim_warehouse (SCD Type 1)

• Estimated rows: 5

dim_date (Conformed)

Design Decisions

Query Patterns