Index of every page on this site: llms.txt
All skills
posthog avatar

/modeling-dimension-tables

@028173b official
by posthogposthog/posthog41k stars
3,718
Run it yourself

Build reusable dimension / lookup tables for a star schema — country/region, timezone, currency, date, plan/product, and other descriptive attributes — on either PostHog data-warehouse views (HogQL) or an external dbt project. Use when the user wants to model dimension tables, lookup tables, a star schema, conformed dimensions, or wants to enrich events/revenue/usage with country, region, timezone, plan, or currency attributes without repeating JOINs. Covers sourcing the dimension data (upload, warehouse source, or derive from events), shaping it into an aliased one-row-per-entity view (optionally materialized on a slow schedule since dimensions change rarely), and attaching it to facts via a saved or person join so its columns read as native fields. Key rule: for currency use the built-in convertCurrency() instead of a hand-rolled rate table. Read modeling-warehouse-foundations first; dimensions here are reused by the revenue, conversion, activation, and product-usage modeling skills.

  • 7 files
  • 12.7 KB
  • Updated last month
  • GitHub
Use this Skill: https://skilld.dev/gh/posthog/posthog/modeling-dimension-tables

Nothing lands on disk. Nothing to clean up.

Fork this Skill

Edit a local copy. It keeps the author and licence.

SKILL.md

≈258 tokens for metadata: the name and description. ≈1.1k when used: this file. ≈1k more on demand in 2 files.

Description uses 12.8% of example budget

Before choosing a Skill, your Agent reads its name and description. All available Skills share that space.

  • A shorter description leaves more room for other Skills. This entry exceeds our 1% size suggestion.

In our Claude Code example, all Skill names and descriptions share 8,000 characters. This Skill uses ≈1028 characters, or 12.8%.

The 1% threshold is a size suggestion. Longer descriptions can still fit.

Your model, settings, and other Skills decide how much text your Agent can read.

Example settings and source

The example uses a 200k-token context and default Claude Code settings. The count includes the name, description, separators, and when_to_use when present. Codex also counts local file paths.

Skit's source and limits: Codex 0.160.1, Claude Code 2.1.292.

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:

  1. 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).
  2. 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.
  3. 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

  1. 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).
  2. Drop NULL keys when you derive a dimension from events. HogQL keeps a row whose property is missing when the filter only compares it to '', so add isNotNull(...) as well. A NULL key becomes a dimension row of its own and it matches nothing on the fact side.
  3. Alias to clean, stable names — country_code, region, plan_tier. These names become the join surface everything else depends on.
  4. Materialize static dimensions on a slow schedule; don't leave a constantly-read lookup virtual.
  5. 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.
  6. 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.

Source: SKILL.md on GitHub

No rule matched.

skilld matched fixed text patterns in SKILL.md and file names. Patterns miss obfuscated code.

skilld run checks every file with the same patterns. It asks for approval before it loads a Skill with a behavior marked Needs approval.

No third-party reports yet.

Signed by skilld at 028173b. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub yesterday.

Activeupdated last month

README badge

README badge for posthog/posthog/modeling-dimension-tables