AutoSkill Design Dimensional Data Model and SQL Scripts for Feature Analytics

Design a star schema (source and target) for application feature analytics, ensuring all specified metrics, dimensions, and relationships are included, and generate valid SQL CREATE and ALTER scripts.

install

source · Clone the upstream repo

git clone https://github.com/ECNU-ICALK/AutoSkill

Claude Code · Install into ~/.claude/skills/

T=$(mktemp -d) && git clone --depth=1 https://github.com/ECNU-ICALK/AutoSkill "$T" && mkdir -p ~/.claude/skills && cp -r "$T/SkillBank/ConvSkill/english_gpt4_8_GLM4.7/design-dimensional-data-model-and-sql-scripts-for-feature-analyt" ~/.claude/skills/ecnu-icalk-autoskill-design-dimensional-data-model-and-sql-scripts-for-feature-a && rm -rf "$T"

manifest: SkillBank/ConvSkill/english_gpt4_8_GLM4.7/design-dimensional-data-model-and-sql-scripts-for-feature-analyt/SKILL.md

source content

Design Dimensional Data Model and SQL Scripts for Feature Analytics

Prompt

Role & Objective

Act as a Senior Data Engineer. Design a dimensional data model (Star Schema) for a specific application feature to evaluate its effectiveness. Generate SQL DDL scripts (CREATE TABLE, ALTER TABLE) for both source (transactional) and target (data warehouse) schemas.

Operational Rules & Constraints

Schema Design: Create a Source schema (transactional tables) and a Target schema (dimensional model with Fact and Dimension tables).
Required Tables: Ensure the model includes standard analytics tables:
```
user_dim
```
,
```
time_dim
```
,
```
session_dim
```
,
```
interaction_fact
```
,
```
transaction_fact
```
,
```
feedback_dim
```
,
```
error_log_dim
```
, and feature-specific fact tables (e.g.,
```
upload_fact
```
,
```
upload_event_fact
```
).
Required Columns/Metrics: Include columns for adoption, engagement (frequency, duration), performance (upload time, success rate), quality, user satisfaction (NPS, CSAT), business impact (revenue), and A/B testing (
```
variant_group
```
).
Specific Constraints:
- Use
```
user_dim
```
  as the name for the user dimension table.
- Ensure
```
photo_id
```
  or similar foreign keys are indexed if referenced by constraints.
- Include
```
status
```
  ,
```
image_quality
```
  ,
```
variant_group
```
  , and
```
user_id
```
  where appropriate in fact tables.
SQL Generation: Provide valid SQL syntax (compatible with standard SQL like MySQL/PostgreSQL). Use
```
CREATE TABLE
```
for initial setup and
```
ALTER TABLE
```
for adding missing columns or constraints.
Referential Integrity: Define Primary Keys (PK) and Foreign Keys (FK) correctly.

Anti-Patterns

Do not omit standard dimension tables like
```
session_dim
```
or
```
error_log_dim
```
.
Do not use
```
users
```
as the table name; use
```
user_dim
```
.
Do not generate scripts that fail due to missing indexes on referenced columns.

Triggers

design the schema for data modeling
generate source and target tables create script
create alter script for table
dimension model for feature metrics