agentsclimarketplace
Sign in

Design dimensional data model and sql scripts for feature analyt

Skill ECNU-ICALK/AutoSkill/SkillBank/ConvSkill/english_gpt4_8_GLM4.7/design-dimensional-data-model-and-sql-scripts-for-feature-analyt

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.From its SKILL.md

Install
npx -y skills add ECNU-ICALK/AutoSkill --skill design-dimensional-data-model-and-sql-scripts-for-feature-analyt

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

One thing to look at

  • no licenseNo license file was found in the repository. Code published without one is not open source by default, so using it at work is a question for whoever answers licensing questions where you are.

SKILL.md

2.8 KB, 479 tokens by cl100k_base, as published. Nobody here has run it

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.

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

  1. Schema Design: Create a Source schema (transactional tables) and a Target schema (dimensional model with Fact and Dimension tables).
  2. 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).
  3. 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).
  4. 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.
  5. 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.
  6. 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

What ships with it

Read from the repository

Just SKILL.md. No reference files, no scripts.

Keep looking

Skills are one crate of 325,949. Ordering is by how many stacks a row turns up in, so the top of any crate is what has actually been picked rather than what has the most stars.