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.

설치로 이동 파일 검토

저장소: PostHog/ai-plugin
최근 소스 활동: 2026년 10월 16일 08:22
감지된 SKILL.md 언어: 영어
스타: 95
포크: 20

설치 방법

기본적으로 소스를 먼저 확인하는 Prompt가 선택됩니다. 직접 명령으로 전환하거나 로컬 사본을 다운로드할 수도 있습니다.

AI 어시스턴트로 설치

이 프롬프트를 복사해 사용 중인 AI 어시스턴트에 입력하세요.

Follow https://skillsmp.com/skill-install/prompt.md to install only the Agent Skill at https://github.com/PostHog/ai-plugin/tree/main/skills/modeling-dimension-tables.

직접 명령은 검토 Prompt를 거치지 않습니다. 실행하기 전에 소스를 확인하세요.

npx skills add https://github.com/PostHog/ai-plugin --skill modeling-dimension-tables

명령은 한 줄로 유지됩니다. 복사하기 전에 가로로 스크롤해 전체 내용을 확인하세요.

로컬 사본을 원하시나요? SkillsMP에서 현재 제공할 수 있는 파일을 다운로드하세요.

소스 파일 검토

설치 여부를 결정하기 전에 SKILL.md와 SkillsMP에 표시된 보조 파일을 읽어 보세요.

파일 탐색기

7 개 파일

SKILL.md

소스 지침 · 읽기 전용 미리보기

name: modeling-dimension-tables
description: 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.

# 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`](references/dimension-catalog.md); recipes in [`references/posthog/`](references/posthog/) and [`references/dbt/`](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`](references/posthog/dim_country.sql) (derive + enrich from events), [`dim_plan.sql`](references/posthog/dim_plan.sql) (lookup/upload pattern). **dbt:** conformed `dim_*` models with `unique`/`not_null`/`relationships` tests, plus a generated `dim_date`. Recipes: [`references/dbt/`](references/dbt/). ## File map | File | Read when | | -------------------------------------------------------------------- | ----------------------------------------------------------- | | [`references/dimension-catalog.md`](references/dimension-catalog.md) | Common dimensions, how to source each, and the natural key. | | [`references/posthog/`](references/posthog/) | HogQL aliased-dimension view recipes. | | [`references/dbt/`](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`.

GitHub에서 보기

검색으로 돌아가기 설치로 돌아가기

creating-online-evaluations

Author continuously-running online evaluations in PostHog AI observability, grounded in real failure modes you've identified. Use when the user wants evaluations that automatically score new generations or whole traces going forward — "create an eval to catch X", "continuously check that responses do Y", "turn these failures into evals". Covers letting the explored data decide how many evals to create, proposing that set for the user to pick, choosing the target and eval type (hog / llm_judge / sentiment), configuring a provider and model for an llm_judge eval (a provider key gates enabling, not creation), scoping which generations trigger it via conditions, creating disabled, verifying scope, enabling, and backfilling past data. Proposes a sentiment eval when no failure mode is worth catching. Finding and ranking the failure modes worth evaluating is its own job — use exploring-ai-failures first. To debug or manage evaluations that already exist, use exploring-llm-evaluations.

2026-10-23

querying-posthog-data

Explains how to query PostHog data, defaulting to typed runners for supported product analytics. Read it before you write HogQL/SQL. Also read it before you call execute-sql against PostHog. Use it to find or aggregate PostHog entities. These entities include insights, dashboards, cohorts, feature flags, experiments, surveys, hog flows, warehouse data, and persons. Use it for trends, funnels, retention, lifecycle, paths, stickiness, web analytics, error tracking, logs, sessions, and LLM traces. Before you calculate a governed business or telemetry measure, check system.information_schema.metrics for an approved definition. Examples include MRR, activation, billable usage, active organizations, and failure rates. Use the approved definition before you derive a measure from raw events or use a typed domain tool. It also covers HogQL differences, system table schemas, functions, query examples, and schema discovery.

2026-10-23

reviewing-warehouse-suggestions

Reviews PostHog's warehouse suggestions with a person and acts on them. Explains why a view should be materialized, unmaterialized, certified or deprecated using the numbers in the suggestion, changes a materialize suggestion's refresh mode, keys or interval when the person wants something else, then accepts or dismisses it. Use when the user asks what PostHog suggests for their data warehouse, whether to materialize a view, why a view should refresh incrementally or every few hours, or wants to accept, change or dismiss a warehouse suggestion.

2026-10-23

이 저장소의 모든 Skills 보기

modeling-dimension-tables

소스 정보

설치 방법

AI 어시스턴트로 설치

소스 파일 검토

이 저장소의 다른 Skills

AI 어시스턴트로 설치

이 저장소의 다른 Skills