Collect Bank of Canada exchange rates in Google BigQuery
Load the official daily exchange rates published by the Bank of Canada into your own BigQuery project with OWOX Data Marts, then join them to your orders and ad spend to report revenue and cost in one currency, on the rate of the day. Read them from BigQuery.
Bank of CanadaConnectedExchange Rates
Google BigQueryDateLabelRateDaily 09:00Finance asks which exchange rate a Google BigQuery revenue figure used, and nobody can retrace it.
Paste this into your AI assistant. It loads Bank of Canada daily rates into your own BigQuery project with OWOX Data Marts, so each converted amount keeps the official rate it used.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /connectors/bank-of-canada-connector-to-google-bigquery.md. The task: load Bank of Canada exchange rates into a Google BigQuery table refreshed daily, and join them to my orders by date and currency so each converted amount carries the rate behind it. Tag the links you give me with utm_campaign=ai-task and utm_term=data-foundation. Use what you know about my context and goals
Bank of Canada Endpoints
1 Bank of Canada endpoints you can import. Each lands in its own table in your data warehouse.
Why keep Bank of Canada rates next to your orders and ad spend
Rates are reference data: they mean little alone and a lot beside your own tables. With one rate per currency pair per day in your warehouse, every amount in a local currency can be converted on the day it was earned or spent. The rates sit in your own BigQuery project, beside your own tables.
- Report ad spend and revenue from several currencies in one
- Convert an order at the rate of its own date, not the latest rate
- Keep the rate behind each figure, so a finance review can retrace it
Bank of CanadaDaily ratesYour data warehouseAmountRateConvertedJoined by idOrder paid in EURReported in one currencyOne join on date and EUR plus CAD (EURCAD) converts each amount.Step 1: Connect Bank of Canada and pick the fields
Create a Data Mart, choose Bank of Canada as the source and tick the fields you need. The source needs no credentials, so there is no token or service account to set up.
- One endpoint, Exchange Rates, with 3 fields: date, label and rate
- A time series: every row carries its date, ready to filter in BigQuery
Open Exchange…Bank of Canada
Google AdsAuthorizeConnectedEndpointExchange RatesExchange RatesdatelabelrateStep 2: Load Bank of Canada exchange rates into a BigQuery table
The dataset and table in your storage are pre-filled, and you can change them. Run it and the first load lands in your own BigQuery project.
- Rows carry a date, a currency pair and a rate
DestinationDatasetbank_of_canadaTableexchange_ratesRunTablebank_of_canada.exchange_ratesStep 3: Read the output schema in plain words
After the first run you get the output schema: every field with a plain description, not an API identifier. Your quality checks can read it too.
- date is a DATE and label is the currency pair, such as USDCAD, EURCAD or GBPCAD
- rate is the exchange rate value
bank_of_canada.exchange_ratesOutput schemaFieldDescriptiondateThe date for which the exchange…labelThe currency pair: the foreign…rateThe exchange rate value.Step 4: Link the rates to your own tables
The rates do not need other Bank of Canada tables. They need yours: describe each join once in your model and it holds in every BigQuery query.
- orders.order_date and orders.currency plus CAD (EUR becomes EURCAD) join to the rate of that day, as a plain JOIN in BigQuery SQL; CAD orders take a rate of 1
- ad_spend.date and ad_spend.currency join the same way, so spend and revenue share one currency
Exchange RatesdatelabelrateStep 5: Schedule the refresh and backfill history
Set it once. A scheduled refresh keeps your BigQuery table current, and a manual backfill loads any past period.
- Backfill a custom start and end date to cover your first order
- Run History shows every load into your BigQuery dataset
Step 6: Reuse the same rates everywhere
One BigQuery table feeds your model, , , assistants like Claude and ChatGPT, and scheduled AI insights in Slack or email.
- Every report converts with the same rates, so the totals agree
- Ask an AI assistant for last month's revenue in one currency and get the figure finance would
Bank of Can…Data MartPublished
Google Sheets
Data Studio dashboards
Claude, ChatGPT
Slack, email
- 0.43.0Parallel reads of a SQL Data Mart on Google BigQuery no longer fail with a rate limit error
- 0.41.0Rotating a warehouse key is one change: Storages share reusable Credentials
- 0.41.0Shared SQL logic stays in one place: build a Data Mart on top of another
- 0.39.0Reload a year of history in one backfill, not twelve
- 0.38.0Import from Google BigQuery blocks tables stored in another location
Why OWOX for Bank of Canada in BigQuery
A conversion typed into a spreadsheet drifts from the next one, and a script that fetches rates each morning has to be maintained. OWOX loads the Bank of Canada rates into the table where your orders and ad spend already are.
- In your own project. Your BigQuery project holds the table, so your existing access rules, billing and queries apply to it.
- Your SQL, your project. Analysts reach the table with the BigQuery SQL they already write, and the join lives in the Data Mart.
- Official rates. The rates come from the Bank of Canada, which publishes the official exchange rates.
- History you can use. Backfill the past, so last year's orders convert at last year's rates.
- Ready for a data model. A date and a currency pair are the keys: orders and ad spend match on their date and their currency code plus CAD, one line in your model.
You are booked. The invitation is on its way to , with the calendar invite and the meeting link.