AWS CloudTrail to ClickHouse: How to Move Your Data

Moving AWS CloudTrail into ClickHouse is a retention problem wearing a security costume. The CloudTrail lookup API returns events from roughly the last 90 days. After that they are gone from that interface, which means any question spanning a year, correlating an incident with what changed six months earlier, or proving who did what for an auditor cannot be answered from event history alone.

ClickHouse is a good home for that history: high-volume event data, cheap storage, fast aggregation. This guide covers the managed path with Airbyte, and two constraints that decide whether this pipeline suits your purpose at all, one about which events you get and one about how fast you can get them.

AWS CloudTrail to ClickHouse at a glance:

Why move data from AWS CloudTrail to ClickHouse?

Two situations account for most of these pipelines.

The first is retention beyond the lookup window. Audit and compliance work asks what happened, when, and by whom, over periods far longer than 90 days. A warehouse or an analytical database is where that record lives once the console can no longer show it to you.

The second is joining and aggregation. Correlating account activity with deployment records, incident timelines or your identity provider needs one place to query, and ClickHouse is well suited to the shape of this data: a large number of small, timestamped, semi-structured events that you mostly aggregate and filter by time.

What do you need before you start?

Two of these are expectations rather than configuration, and both can change whether the project is worth doing:

AWS credentials with CloudTrail read access. An access key ID and secret access key, plus the region you are reading. The connector is built on the Boto3 CloudTrail client, so it behaves like any other AWS SDK caller. The AWS CloudTrail source documentation covers the fields.

Acceptance that you get management events only. Insight events are not supported by this connector. If what you actually wanted was object-level access logging or function invocation records, this pipeline will not provide them and you should stop here rather than discover it after building.

A realistic view of the lookup window. The CloudTrail lookup API returns events from approximately the last 90 days. Your archive therefore begins on the day of your first sync, and no configuration recovers what is already outside that window.

A plan for multiple accounts and regions. Lookup requests are limited to two per second, per account, per region. Most organisations have several of both, so decide early how many connections you will run and how they are spaced, because piling several against the same account and region is a documented way to fail syncs.

On the ClickHouse side you need an instance and a user with permission to create tables in the target database. If it restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.

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

Step 1: Decide your account and region coverage

List the accounts and regions whose activity you actually need. Because the rate limit is scoped per account and region, coverage is the thing that determines both how many connections you build and how long each sync takes. An organisation with twelve accounts across three regions is a very different project from a single account in one region, and it is better sized now than discovered later.

Step 2: Configure the CloudTrail source

Click Sources in the left navigation, then New Source, and select AWS CloudTrail, following adding a source. Supply the access key, secret key, region and a start date. Bear in mind the start date cannot reach back further than the lookup window allows, so setting it to two years ago does not give you two years of events.

Step 3: Configure the ClickHouse destination

Click Destinations, then New Destination, and select ClickHouse, following adding a destination. Supply host, port, database and credentials. The destination writes through ClickHouse's native protocol into typed columns, and uses the ReplacingMergeTree engine for deduplication, which matters for the query advice below.

Step 4: Use incremental sync and sync often

Click Connections, then New connection, and choose an incremental sync mode. Frequency here is not about dashboard freshness, it is about staying ahead of a source window that keeps moving. Syncing daily against a 90 day window leaves plenty of margin; syncing monthly and then pausing for a quarter loses events permanently.

The connector retries gracefully when it hits throttling. But if throttling errors continue after multiple retries, for instance because you have several connector instances configured against the same account and region, the sync fails. Alert on that rather than assuming retries will always save you.

Why does the 90 day window change your design?

Because it makes this an archive pipeline rather than a replication one. Airbyte can only read what the lookup API still returns, so your ClickHouse tables start accumulating from the day of your first sync and nothing recovers what came before.

That is a good argument for setting this up before you need it. The value of an audit archive is entirely in its age, and the difference between configuring it this quarter and next is a quarter of history you will never have. If a compliance requirement is coming, the pipeline should predate it.

It also means a paused connection is more expensive than usual. On most pipelines a pause produces staleness you can catch up on. Here, if the pause outlasts the window, those events are unrecoverable from this source. Treat a stalled sync as an incident rather than a backlog.

What does two requests a second actually mean?

It is the ceiling on this pipeline, and it is low. The lookup API returns a limited number of events per request, so two requests per second per account per region puts a hard bound on how many events you can pull in a given period regardless of how much compute you throw at it.

On a busy AWS account generating a large volume of management events, that ceiling can be lower than your event rate, which means you never catch up and the moving window eventually overtakes you. Measure your event volume before committing, because this is the failure that is invisible until the gap is permanent.

Where volume is genuinely high, CloudTrail's own delivery to object storage is the mechanism designed for it, and this connector is better suited to moderate accounts or to specific regions you care about. That is a legitimate architectural conclusion rather than a failure, and it is much cheaper to reach before building.

Frequently asked questions

Can Airbyte backfill CloudTrail events older than 90 days?

No. The lookup API returns roughly the last 90 days, so your archive starts at your first sync. Setting an earlier start date does not recover events outside that window.

Does this include S3 object-level or Lambda invocation events?

No. Only management events are available, and Insight events are not supported by this connector. If you need data events, this is not the right pipeline.

Why did my sync fail with throttling errors?

Lookup requests are limited to two per second per account per region. The connector retries, but if errors persist, for example because several connector instances share an account and region, the sync fails.

Why does my ClickHouse table contain duplicate events?

ReplacingMergeTree removes duplicates during background merges rather than immediately, so a query run shortly after a sync can see them. Add the FINAL operator where exact counts matter.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven. You will want ClickHouse views over the event records, which is where your audit queries and any use of FINAL belong.

Get your CloudTrail data into ClickHouse

Confirm that management events are what you need, measure your event volume against the two per second ceiling, and set the pipeline up before the compliance requirement arrives rather than after. Then sync often enough that the moving window never overtakes you, and treat a stalled connection as an incident because those events do not come back.

Airbyte's connector catalog includes 600+ pre-built connectors, so audit activity can sit beside deployment and incident data in one engine. For another source where retention drives the design, see Sentry to BigQuery, and for another event source landing in ClickHouse, Amplitude to ClickHouse.

Start syncing now →