top of page
Gemini_Generated_Image_namgxnamgxnamgxn.png

Global Credit Corp - API Ingestion

Case Study

About

This case study demonstrates the ingestion of daily currency exchange rates from an external API into Microsoft Fabric Data Warehouse using pipelines and T-SQL stored procedures. The automated solution ensures up-to-date and accurate currency data, enabling reliable risk reporting and seamless joins with transaction data.

Challenge

  • Outdated Risk Reporting – International risk reports were using old currency exchange rates, impacting accuracy and business sign-off

​

  • External Data Dependency – Required exchange rate data was not available internally and had to be fetched from an external API

​

  • API and Data Complexity – The API returned only one day of data per call and provided the response in JSON format, requiring looping and SQL-based parsing

​

  • Automation Requirement – The solution needed to be a single, automated, and efficient pipeline that loads data directly into the Fabric Data Warehouse for reporting use.

Solution

  • API Integration via Pipeline – A Fabric pipeline was built using a Web activity to call the Frankfurter currency exchange API for the required dates

​

  • Loop-Based Data Fetching – The pipeline was designed to call the API multiple times to retrieve daily historical exchange rates instead of a single bulk response

​

  • JSON Parsing with T-SQL – A stored procedure was created in the Fabric Data Warehouse to parse the API’s JSON response using OPENJSON and extract currency rates

​

  • Direct Warehouse Loading – The parsed exchange rate data was inserted directly into the CurrencyRates table, enabling automated, reliable, and query-ready data for reporting.

Business impact

  • Accurate Risk Reporting – Daily and up-to-date currency exchange rates improved the accuracy and reliability of international risk reports

​

  • End-to-End Automation – The automated pipeline removed manual data collection and ensured exchange rates were loaded consistently without human intervention

​

  • Operational Efficiency – Using a single pipeline and stored procedure reduced processing time and simplified maintenance

​

  • Scalable and Reusable Design – The solution can be easily extended for future dates or additional currencies without changes to the core logic.

carbon (27).png

This screenshot shows a sample currency exchange rate API response stored in a SQL variable and parsed using T-SQL. The JSON data is processed using OPENJSON to extract individual currency rates, while JSON_VALUE is used to capture the rate date and base currency. This step validates that the API response can be successfully converted into a relational format before inserting the data into the Fabric Data Warehouse table.

A T-SQL stored procedure is created in the Fabric Data Warehouse to handle currency rate ingestion. It accepts the API JSON response as an input parameter, parses the data using OPENJSON, and inserts the rate date, base currency, target currency, and rate value into the CurrencyRates table. This logic enables reusable and automated data loading.

This screenshot shows the Microsoft Fabric pipeline used to ingest currency exchange rates. A Web activity calls the Frankfurter API to fetch exchange rate data, and the response is passed directly to a stored procedure in the Fabric Data Warehouse. The successful pipeline run confirms that the API call and data loading steps execute correctly, enabling automated and reliable ingestion of daily exchange rate data.

The Currency Rates table in the Fabric Data Warehouse is populated with daily exchange rate records. Each row represents a base currency, target currency, rate value, and rate date, verifying that the ingestion process completed successfully. The data is now ready to be joined with transaction data for reporting and analysis.

bottom of page