Load Bank of Canada exchange rates into Snowflake
Load the official daily exchange rates published by the Bank of Canada into your own Snowflake 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 Snowflake.
Bank of CanadaConnectedExchange Rates
SnowflakeDateLabelRateDaily 09:00Snowflake holds revenue and costs in local currencies, and every report picks its own exchange rate to combine them.
Paste this into your AI assistant. It loads Bank of Canada daily rates into your own Snowflake account with OWOX Data Marts, so one join on date and currency converts every amount at the official rate.
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-snowflake.md. The task: load Bank of Canada daily exchange rates into a Snowflake table with history backfilled, refresh it every day, and join it to my orders and ad spend to report both 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 Snowflake 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 Snowflake and pick 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 Snowflake
Open Exchange…Bank of Canada
Google AdsAuthorizeConnectedEndpointExchange RatesExchange RatesdatelabelrateStep 2: Load Bank of Canada exchange rates into a Snowflake table
Choose your Snowflake database; the schema and table are pre-filled and editable. Run it and the first load lands in your own Snowflake account.
- Rows carry a date, a currency pair and a rate
DestinationDatabaseSchemabank_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 write it as plain SQL in Snowflake.
- orders.order_date and orders.currency plus CAD (EUR becomes EURCAD) join to the rate of that day, as a plain JOIN in a Snowflake 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 Snowflake 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 Snowflake schema
Step 6: Reuse the same rates everywhere
One Snowflake 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 Snowflake
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.
- Inside your account. The table lives in your Snowflake account, next to the tables you already query and under your own roles.
- Your SQL, your account. Analysts reach the table with Snowflake 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.