DuckDB can be used to query a JSON API endpoint, and Malloy makes it simple to transform the resulting data. The example below reads exchange rate data from the US Treasury and shows the data by currency over time.
-- source: exchange_rates is -- duckdb.table('https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/rates_of_exchange?fields=country_currency_desc,exchange_rate,record_date&page[size]=10000&filter=country_currency_desc.json')
This query builds a line chart showing exchange rate values over time. With a simple Malloy query, we can easily transform and analyze a dataset directly from an HTTP API, without any intermediate database required.