Guidelines

Search types

Proactively use different search types depending on a task:

• Grep-like (regex search) with match(), LIKE, ILIKE, position, multiMatch, etc.
• Full-text search with hasToken, hasTokenCaseInsensitive, etc. Make sure you pass string constants to hasToken* functions.
• Dumping results to a file and using bash commands to process potentially large outputs.

Substring search on events-table strings is a full scan: LIKE '%term%', ILIKE '%term%', and position() read the column for every row in the time range, and a leading % makes indexes useless. Before fuzzy-matching a property value, try read-data-schema (event_property_values) to find common exact values. If the requested value is not returned, or the user needs true contains semantics, keep the timestamp window tight, filter event first, and use the substring predicate.

Data Groups

PostHog has two distinct groups of data you can query:

1. System Data (PostHog-Created Data)

Data created directly in PostHog by users - metadata about PostHog setup.

All these tables are prefixed with system.. The most-used entities are system.insights, system.dashboards, system.cohorts, system.feature_flags, system.experiments, system.surveys, system.actions, and system.notebooks — list the full, current set (it drifts as products are added) via information_schema, covered in Schema discovery below.

Example - List insights:

Example - Count insight variables:

All entities are scoped by a team by default. You cannot access data of another team unless you switch a team.

2. Captured Data (Analytics Data)

Data collected via the PostHog SDK - used for analytics.

Table | Description events | Recorded events from SDKs persons | Individuals captured by the SDK. “Person” = “user” groups | Groups of individuals (organizations, companies, etc.) sessions | Session data captured by the SDK Data warehouse tables | Connected external data sources and custom views

Discover the columns and relationships of these tables with information_schema, covered in Schema discovery below.

Key concepts:

• Events: Standardized events/properties start with $ (e.g., $pageview). Custom ones start with any other character.
• Properties: Key-value metadata accessed via properties.foo.bar or properties.foo['bar'] for special characters
• Person properties: Access via events.person.properties.foo or persons.properties.foo
• Person property modes: person.properties.* behavior depends on the project’s person-on-events setting. Check the project metadata to determine if values are event-time (value at ingestion) or query-time (current value). See Person property modes (opens in a new tab) for details.
• Unique users: Use events.person_id for counting unique users

Example - Weekly active users:

3. Document Embeddings (Semantic Search)

The document_embeddings table stores text content with vector embeddings, partitioned by model_name. To discover what kinds of data are available:

Run separately for each model. Available models: 'text-embedding-3-small-1536', 'text-embedding-3-large-3072'. You MUST filter on exactly one model_name per query — it routes to the correct underlying ClickHouse table. IN clauses and cross-model queries will fail.

Use embedText(text, model_name) and cosineDistance() for semantic search. See the signals skill for detailed query patterns around the signals product specifically, including required deduplication and metadata extraction.

For a named business or operational measure, look it up in the data catalog (system.information_schema.metrics) before any schema discovery. Run an approved, non-drifted match with data-catalog-metric-run instead of deriving it. Every other outcome means there is no canonical definition to reuse — no match, a drifted match, or a match that is not approved: derive the measure with the schema workflow below and label the result noncanonical. A project without the data catalog has neither that table nor that tool, so an unknown-table error is that case too, and it holds for the rest of the session: stop checking. Everything else starts with that workflow.

Schema discovery (information_schema)

Don’t guess table or column names — they differ per entity and drift over time. Discover the live schema for every data group above (system, captured, and data-warehouse tables) by querying system.information_schema via execute-sql. Four virtual tables carry the schema itself, and each one holds a different set of fields — project a field on the surface that owns it, or the query fails:

• tables — one row per table. Fields: table_catalog, table_schema, table_name, table_type, description, row_count, certification. table_type is one of system, data_warehouse, view, posthog (built-in analytics tables like events / persons), or information_schema. certification is the settled trust mark (certified / deprecated) and lives only here, not on columns.
• columns — one row per column. Fields: table_schema, table_name, column_name, ordinal_position, data_type, is_nullable, is_array, field_kind, description, null_fraction, min_value, max_value. The last three are profiling statistics and are filled in for data-warehouse columns only.
• relationships — one row per joinable relationship. Fields: source_table, source_column, target_table, target_column, relationship_kind, via, confidence, reasoning.
• data_types — one row per HogQL type. Fields: type_name, description.

certification on tables and confidence / reasoning on relationships come from the data catalog. The project catalog, which is what execute-sql reads by default, always carries all three. A caller without data catalog access still selects them, and every value reads NULL. A NULL there never means a wrong field name.

The two surfaces differ in what else a NULL means. On tables, certification reads NULL for a table nobody marked. On relationships, confidence and reasoning hold the review evidence of an accepted relationship proposal. Only a data warehouse join that still matches its proposal carries that evidence. Every built-in join and every field traverser reads NULL for both fields, even on a project with full catalog access. A NULL there means no review evidence, not a broken join: read source_column and target_column, and use the join.

The same namespace carries six more catalog surfaces, each about project state rather than schema: metrics, certifications (the full trust-mark review queue, as opposed to the settled tables.certification mark), relationship_proposals, data_quality_checks, data_quality_check_runs, and data_quality_health. The project serves the three data-quality surfaces only while data quality checks are on for it.

A direct connection queried with connectionId is the runtime that drops surfaces. It serves tables, columns, and data_types only, and its tables has no certification. Leave certification out of a connectionId query. Do not read relationships there before a join, because the surface is absent and the query fails on an unknown table.

Every surface describes itself, so its live field set is always discoverable — ask the catalog instead of trusting the lists above:

List tables — filter table_type to target a group (system, data_warehouse, view, posthog):

Find a table by what its docs say — names are often opaque (especially data-warehouse tables), so search the description text instead of guessing names. The documentation lives in system.information_schema.tables.description (the catalog) — not on the system.data_warehouse_tables entity, which only holds connection metadata:

Column docs are searchable the same way via system.information_schema.columns.description (the system. prefix is required — a bare information_schema.columns is an unknown table). Prefer an ILIKE filter over dumping the whole catalog and scanning it yourself.

Inspect a table’s columns:

This works for system.* entity tables too — query them by full name, e.g. WHERE table_name = 'system.insights'. Their column sets differ per entity, so confirm columns before projecting them.

Discover how a table joins to others:

relationship_kind is either lazy_join (a foreign-key-style join to a related table, e.g. events.person_id to persons) or field_traverser (an alias that hops to another field on the same row); via names the resolver when one applies.

Interpret a data type — data_types describes the possible values of columns.data_type (String, Integer, Float, Decimal, Boolean, Date, DateTime, UUID, JSON, Array, Struct, Expression, VirtualTable, Unknown):

information_schema covers table and column structure. To verify which events, properties, and property values actually exist in captured data, use read-data-schema (see Schema verification below).

Querying guidelines

Schema verification

Before writing analytical queries, always verify that:

• The required event names or actions exist.
• Properties and property values of events, persons, sessions, and groups data exist.

Follow this workflow:

1. Fetch the tool schema - Use posthog:read-data-schema to get the latest schema from the MCP.
2. Verify data exist - Use posthog:read-data-schema with different data types to check if the data you need is captured
3. Only then write the query - Once you’ve confirmed the data exists, write and execute your analytical query

This prevents wasted API calls and gives users immediate feedback when the data they’re looking for doesn’t exist.

Progressive exploration

For unfamiliar or potentially large datasets, probe cheaply before running the expensive aggregation. Widen only if the cheap step looks reasonable:

1. Count first — SELECT count() FROM events WHERE timestamp >= now() - INTERVAL 1 DAY AND event = 'foo'. Confirms the data exists and gives a sense of volume.
2. Small sample — inspect a handful of rows (LIMIT 10) to verify property shapes and values match expectations.
3. Full query — run the real aggregation with a time range and LIMIT, having confirmed it won’t scan needlessly or return empty.

This is faster than discovering an empty result or a mis-shaped property after the full aggregation, and it costs less.

Skipping index

You should use the skipping index signature to write optimized analytical queries.

Time ranges

All analytical queries and subqueries must always have time ranges set for supported tables (events). If the user doesn’t state it, assume default time range based on the data volume, like a day, week, or month.

The bound must be a WHERE predicate on timestamp. A time condition that appears only inside an aggregate argument, like countIf(event = 'x' AND timestamp > now() - INTERVAL 1 DAY), filters nothing: every historical row is still read. Put the outer window in WHERE and keep only the split inside the aggregate.

Point lookups need a time bound too. Filtering on a session id, trace id, distinct_id, or a property value without a timestamp bound scans the team’s entire history, because those filters don’t align with the table’s date-first sort key. Derive the window from context (the session’s day, the incident’s date), or start with a recent window and widen only if the result is empty.

How you should use time ranges

How you should NOT write queries

JOINs

General guidelines

Keep in mind that the right expression is loaded in memory when joining data in ClickHouse, so the joining query or table must always fit in memory. Common strategies:

• Analytical functions and combinators.
• Subqueries as a source or filter.
• Arrays (arrayMap, arrayJoin) and ARRAY JOIN.

A subquery used as a join/correlation source must pre-filter and, where possible, pre-aggregate — push the time range, WHERE, and any GROUP BY inside it so the right side stays small in memory. Wrapping a full table in a subquery without narrowing it gains nothing. When you only need a single match per row (enrichment lookups, e.g. attaching one attribute from system data), use LEFT ANY JOIN — it stops at the first match, using less memory and running faster than a regular join.

System data

You are allowed joining system data. Insights are the most used entity, so keep it on the left.

Analytical data

Prefer using analytical functions and subqueries for joins. Do not use raw joins on the events table.

Scan events once per question where you can: conditional aggregates (countIf, sumIf, uniqIf, argMax) or a window function over one scan replace a self-join, repeated subqueries over the same rows, and UNIONs of the same range. CTEs are inlined, not materialized: a CTE referenced twice executes twice. Subqueries that correlate different events (like the example below) are fine.

How you should join data

How you should NOT join data

Other constraints

• Your query results are capped at 100 rows by default. You can request up to 500 rows using a LIMIT clause. If you need more data, paginate using LIMIT and OFFSET in subsequent queries.
• You should cherry-pick properties of events, persons, or groups, so we don’t get OOMs. Never select the full properties object (e.g., SELECT properties FROM events) and dump it into the conversation output. Instead, select only the specific properties you need (e.g., properties.$browser, properties.$os). If you must inspect the full properties object, dump the query results to a file and use bash commands to explore it.
• When query results contain large JSON blobs (e.g., AI trace inputs/outputs, full property objects), always dump them to a file rather than outputting them directly. Use bash commands to process the file.

HogQL Differences from Standard SQL

Property access

Unsupported/changed functions

Don’t use | Use instead toFloat64OrNull(), toFloat64() | toFloat() toDateOrNull(timestamp) | toDate(timestamp) LAG(), LEAD() | lagInFrame(), leadInFrame() with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING count(*) | count() cardinality(bitmap) | bitmapCardinality(bitmap) split() | splitByChar(), splitByString()

JOIN constraints

Relational operators (>, <, >=, <=) are forbidden in JOIN clauses. Use CROSS JOIN with WHERE:

Syntax extensions and HogQL functions

Find the reference for Sparkline, SemVer, Session replays, Actions, Translation, HTML tags and links, Text effects, and more (opens in a new tab).

Other rules

• WHERE clause must come after all JOINs
• No semicolons at end of queries
• toStartOfWeek(timestamp, 1) for Monday start (numeric, not string)
• Always handle nulls before array functions: splitByChar(',', coalesce(field, ''))
• Performance: always filter events by timestamp
• Correctness: count unique users with uniq(person_id) on events, never uniq(distinct_id) (one person has many distinct_ids, so distinct_id overcounts users)
• Memory: avoid GROUP BY on unbounded high-cardinality expressions (raw URLs, ids, free text) over wide windows: the aggregation holds every distinct value in memory regardless of LIMIT; normalize the value (strip ids from paths) or narrow the window

SQL Variables

Review the reference (opens in a new tab) for SQL variables and dashboard filters.

Available HogQL functions

Verify what functions are available using the reference list (opens in a new tab) with suitable bash commands.

Examples

Weekly active users with activation event:

Find cohorts by name:

List feature flags:

• references/guidelines.md(opens in a new tab)
• Raw file(opens in a new tab)
• PostHog/skills(opens in a new tab)