We hit it in real time for most datasets. A lot of government open data portals are powered by Socrata [0] and we wrote a foreign data wrapper that translates the query into their proprietary query language. If it's a dataset that we host ourselves, there's no problem with the upstream being unavailable.

We have a cache too, but it currently just hashes the query's AST. In the future, we'll be looking at caching actual tables as well -- basically, we'd store some regions of the remote table and selectively pull data from upstream when a query comes in to fill out our view of what the remote table looks like.

[0] https://www.splitgraph.com/docs/ingesting-data/socrata