Bring Bank of Canada exchange rates into AWS Redshift
Load the official daily exchange rates published by the Bank of Canada into your Redshift cluster or workgroup 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 a Redshift query.
Bank of CanadaConnectedExchange Rates
AWS RedshiftDateLabelRateDaily 09:00Revenue in AWS Redshift is converted at the latest rate, not the rate on the day each order was paid.
Paste this into your AI assistant. It loads Bank of Canada daily rates into your Redshift cluster or workgroup with OWOX Data Marts, so each amount converts at the rate of its own date.
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-redshift.md. The task: load Bank of Canada exchange rates into an AWS Redshift schema and table, refresh them daily, and join them to my orders by date and currency so each order converts at its own day's rate. 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 Redshift cluster or workgroup, 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 Redshift storage, choose Bank of Canada as the source and select the fields. 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 a Redshift query
Open Exchange…Bank of Canada
Google AdsAuthorizeConnectedEndpointExchange RatesExchange RatesdatelabelrateStep 2: Load Bank of Canada exchange rates into a Redshift schema and table
The schema and table in your Redshift database are suggested and can be changed. Run it and the first load arrives in your cluster or workgroup.
- Rows carry a date, a currency pair and a rate
DestinationSchemabank_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 run it in Redshift.
- orders.order_date and orders.currency plus CAD (EUR becomes EURCAD) join to the rate of that day, as a plain JOIN in a Redshift 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 Redshift 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 Redshift schema
Step 6: Reuse the same rates everywhere
One Redshift 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.36.0Start a connector run without expanding the Input Source card
Why OWOX for Bank of Canada in AWS Redshift
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 cluster. The table belongs to your Redshift cluster or workgroup, so it joins the data your analysts already use there.
- Your query, your cluster. Analysts reach the table with a Redshift 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.