PostHog as a data warehouse source

Last updated:

|

Much of your PostHog data is available in the data warehouse by default. This includes data like events, persons, sessions, groups, and the query log.

Using our SDKs or API to capture events, identify users, create groups, and add properties adds data to these tables, that you can query using the SQL editor.

PostHog data warehouse tables

events

Events are the core unit of data in PostHog, so you'll likely have a lot of them. This also makes the events table one of the most useful.

Some of the columns in the events table are:

ColumnDescription
uuidUnique ID of the event
eventName of the event
propertiesObject containing all properties of the event (stored as a String). You can see the default properties in our data docs.
timestampTime the event was captured (ISO 8601 format)
distinct_idUnique identifier tying together events from the same person
session_idUnique identifier for the session the event belongs to
person_idUnique identifier for the person

event names and properties can be set to any value you want, so you might many potential values for each.

Some products have special event names and properties that can be useful to know. For example:

  • Autocaptured events are named $autocapture and have the elements_chain property.

  • LLM analytics captures events like $ai_generation, $ai_span, $ai_embedding with properties like $ai_model, $ai_input_tokens, and $ai_total_cost_usd.

  • Error tracking captures $exception events with properties like $exception_list and $exception_fingerprint both with a specific expected schema.

persons

The users behind your events are known as persons in PostHog.

Some of the columns in the persons table are:

ColumnDescription
idUUID of the person. Relates to person_id in the events table.
created_atDate and time when the person was first created (ISO 8601 format).
propertiesObject containing all properties associated with the person (stored as a String).
is_identifiedIndicates if the person is identified (1) or anonymous (0).

Beyond storing demographic data about your users, the persons table is powerful as it can connect to many other tables. For example, the persons table can be queried through the events using the person field like SELECT person.properties.$initial_browser FROM events. It can also be joined to external sources to create extended person properties.

groups

Groups enable you to aggregate events based on an entity like an organization, project, or team if you have set up group analytics.

Some of the columns in the groups table are:

ColumnDescription
indexNumeric index of the group type. You can view the group types in the People tab. It is zero-based, so the first group type listed there is 0.
keyUnique string identifier for the group. Relates to one of the group keys on the events table, such as $group_0, depending on the index.
created_atDate and time when the group was first created (ISO 8601 format).
updated_atDate and time when the group was last updated (ISO 8601 format).
propertiesObject containing all properties associated with the group (stored as a String).

sessions

If you are using PostHog's snippet or JavaScript Web SDK, PostHog automatically captures session data which you can query using the sessions table.

Unlike the other tables, the sessions table stores all its properties as separate columns. This means there are a lot more of them. Some of them include:

ColumnDescription
session_idUnique identifier for the session. Relates to $session_id on the events table.
distinct_idUnique identifier for the user (person). Relates to $distinct_id on the events table.
$start_timestampTimestamp when the session started
$end_timestampTimestamp when the session ended
$num_uniq_urlsNumber of unique URLs visited in the session
$entry_current_urlURL of the page where the session started
$end_current_urlURL of the page where the session ended
$entry_referring_domainReferring domain for the session's entry
$entry_utm_sourceUTM source for the session's entry
$pageview_countNumber of pageviews in the session
$autocapture_countNumber of autocapture events in the session
$screen_countNumber of screen events in the session
$channel_typeChannel type for the session's entry (e.g., organic, paid)
$session_durationDuration of the session in seconds
$is_bounceIndicates if the session was a bounce (1 = bounce, 0 = not)
$last_external_click_urlLast external click URL in the session
$vitals_lcpLargest Contentful Paint (LCP) web vital for the session
... moreSee full list of session properties

The sessions table is heavily used in web analytics. It is also useful for marketing analytics and session-based metrics more broadly.

query_log

The query_log table provides access to query execution metadata and performance metrics. You can learn more about it in the query log data docs.

Like the sessions table, the query_log table stores all its properties as separate columns. Some of them include:

FieldDescription
event_dateDate when the query was executed
event_timeExact timestamp when query started
query_idUnique identifier for the query
endpointAPI endpoint that triggered the query
queryThe actual HogQL/SQL query that was executed
query_start_timeWhen query execution began
query_duration_msQuery execution time in milliseconds
created_byUser ID who initiated the query
read_rowsNumber of rows processed
read_bytesBytes read during execution
result_rowsNumber of rows returned
result_bytesSize of result set in bytes
memory_usagePeak memory usage in bytes
exception_codeException code if the query failed
is_personal_api_key_requestWhether request used personal API key
api_key_maskMasked API key used
cpu_microsecondsCPU time consumed (for computation, excluding waiting)
RealTimeMicrosecondsWall clock time in microseconds
... moreSee full list of query log properties

The query_log table is the only table with data on you and your team's internal usage of PostHog, making it useful for security, governance, and performance optimization.

Questions? Ask Max AI.

It's easier than reading through 800 pages of documentation

Community questions

Was this page useful?

Next article

Linking Azure SQL Server as a source

Read next article