Tempo to ClickHouse: How to Move Your Data

Moving Tempo into ClickHouse turns logged hours into something you can aggregate across teams and years. Tempo reports well inside Jira, and the questions finance and delivery leads actually ask, about billable ratios by client or how estimates compare with reality over eighteen months, need a database rather than a reporting screen.

This guide covers the managed path with Airbyte. Two things shape the build: the token belongs to one person and sees only what that person sees, and worklogs arrive without the issue context that makes them meaningful.

Tempo to ClickHouse at a glance:

Why move data from Tempo to ClickHouse?

Two situations account for most of these pipelines.

The first is commercial. An agency or consultancy lives on billable hours, and the questions that matter, utilisation by person, realisation by client, which engagements quietly lose money, all require aggregating worklogs across long periods and joining them to contract values Tempo has never heard of. ClickHouse answers those in the time it takes to press a key.

The second is delivery analysis, comparing where time actually went against where anybody expected it to go. The important caveat is that this connector alone will not get you there, because it carries hours without carrying what the hours were spent on. Budget for the Jira connector alongside it, or accept that you are building a report about durations attached to identifiers nobody recognises.

What do you need before you start?

Four things, and the first one quietly determines how much of your organisation you can see:

An API token, generated for a specific person. Found under Tempo, then Settings, then Data Access, then API integration. The connector reads whatever the token's owner is permitted to read, so whose account you use is a decision rather than an administrative detail. The Tempo source documentation covers generating one.

A plan for the Jira side. Worklogs identify issues by key, and nothing in these four streams tells you what an issue was, which project it belonged to or who the client was. A second pipeline from Jira is what makes this data legible.

A ClickHouse database and a view on merge behaviour. Hours are numbers people add up and report, so knowing how deduplication actually works here matters more than it would for a dataset nobody totals.

A timestamp cursor for the worklogs stream. Use a date or updated timestamp rather than anything string-shaped, since string cursors compare awkwardly and degrade as the table grows.

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

Step 1: Decide whose account issues the token

The token inherits one individual's view of Tempo, so a token from a team lead returns their team's worklogs and a token from someone with organisation-wide visibility returns everybody's. Use an account whose access matches the reporting you intend to produce, ideally a service account rather than a person who might change teams. Then write down what that account can see, because nothing downstream reveals the boundary.

Step 2: Configure the Tempo source

Click Sources in the left navigation, then New Source, and select Tempo, following adding a source. Supply the API token, which is the entire configuration. Four streams are available: accounts, customers, worklogs and workload schemes, and worklogs is almost certainly the one you came for.

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 several years of worklogs quick, and it also means the types chosen at table creation are the types you live with.

Step 4: Create the connection and set up the Jira join

Click Connections, then New connection, select your streams and a sync mode. Incremental suits worklogs, since they accumulate rather than churn. Land the Jira issue data in the same database, and build the joined view early, because nobody wants a table of hours against issue keys and neither does anybody want to write that join twice.

Daily is right for time tracking. People log hours retrospectively, so allow for worklogs arriving against dates already past rather than assuming today's sync covers today's work.

Why are hours on their own not enough?

Because the connector carries four streams and none of them describes work. Accounts, customers, worklogs and workload schemes give you who logged how long against which issue key, and the issue key is where the trail stops. What that issue was, which project it sat in, whether it was a bug or a feature, none of it is here.

That is reasonable, since Tempo is an add-on to Jira and Jira holds the issues. It does mean the useful version of this dataset is two pipelines rather than one, and a project planned as a single connector tends to reach the reporting stage before anybody notices that hours per issue key is not a report anyone wanted.

Plan the join from the start. Land Jira issues beside the worklogs, join on the issue key, and expose a view carrying hours alongside project, issue type, status and whatever custom field encodes your client or cost centre. That view is the actual deliverable; the Tempo pipeline on its own is half of it, and the accounts and customers streams help with the commercial side but not with the work itself.

Why do your total hours change between queries?

Because ReplacingMergeTree deduplicates during background merges rather than at write time. Two versions of an edited worklog can sit in the table together, legitimately, until a merge runs and collapses them. The table is eventually deduplicated rather than immediately so, and the timing is not yours to control.

This matters more here than it would elsewhere, because these numbers get summed and reported. A total of billable hours run twice with a merge in between returns two different answers, and the second is lower. If that figure went into an invoice or a board pack, the discrepancy is not academic, and it resolves itself before anybody can reproduce it.

Add FINAL to any query whose result somebody acts on. It forces resolution at query time at some cost in speed, which is an easy trade for a monthly invoicing figure and a poor one for an exploratory dashboard scanning trends across quarters. The tidy approach is a view applying FINAL for the reported numbers and leaving the raw table available for analysis, so nobody has to remember which kind of query they are writing.

Frequently asked questions

Why can I only see some people's worklogs?

The token is issued for an individual and returns what that individual is permitted to see. For organisation-wide reporting, generate it from an account with the matching visibility.

Does this include the Jira issues themselves?

No. There are four streams covering accounts, customers, worklogs and workload schemes, and worklogs reference issues by key only. Run the Jira connector alongside this one.

Why did my hours total drop?

A background merge deduplicated rows that were waiting to be resolved. Use FINAL for figures that must be exact rather than treating the earlier, higher number as correct.

How often should this sync?

Daily is usually plenty. People log time retrospectively, so expect worklogs to appear against dates in the past and avoid assuming any single sync closes off a period.

Can I do this without writing code?

The pipeline, yes, and the Tempo configuration is a single token. The join to Jira and the views applying FINAL are SQL, and both are what turn this into something reportable.

Get your Tempo data into ClickHouse

Choose the token's owner deliberately and record what that account can see, since the permission boundary is invisible from the data. Plan the Jira pipeline at the same time rather than afterwards, because hours against issue keys are not yet a report. Use timestamp cursors, and apply FINAL wherever a total leaves the building, because a figure that quietly changes between two runs is the worst kind of number to put in front of a client.

Airbyte's connector catalog includes 600+ pre-built connectors, so time tracking can sit beside the work it describes. For the issue data that gives worklogs meaning, see Jira to PostgreSQL, and for another narrow operational source into the same destination, n8n to ClickHouse.

Start syncing now →