Dimensional Modeling skill for Codex
Star schemas with one grain and honest totals. Codex designs warehouse marts the Kimball way: it writes down the business process and grain before any column, picks the right fact table type, flattens dimensions with surrogate keys and unknown members, and decides SCD handling per attribute. It ships grain, foreign-key and current-row tests and reconciles one total against the source before calling the model done.
curl -fsSL codexskills.dev/install.sh \ | sh -s -- dimensional-modelingInstalls to ~/.agents/skills/dimensional-modeling/ for every project on this machine.
curl -fsSL codexskills.dev/cli.mjs -o codex-skills.mjsnode codex-skills.mjs install dimensional-modelingThe CLI also searches, updates and removes skills. Run it with no arguments to pick from a list. Needs Node 20.12+.
curl -fsSL codexskills.dev/install.sh \ | sh -s -- --project dimensional-modelingInstalls to .agents/skills/dimensional-modeling/ in the current repository. Commit it and everyone who runs Codex there gets it.
curl -fsSLO codexskills.dev/downloads/dimensional-modeling.zipunzip -o dimensional-modeling.zip -d ~/.agents/skills/The archive holds the skill folder with its generated agents/openai.yaml. Unzip into .agents/skills/ to scope it to a repo.
& ([scriptblock]::Create((irm codexskills.dev/install.ps1))) dimensional-modelingPowerShell 5.1 or 7. Installs to %USERPROFILE%\.agents\skills\. Add -Project for the current repo.
104-line POSIX script. No sudo, no telemetry. Read install.sh
Use it in Codex
Start a prompt with $dimensional-modeling, or pick it from /skills.
You can also just describe the task: Codex reads the description below and loads the playbook when your request matches.
What Codex reads before loading it
Designs Kimball-style star schemas for analytics warehouses - declares the grain, separates facts from dimensions, chooses transaction, periodic snapshot or accumulating snapshot facts, assigns surrogate keys and handles slowly changing dimensions (SCD type 1, 2 and 6). Use when modelling a new subject area in Snowflake, BigQuery, Redshift, Databricks or DuckDB, reviewing a dbt mart layer, fixing double-counted metrics from fan-out joins, or deciding how to track history on a customer or product dimension.
Use it when
- Modelling a new subject area for BI or analytics in a warehouse
- Reviewing a mart layer where totals disagree between reports
- Choosing between SCD type 1, type 2 and type 6 for dimension attributes
- Building snapshot facts for balances, inventory or pipeline stages
Use something else when
- You need the dbt project structure, materializations and model code: use dbt-models
- You are designing an OLTP application schema rather than an analytics warehouse: use postgres-schema-design
How to check the result
Grain uniqueness, foreign-key coverage and single-current-row queries return zero rows, and one headline total reconciles with the source system or finance report.
The mistake it prevents
Joining facts to a type 2 dimension on the natural key instead of the surrogate key, which silently multiplies every measure by the number of versions.
A good star schema lets an analyst answer a business question with one fact table, a few dimension joins and a GROUP BY, and get the same number every time. Every fact table has exactly one declared grain, every dimension join is many-to-one, and history behaves the way the business expects when an attribute changes.
Before designing anything
- Read
AGENTS.mdand the existing warehouse layout (models/,marts/, schema naming). Match the project’s prefixes (fct_,dim_,f_,d_) and key naming. - List the business process being modelled (orders placed, invoices paid, tickets resolved), not the source tables. One process usually means one fact table.
- Collect 5-10 real questions the model must answer (“revenue by region by week”, “average days from signup to first order”). Every design choice is tested against them.
- Profile the sources: row counts, candidate keys, null rates, how often attributes change. Uniqueness assumptions you have not tested are the main source of fan-out bugs.
The four-step design
- [ ] 1. Business process: one sentence, e.g. "a line item is added to a placed order"
- [ ] 2. Grain: what one row means, stated in business terms
- [ ] 3. Dimensions: who, what, where, when, how, true at that grain
- [ ] 4. Facts: numeric measures true at that grain, with additivity noted
- [ ] 5. Bus matrix row: which conformed dimensions this fact shares with othersDeclare the grain before naming any column. “One row per order line per shipment” and “one row per order” are different tables; mixing them in one table is the classic error.
Choose the fact table type
| Question shape | Fact type | Grain example | Notes |
|---|---|---|---|
| What happened, how much | Transaction | one row per order line | Most common; insert-only; fully additive measures |
| What was the level at period end | Periodic snapshot | one row per account per day | Balances, inventory; semi-additive (never sum across time) |
| How long did each stage take | Accumulating snapshot | one row per order, updated as milestones land | Multiple date keys; rows are updated in place |
| Did an event occur (no measure) | Factless | one row per student per class attended | Count rows; pair with a coverage table for “did not happen” |
Measure additivity
| Kind | Example | Rule |
|---|---|---|
| Additive | quantity, extended amount | Sum across every dimension |
| Semi-additive | account balance, stock on hand | Sum across accounts, never across dates; use the period-end value or average |
| Non-additive | unit price, margin %, ratios | Store the numerator and denominator; compute the ratio after aggregating |
Never store a pre-computed percentage as the only measure. sum(margin_pct) is wrong
at every level above the grain.
Dimensions
- Surrogate keys. Give every dimension a warehouse-owned key (integer sequence or a deterministic hash of natural key plus validity start). Keep the natural key as a column. Facts reference the surrogate key, which is what lets SCD type 2 work.
- Unknown members. Insert a row with key
-1(or the hash of'unknown') labelled “Unknown” and point unmatched fact rows at it. Never leave a fact foreign key null: inner joins silently drop those rows from every report. - Date dimension. Generate one row per calendar day covering the full history plus
future years. Include fiscal periods, ISO week, holiday flags. Use a smart key
20250314only for the date dimension. - Denormalise. Flatten hierarchies (product -> subcategory -> category) into one dimension. Snowflaking saves little storage and costs every query a join.
- Junk dimension. Combine low-cardinality flags (is_gift, payment_type, channel) into one small dimension instead of five tiny ones or leaving them on the fact.
- Degenerate dimension. Order number or invoice number lives on the fact as a plain column; it has no attributes worth a table.
- Role-playing. One date dimension joined as
order_date_key,ship_date_key,delivery_date_key; expose views or aliases with role-specific column names.
Slowly changing dimensions
| Business wants | Type | Mechanism |
|---|---|---|
| Correct the value everywhere, history irrelevant (typo fix, email) | 1 | Overwrite in place |
| Report each fact under the value true at the time (sales region, customer tier) | 2 | New row per change with valid_from, valid_to, is_current |
| Both “as was” and “as is” in the same query | 6 (1+2+3) | Type 2 rows plus a current_<attr> column overwritten on all rows |
| Rapidly changing attribute (credit score band changing monthly) | Mini-dimension | Split the volatile attributes into their own dimension keyed from the fact |
Decide per attribute, not per table: a customer dimension commonly has type 1 email and type 2 segment. Ask the business owner which history they expect; do not guess.
For SCD type 2 SQL (merge pattern, dbt snapshots, point-in-time fact lookup) and the late-arriving dimension pattern, read references/scd-patterns.md when you are implementing history tracking.
Conformed dimensions and the bus matrix
Facts from different processes can be compared only through dimensions they share with identical keys and attribute values. Keep a bus matrix in the repo docs:
| Fact \ Dimension | date | customer | product | store | promotion |
|---------------------|------|----------|---------|-------|-----------|
| fct_order_lines | x | x | x | x | x |
| fct_returns | x | x | x | x | |
| fct_inventory_daily | x | | x | x | |Never build a second dim_customer for one mart. Extend the shared one or create a
shrunken dimension (a strict subset of columns and rows, e.g. dim_month from dim_date).
Example: order lines star
create table fct_order_lines (
order_line_key bigint not null, -- surrogate for the fact row
order_date_key int not null references dim_date (date_key),
ship_date_key int not null references dim_date (date_key), -- -1 until shipped
customer_key bigint not null references dim_customer (customer_key),
product_key bigint not null references dim_product (product_key),
store_key bigint not null references dim_store (store_key),
order_number varchar(32) not null, -- degenerate dimension
quantity int not null,
gross_amount numeric(18,2) not null,
discount_amount numeric(18,2) not null,
net_amount numeric(18,2) not null
);Many cloud warehouses do not enforce foreign keys; declare them anyway as documentation and test them (relationships tests in dbt, or an anti-join query).
Gotchas
- Fan-out. Joining a fact to a table that is not unique on the join key multiplies measures. Test uniqueness of every dimension key and every bridge before trusting a sum.
- Mixed grain. Putting an order-level shipping fee on every order line overstates it. Allocate it across lines or keep it in an order-grain fact.
- Type 2 joins on natural key. Joining facts to a type 2 dimension by natural key returns one row per version. Facts must carry the surrogate key resolved at load time.
- Late-arriving facts. Resolve the dimension version by event timestamp, not load time, or old facts attach to today’s attributes.
- Null foreign keys. Use the unknown member; count how many facts land on it and alert when that share grows.
- One Big Table temptation. A wide denormalised table is fine as a serving layer built from the star, not as a replacement for it: it loses conformed reuse and makes history changes expensive.
- Many-to-many. Patient to diagnoses, account to holders: use a bridge table with a weighting factor if measures must allocate, and document that unweighted sums double count.
Verification
-- Grain holds: zero rows expected
select order_number, product_key, count(*) from fct_order_lines
group by 1, 2 having count(*) > 1;
-- Every fact row finds its dimension: zero rows expected
select f.customer_key from fct_order_lines f
left join dim_customer d on d.customer_key = f.customer_key
where d.customer_key is null;
-- Type 2 has exactly one current row per natural key: zero rows expected
select customer_id from dim_customer where is_current group by 1 having count(*) <> 1;- Reconcile one total (net revenue for last month) against the source system or finance report. A star that disagrees with the ledger is wrong until explained.
- Run each of the business questions from the start; each should need one fact table.
- If a test fails, fix the grain or key resolution, not the report query.
Output template
**Process:** order line placed
**Grain:** one row per order line at time of placement
**Fact type:** transaction, insert-only
**Dimensions:** date (order, ship), customer (SCD2: segment, region; SCD1: email), product (SCD2: category), store
**Measures:** quantity, gross_amount, discount_amount, net_amount (all additive)
**Degenerate:** order_number
**Conformed with:** fct_returns (date, customer, product, store)
**Open questions:** does the business want returns by original or current customer region?
**Tests:** grain uniqueness, FK coverage, single current SCD row, revenue reconciles to ledger for 2025-09What the zip contains
Codex reads bundled files only when a step points to them. Every one is shown here, so you can review it before installing.
agents/openai.yaml
Gives the Codex app a display name, a one-line description and a starter prompt for this skill.
interface:
display_name: "Dimensional Modeling"
short_description: "Star schemas with one grain and honest totals"
default_prompt: "$dimensional-modeling Design a star schema for our Shopify orders, refunds and inventory data"references/scd-patterns.md
# SCD and late-arriving data patterns
Read this when implementing history tracking. Syntax shown is ANSI-ish SQL that works
with small changes on Snowflake, BigQuery, Databricks SQL and Postgres 15+ (`MERGE`).
## Type 2 with a hash diff
Compute a hash of the tracked attributes so change detection is one comparison.
```sql
-- staging: latest source state, one row per natural key
create or replace temporary table stg_customer as
select
customer_id,
segment,
region,
email,
md5(coalesce(segment, '') || '|' || coalesce(region, '')) as type2_hash,
updated_at
from raw.customers
qualify row_number() over (partition by customer_id order by updated_at desc) = 1;
```
`qualify` exists in Snowflake, BigQuery, Databricks and DuckDB; in Postgres wrap the
window in a subquery. Separate tracked (type 2) and overwritten (type 1) columns: only
type 2 columns go into the hash.
Two statements, in one transaction where the engine supports it:
```sql
-- 1. close current rows whose tracked attributes changed
update dim_customer d
set valid_to = s.updated_at, is_current = false
from stg_customer s
where d.customer_id = s.customer_id
and d.is_current
and d.type2_hash <> s.type2_hash;
-- 2. insert new versions (new keys plus keys just closed)
insert into dim_customer (customer_key, customer_id, segment, region, email,
type2_hash, valid_from, valid_to, is_current)
select
md5(s.customer_id || '|' || cast(s.updated_at as varchar)),
s.customer_id, s.segment, s.region, s.email,
s.type2_hash, s.updated_at, timestamp '9999-12-31', true
from stg_customer s
left join dim_customer d
on d.customer_id = s.customer_id and d.is_current
where d.customer_id is null;
-- 3. type 1 columns: overwrite on every version
update dim_customer d
set email = s.email
from stg_customer s
where d.customer_id = s.customer_id and d.email is distinct from s.email;
```
Use a far-future `valid_to` rather than null so range predicates stay simple. Pick a
half-open convention, `valid_from <= ts < valid_to`, and use it everywhere.
## dbt snapshots
dbt snapshots implement type 2 for you. Prefer the `timestamp` strategy when the source
has a reliable `updated_at`; use `check` with an explicit column list otherwise. Since
dbt 1.9 snapshots can be configured in YAML, and `snapshot_meta_column_names` renames the
`dbt_valid_from`/`dbt_valid_to` columns. Build the dimension as a model on top of the
snapshot that adds the surrogate key and unknown member; do not expose the snapshot
directly. Hard deletes are ignored unless you configure delete handling
(`hard_deletes: invalidate` or `new_record` in 1.9+; older versions use
`invalidate_hard_deletes`). For dbt specifics beyond this, hand off to `$dbt-models`.
## Point-in-time key lookup for facts
Resolve the surrogate key valid at the event time, falling back to the unknown member:
```sql
select
coalesce(d.customer_key, '-1') as customer_key,
o.*
from stg_order_lines o
left join dim_customer d
on d.customer_id = o.customer_id
and o.ordered_at >= d.valid_from
and o.ordered_at < d.valid_to;
```
Check afterwards that the join did not duplicate rows (overlapping validity ranges in the
dimension are a data bug):
```sql
select customer_id from dim_customer a
join dim_customer b using (customer_id)
where a.customer_key <> b.customer_key
and a.valid_from < b.valid_to and b.valid_from < a.valid_to;
```
## Late-arriving dimensions (early-arriving facts)
The fact references a customer the dimension has not seen yet.
1. Insert an inferred member: natural key known, attributes "Pending", `is_inferred = true`.
2. Point the fact at that member's key.
3. When the real dimension row arrives, overwrite the inferred row in place (type 1
style) instead of creating a new version, then clear `is_inferred`.
Alert when inferred members older than a threshold (for example 3 days) remain.
## Late-arriving facts
A fact for last month lands today. Resolve the dimension version by the fact's event
timestamp (lookup above), and if the warehouse has aggregates or snapshots built from
the fact, rebuild the affected partitions rather than appending to today's.
## Type 6 current-value column
Add `current_region` to `dim_customer` and after each load:
```sql
update dim_customer d
set current_region = c.region
from dim_customer c
where c.customer_id = d.customer_id and c.is_current
and d.current_region is distinct from c.region;
```
Reports then choose `region` (as was) or `current_region` (as is) without a self-join.
About this skill
Written and maintained by the Codex Skills editors to the same editorial standard as every skill here: a selective description, a real workflow, known pitfalls and a way to verify the work. First published , last updated . MIT licensed. Something wrong or out of date? Tell us.