top of page
Screenshot 2025-11-15 205637_edited_edited.jpg

From Manual Downloads to Intelligent Pipelines: The Currency Rate Automation Using T- SQL in MS Fabric Warehouse

Project Overview​

This project involved building an automated pipeline for Financial Solutions Ltd., a financial services company, to fetch and load daily historical currency exchange rates into their Fabric Data Warehouse. The goal was to provide the Risk team with real-time exchange rate data, enabling accurate international risk assessments and seamless integration with transaction data.

​

The Challenge

  • API Limitations: The Frankfurter.dev API only returns one day's data per call, requiring multiple requests to fetch 30 days' worth of exchange rates.

  • Data Parsing: The data from the API is in JSON format and needed to be parsed and transformed for the Fabric Data Warehouse.

  • Automation: The entire process had to be automated to ensure daily updates without manual intervention.

 

Solution Overview​

I designed and implemented an automated data pipeline to fetch and load daily historical currency exchange rates into the Fabric Data Warehouse. The pipeline works as follows:

  1. Pipeline Trigger (Daily): The process is triggered daily to ensure up-to-date data.

  2. Lookup Activity: This step generates the required date range (start date, end date) and the base currency parameters.

  3. ForEach Activity: The pipeline loops through each date, calling the Web Activity to fetch the exchange rate data from the Frankfurter.dev API.

  4. Web Activity: Retrieves the JSON data for each date from the API.

  5. Stored Procedure: A stored procedure processes the JSON data and inserts it into the CurrencyRates table.

  6. Write to Warehouse Table: Finally, the exchange rates are written into the data warehouse, enabling seamless integration with transaction data for risk analysis.

This end-to-end solution automates the retrieval and loading of exchange rate data, ensuring accuracy and timeliness for the Risk team.

Screenshot 2025-11-15 213602.png

Implementation

Screenshot 2025-11-15 213846.png

Lookup Activity
Generates all dates between StartDate & EndDate

For Each

Loops through all the generated dates

Screenshot 2025-11-15 214003.png

Web Activity 

Gets Data from Frankfurter.Dev website dynamically for every Date

Screenshot 2025-11-15 214011.png

Stored Procedure

Stored Proc Parses JSON output from Web Activity and loads into Warehouse Table

8.1.png

This T-SQL script prepares the database objects required for the FX ingestion pipeline. It first creates a dedicated staging schema, followed by the CurrencyRates table used to store daily exchange rate data. The table includes columns for the rate date, base currency, target currency, and the exchange rate value. Finally, an IngestedAt timestamp column is added to track when each record was loaded, supporting auditability and downstream reporting.

8.2.png

This stored procedure operationalizes the ingestion of FX rate data by transforming raw JSON from the API into clean, analytics-ready records. It first extracts the rate date from the payload and enforces idempotency by removing any previously loaded data for that day—ensuring accuracy and preventing duplication. Using OPENJSON, it dynamically expands the currency rate objects into structured rows and inserts them along with an ingestion timestamp for full auditability.

From a business perspective, this logic guarantees that the Risk team receives consistent, trustworthy, and up-to-date exchange rates, while the technical design ensures scalability, automated reprocessing, and reliability within the Fabric Warehouse environment. 

Validation

8.3.png

Check if all days are populated

This query checks whether every date in the selected range has FX rate records. It helps confirm that the pipeline did not miss any days.

8.4.png

Check for duplicates

This query identifies any duplicate currency rates for the same date. It ensures the stored procedure’s idempotent logic is working correctly.

8.5.png

Check timestamps

This query displays the latest ingested records to verify that the IngestedAt timestamp is being populated correctly.

8.6.png

Check daily volume

This query counts how many records were loaded for each date. It helps confirm that each day has the expected number of exchange rates.

Final Result

Screenshot 2025-11-15 234302.png

  • The automated FX ingestion pipeline is running successfully from end to end.

  • The CurrencyRates table in the Warehouse is fully populated with exchange rate records across multiple dates.

  • Each row includes accurate values for RateDate, BaseCurrency (USD), TargetCurrency, and RateValue.

  • The IngestedAt timestamps confirm that data was loaded correctly by the stored procedure.

  • The idempotency logic is working as expected, ensuring no duplicate records for any date.

  • The dataset is clean, validated, and ready for reporting, analytics, and risk calculations.

Business Impact

  • Provides accurate, up-to-date FX rates for reliable financial and risk reporting.

  • Eliminates manual data collection, reducing effort and errors.

  • Ensures clean, auditable, and consistent data for downstream analytics.

Closing Note

In the end, choosing the Warehouse with T-SQL was the most practical and efficient solution. The task involves small, structured JSON arriving daily - data that can be parsed instantly with OPENJSON and made immediately available for reporting. A Lakehouse and PySpark setup would add unnecessary complexity and overhead designed for large-scale workloads. This approach keeps the pipeline fast, clean, reliable, and perfectly aligned with the business need.

bottom of page