Search the docs, videos, blog, apps, teams, skills and release notes.
At a glance
What it is
Designs a data warehouse structure by selecting star or snowflake schema, defining fact tables and dimension tables, specifying grain for each fact table, and producing the entity-relationship description.
A skill is a written procedure a bot loads when a job calls for it. This one is a single file, SKILL.md, and the whole file is on this page.
The whole file, exactly as it downloads. Read it before you rely on it.
SKILL.md
345 lines · 2,804 words
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
Designs a data warehouse structure by selecting star or snowflake schema, defining fact tables and dimension tables, specifying grain for each fact table, and producing the entity-relationship description.
Nothing. It ships with Murage, which is open source. Your bots run on the AI plan you already have.
Yes. Open it in Settings → Skills to read, edit or duplicate it. Skill Guard scans it again after every edit.
Download the app, connect the AI you already pay for, and tell your Chief of Staff what needs doing. Plan on about ten minutes from install to a working team.
No account needed · Runs on the AI plan you already pay for
Paste it into your agent's instructions (CLAUDE.md, AGENTS.md or a custom GPT), or add it to a bot in Murage.
Ask Ember for the bot, then switch the skill on in its settings.
Step 1
Send your Chief of Staff this message. It proposes the bot and its role first, and creates it when you say go.
Send this to Ember
“Create a bot that uses the Data Warehouse Design skill, and show me the plan first.”
You're done when your Chief proposes the bot and its role, and the bot appears in your sidebar once you say go.
Step 2
You're done when you see the bot’s skills and the library.
Step 3
You're done when the skill shows as on for that bot.
Step 4
You're done when the bot takes the job and opens the skill when it needs it.
Prefer to do it yourself? Choose + in the sidebar, then New Bot.Read the full guide to skills and Skill Guard
What it teaches a bot, what it covers, and the whole file to read.
What it covers
When to Use
Process
Output Format
Rules
Edge Cases
Example
Data Warehouse Design: E-Commerce Analytics Warehouse
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)
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?
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)
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
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)
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).
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
Produce the warehouse schema specification.
## 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] |NEVER create a fact table without explicitly stating the grain -- the grain determines everything else about the table
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
ALWAYS create a conformed dim_date dimension that every fact table references -- date is the most common query filter and must be consistent
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
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
SCD Type 2 dimensions must include valid_from, valid_to, and is_current columns -- without valid_to, point-in-time queries are impossible
NEVER mix different grains in the same fact table -- if you need both transaction-level and daily snapshot, create two fact tables
Star schema is the default recommendation unless the user has a documented reason for snowflake -- star is faster to query and easier to understand
Every dimension attribute used as a filter in the top 5 most common queries must be indexed
The warehouse design must include estimated row counts and growth rates -- without volume estimates, performance implications cannot be assessed
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.
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:
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
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)
| Column | Type | Measure Type | Description |
|---|---|---|---|
| sale_key | BIGINT | -- | Surrogate primary key |
| order_id | VARCHAR(20) | Degenerate dim | Order identifier (not a FK; stored in the fact for drill-through) |
| order_date_key | INT | FK -> dim_date.date_key | Date the order was placed |
| customer_key | BIGINT | FK -> dim_customer.id | Customer who placed the order |
| product_key | BIGINT | FK -> dim_product.id | Product purchased |
| promotion_key | BIGINT | FK -> dim_promotion.id | Promotion applied (0 = no promotion) |
| quantity | INT | Additive | Units purchased |
| unit_price | DECIMAL(10,2) | Non-additive | Price per unit (do not SUM; use for avg price calc) |
| gross_revenue | DECIMAL(10,2) | Additive | quantity x unit_price before discounts |
| discount_amount | DECIMAL(10,2) | Additive | Total discount applied |
| net_revenue | DECIMAL(10,2) | Additive | gross_revenue - discount_amount |
| tax_amount | DECIMAL(10,2) | Additive | Tax charged |
| shipping_cost | DECIMAL(10,2) | Additive | Shipping allocated to this line item |
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
| Column | Type | Measure Type | Description |
|---|---|---|---|
| snapshot_date_key | INT | FK -> dim_date.date_key | Snapshot date |
| product_key | BIGINT | FK -> dim_product.id | Product being tracked |
| warehouse_key | BIGINT | FK -> dim_warehouse.id | Warehouse location |
| quantity_on_hand | INT | Semi-additive | Units in stock (sum across warehouses, NOT across dates) |
| quantity_reserved | INT | Semi-additive | Units reserved for pending orders |
| quantity_available | INT | Semi-additive | on_hand - reserved |
| reorder_flag | BOOLEAN | Non-additive | True if available < reorder_point |
SCD Type: Type 2 (track address and segment changes)
Estimated rows: 100,000 (with ~10% having multiple historical rows)
| Column | Type | Description |
|---|---|---|
| id | BIGINT | Surrogate key |
| customer_natural_key | VARCHAR(20) | Business customer ID |
| name | VARCHAR(200) | Full name |
| VARCHAR(255) | Email address | |
| city | VARCHAR(100) | City |
| state | VARCHAR(50) | State/province |
| country | VARCHAR(50) | Country |
| customer_segment | VARCHAR(20) | VIP, Regular, New (derived from purchase history) |
| first_order_date | DATE | Date of first purchase |
| valid_from | DATE | SCD2 effective start |
| valid_to | DATE | SCD2 effective end (9999-12-31 for current) |
| is_current | BOOLEAN | True for the active row |
SCD Type: Type 1 (overwrite; product attributes are corrected, not tracked historically)
Estimated rows: 2,000
| Column | Type | Description |
|---|---|---|
| id | BIGINT | Surrogate key |
| sku | VARCHAR(20) | Product SKU |
| product_name | VARCHAR(200) | Display name |
| category | VARCHAR(50) | Top-level category |
| subcategory | VARCHAR(50) | Second-level category |
| brand | VARCHAR(100) | Brand name |
| cost_price | DECIMAL(10,2) | Current cost (for margin calculation) |
| is_active | BOOLEAN | Currently for sale |
Estimated rows: 500
| Column | Type | Description |
|---|---|---|
| id | BIGINT | Surrogate key (0 = no promotion) |
| promotion_code | VARCHAR(20) | Promo code |
| promotion_name | VARCHAR(100) | Descriptive name |
| discount_type | VARCHAR(20) | percentage, fixed_amount, buy_x_get_y |
| discount_value | DECIMAL(10,2) | Discount amount or percentage |
| start_date | DATE | Promotion start |
| end_date | DATE | Promotion end |
Estimated rows: 5
| Column | Type | Description |
|---|---|---|
| id | BIGINT | Surrogate key |
| warehouse_code | VARCHAR(10) | Internal code |
| warehouse_name | VARCHAR(100) | Display name |
| city | VARCHAR(100) | Location city |
| region | VARCHAR(50) | Geographic region |
| Column | Type | Description |
|---|---|---|
| date_key | INT | YYYYMMDD integer key |
| full_date | DATE | Calendar date |
| day_of_week | VARCHAR(10) | Monday through Sunday |
| day_of_month | INT | 1-31 |
| month_number | INT | 1-12 |
| month_name | VARCHAR(10) | January through December |
| quarter | VARCHAR(6) | Q1-2026 format |
| year | INT | Calendar year |
| fiscal_quarter | VARCHAR(6) | Fiscal quarter (if different) |
| is_weekend | BOOLEAN | Saturday or Sunday |
| is_holiday | BOOLEAN | Company-defined holidays |
| Decision | Choice | Rationale |
|---|---|---|
| Schema type | Star | BI tool compatibility (Tableau performs best with star); simpler queries for analyst team |
| Sales grain | Order line item | Most atomic level; enables both per-product and per-order analysis |
| Inventory grain | Daily snapshot per product per warehouse | Daily granularity balances storage cost with analysis need; sub-daily not required |
| Customer SCD | Type 2 | Segment and location changes matter for historical analysis ("what segment was this customer in when they purchased?") |
| Product SCD | Type 1 | Product corrections should update in place; historical product attribute tracking not required |
| unit_price as non-additive | Store in fact but mark as non-additive | Analysts need it for drill-through; SUM(unit_price) is meaningless but AVG and per-row display are useful |
| Question | Query Approach | Tables Joined |
|---|---|---|
| Monthly revenue by category | GROUP BY dim_date.month_name, dim_product.category; SUM(net_revenue) | fact_sales + dim_date + dim_product |
| Customer segment contribution to revenue | GROUP BY dim_customer.customer_segment; SUM(net_revenue) | fact_sales + dim_customer (WHERE is_current = true) |
| Promotion effectiveness (revenue with vs without) | GROUP BY dim_promotion.promotion_name; SUM(net_revenue), AVG(discount_amount) | fact_sales + dim_promotion |
| Current inventory levels by category | GROUP BY dim_product.category, dim_warehouse.region; SUM(quantity_available) WHERE date_key = today | fact_inventory_snapshot + dim_product + dim_warehouse + dim_date |
SKILL.md
345 lines · 2,804 words
The text of SKILL.md is shown as it downloads, under the Apache-2.0 license, by Ferrox Labs. The license is the one in the file's own frontmatter.
Your new bot
Data Warehouse Design bot
Waiting in your sidebar once you say go.
No account needed · Runs on the AI plan you already pay for