Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Fact table with multiple currencies

Data modeling · Fact Table Design

Fact table with multiple currencies

Mediumdata-modeling-40
multi-currencyfact-tablesexchange-ratesfinancial-modeling

Question

How do you model sales recorded in multiple currencies?

Solution

To model sales recorded in multiple currencies, store both the original transaction amount with its currency code and the converted amount in the primary reporting currency directly on the fact row. Conversion must use the exchange rate effective on the transaction date, referencing a dedicated exchange-rate table.

Storing original and converted amounts

A global business must record transactions in local currencies while providing consolidated executive reporting in a corporate currency like USD:

fact_sales
sale_id
customer_key
product_key
sale_date_key
local_currency_code         -- e.g. 'EUR', 'GBP', 'JPY'
local_amount                -- original transaction value
converted_usd_amount        -- local_amount * exchange_rate_to_usd
exchange_rate_applied       -- rate on sale_date_key

A short look at the columns:

  • local_amount guarantees transactional fidelity for local tax and store reconciliation.
  • converted_usd_amount enables corporate-wide revenue aggregations without runtime conversion joins.
  • exchange_rate_applied provides a clear audit trail for accounting reviews.

Daily exchange rate table

Maintain a conformed exchange rate table (dim_exchange_rate or fact_daily_exchange_rates) populated daily by a trusted financial feed. The table records conversion rates for each currency pair by date:

  • Keys: date_key, from_currency_code, to_currency_code.
  • Measures: spot_rate, daily_average_rate, month_end_rate.

During the ETL load of fact_sales, join to the rate table using the transaction date to calculate the converted amount before writing the record.

Common query mistakes

Never convert historical transactions at query time using today's exchange rate. Doing so recalculates past quarterly sales figures every morning as currency markets move, violating accounting standards. If the business requires reporting in multiple currencies (such as both USD and EUR), store converted columns for each target currency directly on the row, or provide certified views joined to historical daily rate tables.

PreviousNext