Facebook Marketing to ClickHouse: How to Move Your Data

Moving Facebook Marketing into ClickHouse puts campaign performance somewhere you can slice it without waiting. Meta's own reporting is capable and slow to interrogate at scale, and it cannot compare its results against the other platforms competing for the same budget.

This guide covers the managed path with Airbyte. Two things shape the build: the connector deliberately re-reads recent days because attribution keeps changing them, and ClickHouse resolves those repeated versions in a way that quietly discards the history of how a figure matured.

Facebook Marketing to ClickHouse at a glance:

Why move data from Facebook Marketing to ClickHouse?

Two situations account for most of these pipelines.

The first is comparing channels honestly. Every advertising platform reports favourably on itself using its own attribution rules, and the only way to judge Meta against search, affiliates or anything else is to bring the numbers together and apply consistent definitions yourself. That work is aggregation across a lot of rows, which is what a column store exists for.

The second is granular analysis across many accounts and long periods, which Meta's interface makes tedious. If you specifically need to keep a record of how figures were restated over time, a warehouse partitioned on extraction makes that visible, and Facebook Marketing to BigQuery preserves it more naturally than the design described below.

What do you need before you start?

Four things, and the second one is expensive to change afterwards:

Meta credentials and your ad account identifiers. An access token with the permissions the streams require, plus the accounts you intend to cover. The Facebook Marketing source documentation covers obtaining them.

A decided time increment. This sets the grain of your insights data, and changing it later triggers a full re-sync rather than adjusting going forward. Daily suits most analysis, and agreeing it with whoever consumes the data now saves repeating the backfill.

An agreed lookback window. Up to twenty-eight days, and it governs how far back each sync re-reads to capture attribution that has settled since. Longer means more complete figures and more repeated records to resolve.

A ClickHouse database and a timestamp cursor. Use a date or timestamp rather than anything string-shaped, since string cursors compare awkwardly and degrade as the tables grow.

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 Facebook Marketing to ClickHouse pipeline in Airbyte?

Step 1: Settle the time increment before anything is built

Ask the people who will use this data what grain they need, because changing the time increment later triggers a full re-sync rather than applying from now on. On a long history across several accounts, that is a meaningful amount of time and API activity to repeat for a decision somebody could have made in five minutes. Daily is the usual answer and worth confirming rather than assuming.

Step 2: Configure the Facebook Marketing source

Click Sources in the left navigation, then New Source, and select Facebook Marketing, following adding a source. Supply the token, account identifiers, start date, time increment and lookback window. If you are covering accounts in different countries, timezone handling is managed per account automatically, so that is one thing you do not have to reconcile yourself.

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 slicing several years of daily insights across many campaigns quick enough to be interactive.

Step 4: Create the connection and plan for repeated records

Click Connections, then New connection, select your streams and a sync mode. Every sync will re-read the lookback period, so the same campaign day arrives repeatedly by design. Deduplication is therefore not an optimisation but the thing that makes the table correct, and how ClickHouse performs it deserves a moment of attention.

Daily syncing is sufficient. Attribution settles over weeks rather than hours, so a more frequent schedule produces repeated work rather than fresher insight.

Why does the same day keep arriving?

Because Meta keeps changing it. A conversion today can be attributed to an impression from three weeks ago, so the figures for a past day continue to move long after that day ended. The connector handles this by re-reading a lookback period of up to twenty-eight days on every sync, which is deliberate rather than wasteful.

The consequence is that duplicates are a normal part of this pipeline. The same campaign and day arrive repeatedly with different numbers, and the record you want is the most recent one. Any consumer treating each arriving row as a new fact will overstate spend and conversions dramatically, so deduplication is load-bearing here rather than tidy.

Worth knowing too that you receive only the most recent attribution value rather than every window Meta could report. That is usually what people want and it means your table holds one view of attribution rather than several, so if somebody asks whether a figure uses a seven or twenty-eight day window, the answer comes from your source configuration rather than from the data.

Why can't you see how a figure changed?

Because ReplacingMergeTree is designed to keep the newest version and discard the rest. For this pipeline that behaviour is exactly right, since the latest figure is the most complete one and older versions were simply incomplete. It also means the record of how a day's numbers matured is thrown away as a matter of routine.

That matters when somebody asks why last month's report no longer matches the one they printed. With versions collapsed you can say that attribution settled and not demonstrate it, which is an uncomfortable position during a budget discussion. A warehouse partitioning on extraction timestamp keeps every version by default; here the default is the opposite.

If restatement history matters to you, design for it rather than hoping. The straightforward approach is a second, append-only table capturing each sync's figures alongside the extraction time, kept beside the deduplicated one that everybody queries. It costs a little storage and answers the question properly. Wherever exact figures are reported from the main table, use FINAL, since merges run on ClickHouse's schedule rather than yours and an unmerged duplicate will inflate a total.

Frequently asked questions

Why do I see the same campaign day more than once?

The lookback window re-reads recent days on purpose, because attribution keeps changing them. The newest version is the one to keep, which is what deduplication resolves.

Can I change the time increment later?

You can, and it triggers a full re-sync rather than applying from that point. On a long history across several accounts that is a significant repeat, so settle it first.

Do I need to handle timezones across accounts?

No, that is handled per account automatically, which is a genuine convenience when your accounts span several countries.

How do I keep a record of restatement?

Build an append-only table alongside the deduplicated one, capturing each sync's figures with its extraction time. The main table keeps only the newest version by design.

Can I do this without writing code?

The pipeline, yes. Views applying FINAL, and an append-only table if you want restatement history, are modest SQL worth writing once.

Get your Facebook Marketing data into ClickHouse

Settle the time increment before you build, because changing it means syncing everything again. Agree a lookback window and tell consumers that repeated days are deliberate rather than a fault. Then decide whether you need the history of restatement, since ClickHouse keeps only the newest version by design, and if you do, build an append-only table for it now rather than after somebody queries a number that has since moved.

Airbyte's connector catalog includes 600+ pre-built connectors, so advertising channels can be judged on the same terms. For the same source into an operational database, see Facebook Marketing to MySQL, and for a comparable ad platform into the same destination, Google Ads to ClickHouse.

Start syncing now →