Exchange Rates API to ClickHouse: How to Move Your Data
Move Exchange Rates API data into ClickHouse with Airbyte. Why the wide table costs nothing on a columnar engine, base currency limits, and deriving cross rates.
Moving Exchange Rates API data into ClickHouse solves a small problem that causes disproportionate trouble: converting figures between currencies consistently. Every team reporting across borders eventually discovers that finance, sales and analytics are each using a different rate for the same day, and nobody can say which is right.
This guide covers the managed path with Airbyte. It is a small, well-behaved pipeline, and the shape of what arrives will surprise you: rather than one row per currency per day, you get one row per day with a column for every currency the API supports.
Exchange Rates API to ClickHouse at a glance:
Why move Exchange Rates API data into ClickHouse?
Two situations account for most of these pipelines.
The first is one agreed rate table. Once it exists in the engine your reporting already uses, every conversion draws on the same numbers and disagreements between teams become impossible rather than merely discouraged. That is the whole value, and it is worth more than the effort suggests.
The second is joining rates to transactions at query time. If your order or event data already lives in ClickHouse, converting currency in the same engine that holds the facts avoids shipping either dataset elsewhere, and at these volumes the join costs nothing.
What do you need before you start?
A very short list, and one item depends on what you are paying for:
An API access key. Generated from your Exchange Rates API account and entered as the access key. The Exchange Rates API source documentation covers the fields.
A start date, in the right format. Entered as year, month and day. Data added on and after this date is replicated. Set it to cover the earliest transactions anyone will convert, because a rates table that starts after your revenue history is only half useful.
A base currency you are actually allowed to use. On the free plan EUR is the only option available, and if you specify nothing EUR is used. A business reporting in dollars or pounds therefore either upgrades or derives the conversion downstream.
A ClickHouse instance and credentials. With permission to create tables in the target database. Volume is genuinely not a concern here, so put it wherever your other reference data lives.
If your ClickHouse instance restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.
How do you build an Exchange Rates to ClickHouse pipeline in Airbyte?
Step 1: Settle the base currency question
Check which plan you are on and therefore which base currencies you can request. If you are on the free plan and report in something other than euros, decide now whether to upgrade or to derive the rates you need downstream, because both are reasonable and they lead to different modelling.
Step 2: Configure the Exchange Rates API source
Click Sources in the left navigation, then New Source, and select Exchange Rates API, following adding a source. Enter the access key, the start date and optionally the base currency, then click set up source. This is one of the simplest configurations in the whole catalogue.
Step 3: Configure the ClickHouse destination
Click Destinations, then New Destination, and select ClickHouse, following adding a destination. Supply host, port, database and credentials. A table with a date and roughly 160 numeric columns sounds alarming and is entirely unremarkable to a columnar engine, which only reads the columns a query touches.
Step 4: Create the connection and schedule daily
Click Connections, then New connection, and choose a sync mode. Daily matches the grain of the data exactly, and there is no reason to sync more often since a published day's rate does not change afterwards. That immutability also means you never need a lookback window here.
One scheduling note: rates are published on business days, so weekends and holidays produce no new values. Your downstream models need a rule for what rate applies on a Sunday, and carrying the last published rate forward is the usual answer.
Why is the table so wide, and does it matter here?
It is wide because the connector mirrors the API's response shape. Each record carries the date, then one field for every supported currency containing that currency's value on that date. With around 160 currencies covered, that is a date column and roughly 160 rate columns.
On a row-oriented database that width is a genuine cost, because reading one rate means reading the whole row. ClickHouse stores each column separately and only reads what a query references, so a query touching two currency columns ignores the other hundred and fifty-eight entirely. The width is close to free.
What the width still costs you is ergonomics. Joining a transactions table on both date and currency code needs the long form, a row per date and currency, and against a wide table that means a different join for every currency. So build an unpivoted view for anyone writing joins, while leaving the wide table alone for direct lookups where it is genuinely convenient.
What if you cannot set the base currency you want?
On the free plan you are limited to euros as the base, which is inconvenient if your accounts are in dollars or pounds. The good news is that it does not block you, because every rate in a row shares the same base and you can therefore derive any pair from any other by division.
If a row gives you the euro rate for both dollars and pounds, the dollar to pound rate is one divided by the other. Doing that once in your unpivoted view lets you expose rates in whatever base your business reports in, without changing the pipeline or the plan.
Two caveats. Derived cross rates accumulate small rounding differences, which is immaterial for reporting and worth knowing if anyone reconciles to the penny. And if finance has a mandated rate source, use theirs rather than this, because being consistent with the accounts matters more than being consistent with a public API.
Frequently asked questions
Can I choose any base currency?
Not on the free plan, where EUR is the only option. If you specify nothing, EUR is used. You can derive other bases downstream by dividing one rate by another.
Is a table with 160 columns a problem for ClickHouse?
No. Columnar storage means a query only reads the columns it references, so the width costs nothing. The inconvenience is for joins, which is what the unpivoted view solves.
Do I need a lookback window?
No. Once a day's rate is published it does not change, so unlike most reporting sources there is nothing to re-read.
What rate applies at the weekend?
Rates are published on business days, so define a rule in your models. Carrying the last published rate forward is the usual approach, and it should be agreed with finance rather than assumed.
Can I do this without writing code?
The Airbyte setup is entirely UI-driven. You will want a ClickHouse view unpivoting the currency columns, which is short SQL and the step that makes joins practical.
Get your exchange rates into ClickHouse
Check which base currencies your plan allows, set a start date covering your earliest transactions, and build the unpivoted view for anyone writing joins. The wide table itself is no burden on a columnar engine, so keep both and point people at whichever suits the query.
Airbyte's connector catalog includes 600+ pre-built connectors, so reference data can sit beside the financial sources that need it. For the same source in a warehouse, see Exchange Rates API to Snowflake, and for payment data to convert, Stripe to ClickHouse.
Integrate with 700+ apps using Airbyte
Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.