Exchange Rates API to BigQuery: How to Move Your Data

Loading Exchange Rates API to BigQuery gives your warehouse a daily currency reference table, which is what you need the moment revenue arrives in more than one currency and someone asks for a consolidated figure. Airbyte handles it with no code, and the connector is refreshingly simple: an access key, a start date, and a base currency.

The one thing worth understanding before you build is the shape of what lands. This is not a tidy table of currency and rate. It is one row per date with a separate column for every currency the API supports.

Why the table is so wide

The Exchange Rates API source produces records containing the date of the record, plus one field for every supported currency holding that currency's value on that date. The API covers around 160 currencies, so that is roughly 160 columns.

BigQuery will store that without complaint, but it is an awkward shape to work with. Joining an orders table to it means writing a CASE expression across every currency you trade in, and adding a new currency to your business means editing that expression. Worse, if the API adds or removes a supported currency, your table's schema changes underneath you.

The fix is a downstream model that unpivots the wide table into three columns: date, currency code, and rate. That gives you something you can join on directly, it survives the API changing its currency list, and it is a single view rather than logic repeated in every query. Build it before anyone writes a report against the raw table, because migrating reports afterwards is the expensive version.

Configuration, and what the free tier restricts

There are three settings. The access key from your Exchange Rates API account. A start date in year-month-day format, from which data will be replicated. And optionally a base currency, which defaults to EUR.

That base currency setting carries a restriction worth knowing before you plan anything: on the free plan, EUR is the only option available. If your business reports in dollars or pounds and you want rates expressed against that base, you need a paid API plan. The alternative is to sync against EUR and convert in your model, which is arithmetic you can do in SQL, but it introduces a second conversion step and the rounding questions that come with it.

The start date deserves more thought than it usually gets. The connector fetches historical rates by requesting each date individually, so a start date several years back means thousands of API requests on your first sync. Set it to the earliest date your analysis genuinely needs rather than reaching for a round number, and check your API plan's request allowance before running a deep backfill.

Incremental sync needs no configuration here. The connector already knows the date field determines which records have been synced, so unlike a database source you do not choose a cursor. Select incremental in the connection's schema tab and it works.

One characteristic of currency data is worth designing around: rates are published for trading days. Weekends and holidays either repeat the previous value or are absent depending on the API's behaviour, so a report joining orders to rates on an exact date match will drop weekend transactions. Handle that in your model with a forward fill or a nearest-prior-date join rather than discovering it when a Monday total looks wrong.

Loading into BigQuery

The BigQuery destination uses direct load, writing straight to final tables. Batched Standard Inserts is the default and is comfortably right here, since this is one of the smallest datasets you will ever sync: one row per day. GCS staging exists but adds a bucket, an HMAC key, and a Storage Object Admin grant for no benefit at this volume.

Your service account needs BigQuery User and BigQuery Data Editor. The dataset location matters more than usual on this pipeline, because a currency table exists to be joined against your revenue data, and BigQuery cannot query across physical locations. Put it in the same location as your orders and finance datasets, and remember the location cannot be changed after creation.

Use incremental append with deduplication on the date so that a re-synced day replaces rather than duplicates. Partitioning is not worth worrying about on a table of a few thousand rows.

Frequently asked questions

Can I use a base currency other than EUR?

Not on the free plan, where EUR is the only option. A paid API plan unlocks other bases, or you can sync against EUR and convert in your model.

Why does my table have so many columns?

The record carries a date plus one field per supported currency. Unpivot it into date, currency, and rate in a downstream model so it can be joined directly.

Do I need to choose a cursor field?

No. The connector already knows the date field determines recency, so you simply select incremental sync in the schema tab.

Why are weekend dates missing or unchanged?

Currency rates follow trading days. Use a forward fill or a nearest-prior-date join in your model so weekend transactions still find a rate.

Get your Exchange Rates API data into BigQuery

Set a realistic start date, put the dataset alongside your revenue data, and unpivot the wide table before anyone reports against it. For the general pattern of loading API data, see our guide to API to database. For another API source into the same warehouse, see Datadog to BigQuery.

Start syncing now →