Query Bank of Canada exchange rates in AWS Athena
Load the official daily exchange rates published by the Bank of Canada into your own AWS account 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 an Athena query.
Bank of CanadaConnectedExchange Rates
AWS AthenaDateLabelRateDaily 09:00Orders and ad spend arrive in several currencies, and AWS Athena has no official rate of the day to convert them.
Paste this into your AI assistant. It loads Bank of Canada daily rates into your own AWS account with OWOX Data Marts, so one join on date and currency converts every amount in an Athena query.
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-aws-athena.md. The task: load Bank of Canada daily exchange rates into an AWS Athena table on a daily schedule with history backfilled, then join them to my orders by date and currency to report revenue in one currency. 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 AWS account, 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 on your Athena storage and choose Bank of Canada as the source. The source needs no credentials, so there is no token or service account to set up. Then tick the fields.
- One endpoint, Exchange Rates, with 3 fields: date, label and rate
- A time series: every row carries its date, ready to filter in an Athena query
Open Exchange…Bank of Canada
Google AdsAuthorizeConnectedEndpointExchange RatesExchange RatesdatelabelrateStep 2: Load Bank of Canada exchange rates into an Athena database and table
Choose the Athena database and table, or keep the pre-filled ones. Run it and the first load lands in your own AWS account.
- Rows carry a date, a currency pair and a rate
DestinationDatabasebank_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 query it from Athena.
- orders.order_date and orders.currency plus CAD (EUR becomes EURCAD) join to the rate of that day, as a plain JOIN in an Athena query; 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 Athena 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 Athena database
Step 6: Reuse the same rates everywhere
One Athena 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.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.0Athena query results are cleaned up when the Output Bucket names a folder
- 0.37.0Clearer connector errors, and custom connectors that handle more APIs
Why OWOX for Bank of Canada in AWS Athena
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 AWS account. The table sits in your own AWS account, queried through Athena like your other tables.
- Your query, your account. Analysts reach the table with an Athena query, 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.