Tutorial

Exchange Rates API to BigQuery and Snowflake: A Daily FX Pipeline

By Madhushan Published November 11, 2026 8 min read

Reviewed by Madhushan, Fintech Developer — November 2026

Most companies that sell in more than one currency end up with the same task: get daily exchange rates into the warehouse and convert fact tables to one reporting currency. This tutorial builds that pipeline with an exchange rates API, for BigQuery and Snowflake. It covers a daily load job, a staging table and MERGE, a dim_fx_rates table, a dbt model that converts orders with an as-of join, and weekend and holiday handling that an auditor can follow.

Decide which rate you are storing

There are two kinds of rate, and a warehouse usually needs both.

Official rates. The table a central bank or tax authority issues for a given day. Each row has the institution's own rate_date. Accounting policies, VAT rules and auditors name these. AllRatesToday serves the official tables of 123 central banks and 6 tax authorities. See the official rates methodology.
Mid-market rates. A one-minute snapshot of the market mid for 160+ currencies, refreshed every 60 seconds. Good for operational dashboards and for "what is this basket worth now".

Keep both in one table with a source_id column (ecb, fed, mid-market). A report then states which rate it used, and switching source is a filter, not a rebuild.

The exchange rates API calls the job makes

The daily job needs one call per central bank. The dated endpoint returns that bank's full table for a day, and ?format=csv returns it as a file your loader can read:

curl "https://allratestoday.com/api/v1/central-bank/ecb/2026-11-10?format=csv" \
  -H "Authorization: Bearer $ALLRATES_API_KEY" -o ecb_2026-11-10.csv

The header is rate_date,base,quote,rate_type,value, one row per pair in the bank's own quoting direction. For the ECB that is EUR as base:

rate_date,base,quote,rate_type,value
2026-11-10,EUR,AUD,reference,1.6244
2026-11-10,EUR,BRL,reference,6.2257
2026-11-10,EUR,CAD,reference,1.5852

If you ask for a day the bank did not publish, such as a Saturday, the API returns the most recent table, and rate_date shows the day it really refers to. Store that value. Never assume it equals the date you requested.

For a mid-market snapshot, one call covers many targets and costs one request. The response is a JSON array, even for a single pair:

curl "https://allratestoday.com/api/v1/rates?source=USD&target=EUR,GBP,JPY,CAD" \
  -H "Authorization: Bearer $ALLRATES_API_KEY"
# [{"rate":0.9215,"source":"USD","target":"EUR","time":"2026-04-03T12:00:00Z"}, ...]

Use the time field as the snapshot timestamp, not the time your job ran.

The load job

A small Python script fetches one bank for one date, archives the raw response, and writes a staging file with the columns the warehouse expects. Run it from a scheduler (Cloud Scheduler, Airflow, cron) after the bank's usual publication time. The latest endpoint for each bank includes an available_by_utc field you can use to set the schedule; for the ECB it is 15:15 UTC.

import csv, io, os, sys, datetime as dt
import requests

KEY = os.environ["ALLRATES_API_KEY"]
BANK = os.environ.get("FX_BANK", "ecb")
day = sys.argv[1] if len(sys.argv) > 1 else dt.date.today().isoformat()

r = requests.get(
    f"https://allratestoday.com/api/v1/central-bank/{BANK}/{day}",
    params={"format": "csv"},
    headers={"Authorization": f"Bearer {KEY}"},
    timeout=30,
)
r.raise_for_status()
text = r.content.decode("utf-8-sig")   # removes the byte order mark

# keep the raw file for audit
os.makedirs("raw", exist_ok=True)
with open(f"raw/{BANK}_{day}.csv", "w", encoding="utf-8") as f:
    f.write(text)

rows = list(csv.DictReader(io.StringIO(text)))
with open(f"stg_{BANK}_{day}.csv", "w", newline="") as f:
    w = csv.writer(f)
    w.writerow(["requested_date", "rate_date", "source_id", "base", "quote", "rate_type", "rate"])
    for row in rows:
        w.writerow([day, row["rate_date"], BANK, row["base"], row["quote"],
                    row["rate_type"], row["value"]])
print(f"{len(rows)} rows, rate_date {rows[0]['rate_date']}")

In production, copy the raw/ file to Cloud Storage or an S3 stage instead of local disk.

Staging and MERGE in BigQuery

The target table holds one row per source, date and pair:

CREATE TABLE IF NOT EXISTS fx.dim_fx_rates (
  rate_date  DATE    NOT NULL,
  source_id  STRING  NOT NULL,
  base       STRING  NOT NULL,
  quote      STRING  NOT NULL,
  rate_type  STRING,
  rate       NUMERIC NOT NULL,
  loaded_at  TIMESTAMP,
  updated_at TIMESTAMP
)
PARTITION BY rate_date
CLUSTER BY source_id, quote;

Load the staging file, replacing the previous run:

bq load --replace --source_format=CSV --skip_leading_rows=1 fx.stg_fx_rates stg_ecb_2026-11-10.csv \
  requested_date:DATE,rate_date:DATE,source_id:STRING,base:STRING,quote:STRING,rate_type:STRING,rate:NUMERIC

Then merge. A Saturday run brings Friday's rows again; the MERGE ignores them because they match and are unchanged.

MERGE fx.dim_fx_rates T
USING (
  SELECT DISTINCT rate_date, source_id, base, quote, rate_type, rate
  FROM fx.stg_fx_rates
) S
ON  T.source_id = S.source_id AND T.rate_date = S.rate_date
AND T.base = S.base AND T.quote = S.quote
WHEN MATCHED AND T.rate != S.rate THEN
  UPDATE SET rate = S.rate, updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
  INSERT (rate_date, source_id, base, quote, rate_type, rate, loaded_at)
  VALUES (S.rate_date, S.source_id, S.base, S.quote, S.rate_type, S.rate, CURRENT_TIMESTAMP());

An official figure should not change once issued. If updated_at is ever set, alert on it and look at the raw file before anyone reruns a report.

The same in Snowflake

Snowflake uses an internal stage and COPY INTO. The MERGE statement above runs unchanged.

CREATE STAGE IF NOT EXISTS fx_stage
  FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);

PUT file://stg_ecb_2026-11-10.csv @fx_stage AUTO_COMPRESS = TRUE;

TRUNCATE TABLE stg_fx_rates;
COPY INTO stg_fx_rates FROM @fx_stage
  PATTERN = '.*stg_ecb_2026-11-10.*';

Use NUMBER(18,8) for the rate column in Snowflake, which covers fiat rates from small to large.

Weekends and holidays: carry the last official rate forward

Orders happen on Sundays. Central banks do not publish on Sundays. The usual policy is to use the last official rate before the transaction date, and the model should show that it did. This dbt model builds a calendar spine for every pair and carries the rate forward (BigQuery dialect):

-- models/fx_daily.sql
with spine as (
  select d as calendar_date
  from unnest(generate_date_array('2020-01-01', current_date())) as d
),
rates as (
  select * from {{ source('fx', 'dim_fx_rates') }} where source_id = 'ecb'
),
pairs as (select distinct base, quote from rates),
joined as (
  select s.calendar_date, p.base, p.quote, r.rate, r.rate_date
  from spine s
  cross join pairs p
  left join rates r
    on r.rate_date = s.calendar_date and r.base = p.base and r.quote = p.quote
)
select
  calendar_date, base, quote,
  last_value(rate ignore nulls) over w      as rate,
  last_value(rate_date ignore nulls) over w as rate_date,
  rate is null                              as is_carried_forward
from joined
window w as (partition by base, quote order by calendar_date
             rows between unbounded preceding and current row)

Every calendar day now has a rate, and every row still says which official table it came from.

Converting a fact table to a reporting currency

ECB rows are quoted as units of a currency per one euro. To report in US dollars, divide the order amount by its currency's rate to get euros, then multiply by the USD rate for the same day. This is a cross rate through the bank's base; the base currency conversion guide explains the arithmetic.

-- models/fct_orders_usd.sql
with fx as (select * from {{ ref('fx_daily') }} where base = 'EUR')
select
  o.order_id,
  o.order_date,
  o.currency,
  o.amount,
  case when o.currency = 'EUR' then o.amount
       else o.amount / src.rate end * usd.rate  as amount_usd,
  usd.rate_date                                 as fx_rate_date,
  coalesce(src.is_carried_forward, usd.is_carried_forward) as fx_carried_forward
from {{ ref('stg_orders') }} o
join fx usd
  on usd.quote = 'USD' and usd.calendar_date = o.order_date
left join fx src
  on src.quote = o.currency and src.calendar_date = o.order_date

For USD orders the two factors cancel and the amount is unchanged, which makes a handy test. Add a dbt not_null test on amount_usd: it fails when an order uses a currency the bank does not publish, which is the moment to pick a second source.

In Snowflake you can skip the spine and use ASOF JOIN, which picks the latest row on or before the order date:

select o.order_id, o.amount, r.rate, r.rate_date
from orders o
asof join (
  select * from dim_fx_rates where source_id = 'ecb' and base = 'EUR'
) r
  match_condition (o.order_date >= r.rate_date)
  on o.currency = r.quote;

Auditability checklist

Store rate_date and source_id on every converted row, not only the rate.
Keep the raw API file for each run. It is your evidence of what the source said that day.
Never overwrite an official row silently. Alert when the MERGE update branch fires.
Add dbt source freshness on loaded_at, so a missed run is caught the same day.
Test unique on (source_id, rate_date, base, quote).

Cost and plans

The daily job is one request per bank per day, plus one per mid-market snapshot. Dated tables, history and commercial use need a paid plan; the free plan is for evaluation. A backfill can use the history endpoint, for example /api/v1/central-bank/ecb/history?source=USD&target=EUR&from=2026-01-01&to=2026-03-31, which is billed per month of the range. The Official plan covers all 123 central banks and 6 tax authorities for any past date. See pricing, the API docs, and, if you are still choosing a provider, the free currency API comparison.

FAQ

Which rate should a data warehouse store, official or mid-market?

Store official central bank rates for anything finance or audit will read, because each row carries the institution's own rate date. Store mid-market snapshots for operational dashboards where you want the market level at a known time. Keep them in the same table with a source_id column so a report always says which one it used.

How do I handle weekends and public holidays?

Central banks do not publish on those days. Build a calendar spine, join the official rows to it, and carry the last official rate forward with LAST_VALUE IGNORE NULLS. Keep the original rate_date and an is_carried_forward flag next to the rate so the report shows which table was really used.

Can I load the CSV straight into BigQuery or Snowflake?

Yes. Add ?format=csv to the central bank endpoint. The header is rate_date, base, quote, rate_type, value. The file starts with a UTF-8 byte order mark, so decode it with utf-8-sig in Python or let the loader skip the header row.

How do I backfill years of history?

The central bank history endpoint returns a date range for one pair and is billed per month of the range, and the dated endpoint returns one full table per call. Both need a paid plan. Run the backfill once from your own machine, load it through the same staging table and MERGE, then switch to the daily job.

Why use MERGE instead of a plain insert?

The job can run twice, and a request for a Saturday returns Friday's table again. MERGE on source, rate date, base and quote makes every run idempotent, and the update branch records when a stored figure changed.

Official and mid-market rates for your warehouse

Official tables from 123 central banks and 6 tax authorities with each institution's own rate date, plus mid-market rates for 160+ currencies. CSV, JSON, XML or Excel.

See plans