Metabase to ClickHouse: How to Move Your Data

Moving Metabase into ClickHouse only makes sense for one reason: joining your reporting catalogue to something high-volume you already keep there. The catalogue itself is small, and a column store has nothing to offer a few thousand rows on their own.

This guide covers the managed path with Airbyte. Two things shape the build: the join is the entire justification and should be named before you start, and the catalogue describes only the present unless you arrange otherwise.

Metabase to ClickHouse at a glance:

Why move data from Metabase to ClickHouse?

One situation genuinely justifies this, and it is narrow enough to check first.

The good case is joining the catalogue to volume you already hold here. If your query logs, warehouse usage records or web analytics live in ClickHouse, adding the definitions of your dashboards lets you ask which reports drive load, which are expensive and which nobody has opened.

The poor case is the catalogue by itself, which is a few thousand rows and needs nothing this destination provides. For governance questions with no high-volume join, Metabase to PostgreSQL is far less to operate.

What do you need before you start?

Four things, and the first decides whether to continue:

A named table to join against. Query logs, usage events or anything else large already sitting in ClickHouse. Without one, this destination is doing nothing a small database could not.

Metabase credentials, and a plan for keeping them working. A session token expires after roughly a fortnight, while username and password authentication creates a session per query and can trigger security alerts. The Metabase source documentation covers both.

Agreement about what this contains. Questions, dashboards and collections are definitions rather than results, so the rows your reports return stay in whichever database Metabase queries.

A decision about keeping history. The catalogue describes the present, so whether you can answer questions about how the estate changed is something you choose at setup.

If your database or Metabase instance restricts traffic by IP, add the Airbyte Cloud IP addresses to the relevant allow lists before you begin.

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

Step 1: Name the table you are joining to

Identify the high-volume table this catalogue will sit beside, because that is the justification and there is not another one. Query logs joined to dashboard definitions answer which reports cost you most; usage events joined to collections show which teams' work is actually read. If no such table exists here, build this somewhere lighter.

Step 2: Configure the Metabase source

Click Sources in the left navigation, then New Source, and select Metabase, following adding a source. Supply your instance URL and credentials. Expect definitions of questions, dashboards and collections rather than the data those questions return.

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. Put these tables in the same database as whatever you are joining them to, since crossing databases for a small lookup is needless friction.

Step 4: Create the connection and accumulate

Click Connections, then New connection, select your streams and a sync mode. Daily is generous for a catalogue that changes when somebody builds a dashboard. Prefer accumulating over overwriting, since the dataset is tiny and the history is the interesting part.

Then set a sorting key that suits how you will read it, which is not the obvious one.

What makes a small catalogue worth a column store?

Company it keeps. A Metabase catalogue is questions, dashboards and collections, numbering in the hundreds or low thousands even at a large organisation, and nothing about that needs a system designed for scanning billions of rows quickly.

What changes the calculation is the table next to it. If you already keep query logs or usage events here, the catalogue turns opaque identifiers into names, and suddenly you can ask which dashboards generate the most warehouse spend, which questions run hourly and return nothing, and which collections belong to teams that left.

That is a genuinely useful analysis and it is entirely dependent on the large table existing. If it does not, you have moved a small dataset into a system that will happily hold it and answer nothing you could not have asked elsewhere. Name the join first, and be willing to conclude that another destination suits you better.

How should you sort a table of snapshots?

By the object first, which is the opposite of the usual advice here. Most tables in this destination want a date at the front of the sorting key because queries bound a period. A catalogue accumulating snapshots is different: the questions are about one dashboard's history, so grouping every version of an object together is what makes those reads cheap.

Sorting by identifier and then extraction timestamp gives you that directly. When a dashboard first appeared, when it was last modified and whether it has stopped appearing are all answered by scanning one contiguous run of rows rather than filtering a date-ordered table down to a handful of matches scattered through it.

Use FINAL where you want the current picture, since counting the accumulated table would report every dashboard once per sync. And remember that a dashboard absent from recent snapshots is how deletion appears, because nothing in the catalogue announces it, which is exactly the signal an estate review is looking for.

Frequently asked questions

Does this give me the data behind my dashboards?

No. It carries the catalogue, meaning definitions of questions, dashboards and collections. The rows those questions return stay in whichever database Metabase queries.

Is ClickHouse overkill for this?

On its own, yes. It earns its place only when you join the catalogue to high-volume data already here, such as query logs or usage events.

How do I find deleted dashboards?

From accumulated snapshots, by finding identifiers present in earlier extractions and absent from later ones. Overwriting discards exactly that evidence.

Why did the pipeline stop after two weeks?

The session token expired, since those last around a fortnight. Rotate it, or self-host and extend the session duration.

Can I do this without writing code?

The pipeline, yes. The join to your usage data and the views over accumulated snapshots are SQL, and they are the entire point.

Get your Metabase data into ClickHouse

Name the high-volume table you are joining to, because a catalogue of a few thousand rows justifies nothing on its own and another destination would hold it more cheaply. Plan for the session token expiring. Then accumulate snapshots rather than overwriting, sort by object identifier before timestamp so an estate history reads contiguously, and use FINAL for the current picture.

Airbyte's connector catalog includes 600+ pre-built connectors, so a reporting estate can be analysed beside the load it creates. For the same source into a warehouse, see Metabase to BigQuery, and for the same source into a lake format, Metabase to Amazon S3 with AWS Glue.

Start syncing now →