top of page

Background

Global Credit Corp, a Tier-1 lender managing a $4.2 billion international credit portfolio, had no reliable way to track daily currency exchange rates. Analysts were manually pulling exchange rates from public websites and typing them into spreadsheets, which meant risk reports often relied on data that was weeks or months out of date. With currency values swinging 8-12% a year, this gap was creating potential valuation errors of $35-50 million per quarter. For this project, I built an automated pipeline in Microsoft Fabric that pulls daily exchange rates directly from a financial data API and loads them into a warehouse table, using T-SQL to parse the incoming data and a repeatable process that ensures rates are never duplicated or lost, even if the pipeline runs more than once for the same day.

Problem

  • Exchange rates were sourced manually from public websites and typed into spreadsheets, with no automated feed into the data warehouse.

  • Risk reports relied on static, often months-outdated conversion rates, despite 8-12% annual currency volatility across key markets.

  • Manual entry created a real risk of costly typing errors, since a single misplaced decimal in an exchange rate could skew portfolio valuations by millions.

  • The data team lacked the skills to work with the nested, complex format the exchange rate data came in, so they could only capture occasional snapshots instead of a full daily trend.

  • Different business units used different, inconsistent rates for the same date, leading to mismatched financial reporting across the firm.

  • The reporting delays and inconsistent data made it difficult to meet financial industry standards for accuracy and timeliness (Basel III).

Solution

Warehouse Environment (SQL codes)

Started by setting up the foundation in the Fabric Data Warehouse, creating a dedicated "staging" schema to keep the raw, incoming exchange rate data organized and separate from other data.

Created the CurrencyRates table inside the staging schema, with columns to store the rate date, the base currency, the target currency, and the actual exchange rate value, giving the incoming data a clear, structured home.

Tested the JSON parsing logic using a sample exchange rate response, using OPENJSON to break down the nested currency data into clean, individual rows, one for each currency pair, ready to be inserted into the table.

  • Built the sp_InsertRates stored procedure to take the incoming exchange rate data, parse out each currency pair, and use a MERGE statement to either update an existing rate or insert a brand new one, so the same day's data never gets loaded twice.

  • Also added a timestamp column that automatically records the exact moment each rate was fetched, giving the whole system a clear, built-in audit trail.

Data Pipeline (For Each Activity)

Built a Fabric Pipeline with a ForEach loop that calls the exchange rate API and runs the stored procedure for each currency in turn, so the whole process can repeat automatically without any manual work.

  • Set up a Web activity to connect to the exchange rate API, building the request URL dynamically so it fetches a date range starting from 1 October 2025 up to today's date.

  • Used the base currency (EUR) plus the current currency from the ForEach loop to pull in each target currency's rate one at a time, keeping the whole date range covered without any manual input.

Connected the Stored Procedure activity to the pipeline, passing the API's response directly into sp_InsertRates as its input, so every fetched rate flows straight into the warehouse table without any manual copying.

Ran the full pipeline and confirmed every step succeeded: the loop called the API and ran the stored procedure once for each currency, showing the automated backfill worked end-to-end with no failures.

Checking the Output table

  • Verified the final results by checking the total row count, confirming 696 exchange rate records were successfully loaded into the warehouse table, covering the full backfill period from October 2025 to  today. Also ran the pipeline multiple times to check whether the no. of rows remained the same or not, thereby making the pipeline idempotent, i.e., no duplicate records.

  • Reviewed the actual data to confirm each row had a correct rate date, currency pair, exchange rate value, and a timestamp showing exactly when it was fetched, proving the pipeline captured a clean, complete daily record.

Outcome

  • ​Eliminated the $35-50M quarterly Value-at-Risk blind spot by replacing outdated, manually-updated exchange rates with a daily automated feed the Risk desk can trust.

  • Cut down the 15-20 hours per month analysts spent manually sourcing and entering exchange rates, freeing up time for higher-value risk analysis work.

  • Removed the risk of costly manual entry errors, since rates now flow directly from a trusted source straight into the warehouse with no copy-pasting involved.

  • Built in a safeguard so the pipeline can be re-run safely at any time without creating duplicate or conflicting records, making the data dependable for audits and financial reporting.

  • Created a scalable foundation that can easily expand to new currencies and regions in the future, without needing to rebuild the pipeline from scratch.

bottom of page