Works with the AI Agents you already use

    Claude CodeClaude CodeCursorCursorCodex CLICodex CLIDeepSeekDeepSeekMuseMuse+22 more

    dimensional data model designer

    1

    This skill automates the rigorous design of star schemas by mapping source schemas to business processes while enforcing strict dimensional modeling rules.

    $2.99

    Secure checkout via Stripe

    Money-back guarantee

    Converts to your local currency at checkout

    Security scanned
    dimensional data model designer

    dimensional data model designer

    Example session with this skill installed

    Design a star schema for our e-commerce orders. Source tables: e_orders (id, user_id, total, created_at) and e_users (id, email, city). Grain is one row per order. City should be SCD Type 1.

    • Read your context and instructions
    • Compiled the dimensional data model

    I have processed the e-commerce order sources and generated the dimensional model design.

    Dimensional Data Model Design — E-Commerce Project, 2024-05-24

    1. Scope, sources and assumptions

    • E1: e_orders (Approved: True, Sensitivity: none)
    • E2: e_users (Approved: True, Sensitivity: personal)
    • Assumption: created_at is the primary event timestamp for the business process. [user_supplied]

    2. Logical model (facts, grain, keys)

    FactGrainGrain statusPrimary keyMeasures (additivity)
    fact_ordersOne row per orderverifiedorder_sktotal (additive)

    3. Dimensions, SCD policies and bus matrix

    DimensionSurrogate keyNatural keySCDConformedUsed by
    dim_usersuser_skid1Yesfact_orders

    Bus Matrix

    Processdim_usersdim_date
    OrdersXX

    4. Table DDL draft

    CREATE TABLE dim_users (
        user_sk BIGINT PRIMARY KEY,
        user_id INT,
        email VARCHAR(255),
        city VARCHAR(100),
        effective_date TIMESTAMP,
        is_current BOOLEAN
    );
    
    CREATE TABLE fact_orders (
        order_sk BIGINT PRIMARY KEY,
        order_id INT,
        user_sk BIGINT REFERENCES dim_users(user_sk),
        order_date_sk INT,
        total DECIMAL(18,2)
    );
    

    5. Mapping rules

    Target TableTarget ColumnSourceRule
    dim_userscityE2.cityDirect map; SCD1 overwrite
    fact_orderstotalE1.totalDirect map

    6. Data quality tests

    Test IDTableColumnCheck
    DQ_01fact_orderstotalNOT NULL
    DQ_02dim_usersuser_idUNIQUE

    7. Metric validation

    No metric definitions were supplied.

    8. Exclusions, findings and approval

    • Exclusions: None.
    • Findings: No join fan-out detected.
    • Proposed Actions: Draft DDL for review (not executed).

    9. Items for human review

    • Information gap — organizational validation required: Verify if email in dim_users requires PII masking.
    • Analytical estimate — validation required: Assigned total as fully additive across all dimensions.
    • Management decision required: Approve SCD Type 1 policy for city changes.

    This design supports, and does not replace, review by the data owner and management's decision.

    Next steps

    1. Review the surrogate key data types for compatibility with your target warehouse.
    2. Provide specific metric formulas (e.g., Average Order Value) to validate aggregation logic.
    3. Confirm the sensitivity level of the email column before finalizing the DDL.

    Connects securely to your tools. The creator never sees your data.

    About this skill

    Inconsistent grain and improper join logic lead to double-counted metrics and unreliable BI reports. This skill automates the rigorous design of star schemas by mapping source schemas to business processes while enforcing strict dimensional modeling rules. It checks declared grain against source keys, identifies join fan-out risks, and defines surrogate keys and slowly changing dimension (SCD) policies using a deterministic validation script.

    What it does

    • Grain verification checks declared business process grain against source primary keys to ensure data integrity.
    • SCD policy design defines how dimensions track changes using Type 0, 1, 2, 3, or 6 policies for reporting.
    • Bus matrix generation maps conformed dimensions across business processes to ensure cross-functional alignment.
    • DDL drafting produces ANSI-style SQL for fact and dimension tables, including surrogate keys and audit columns.
    • Metric validation tests if metrics can be safely aggregated across specific dimensions without double counting.

    How it works

    1. Catalog sources by registering each input table with E-numbers and verifying approval status and data sensitivity.
    2. Configure request.json with source columns, business process grains, and dimension attributes following the strict schema.
    3. Execute validation using the internal script to perform deterministic structural checks and join safety analysis.
    4. Review findings by analyzing the generated logical model, mapping rules, and data quality tests for any excluded items or warnings.

    Frameworks & tools

    The skill utilizes a custom Python validation engine, JSON schema enforcement, and ANSI SQL. It is designed for data architects working with platforms like Snowflake, BigQuery, Databricks, or Redshift.

    Why this beats prompting it yourself

    Manual prompting often misses subtle join fan-out issues and inconsistent grain definitions that break downstream reports. This skill uses a deterministic script to block unapproved data and force strict adherence to conformed dimension rules that general LLMs often ignore.

    Use cases

    • Drafting a new star schema for a sales department from raw ERP table exports.
    • Auditing a proposed fact table design to check for potential double-counting risks.
    • Defining SCD Type 2 policies for customer and product dimensions in a warehouse.
    • Creating source-to-target mapping rules for an offshore engineering team.

    Known limitations

    Does not design ETL/ELT orchestration, schedules, or retries. Cannot run DDL on live databases or load data. Requires manual input of sensitivity and approval flags.

    Find more skills like this on the Claude Skills Marketplace, the Codex Skills Marketplace and the Gemini Skills Marketplace, or browse Claude Skills for Data & Databases.

    Does not handle ETL orchestration or pipeline scheduling. Cannot execute SQL or load data. Requires explicit user declaration of table approvals and column sensitivity.

    How to use

    1. 1

      Connect your agent to the Agensi MCP

      Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup

    2. 2

      Sign in to Agensi

      Your agent shows a sign-in link. Sign in and click Allow.

    3. 3

      Ask for what you need

      Your agent loads this skill and uses it on your task. It always asks before buying a skill.

    Reviews

    No reviews yet

    Be one of the first to try it. Every listed skill goes through the trust checks below.

    Security scanned

    Checked by our 8-point scan before listing

    Fresh listing

    Recently published to Agensi

    Money-back guarantee

    Doesn't do what its page promises? You get your money back.

    Trust & safety

    Security scan

    Security scan passed 2 days ago

    Permissions

    Terminal / Shell
    Read Files
    Write Files
    Environment Variables
    Listed2 days ago

    What's inside

    Frequently Asked Questions

    This skill prevents 'double-counting' and join fan-out issues by using a deterministic validation script to check declared grains against source keys, ensuring your BI reports remain accurate and reliable.

    The skill is platform-agnostic and generates ANSI-style SQL DDL compatible with major data warehouses like Snowflake, BigQuery, Databricks, and Redshift.

    You will receive the core AI agent skill, the JSON schema for the request configuration, the Python-based validation engine, and a set of documentation templates for bus matrices and SCD policies.

    No, this skill is focused on structural design and DDL drafting; it does not handle ETL/ELT orchestration, data loading, or live execution of SQL on your databases.

    Yes, the skill allows you to assign specific SCD policies (Type 0, 1, 2, 3, or 6) to individual dimension attributes to ensure historical accuracy in your reporting.

    Setup involves registering your source table metadata with the required sensitivity and approval flags, then configuring a request.json file to map these sources to your desired business processes.

    New in Data & Databases