Notion to ClickHouse: How to Move Your Data

Moving Notion into ClickHouse gives you fast counting over a workspace that has grown past the point anybody can survey by scrolling. How many pages exist, who created them, which have not been touched in a year: all easy questions that Notion answers slowly at scale.

This guide covers the managed path with Airbyte. Two things shape the build: you can only see what somebody shared, and nothing tells you what they did not, and page content arrives as a tree that a column store is poorly suited to walking.

Notion to ClickHouse at a glance:

Why move data from Notion to ClickHouse?

One situation genuinely suits this, and the other is worth naming plainly.

The good case is aggregating over a large workspace, or joining Notion metadata to something high-volume you already keep here. Counting pages by creator, measuring how documentation has grown and finding what has gone stale are simple aggregations that get slow in the interface once a workspace is big.

The poor case is working with the writing itself, because page content is a nested tree and a column store is not built for walking one. If you want to process the text, Notion to Databricks reads that structure natively and is the better destination by a wide margin.

What do you need before you start?

Four things, and the first determines what your numbers will actually mean:

An integration, and pages shared with it. Notion access is granted per page and inherited by children, so sharing a top-level parent is far more efficient than sharing pages individually. The Notion source documentation covers the setup.

A written record of what was shared. Nothing in the data describes the boundary, so without a note your aggregates are over an unknown proportion of the workspace.

Awareness of the version 4.0.0 stream rename. What was called databases became data sources, so anything written against the old name stops finding data while the pipeline continues reporting success.

A ClickHouse database and realistic expectations. This destination will count your pages quickly and will not help you read them, which is worth agreeing before anybody hopes otherwise.

If your database restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.

How do you build a Notion to ClickHouse pipeline in Airbyte?

Step 1: Share from the top, and write down what you shared

Work out which top-level pages cover the parts of the workspace your analysis is about, share those with the integration, and record the list somewhere your team will find it. Sharing inherits downward, so a handful of parents usually covers everything. The record matters more than it sounds, because every number you produce afterwards is a number about whatever was on that list.

Step 2: Configure the Notion source

Click Sources in the left navigation, then New Source, and select Notion, following adding a source. Authenticate and set a start date, then select streams. Take the blocks stream only if you have a reason, since it is by far the largest and the least useful in this destination.

Step 3: Configure the ClickHouse destination

Click Destinations, then New Destination, and select ClickHouse, following adding a destination. Supply the host, port, database and credentials. Records land in typed columns, and anything Notion expresses as a nested structure will be considerably less pleasant to query than the flat fields beside it.

Step 4: Create the connection and sort by date

Click Connections, then New connection, select your streams and a sync mode. Daily is generous for a workspace that changes when somebody writes something. Sort by the created or edited timestamp, since questions about growth and staleness all bound a period.

Then check the stream names against anything you have written, particularly if you upgraded across version 4.0.0.

What is missing from your aggregate?

Whatever nobody shared, and nothing will tell you. Notion grants access page by page, an integration sees only what has been shared with it, and an unshared page does not produce an error or a warning. It is simply absent, which is a very different failure from one that announces itself.

That matters more in a counting destination than anywhere else. The whole point of bringing a workspace here is to say how many pages exist, how fast documentation is growing and which teams maintain theirs, and every one of those is a proportion of an unknown whole. A department that never shared its space is invisible rather than zero, and the two look identical in a chart.

So treat the shared list as part of the dataset's definition. Keep it beside the tables, revisit it when somebody asks why a team looks inactive, and share from top-level parents so the boundary is a short list rather than a long one. If a number is going to be presented to anybody, say what it covers alongside it.

Why is page content awkward here?

Because a Notion page is a tree and this destination is built for flat rows. Content is made of blocks, each of which can contain further blocks, nested many levels deep, and reconstructing a page means walking that structure from the top. ClickHouse is superb at scanning columns and was never intended for recursive traversal.

This is the clearest case in the catalogue where the same source suits two destinations completely differently. A lakehouse reads that nesting natively and lets you assemble page text in a notebook, which is why language processing over documentation belongs there. A column store gives you speed on the flat metadata surrounding the content and very little help with the content itself.

So scope this pipeline to the metadata and mean it. Pages, users, comments and the database definitions give you everything a governance or adoption question needs, and they are flat enough to model comfortably. If somebody later wants to search or classify the writing, that is a second pipeline to a different destination rather than a query you can rescue with a clever view.

Frequently asked questions

A whole section of the workspace is missing.

It was not shared with the integration. Unshared pages produce no error, so share the relevant top-level parent and re-sync.

Should I sync the blocks stream?

Only with a reason. It is the largest stream by far and its nested structure is exactly what this destination handles least well.

My queries stopped returning rows after an upgrade.

Version 4.0.0 renamed the databases stream to data sources, so anything referencing the old name finds nothing while the pipeline still reports success.

Can I search page text from here?

Not comfortably. Content is a nested block tree, and assembling or searching it belongs in a lakehouse or a search engine rather than a column store.

Can I do this without writing code?

The pipeline, yes, and the metadata questions need only ordinary SQL. It is the content questions that need a different destination entirely.

Get your Notion data into ClickHouse

Share from top-level parents and write down what you shared, because unshared pages are absent rather than flagged and every aggregate you produce is a proportion of that list. Scope the pipeline to flat metadata and leave the blocks stream alone unless you have a reason. Then sort by date, and send anybody who wants to work with the writing itself to a destination built for trees.

Airbyte's connector catalog includes 600+ pre-built connectors, so a workspace can be measured without anybody scrolling through it. For the same source into a warehouse, see Notion to Snowflake, and for another collaborative tool into the same destination, Airtable to ClickHouse.

Start syncing now →