Modeling Dimension Tables Agent Skill

Modeling dimension tables (star schema)

Dimensions are the descriptive tables (dim_country, dim_plan, dim_date) that fact tables join to for

slicing. This skill builds them once, cleanly, so every other model reuses them instead of re-deriving

lookups. Read modeling-warehouse-foundations first (joins + convertCurrency() live there). Catalog of

common dimensions: references/dimension-catalog.md; recipes in

references/posthog/ and references/dbt/.

Star schema in one screen

Facts (events, charges, revenue items) are long, keyed, and additive. Dimensions are short, one row

per entity, descriptive. You model a dimension in three moves:

• Source it — where does the dimension data come from?
• _Upload / seed_ a lookup (country→region, plan→tier) as a CSV (warehouse source or dbt seed).
• _Sync_ it from a system of record (your app DB, Stripe products) as a warehouse source.
• _Derive_ it from events (distinct countries seen, a plan property observed per person).
• Shape it — an aliased SELECT with clean column names, one row per entity (dedupe hard).

Save as a view; materialize it on a slow sync_frequency (7day/30day) since dimensions change

rarely and are read constantly.

• Attach it — a saved join (dimension → a fact table) or person join (dimension → persons) so

its columns appear as native fields in any query, filter, or breakdown. See foundations

joins-and-dimensions.md.

Currency is already a managed dimension — don't build it

PostHog ships exchange rates behind convertCurrency(from, to, amount, timestamp?) (Open Exchange Rates,

historical-rate-correct). Use it directly for any money conversion. Only build a currency dimension yourself

in dbt (which has no equivalent), or if you need a rate provider PostHog doesn't offer.

Rules before you model

• One row per entity, unique key. A dimension with duplicate keys silently fan-outs every fact it joins.

Test uniqueness (PostHog: verify in the shaping query; dbt: unique + not_null).

• Alias to clean, stable names — country_code, region, plan_tier. These names become the join

surface everything else depends on.

• Materialize static dimensions on a slow schedule; don't leave a constantly-read lookup virtual.
• Register and certify. Annotate the dimension and, if it's load-bearing, certify it in the catalog

(foundations governance.md) so other models discover it and don't build a rival copy.

• Prefer built-in currency (convertCurrency) over a hand-rolled FX table on PostHog.

Build it

PostHog: shape an aliased dimension view, then materialize + join. Recipes:

references/posthog/dim_country.sql (derive + enrich from events),

dim_plan.sql (lookup/upload pattern).

dbt: conformed dim_* models with unique/not_null/relationships tests, plus a generated

dim_date. Recipes: references/dbt/.

File map

| File | Read when |

| -------------------------------------------------------------------- | ----------------------------------------------------------- |

| references/dimension-catalog.md | Common dimensions, how to source each, and the natural key. |

| references/posthog/ | HogQL aliased-dimension view recipes. |

| references/dbt/ | dbt dim_date / dim_country + schema.yml tests. |

Companions

modeling-warehouse-foundations (joins + currency), setting-up-a-data-warehouse-source /

suggesting-data-imports (sync/upload the source data), and the models that consume these dimensions:

modeling-revenue-metrics, modeling-conversion-metrics, modeling-activation-metrics,

modeling-product-usage-metrics.