🎯 Hands-on Portfolio Deliverable: In this project, you will build the financial revenue fact table for a global multinational marketplace operating across 18 countries. You will design multi-currency storage, implement daily snapshotted exchange rates, and author financial reconciliation queries that balance local transaction amounts against consolidated USD board reports.
🏢 Business Scenario & Executive Prompt
You are the Principal Analytics Architect at Apex Global, an international marketplace serving customers across Japan (JPY), the UK (GBP), the Eurozone (EUR), India (INR), and the United States (USD). At the end of Q3, the CFO and corporate controller flagged an alarming issue: while European sales grew by 14% in local Euros, consolidated USD revenue showed a 3% decline due to currency fluctuations. Furthermore, because analysts joined current exchange rates dynamically at query time, historical revenue figures changed every single morning.
You must architect a production-grade multi-currency dimensional model:
- Three-Currency Model: Distinctly model Transaction Currency (what the buyer paid), Settlement Currency (what the merchant received), and Reporting Currency (USD for board financials).
- Snapshotted Exchange Rates: Lock in the daily exchange rate at the transaction timestamp to ensure immutable financial history.
- Financial Reconciliation: Author audit queries that reconcile regional revenue against corporate general ledger totals.
🎯 Required Deliverables
You must upload a single .sql or .zip file containing:
multicurrency_revenue_fact.sql: Production DDL forfct_order_revenue_multicurrencyanddim_currency.- Architecture Justification: Explain your strategy for snapshotting FX rates versus querying an external FX rate table.
financial_fx_reconciliation.sql: SQL verification queries proving (a) Consolidated USD quarterly revenue, and (b) Regional currency exposure analysis.
"Your e-commerce business has customers in India (INR), the US (USD), the EU (EUR), and Singapore (SGD). The board reports revenue in USD. How do you model the fact table so analytics can: (a) compute total revenue in USD for board reporting, (b) compute country-level revenue in local currency, (c) historically accurate exchange rates at transaction time?"
Multi-currency is one of those problems that looks simple but tangles everything if you don't model it cleanly up front. Almost every B2C and B2B-international company hits this.
Step 1 — The three currency concepts
- Transaction currency — what the customer actually paid. Stays the same forever after the transaction.
- Reporting currency — what you want to roll up to (usually USD). May change at the company level (e.g., switching to EUR).
- Snapshot exchange rate — the rate used to convert at transaction time. Must be preserved historically.
Step 2 — The fact table design
CREATE TABLE fct_order_items (
order_item_key BIGINT PRIMARY KEY,
order_id BIGINT,
order_date_key INTEGER,
customer_key INTEGER,
product_key INTEGER,
-- transaction currency
transaction_currency CHAR(3), -- 'INR', 'USD', 'EUR', 'SGD'
-- amounts in transaction currency
quantity INTEGER,
unit_price_txn NUMERIC(12, 2),
revenue_txn NUMERIC(12, 2),
discount_txn NUMERIC(12, 2),
-- exchange rate at time of transaction
fx_rate_to_usd NUMERIC(15, 8), -- e.g., 0.012 for INR→USD
-- amounts converted to USD at transaction time (snapshotted)
revenue_usd NUMERIC(12, 2), -- revenue_txn * fx_rate_to_usd
discount_usd NUMERIC(12, 2),
inserted_at TIMESTAMPTZ
);
Two parallel sets of measures: one in transaction currency, one converted to USD at the time of the transaction.
Step 3 — Why snapshot the FX rate
Imagine an order from June 2024:
- Customer paid ₹10,000 (INR).
- INR/USD rate on June 2024: 0.012 → $120 USD.
- INR/USD rate today: 0.011 → ₹10,000 today is $110.
What should historical revenue show for this order? $120 — the rate at the time. The customer paid that much in USD-equivalent value when they paid.
If we joined to a current dim_currency_rate table, the historical $120 would silently drift to $110 every time the rate updated. Reports would change retroactively. Disaster.
Solution: snapshot the FX rate (and the converted USD value) at fact-write time. Historical numbers stay stable.
Step 4 — The currency dimension
CREATE TABLE dim_currency (
currency_key INTEGER PRIMARY KEY,
currency_code CHAR(3),
currency_name VARCHAR,
symbol VARCHAR,
decimal_places SMALLINT, -- 2 for USD, 3 for some currencies
is_active BOOLEAN
);
Plus a separate FX-rate table (NOT a Type 2 dim — it's a daily rate table):
CREATE TABLE dim_currency_rate (
rate_date DATE,
from_currency CHAR(3),
to_currency CHAR(3),
rate NUMERIC(15, 8),
PRIMARY KEY (rate_date, from_currency, to_currency)
);
The fact references this for the snapshot at write-time, but doesn't join to it for historical queries (the snapshotted fx_rate_to_usd and revenue_usd are sufficient).
Step 5 — Queries
Total revenue in USD for board reporting
SELECT order_date_key, SUM(revenue_usd) AS revenue_usd
FROM fct_order_items
GROUP BY order_date_key;
Trivial — measure already snapshotted.
Country-level revenue in local currency
SELECT
customer.country,
fct.transaction_currency,
SUM(fct.revenue_txn) AS revenue_local
FROM fct_order_items fct
JOIN dim_customer customer ON customer.customer_key = fct.customer_key
GROUP BY 1, 2;
Each currency's totals are clean — no conversion lossy math, no rate drift.
Currency exposure (how much revenue per currency)
SELECT
transaction_currency,
SUM(revenue_usd) AS revenue_usd,
COUNT(*) AS orders
FROM fct_order_items
WHERE order_date_key >= 20260101
GROUP BY transaction_currency;
Finance teams love this — answers "how much USD revenue came from INR purchases this year". Currency hedging decisions depend on it.
Step 6 — When the reporting currency changes
The company switches reporting from USD to EUR. Two options:
Option A: Add a new column
ALTER TABLE fct_order_items ADD COLUMN
fx_rate_to_eur NUMERIC(15, 8),
revenue_eur NUMERIC(12, 2);
Backfill with historical rates. Now reports in both USD and EUR.
Option B: Don't store per-currency; compute at query time
Skip the snapshot to a specific reporting currency. Always store transaction currency + transaction-date FX rate to USD. For any reporting currency Q, compute:
revenue_in_Q = revenue_txn × fx_rate_txn_to_Q(rate_date = order_date)
Requires joining to the historical rate table at query time. More flexible (any reporting currency) but slower and requires the rate table to never lose history.
Most warehouses pick Option A — snapshot once for the canonical reporting currency, add columns only when needed. Less flexible but stable and fast.
Step 7 — Edge cases
Subscription revenue across currencies
Subscriptions billed in USD to a customer in India: transaction_currency = 'USD'. No conversion needed; just record what was charged.
Refunds and returns
Refund issued in the original transaction currency at the original FX rate. Use the SAME fx_rate_to_usd as the original transaction (or the rate at refund time, depending on accounting policy — confirm with finance).
Free orders / promotions
revenue_txn = 0 → revenue_usd = 0. No exotic handling.
Currency conversion fees (FX margins)
The rate you charge the customer might include a 1-3% FX margin. Source data may include this. Decide whether to model the gross rate or the net-of-margin rate. Document the choice.
Step 8 — Talk through it
You: "The key insight is snapshotting both the FX rate and the converted USD revenue at transaction time, on the fact table itself. Joining to current rates at query time would let historical numbers drift retroactively, which is unacceptable for finance.
The transaction-currency amounts are also kept on the fact for local-currency analyses. No conversion ambiguity in either direction.
A separate
dim_currency_ratetable holds the daily rates for backfilling and audit. We don't join to it for historical queries — the snapshot on the fact is the truth — but it's there if we need to add a new reporting currency later.Edge cases (refunds, FX margins, multi-currency promotions) need explicit business rules. They can't be solved by schema alone; finance has to decide and document."
Common follow-ups
Follow-up 1: "What FX rate do you use for daily revenue reporting?"
Two choices:
- Daily closing rate (most stable for accounting).
- Daily average rate (smoother day-to-day fluctuation).
Confirm with finance. Document it in the warehouse.
Follow-up 2: "What if the source system gives you only transaction currency revenue, no FX rate?"
The ETL must do the conversion. Use the rate from dim_currency_rate for the transaction's date. Store the rate used on the fact.
Follow-up 3: "How do you reconcile to the GL?"
Daily reconciliation: SUM(revenue_usd) per day must match the GL's reported daily USD revenue. Differences usually trace to: timing differences, refunds processed late, FX margin variances. A reconciliation dashboard with explicit drill-downs is standard.
📥 Download Starter Assets & Mock Database
Choose your preferred environment to get started immediately:
💾 Pre-Seeded SQLite Database (Recommended — Zero Setup):
⬇ Download case1_multicurrency.sqlite
Pre-loaded with raw source tables, mock transactional records, and schema skeletons. Double-click to open in DB Browser for SQLite, DBeaver, or VS Code.📄 ANSI / PostgreSQL SQL Starter Script:
⬇ Download case1_multicurrency_starter.sql
Contains clean DDL statements, table definitions, and mock INSERT statements ready to paste into PostgreSQL, DuckDB, Snowflake, or BigQuery.
🛠️ How to Complete & Run This Case Study
You can complete this hands-on project using whichever tool matches your local setup:
- Option 1: SQLite / DB Browser / DBeaver (Easiest — Zero Installation)
Downloadcase1_multicurrency.sqliteabove. Open it in DB Browser for SQLite, DBeaver, or VS Code (with the free SQLite Viewer extension). All source ingestion tables and sample records are already populated. Write your target dimensional models and verification queries directly against the database with 0 server configuration. - Option 2: Local PostgreSQL or DuckDB
Downloadcase1_multicurrency_starter.sql. Execute the script in your local PostgreSQL database (via psql, DBeaver, or pgAdmin) or in DuckDB. - Option 3: Cloud Data Warehouse (Snowflake / BigQuery)
Copy and paste the DDL and mock seed data into a Snowflake worksheet or Google BigQuery dataset.
💻 Starter Scaffold
Use this template to begin your implementation:
-- ====================================================================
-- DELIVERABLE 1: multicurrency_revenue_fact.sql
-- ====================================================================
-- 1. FACT TABLE: fct_order_revenue_multicurrency
CREATE TABLE IF NOT EXISTS fct_order_revenue_multicurrency (
order_revenue_key BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL,
order_date_key INTEGER NOT NULL, -- FK -> dim_date
customer_key INTEGER NOT NULL, -- FK -> dim_customer
-- Currency Dimension FKs
transaction_currency_key INTEGER NOT NULL, -- Currency used by buyer (e.g. EUR)
settlement_currency_key INTEGER NOT NULL, -- Currency deposited in bank (e.g. GBP)
reporting_currency_key INTEGER NOT NULL, -- Corporate board currency (USD)
-- Transaction Currency Measures (Raw as swiped)
txn_gross_amount NUMERIC(14, 2) NOT NULL,
txn_discount_amount NUMERIC(14, 2) DEFAULT 0.00,
txn_tax_amount NUMERIC(14, 2) DEFAULT 0.00,
txn_net_amount NUMERIC(14, 2) NOT NULL,
-- Snapshotted Exchange Rates (at time of transaction)
fx_rate_txn_to_usd NUMERIC(18, 6) NOT NULL,
fx_rate_txn_to_settle NUMERIC(18, 6) NOT NULL,
-- Standardized Reporting Measures (USD)
usd_gross_amount NUMERIC(14, 2) NOT NULL, -- txn_gross * fx_rate_txn_to_usd
usd_discount_amount NUMERIC(14, 2) DEFAULT 0.00,
usd_tax_amount NUMERIC(14, 2) DEFAULT 0.00,
usd_net_amount NUMERIC(14, 2) NOT NULL,
-- Audit
inserted_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
-- 2. DIMENSION: dim_currency
CREATE TABLE IF NOT EXISTS dim_currency (
currency_key INTEGER PRIMARY KEY,
currency_code CHAR(3) NOT NULL UNIQUE, -- USD, EUR, GBP, JPY, CAD
currency_name VARCHAR(100) NOT NULL,
currency_symbol VARCHAR(10) NOT NULL,
is_major_currency BOOLEAN NOT NULL
);
-- ====================================================================
-- DELIVERABLE 2: financial_fx_reconciliation.sql
-- ====================================================================
-- Query 1: Consolidated Board Revenue in USD by Quarter
-- TODO: Write query aggregating USD net revenue for board reporting.
-- Query 2: Currency Exposure & FX Volatility Impact
-- TODO: Compare raw transaction volume vs USD converted revenue to highlight FX exposure risk.
📊 Evaluation Rubric (100 Points Total)
| Evaluation Criterion | Weight | Target Standard (Pass ≥ 80%) |
|---|---|---|
| 1. Multi-Currency Grain Clarity | 25 pts | Fact table explicitly accounts for the three currency layers (transaction, settlement, and reporting) without ambiguous column naming. |
| 2. FX Rate Snapshot Strategy | 25 pts | FX exchange rate is permanently snapshotted into the fact table row at the time of transaction, preventing historical revenue drift when currency markets fluctuate. |
| 3. Precision & Rounding Standards | 25 pts | Appropriate numeric data types (NUMERIC(18, 6) for exchange rates; NUMERIC(14, 2) for currency amounts) avoiding floating-point rounding errors. |
| 4. General Ledger Reconciliation | 25 pts | Accompanying verification queries demonstrate exact reconciliation between local transaction currencies and consolidated USD corporate revenue. |
📤 Submission Instructions & Guidelines
- Deliverable Formats: Upload your completed
.sqlfile or a.ziparchive containing your SQL scripts and documentation (.md,.pdf, or ERD diagrams). - Dialect Compatibility: Ensure all DDL and queries execute cleanly on standard ANSI SQL, PostgreSQL, Snowflake, BigQuery, or Databricks.
- Self-Review: Cross-check your solution against the 100-Point Evaluation Rubric above before submitting (passing threshold is 80/100 points).
- Upload: Use the drag-and-drop file upload zone below. You can optionally detail architectural trade-offs in the submission notes box.
- Confirmation: Click Submit Assignment. Your submission will be recorded, your lesson and module progress will be marked complete, and you can download your submitted work at any time.