SUBMISSION: Case 1 — Multi-Currency Financial Fact & Grain

# SUBMISSION: Case 1 — Multi-Currency Financial Fact & Grain > 🎯 **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: 1. **Three-Currency Model:** Distinctly model Transaction Currency (what the buyer paid), Settlement Currency (what the merchant received), and Reporting Currency (USD for board financials). 2. **Snapshotted Exchange Rates:** Lock in the daily exchange rate at the transaction timestamp to ensure immutable financial history. 3. **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: 1. **`multicurrency_revenue_fact.sql`**: Production DDL for `fct_order_revenue_multicurrency` and `dim_currency`. 2. **Architecture Justification**: Explain your strategy for snapshotting FX rates versus querying an external FX rate table. 3. **`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 1. **Transaction currency** — what the customer actually paid. Stays the same forever after the transaction. 2. **Reporting currency** — what you want to roll up to (usually USD). May change at the company level (e.g., switching to EUR). 3. **Snapshot exchange rate** — the rate used to convert at transaction time. Must be preserved historically. ## Step 2 — The fact table design ```sql 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 ```sql 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