AGENTIC MEMORY

Skills

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

← Back to Skills

modeling-warehouse-foundations

Shared foundations for building reusable data models in PostHog, on either of two stacks: PostHog-native data-warehouse views / materialized views (HogQL, via the view-* MCP tools), or an external dbt project (sources.yml + staging/marts + schema tests) run against your own or PostHog's managed warehouse. Read before authoring any specific business model — covers the PostHog-vs-dbt decision, the view-create → view-materialize → sync_frequency workflow and the HogQL column-aliasing rule, the dbt project skeleton and the "no native dbt integration" picture, warehouse joins and star-schema dimensions, currency conversion with convertCurrency(), and checking/registering models in the data catalog for reuse. Companion to modeling-revenue-metrics, modeling-conversion-metrics, modeling-activation-metrics, modeling-product-usage-metrics, and modeling-dimension-tables. Use when the user asks how to build a view, materialized view, or dbt model in PostHog, or which of the two stacks to use.

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-warehouse-foundations 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 · 5

references/joins-and-dimensions.md

Joins, dimensions & currency

How to attach dimension/lookup data to fact data on each stack. The full star-schema treatment (building the dimensions themselves) is the modeling-dimension-tables skill; this is the shared mechanics.

PostHog: three ways to join

  1. Saved table join (persistent). Define once (SQL editor → source table → Add join), specifying source_table.key = joined_table.key. After that the joined table's columns are addressable as nested fields on the source table in any query, filter, or breakdown — e.g. after joining events.distinct_id → stripe_customer.email, you can SELECT stripe_customer.plan FROM events. This is the built-in way to hang a dimension off a fact stream without repeating JOIN syntax.
  2. Person join (persistent, special case). Join a warehouse/dimension table onto persons so its columns behave like native person properties across insights, filters, breakdowns, and cohorts — not just one query. Use for customer/account dimensions you want available product-wide.
  3. Ad-hoc HogQL join (one-off). Plain JOIN / LEFT JOIN in a query or view for a single analysis, no persistence. Fine inside a view's HogQL when the join is specific to that model.

Prefer a saved or person join for a dimension many models reuse; use an ad-hoc join for logic local to one view. When you don't know the join key, check system.information_schema.relationships for an accepted join before guessing (see governance.md).

dbt: joins live in marts

In dbt you JOIN staging models inside a marts/ model and assert the relationship with a relationships test in schema.yml (fact row's FK exists in the dimension). There's no "saved join" concept — the join is just SQL, and the test guarantees referential integrity.

Currency

PostHog ships a managed exchange-rate dimension so you don't build one:

convertCurrency(from_currency, to_currency, amount, timestamp?)
-- e.g. normalize each charge to USD at its historical rate:
SELECT convertCurrency(currency, 'USD', amount, timestamp) AS amount_usd FROM ...

Rates come from Open Exchange Rates, stored at daily granularity, applied at the historical rate as of the timestamp (omit it for the latest rate). Use this for any multi-currency revenue model instead of a hand-rolled rate table. It is not configurable to another rate provider.

In dbt there is no equivalent — supply your own rate table (a dbt seed CSV, or a synced source) keyed by (currency, date) and join to it in the mart. This is the main place the two stacks diverge.

Star schema in one line

Facts (events, charges, revenue items) carry foreign keys; dimensions (dim_country, dim_plan, dim_currency) carry descriptive attributes. On PostHog, dimensions are aliased (often materialized) views attached via saved/person joins; in dbt they are dim_* marts joined in fct_* models. Keep dimension grain at one row per entity and test the key is unique.