Building a Production-Grade Dimensional Model on Snowflake with Cortex Code using Skills

A data engineer built a Cortex Code skill called snowflake-dimensional-modeling that encodes Kimball dimensional-modeling best practices into a reusable SKILL.md markdown file with YAML frontmatter, letting an AI agent generate star-schema DDL on Snowflake. Tested against an e-commerce schema with orders, products, customers, and payments, the skill produced a FACT_ORDER_LINE table clustered by (DATE_SK, PRODUCT_SK), chose SCD Type 2 for DIM_CUSTOMER and SCD Type 1 for DIM_PRODUCT, and in Review mode ran four diagnostic queries that flagged an orphan table and applied all fixes in one pass. The skill was then expanded with three additional fact tables — FACT_RETURN, FACT_INVENTORY_SNAPSHOT, and FACT_CUSTOMER_SESSION — adding three new dimensions while reusing four existing conformed dimensions.

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. Dry run: lint + test, don't publish./scripts/publish-skill.sh --dry-run Publish to account, discoverable by everyone./scripts/publish-skill.sh --roles PUBLIC Publish to specific roles only./scripts/publish-skill.sh --roles ANALYST,DATA ENG 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 https://pub.towardsai.net/building-a-production-grade-dimensional-model-on-snowflake-with-cortex-code-using-skills-76cbb73f6295 was originally published in Towards AI https://pub.towardsai.net on Medium, where people are continuing the conversation by highlighting and responding to this story.