Case study ยท Data engineering
J.P. Morgan treasury balances and transactions, loaded daily.
Sabrosura Foods wanted cash positions and bank transactions across its entities in one place, refreshed every morning, instead of logging into the bank portal and exporting files.
The challenge
Bank APIs are secured differently from typical SaaS products. J.P. Morgan's treasury APIs require a bank-issued client certificate, a signed JWT assertion and the exact token endpoint and audience for the product. The documentation covers several generations of authentication, and the shape of the responses differs from what it describes in more than one place.
What we built
- Certificate-based authentication in an Azure Function: an RS256-signed JWT built from the bank-issued certificate and private key, exchanged for an access token that is cached and refreshed automatically.
- Account balances: daily opening and closing ledger and available balances for every entitled account, with a windowed backfill for history.
- Transaction detail: paginated transaction pulls with the nested account, BAI type and narrative fields flattened into a 55-column table and a typed reporting view with signed amounts.
- Lightweight pipelines: Data Factory calls the API directly and hands the JSON to stored procedures that parse and merge it. No staging files, no Copy Activity type mapping, and idempotent by design through merge on account and transaction id.
What changed
Treasury data for all entities is now in Azure SQL, ready for a daily 6 AM refresh and for Power BI cash reporting. Accounts with no activity are handled gracefully, backfills can be replayed safely, and the authentication that took the most effort to get right is written down step by step for the client's future reference.