Microsoft Fabric
Data Factory

Market Risk Intelligence Feed

A Microsoft Fabric exercise in turning a nested JSON API into a pipeline that survives being run twice.

Role: Independent project
Year: 2026
Stack: Microsoft Fabric · T-SQL · OPENJSON · Data Factory

Global Credit Corp’s risk desk was still converting foreign currency exposure using rates fetched by hand from public websites and typed into Excel. The task was to replace that with a Fabric pipeline that pulls daily exchange rates from a public API and lands them in a warehouse table that can be run more than once without corrupting itself.

The problem

Global Credit Corp’s international risk desk reports exposure in a single base currency, USD, which means it needs a reliable daily feed of exchange rates for every currency pair it holds. The brief specified the rates should come from Frankfurter, a free exchange rate API, but its response is nested JSON:

{"date":"2023-10-01","rates":{"EUR":0.945,"JPY":149.33,"GBP":0.82}}

That shape does not match a flat SQL table. The risk team lacked the Python skills to parse it at scale, so rates got captured by hand, once a month, rather than daily. The constraint was to do the parsing inside the warehouse itself, with T-SQL, rather than reaching for a notebook.

What I built

The target table. A staging.CurrencyRates table with one row per date and currency pair: the date, the base currency, the target currency, and the rate.

T-SQL CREATE TABLE statement for AnjaliWarehouse.staging.CurrencyRates, with columns RateDate as DATE, BaseCurrency as CHAR(3), TargetCurrency as CHAR(3), and RateValue as DECIMAL(20,6). — One row per currency pair per day. Simple enough that any downstream report can join to it directly.

Parsing the JSON. Before wiring anything into a pipeline, I tested the shredding logic in isolation against a hard-coded sample response. OPENJSON turns the rates object into one row per key, so the same query handles however many currencies the API returns on a given day without listing them by name.

T-SQL query declaring a sample JSON string with date, base and rates fields, then using OPENJSON on the rates object with CAST and JSON_VALUE to select RateDate, BaseCurrency, TargetCurrency and RateValue as separate columns. — Testing the parse against a fixed sample first, before it ever touches a live API call.

The stored procedure. The first version took the raw JSON in as a parameter and inserted every row it parsed, no questions asked.

T-SQL stored procedure sp_InsertRates that declares an NVARCHAR(MAX) parameter, then inserts RateDate, BaseCurrency, TargetCurrency, RateValue and FetchedAtUTC into staging.CurrencyRates by selecting from OPENJSON on the rates object, with no duplicate check. — Version one: correct on a single run, but nothing stops it inserting the same day's rates twice.

The pipeline. A Fabric Data Factory pipeline with a Lookup activity (GetDates) feeding a ForEach loop, so the same pipeline handles one date or a range of them. Inside the loop, a Web activity (FX_web) calls Frankfurter for that date, and its output is passed straight into the stored procedure.

Fabric Data Factory pipeline canvas showing a Lookup activity named GetDates feeding a ForEach loop, containing a Web activity named FX_web connected to a Stored procedure activity. Parameters panel shows TargetDate as a string and DaysBack as a string. — TargetDate and DaysBack turn the same pipeline into either a single day's run or a historical backfill, depending on what's passed in.

I used that ForEach loop to backfill the table from 1 October 2025 to the current date, added a UTC timestamp column recording when each row was fetched, and scheduled the pipeline to run daily.

Challenge 01

Make it safe to run twice

A daily pipeline will eventually be re-run for a date it already loaded: a retry after a failure, a manual re-trigger, a backfill that overlaps a day already covered. If the stored procedure just inserts, every one of those re-runs doubles the rows for that date and quietly corrupts every report built on top of the table.

I rewrote sp_InsertRates to check for an existing row before inserting one, using a WHERE NOT EXISTS against RateDate and TargetCurrency rather than a plain INSERT.

FROM OPENJSON(@sampledata, '$.rates') AS x
WHERE NOT EXISTS (
    SELECT 1
    FROM staging.CurrencyRates c
    WHERE c.RateDate = @RateDate
      AND c.TargetCurrency COLLATE Latin1_General_100_BIN2_UTF8
        = CAST(x.[key] AS VARCHAR(10)) COLLATE Latin1_General_100_BIN2_UTF8
);

With that in place, I could trigger the pipeline twice for the same date and confirm the row count didn’t move between the two runs, which is the whole idempotency contract in one test.

T-SQL stored procedure sp_InsertRates, altered to declare RateDate and BaseCurrency as variables, then insert into staging.CurrencyRates only where no existing row matches the same RateDate and TargetCurrency, using a COLLATE clause on the comparison. — Version two: the WHERE NOT EXISTS check is what makes a second run of the same date a no-op instead of a duplicate.

Checking the final table. Querying staging.CurrencyRates end to end surfaces something the replay test didn’t catch: 11 August’s three rates appear twice, identical values both times.

SQL query results for staging.CurrencyRates ordered by date descending. The three most recent rows, dated 14 August, show a FetchedAtUTC timestamp of 2026-08-15 03:30:42. Older rows, including 11 August, 4 August and 29 July, show NULL in that column. The three 11 August rows, for GBP, JPY and USD against EUR, appear a second time further down the results with identical rate values. — 14 August carries a fetch timestamp because it was loaded after the audit column existed. 11 August does not, and that date is duplicated exactly, caught here but not yet cleaned up.

That’s a genuine duplicate, and it’s a useful reminder about what the idempotency fix actually guarantees. WHERE NOT EXISTS stops a new insert from duplicating a row that’s already there. It does not clean up rows that got in before the check existed. Fixing this table needs a one-off deduplication query on top of the safeguard already built, not a replacement for it.

What this proves

Nested JSON from an external API can be parsed directly in the warehouse with plain T-SQL, without a Spark notebook in the loop, when the workload is a daily batch rather than something that needs distributed compute. And a pipeline that only works correctly the first time it runs is not finished. Idempotency has to be designed in, not assumed.