- Home
- Skills
- Data & Databases
- dimensional data model designer
Works with the AI Agents you already use
dimensional data model designer
This skill automates the rigorous design of star schemas by mapping source schemas to business processes while enforcing strict dimensional modeling rules.
$2.99
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_atis the primary event timestamp for the business process. [user_supplied]
2. Logical model (facts, grain, keys)
| Fact | Grain | Grain status | Primary key | Measures (additivity) |
|---|---|---|---|---|
| fact_orders | One row per order | verified | order_sk | total (additive) |
3. Dimensions, SCD policies and bus matrix
| Dimension | Surrogate key | Natural key | SCD | Conformed | Used by |
|---|---|---|---|---|---|
| dim_users | user_sk | id | 1 | Yes | fact_orders |
Bus Matrix
| Process | dim_users | dim_date |
|---|---|---|
| Orders | X | X |
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 Table | Target Column | Source | Rule |
|---|---|---|---|
| dim_users | city | E2.city | Direct map; SCD1 overwrite |
| fact_orders | total | E1.total | Direct map |
6. Data quality tests
| Test ID | Table | Column | Check |
|---|---|---|---|
| DQ_01 | fact_orders | total | NOT NULL |
| DQ_02 | dim_users | user_id | UNIQUE |
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
emailindim_usersrequires PII masking. - Analytical estimate — validation required: Assigned
totalas 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
- Review the surrogate key data types for compatibility with your target warehouse.
- Provide specific metric formulas (e.g., Average Order Value) to validate aggregation logic.
- Confirm the sensitivity level of the
emailcolumn 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
- Catalog sources by registering each input table with E-numbers and verifying approval status and data sensitivity.
- Configure request.json with source columns, business process grains, and dimension attributes following the strict schema.
- Execute validation using the internal script to perform deterministic structural checks and join safety analysis.
- 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
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
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 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