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

modeling-warehouse-foundations

Build reusable data models as PostHog views or external dbt projects.

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

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

Teams repeatedly re-derive the same metrics (MRR, activation, conversion) across dashboards and queries, producing inconsistent numbers. This Skill provides the shared foundations for turning a metric definition into a durable, reusable model on either PostHog-native data-warehouse views or an external dbt project.

Core Features & Use Cases

  • Stack selection guidance: Decide between PostHog-native views (HogQL via view-create/view-materialize) and an external dbt project (staging/marts with schema tests), with an honest picture of PostHog's lack of native dbt integration.
  • View lifecycle workflow: Write HogQL, create a virtual view, materialize it with a sync_frequency when it earns it, and clean up throwaway views — including the mandatory column-aliasing rule that causes most view-create failures.
  • dbt project skeleton: Copy-paste starting files (dbt_project.yml, sources.yml, staging model, mart, schema.yml tests) for modeling PostHog and business data in your own warehouse.
  • Joins, dimensions, and currency: Attach dimension tables via saved or person joins, and normalize multi-currency revenue with the built-in convertCurrency() HogQL function.
  • Governance: Check the semantic layer for approved canonical metrics before deriving, and register models with column annotations after building.
  • Use Case: A data analyst needs a company-wide MRR model. The Skill guides them to check the data catalog for an approved definition, then build it as a materialized PostHog view with aliased columns and convertCurrency() normalization, or as a dbt mart with uniqueness tests.

Quick Start

Ask the AI to help you build a reusable metric model in PostHog, starting by checking whether an approved definition already exists in the data catalog and choosing between a PostHog view and a dbt model.

Frequently Asked Questions about modeling-warehouse-foundations

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

FAQPage Schema
How do I create a materialized view in PostHog?▼

Write and test your HogQL query first, then call posthog:view-create with a name and HogQLQuery, and finally posthog:view-materialize to add a physical table and sync schedule. Every selected column must be aliased with AS, since view-create rejects SELECT * and unaliased columns.

Should I use PostHog views or dbt for data modeling?▼

Use PostHog-native views when your data already lives in PostHog and you want it usable in insights and dashboards with no extra infrastructure. Choose dbt when your team already runs it, needs multi-model lineage with CI tests, or models data outside PostHog.

Does PostHog have a native dbt integration?▼

No, PostHog has no native dbt connector. dbt always runs externally in your own environment against a warehouse, either one you sync PostHog data into or PostHog's beta managed DuckDB warehouse, which is waitlist-gated.

Why does posthog:view-create reject my query?▼

The most common cause is unaliased columns or SELECT *, which view-create rejects outright. Rewrite every selected expression with an explicit AS name, such as SELECT toStartOfMonth(timestamp) AS month, and verify referenced events and properties exist.

How do I convert currencies in PostHog HogQL queries?▼

Use the built-in convertCurrency(from, to, amount, timestamp?) function, which applies historical daily rates from Open Exchange Rates. In dbt there is no equivalent, so you must supply your own rate table as a seed or synced source and join to it.

When should I materialize a PostHog view instead of leaving it virtual?▼

Materialize when the query is expensive and read often, reused by dashboards or other views, or a slowly-changing dimension. Leave cheap, ad-hoc, or real-time queries virtual, since materialized reads can be stale up to one sync_frequency interval.

Related Skills

Explore More Data & Analytics Skills→
PostHogPostHog
58

setting-up-a-data-warehouse-source

Connect external data warehouse sources to PostHog with per-table sync methods.

Official
Advanced
SignalPilot-LabsSignalPilot-Labs
475

domain-ecommerce

Clarify the ecommerce transaction lifecycle and metric definitions to prevent misreporting.

Official
Intermediate
AradotsoAradotso
7

ktx-data-agent-context-layer

Build a semantic context layer for AI agents querying data warehouses.

Official
Advanced
thenslsthensls

posthog

Query PostHog analytics with HogQL and manage dashboards, funnels, and feature flags.

Official
Advanced