Exploring UK Environment Agency Data with DuckDB and Rill

The UK Environment Agency publishes a feed of data relating to rainfall and river levels. As a prelude to building a streaming pipeline with this data, I wanted to understand the model of it first. The API docs are pretty good, and from them I derived this model: To poke around the data and make sure I understood how the different entities related, and what to expect from each API endpoint, I used DuckDB. Load the data 🔗 The beauty of DuckDB is it is so simple, yet powerful. It generally behaves in a “oh cool, it just works” way. The data is published as JSON from a REST endpoint. To load it into DuckDB was just a case of using the read_json function: CREATE TABLE readings_stg AS SELECT * FROM read_json( 'https://environment.data.gov.uk/flood-monitoring/data/readings' ); CREATE TABLE measures_stg AS SELECT * FROM read_json( 'https://environment.data.gov.uk/flood-monitoring/id/measures' ); CREATE TABLE stations_stg AS SELECT * FROM read_json( 'https://environment.data.gov.uk/flood- ... Read full article.