AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

modeling-dimension-tables

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.

Original source
posthog
License
MIT
Pinned revision
cac7c80a9e5b73484d18eda1bffb47297c938e0c

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

This skill also requires local files or dependencies. Remote reading alone may not be enough.

Ask your assistant

Use Agentic Memory skills to read posthog--modeling-dimension-tables at revision cac7c80a9e5b73484d18eda1bffb47297c938e0c, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 2

references/dimension-catalog.md

Common dimensions catalog

For each dimension: how to source it, its natural key, and notes. Model each as one row per key.

| Dimension | Natural key | Source it by | Notes | | ---------------------- | --------------------------------------- | ------------------------------------------------------------------------------ | -------------------------------------------------------------------------------------------- | | Country | country_code (ISO-2) | Derive from properties.$geoip_country_code on events, or upload an ISO list. | Enrich with region/continent from an uploaded lookup. | | Region / continent | country_code → region | Upload a country→region CSV (seed / warehouse source). | Conforms with the country dimension; join on country_code. | | Timezone | timezone (IANA, e.g. Europe/Berlin) | From properties.$timezone, or a country→timezone lookup (upload). | One country can have many timezones; key on the IANA string, not the country. | | Currency | currency (ISO-4217) | Built in — use convertCurrency(); no table needed on PostHog. | Only build a dim_currency_rate in dbt (no equivalent there) or for a non-default provider. | | Date | date | Generate a calendar spine (date, week, month, quarter, DOW, is_weekend). | Classic conformed dimension; cheap; materialize once. | | Plan / tier | plan_id or plan_name | Upload a plan→tier→price lookup, or sync from Stripe products. | Join to revenue facts for tier-level revenue. | | Product | product_id | Managed revenue_analytics product view, or sync from source. | Prefer the managed view if it exists (see modeling-revenue-metrics). |

Sourcing patterns

  • Upload / seed — small, hand-maintained lookups (country→region, plan→tier). On PostHog: upload a CSV as a warehouse source. In dbt: a seeds/*.csv + dbt seed.
  • Warehouse source — dimensions owned by a system of record (Stripe products, your app's plans table). Connect via setting-up-a-data-warehouse-source, then shape the raw table into a clean aliased view.
  • Derive from events — dimensions implied by captured data (countries actually seen, plans observed). SELECT DISTINCT + argMax to pick the latest value per key. Cheapest, but only covers values that appear in events.

Keys and grain

The natural key must be truly unique in the dimension — dedupe before saving. If a raw source has multiple rows per key (history, per-source duplicates), collapse to one row (argMax by updated-at) or model an explicit slowly-changing dimension. Fact tables carry the key; the dimension carries everything else.