Send Bank of Canada exchange rates to Databricks
Load the official daily exchange rates published by the Bank of Canada into your own Databricks workspace 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 Databricks SQL.
Bank of CanadaConnectedExchange Rates
DatabricksDateLabelRateDaily 09:00Amounts in several currencies sit in Databricks with no official daily rate beside them, so each report converts them differently.
Paste this into your AI assistant. It loads Bank of Canada daily rates into your own Databricks workspace with OWOX Data Marts, so every amount converts at the rate of the day it was earned or spent.
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-databricks.md. The task: load Bank of Canada exchange rates into a Databricks table on a daily schedule, backfill the history, and join them to my orders and ad spend by date and currency for one-currency reporting. 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 Databricks workspace, 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 only 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 Databricks SQL
Open Exchange…Bank of Canada
Google AdsAuthorizeConnectedEndpointExchange RatesExchange RatesdatelabelrateStep 2: Load Bank of Canada exchange rates into a Databricks table
The catalog is main, and the schema and table are pre-filled; change those two if you like. Run it and the first load lands in your own Databricks workspace.
- Rows carry a date, a currency pair and a rate
DestinationCatalogmainSchemabank_of_canadaTableexchange_ratesRunTablemain.bank_of_canada.exchange_…Step 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
main.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 read it from Databricks.
- orders.order_date and orders.currency plus CAD (EUR becomes EURCAD) join to the rate of that day, as a plain JOIN in Databricks 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 Databricks 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 Databricks schema
Step 6: Reuse the same rates everywhere
One Databricks 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.37.0Clearer connector errors, and custom connectors that handle more APIs
- 0.37.0Preview data no longer fails on out-of-range dates
Why OWOX for Bank of Canada in Databricks
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 workspace. The table lands in your own Databricks workspace, in the main catalog, so the rest of your lakehouse can use it.
- Your SQL, your workspace. Analysts reach the table from Databricks SQL, and the join lives in the Data Mart instead of a notebook.
- 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.