From transformations
Generates Canonical Data Model (CDM) schemas in DBML using Kimball dimensional modeling from ontology files. Use after create-ontology for data warehouse implementation.
npx claudepluginhub dlt-hub/dlthub-ai-workbench --plugin transformationsThis skill uses the workspace's default tool permissions.
Translate the ontology into an implementation-ready Canonical Data Model using Kimball dimensional modeling.
Builds business entity graph (ontology) from annotated DBML schemas and taxonomy.json. Confirms natural key strategies for cross-source merging before Kimball CDM design.
Designs data models, database schemas, and modeling approaches like dimensional modeling, star schema, data vault, ER diagrams, and schema evolution for OLTP/OLAP systems.
Creates validated models of entities, relationships, and constraints for database schemas (SQL/NoSQL/graph), knowledge graphs, ontologies, API data models, and taxonomies.
Share bugs, ideas, or general feedback.
Translate the ontology into an implementation-ready Canonical Data Model using Kimball dimensional modeling.
Requires .schema/<cdm-name>/ontology.ison from create-ontology.
If missing, run create-ontology first.
Read _name from .schema/<cdm-name>/taxonomy.json to determine <cdm-name> — all files in this skill are under that folder.
Reference: DBML format — https://dbml.org/docs/
Read .schema/ontology.ison. For each entity, apply Kimball classification:
| Signals | Classification |
|---|---|
| Describes a business event or transaction (e.g. EventAttendance, Order, PageView) | Fact table |
| Describes a stable business object (e.g. Person, Company, Product) | Reference table |
| A reference table shared across multiple fact tables | Shared reference table |
Present the classification to the user and confirm before proceeding:
Here's how I'd structure your data model:
Reference tables (who/what your data is about):
dim_person — Person (shared across all facts)
dim_company — Company (shared across all facts)
dim_event — Event
Fact tables (the events/transactions):
fact_event_attendance — one row per person per event attended
Does this look right?
Agree on shared reference tables early — they must be consistent across all fact tables.
For each fact table, write an explicit grain statement:
"One row per [unit] per [unit]"
Example: "One row per person per event attended."
The grain drives:
Never proceed without a confirmed grain.
For each dimension entity:
<entity_name>_sk, bigint or string hash)valid_from (timestamp), valid_to (timestamp, nullable), is_current (bool)source_id (natural key from source) and source_pipeline for lineageUNKNOWN, NOT_APPLICABLE) — never use NULL as FK (Kimball Rule #6)For each fact table:
event_id)Check for aliases that only become visible at the dimensional modeling stage — e.g. two ontology entities that would produce identical dimension tables. Do not re-open concept collapses already confirmed in taxonomy.json; those are settled.
If a new collapse is warranted, confirm with the user before merging the tables.
Write .schema/CDM.dbml using DBML syntax. Encode metadata in Table notes:
Table dim_person [note: 'table_type:dimension; surrogate_key:person_sk; scd_type:1; conformed:true'] {
person_sk bigint [pk, note: 'surrogate key']
source_id varchar [note: 'source key — original ID from the upstream system, stored for lineage']
source_pipeline varchar
email varchar
first_name varchar
last_name varchar
company_sk bigint [ref: > dim_company.company_sk]
}
Table fact_event_attendance [note: 'table_type:fact; grain:one row per person per event attended'] {
attendance_sk bigint [pk]
person_sk bigint [ref: > dim_person.person_sk]
event_sk bigint [ref: > dim_event.event_sk]
registered_at timestamp
attended bool
}
Required note fields per table type:
table_type, surrogate_key, scd_type, conformedtable_type, grain.schema/<cdm-name>/CDM.dbml — implementation-ready CDM schemaAfter writing the file, explicitly ask the user to open and review .schema/<cdm-name>/CDM.dbml before continuing:
Please review `.schema/<cdm-name>/CDM.dbml` — it contains the full data model with all tables, columns, and relationships.
Let me know if anything looks wrong or needs changing before we move on.
Wait for explicit confirmation before handing over to create-transformation skill.