Warehouse API Ingestion at Global Credit Corp (Microsoft Fabric)

 

Company Background
Global Credit Corp operates global financial and analytical workloads that depend on accurate, up-to-date currency exchange rates for reporting, reconciliation, and downstream analytics. With operations spanning multiple regions and currencies, manual handling of exchange rates introduced risks such as incorrect reporting, inconsistent rate usage, and delayed analytics.

Project Overview
An end-to-end automated currency exchange ingestion pipeline was implemented using Microsoft Fabric. The solution integrates external REST API ingestion, structured warehouse storage, JSON parsing using T-SQL, and orchestrated, scheduled pipelines. Fresh exchange rates are captured daily, stored in a normalized warehouse schema, and made readily available for analytics and reporting.

Project Objectives
Automate daily ingestion of currency exchange rates from an external API. Eliminate manual updates and ad-hoc processes. Store exchange rates in a query-optimized warehouse table. Ensure data consistency, traceability, and reusability. Enable hands-off, production-ready execution through scheduling.

Problem Statement
The organization required accurate daily exchange rates for financial reporting, billing reconciliation, and cost analysis. Exchange rates were not centrally stored in a structured warehouse, manual collection caused delays and errors, historical rate changes were not captured, and raw API responses were semi-structured JSON unsuitable for direct analytics. As a result, financial calculations risked being outdated, inconsistent, and difficult to audit.

Solution Architecture
A structured warehouse schema was designed in Microsoft Fabric with a normalized table storing rate date, base currency, target currency, and exchange rate values using precise financial data types.

A T-SQL stored procedure was developed to accept raw API JSON, parse nested rate objects using OPENJSON, transform semi-structured data into relational rows, and insert results into warehouse tables. This ensured a clean separation between ingestion and transformation and enabled reuse for future API expansions.

Business Outcomes
Exchange rate ingestion became fully automated, eliminating manual updates. Financial reporting accuracy improved through consistent rate usage. Operational overhead was reduced, scalability for additional currencies was enabled with minimal changes, and a production-ready pattern for API-based ingestion in Microsoft Fabric was established.