Adjust to ClickHouse: How to Move Your Data

Moving Adjust into ClickHouse puts mobile attribution somewhere you can slice it quickly across long periods. Adjust reports competently on campaigns and cannot set them against the revenue, retention and support cost that decide whether the spend was worthwhile.

This guide covers the managed path with Airbyte. Two things shape the build: the metrics available to you are specific to your apps and have to be discovered, and every metric you select is treated as a decimal, which a typed column store takes literally.

Adjust to ClickHouse at a glance:

Why move data from Adjust to ClickHouse?

Two situations account for most of these pipelines.

The first is judging channels on the same terms. Every attribution platform reports favourably on itself, and comparing Adjust against other sources of installs needs the numbers in one place with definitions you control. That is aggregation across a lot of rows, which is what a column store is built for.

The second is interactive exploration across long periods, where query speed changes how often anybody asks a question. Worth knowing before you start: the metrics and dimensions are chosen at configuration, so changing your mind later reshapes the tables rather than adding to them.

What do you need before you start?

Four things, and the second one cannot be looked up in a dropdown:

An API token for the reporting API. Obtained from Adjust following their authentication guide. The Adjust source documentation covers the setup and points at the metrics glossary.

A list of the metrics that exist for your apps. Only a subset is offered in the interface, because Adjust lets you define custom events, so the full list comes from querying the filters data endpoint yourself.

An agreed set of dimensions. These group your metrics, so date, country, network and so on decide the grain of what arrives and therefore the shape of every query written against it.

A ClickHouse database and a view on types. Column types are settled at table creation, which interacts with how this connector treats metrics in a way worth understanding before the first sync.

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 an Adjust to ClickHouse pipeline in Airbyte?

Step 1: Enumerate your metrics before configuring anything

Call the filters data endpoint with your token and read what comes back. The connector pre-selects a subset of metrics, because the events your apps define are yours rather than standard, and anything beyond that subset goes into the additional metrics field by name. You cannot type a name you have not discovered, so this call is the difference between configuring what you want and configuring what was offered.

Step 2: Configure the Adjust source

Click Sources in the left navigation, then New Source, and select Adjust, following adding a source. Supply the API token, an ingestion start date in plain date format, your chosen metrics including any custom ones, and the dimensions to group by. Note that the start date is interpreted as UTC.

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 over the native protocol, which is what makes aggregating long periods quick and what makes the type question below worth a moment.

Step 4: Create the connection and inspect the first tables

Click Connections, then New connection, select your stream and a sync mode. Daily suits attribution reporting. Then look at the column types that were created before building anything on top, because changing them later is a rebuild rather than an alteration.

Choose a sorting key that matches how the data will be read, which for attribution reporting almost always begins with the date.

Why do you have to discover your own metrics?

Because half of them are yours. Adjust lets each organisation define custom events for its own apps, so no fixed list could cover everybody, and the connector offers a pre-selectable subset of standard metrics plus a field where you type the rest by name. That is a sensible design and it means the interface understates what is available.

The filters data endpoint is how you find out. One authenticated call returns what exists for your apps, including the event metrics your own team configured, and it takes a minute. Skipping it produces a pipeline built entirely from the defaults, which is the version of this project where somebody later asks why the metric they care about is missing.

Dimensions work the same way conceptually, in that they are a choice rather than a given. They group the metrics, so selecting date, country and network produces a very different table from selecting date alone, both in row count and in what questions are answerable. Decide them with whoever will use the data, since a dimension you omitted is not something a query can recover.

What does assuming decimal mean in a typed column store?

That every metric you select is treated as a decimal value, whatever it actually represents. The connector makes that assumption deliberately because most metrics are numeric measures, and it holds for spend, revenue and rates without any trouble at all. It holds less comfortably for things that are conceptually counts, and not at all for anything that is not a number.

In ClickHouse that matters more than it would elsewhere, because column types are settled when the table is created and the performance you came for depends on them. A count arriving as a decimal is not wrong, and it is not what you would have chosen, and discovering it after six months of data has accumulated means rebuilding rather than adjusting.

So inspect the created tables after the first sync and before anybody builds on them. Check that each metric's type suits what it holds, and if a custom event metric is really a count or a label, decide then whether to cast it in a view or to shape the table yourself. Pair that with a sorting key chosen from your actual queries, usually starting with date, and the tables will behave the way the destination promises.

Frequently asked questions

Why is the metric I want not in the list?

Only a subset is pre-selectable, because custom events are specific to your apps. Query the filters data endpoint to enumerate what exists, then enter the name in the additional metrics field.

How are metric types decided?

Selected metrics are assumed to be decimal values. Check the resulting column types after the first sync, since ClickHouse fixes them at table creation.

Can I change dimensions later?

You can, and it changes the grain of what arrives rather than adding to it, so agree them with your users before building anything on top.

What timezone is the start date in?

UTC is assumed, and the date is supplied in plain year, month and day format without a time component.

Can I do this without writing code?

The pipeline, yes, though enumerating metrics means one API call. Views casting anything the decimal assumption got wrong are SQL worth writing early.

Get your Adjust data into ClickHouse

Call the filters data endpoint first, because the interface offers a subset and your custom event metrics have to be named rather than chosen. Agree dimensions with the people who will query the result, since they set the grain. Then inspect the column types after the first sync, remembering that every metric is assumed decimal and ClickHouse fixes types at creation, and pick a sorting key that starts with the date.

Airbyte's connector catalog includes 600+ pre-built connectors, so attribution can be judged beside the revenue it claims to have produced. For the same source into a warehouse, see Adjust to BigQuery, and for another attribution platform into the same destination, Appsflyer to ClickHouse.

Start syncing now →