skills.rest
PostHog
PostHogOFFICIAL
Explore all skills by this author →
Website𝕏@PostHogGitHub

modeling-dimension-tables

Build reusable dimension tables for star schemas on PostHog warehouse views or dbt projects.

713|118|Updated Aug 11, 2020
Search Tags:#data modeling#data warehouse#PostHog#star schema#DBT#HogQL#dimension tables
One-click install
npx skills add https://github.com/PostHog/posthog-foss --skill modeling-dimension-tables
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: modeling-dimension-tables
Source: https://github.com/PostHog/posthog-foss/tree/main/products/data_modeling/skills/modeling-dimension-tables
Command: npx skills add https://github.com/PostHog/posthog-foss --skill modeling-dimension-tables

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

Analysts repeatedly re-derive the same lookups (country, region, plan, date) across queries, causing duplicated JOINs, inconsistent naming, and fan-out errors from non-unique dimension keys. This Skill builds each dimension once as a clean, one-row-per-entity table that every fact model reuses.

Core Features & Use Cases

  • Three sourcing patterns: upload/seed a CSV lookup, sync from a system of record via a warehouse source, or derive dimensions from event properties like $geoip_country_code.
  • Shaping and materialization: aliased HogQL views or dbt models with unique/not_null tests, materialized on slow schedules since dimensions change rarely.
  • Join attachment: saved joins or person joins so dimension columns read as native fields on revenue, conversion, and usage facts.
  • Use Case: Enrich revenue events with plan tier and price by building a dim_plan view deduped with argMax, then attaching it via a saved join so tier-level revenue breakdowns need no repeated JOINs.

Quick Start

Ask the AI to build a country dimension table from PostHog event geoip properties enriched with an uploaded country-to-region lookup, materialized on a weekly schedule.

Frequently Asked Questions about modeling-dimension-tables

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I build a dimension table in PostHog?▼

Source the data by uploading a CSV lookup, syncing a warehouse source, or deriving values from event properties. Then shape it into an aliased HogQL view with one row per entity, materialize it on a slow sync schedule, and attach it to facts with a saved join.

How to create a date dimension in dbt?▼

Use dbt_utils.date_spine to generate a calendar spine over your date range, then select derived attributes like year, month, week_start, quarter, and is_weekend. Materialize it as a table and add unique and not_null tests on the date column in schema.yml.

Should I build a currency conversion table in PostHog?▼

No, PostHog ships exchange rates behind the built-in convertCurrency(from, to, amount, timestamp) function with historical-rate accuracy. Only build a currency rate dimension in dbt, which has no equivalent, or when you need a rate provider PostHog does not offer.

Why does my dimension join duplicate fact rows?▼

Duplicate rows appear when the dimension's natural key is not unique, causing fan-out on every joined fact. Dedupe to one row per key using argMax by updated-at in HogQL, or enforce unique and not_null tests on the key column in dbt.

When should I materialize a dimension table?▼

Materialize dimensions that are read constantly but change rarely, using a slow sync frequency such as 7day or 30day. Leaving a frequently-read lookup as a virtual view wastes compute on every query that touches it.

Related Skills

Explore More Data & Analytics Skills→
naopensedoit2-designnaopensedoit2-design

stripe

Set up Stripe migrations, managed webhooks, and PostgreSQL sync workflows.

Community
Advanced
HeadyAIHeadyAI
1

heady-monetization-matrix

Develop monetization models for subscription, usage-based, and marketplace billing.

Official
Advanced
outcomeengoutcomeeng
1

understanding

Load Spec Tree core references and emit a <SPEC_TREE_FOUNDATION> marker.

Official
Basic
PostHogPostHog
58

tools-and-features-hogql

Create HogQL queries to analyze PostHog events, properties, and sessions.

Official
Advanced