What is the PostHog to BigQuery template?

It is a free, open-source Bruin pipeline that loads PostHog events, persons, and feature flags into BigQuery, resolves identity across every distinct ID a person has been merged into, rolls persons up into accounts, and scores monthly engagement and product-qualified accounts. Run `bruin init posthog-bigquery` to generate all 13 assets and the dashboard in one command.

Do I need to know SQL to follow this tutorial?

You need to read SQL, not write much of it - the template ships all 13 assets already built. You will edit a handful of pipeline.yml variables to name your product's events and adjust scoring thresholds, and a coding agent can write any new report you add in the last step.

Is this PostHog analytics pipeline free to run?

Yes. The Bruin CLI, ingestr, and DAC are open source and run locally at no cost. You only pay for BigQuery storage and query usage, and PostHog's own API access under your existing plan.

Why does the tutorial insist on backfilling PostHog events one day at a time?

PostHog's events REST API silently returns partial data over any window wider than a day - a three-day window can return a single day's worth of events with no error. Step 2 of this tutorial walks through a day-by-day backfill loop that is the only reliable way to load historical events.

How is this different from PostHog's Group Analytics?

Group Analytics needs a $groups property sent on every event at capture time and cannot be applied retroactively, and it is a paid add-on. This pipeline derives the same account rollup in SQL from ordinary company and plan person properties, so it works on PostHog data you already have.

Can I run this pipeline on Snowflake, ClickHouse, or another warehouse instead of BigQuery?

Yes. Only the asset type, the ingestr destination, and a handful of BigQuery functions are specific to BigQuery. The last step of the tutorial includes a prompt for porting the pipeline to another warehouse with a coding agent.

How long does this tutorial take?

About 40 minutes of active work across seven steps, not counting how long your own PostHog event history takes to backfill one day at a time.

Bruin Academy

Guide

PostHog Product Analytics Pipeline with BigQuery

Build an account-level product analytics pipeline from PostHog in BigQuery. Initialize the posthog-bigquery template, backfill events one day at a time, resolve identity, model engagement and PQL scores, and serve a dashboard locally.

Updated August 25, 2026

Skip ahead

The whole project is one command. Run it, point your coding agent at the Bruin MCP, and it can configure and run everything itself.

$ bruin init posthog-bigquery

Three ways to go from here:

Self-service it. Let the agent configure and run the whole thing, and ask it questions as you go.
Follow this tutorial. Slower, and it explains why each setting matters - the part an agent will not guess for you.
Hand the tutorial to the agent. Point it at this page and have it work through the steps with you.

The template README is the short reference for everything it ships.

What

Turn raw PostHog events into account-level product intelligence in BigQuery. You will end up with 13 assets across three layers, four report tables, and a dashboard running on your machine.

posthog_raw - three ingestr assets: events, persons, and feature flags, loaded through the shared posthog-default connection
posthog_stage - six models that deduplicate events, resolve every distinct ID to one person, roll persons up into accounts, sessionize activity, and attribute feature-flag exposure
posthog_reports - four reports: monthly account engagement, product-qualified accounts, feature adoption by segment, and weekly retention cohorts
dashboards/ - one Dashboards as Code file you serve locally on localhost:8321

How

bruin init posthog-bigquery writes the whole project, so you are not authoring these assets - you are running them and deciding what to change. Backfill is the step to get right first: PostHog's events API silently returns partial data over any window wider than a day, so the raw layer only loads correctly one day at a time. After that, you tell the pipeline what your product's events and account attributes are called, run the three layers, and put the shipped dashboard and a coding agent on top.

PostHog is person-centric and has no account entity unless $groups was instrumented at capture time. This template derives one anyway, in SQL, from the company and plan properties on your person profiles - which is the same reason the account-level reports in this pipeline do not exist inside PostHog itself.

Note

The template targets BigQuery. The staging and reporting SQL is ordinary and ports to another warehouse, but the connection config and a handful of BigQuery functions - JSON_VALUE, SAFE_DIVIDE, APPROX_TOP_COUNT, ARRAY_AGG - are not.

Before you start

A PostHog personal API key with read access to your project
A Google Cloud project with BigQuery enabled and a service account or Application Default Credentials that can create datasets and tables
Git installed
A coding agent such as Claude Code, Cursor, or Codex - optional from Step 1 onwards, required for the last step

Guide overview

Start Guide

Frequently asked questions

What is the PostHog to BigQuery template?
It is a free, open-source Bruin pipeline that loads PostHog events, persons, and feature flags into BigQuery, resolves identity across every distinct ID a person has been merged into, rolls persons up into accounts, and scores monthly engagement and product-qualified accounts. Run `bruin init posthog-bigquery` to generate all 13 assets and the dashboard in one command.
Do I need to know SQL to follow this tutorial?
You need to read SQL, not write much of it - the template ships all 13 assets already built. You will edit a handful of pipeline.yml variables to name your product's events and adjust scoring thresholds, and a coding agent can write any new report you add in the last step.
Is this PostHog analytics pipeline free to run?
Yes. The Bruin CLI, ingestr, and DAC are open source and run locally at no cost. You only pay for BigQuery storage and query usage, and PostHog's own API access under your existing plan.
Why does the tutorial insist on backfilling PostHog events one day at a time?
PostHog's events REST API silently returns partial data over any window wider than a day - a three-day window can return a single day's worth of events with no error. Step 2 of this tutorial walks through a day-by-day backfill loop that is the only reliable way to load historical events.
How is this different from PostHog's Group Analytics?
Group Analytics needs a $groups property sent on every event at capture time and cannot be applied retroactively, and it is a paid add-on. This pipeline derives the same account rollup in SQL from ordinary company and plan person properties, so it works on PostHog data you already have.
Can I run this pipeline on Snowflake, ClickHouse, or another warehouse instead of BigQuery?
Yes. Only the asset type, the ingestr destination, and a handful of BigQuery functions are specific to BigQuery. The last step of the tutorial includes a prompt for porting the pipeline to another warehouse with a coding agent.
How long does this tutorial take?
About 40 minutes of active work across seven steps, not counting how long your own PostHog event history takes to backfill one day at a time.

Resources

PostHog to BigQuery template README Bruin CLI installation PostHog ingestion reference Dashboards as Code installation

Get help & contribute

Join Slack GitHub Docs