data-modeling_skill

This skill designs dimensional models and data warehouses, enabling analytics teams to build scalable schemas, optimize queries, and deliver reliable BI

Python

3

GitHub Stars

1

Bundled Files

2 months ago

Catalog Refreshed

4 months ago

First Indexed

Readme & install

Copy the install command, review bundled files from the catalogue, and read any extended description pulled from the listing source.

Installation

Preview and clipboard use veilstrat where the catalogue uses aiagentskills.

npx veilstrat add skill dasien/claudemultiagenttemplate --skill data-modeling

SKILL.md8.4 KB

Overview

This skill designs dimensional models, star and snowflake schemas, and data warehouse structures optimized for analytics and reporting. I create both normalized transactional schemas and denormalized analytical models that balance query performance, maintainability, and historical tracking. The output includes table layouts, SCD strategies, partitioning and aggregation recommendations.

How this skill works

I start by identifying business processes, metrics, and the correct grain for each fact. For OLTP I apply normalization (typically up to 3NF) with clear keys and constraints. For OLAP I design fact and dimension tables, choose star or snowflake patterns, specify surrogate and business keys, and define SCD handling and aggregate tables for common queries.

When to use it

Designing new transactional or analytical database schemas
Building a data warehouse or data mart for BI/analytics
Planning data migration or consolidation projects
Optimizing query performance and storage for reporting
Defining data grain, hierarchies, and SCD strategies

Best practices

Define the business questions and explicit grain before modeling
Use 3NF for OLTP and star schema for OLAP (snowflake only when space critical)
Use surrogate keys for dimensions and preserve business keys for lookups
Implement SCD Type 2 for entities that require history tracking
Pre-populate a date dimension and partition large fact tables by date
Keep fact tables lean (only additive measures); create aggregate tables for heavy reports

Example use cases

E-commerce sales warehouse: fact_sales with dim_date, dim_product (SCD2), dim_customer (SCD2), dim_store
Monthly executive reports: daily or monthly aggregate tables to speed dashboards
Customer 360: join fact tables to customer dimension with SCD history for lifetime metrics
Migration from OLTP to OLAP: convert normalized source to dimensional model and define ETL/CDC patterns
Ad-hoc analytics: star schema designed for fast group-bys and rollups across hierarchies

FAQ

Prefer star schema for query simplicity and performance; use snowflake when dimension normalization is necessary to reduce storage or enforce strict hierarchies.

When should I use SCD Type 2?

Use SCD Type 2 when you must preserve historical attribute values (e.g., product category or customer segment changes) for accurate time-based analysis.

What is the recommended grain for fact tables?

Choose the finest level that still answers business questions (e.g., transaction line item). Define grain explicitly and ensure all facts align to it.