Every data engineer knows the drill. You need a star schema for analytics. You know the theory — grain first, conformed dimensions, surrogate keys, the whole Kimball playbook. But the execution? That’s 200 lines of DDL, manual constraint wiring, zero-value rows you’ll forget, and clustering keys you’ll second-guess.
What if you could encode those decisions into a reusable skill that enforces best practices every time — and then let an AI agent execute it?
That’s what I built. This article walks through the full journey: creating a Cortex Code skill for dimensional modeling, testing it against a real e-commerce schema, auditing it against best practices, and expanding it with three additional fact tables.
Instead of writing one-off DDL, I created a Cortex Code skill called snowflake-dimensional-modeling. A skill is a markdown file (SKILL.md) with YAML frontmatter that tells the AI agent when to activate and how to behave.
Here’s the core workflow:
The skill supports three modes:
Every invocation, regardless of mode, applies these principles:
I told the skill: “I want to model an e-commerce business. We have orders, products, customers, and payments. Key questions: revenue by product category and region, monthly order trends, customer lifetime value.”
Grain: One row per order line itemDimensions: DIM_DATE - generated, 2020-2027, calendar + fiscal DIM_CUSTOMER - SCD Type 2 (address changes matter for LTV) DIM_PRODUCT - SCD Type 1 (overwrite is fine) DIM_PAYMENT_METHOD - SCD Type 1 Fact: FACT_ORDER_LINE Clustered by (DATE_SK, PRODUCT_SK) Measures: quantity, unit_price, discount, line_total, tax
Two design decisions worth noting:
SCD Type 2 for customers, Type 1 for products. The skill chose Type 2 for customers because the user asked about LTV — you need address history to track customer migration across regions. Products don’t need history for this use case.
Clustering on (DATE_SK, PRODUCT_SK). Date first because “monthly order trends” is a time-series query. Product second because “revenue by category” groups through the product dimension.
After building the initial model, I ran the skill in Review mode. It ran four diagnostic queries and produced a severity-ranked report:
The orphan table was a leftover from a previous task. The skill caught it because it checks for tables with no FK relationships and no audit columns — a table that doesn’t participate in any star schema relationship is suspicious.
The skill applied all fixes in one pass:
The real test of a dimensional model is whether it grows gracefully. I asked the skill to expand with returns, inventory snapshots, and customer sessions.
FACT_RETURN links back to the original order via ORDER_NUMBER (degenerate dimension). This enables return rate analysis: "Which products have the highest return rate by category?"
FACT_INVENTORY_SNAPSHOT is a periodic snapshot — the key insight is that its measures are semi-additive. You can SUM quantity_on_hand across products (how much total stock in a warehouse?) but you must AVG across time (what's the average stock level this month?). The skill marks this in the column COMMENT so BI tools don't accidentally SUM across dates.
FACT_CUSTOMER_SESSION captures web behavior without requiring a purchase. This enables funnel analysis: sessions → products_viewed → added_to_cart → converted. The IS_CONVERTED flag and optional ORDER_NUMBER bridge to FACT_ORDER_LINE for attribution.
The expansion added three new dimensions (DIM_WAREHOUSE, DIM_RETURN_REASON, DIM_TRAFFIC_SOURCE) but reused four existing ones. This is conformed dimensions working as designed — DIM_DATE appears in all four fact tables, DIM_PRODUCT in three, DIM_CUSTOMER in three.
The payoff is cross-fact drill-across queries:
-- Net revenue: orders minus refunds, by productSELECT p.PRODUCT_NAME, SUM(o.LINE_TOTAL) AS GROSS_REVENUE, SUM(r.REFUND_AMOUNT) AS TOTAL_REFUNDS, SUM(o.LINE_TOTAL) - SUM(r.REFUND_AMOUNT) AS NET_REVENUEFROM DIM_PRODUCT pLEFT JOIN FACT_ORDER_LINE o ON p.PRODUCT_SK = o.PRODUCT_SKLEFT JOIN FACT_RETURN r ON p.PRODUCT_SK = r.PRODUCT_SKWHERE p.PRODUCT_SK > 0GROUP BY p.PRODUCT_NAMEORDER BY NET_REVENUE DESC;
This query works because both facts share DIM_PRODUCT as a conformed dimension.
I built three scripts to make the skill CI-friendly:
lint-skill.sh — validates SKILL.md structure (frontmatter, sections, checkpoints, placeholder consistency, balanced code fences). Zero errors, zero warnings on the final version.
test-skill.sh — integration tests against the live ECOMMERCE.ANALYTICS schema. Checks table existence (12 tables), row counts, constraint counts (28), zero-value rows in all dimensions, and a cross-fact join.
publish-skill.sh — orchestrates lint → test → publish. Supports --dry-run, --roles, and --private flags.
A SQL template gives you structure. A skill gives you judgment. The skill chose SCD Type 2 for customers because the user asked about LTV. It chose Type 1 for products because history wasn’t needed. A template can’t make that call.
Designing a model from scratch is the easy part. Reviewing an existing one — finding the orphan tables, the missing natural keys, the DATA_RETENTION_TIME_IN_DAYS = 1 — that's where the skill earns its keep. The nine-item audit checklist catches things humans skip.
Inventory snapshots are the most dangerous fact table type because they look additive but aren’t. Marking measures as semi-additive in the column COMMENT is a small thing that prevents significant downstream errors.
When I added three fact tables, the model grew from 6 tables to 12. But I only added 3 new dimensions — the other 5 were reused. That’s the Kimball promise delivered: the shared dimensions enable cross-fact queries that would be impossible with isolated schemas.
Snowflake’s UNIFORM(min, max, RANDOM()) requires min and max to be constants, not column references. When generating sample data with dynamic bounds, you need to pre-compute random values in a CTE. A small gotcha that cost me one retry.
ECOMMERCE.ANALYTICS├── 8 Dimensions (with zero-value rows, audit columns, informational constraints)│ ├── DIM_DATE — 2,922 rows (2020-2027, calendar + fiscal)│ ├── DIM_CUSTOMER — 21 rows (SCD Type 2, search optimized)│ ├── DIM_PRODUCT — 21 rows (SCD Type 1)│ ├── DIM_GEOGRAPHY — 21 rows (conformed, 6 regions)│ ├── DIM_PAYMENT_METHOD — 9 rows (with natural key)│ ├── DIM_WAREHOUSE — 6 rows (5 fulfillment centers)│ ├── DIM_RETURN_REASON — 9 rows (3 categories)│ └── DIM_TRAFFIC_SOURCE — 8 rows (6 mediums)├── 4 Fact Tables (clustered, FK-constrained, grain-documented)│ ├── FACT_ORDER_LINE — 500 rows (transaction)│ ├── FACT_RETURN — 150 rows (transaction)│ ├── FACT_INVENTORY_SNAPSHOT — 18,400 rows (periodic snapshot)│ └── FACT_CUSTOMER_SESSION — 1,000 rows (transaction)└── 28 Constraints (8 PK + 20 FK)
The skill is published to the Snowflake account and available to anyone with the share URI. Type /snowflake-dimensional-modeling in Cortex Code to start.
Built with Cortex Code on Snowflake. The skill, scripts and DDL are available in the Github*.*
Found this useful? 👏 Give it a clap — it helps others discover it too.
Follow for weekly Snowflake engineering deep dives, practical architecture insights, and technical quick bytes. ❄️
You may use, share, adapt, and build upon this work. For public redistribution or substantial adaptation, please retain attribution and include a link to the original article or repository and the author’s LinkedIn profile. Private and internal use requires no attribution. Provided “as is” for educational purposes. Please validate and test all examples before using them in production. Views are my own and do not represent any current or former employer.
Building a Production-Grade Dimensional Model on Snowflake with Cortex Code using Skills was originally published in Towards AI on Medium, where people are continuing the conversation by highlighting and responding to this story.