On demand refresh for near-realtime use cases

[aaronsteers]

Feature Description

A "refresh" button in the Web UI, which would queue up an on-demand refresh.

Goal of Feature

This could help with use cases that require near-realtime data, by allowing Evidence to get the latest data without having a window into the backend via SSH, and without acruing query costs of refreshing a full set of data every n minutes.

This might be used in combination with a daily refresh or n-times daily.

Variations / Implementation options

Where the button might be:

1. This could be put on a special "Admin view" page.
2. This could be at the top of each rendered page, hidden under an exapandable "dataset on this page" widget.
3. This could be part of the expandable data section per each chart.

What the button might do:

1. Could refresh one selected dataset and only the active page.
2. Could refresh one selected dataset and all impacted pages of the site.
3. Could refresh all the datasets on the active page.
4. Could refresh all the datasets on the site.

Atomic or global:

This on-demand refresh could leave other pages/visuals alone, versus also auto-updating other pages or other visuals on the current page, versus refreshing the site anywhere that dataset is rendered.

Bonus:

For transparency reasons, it becomes even more helpful to have a place on the page that renders "last updated on..." per dataset, and/or for the page overall. (Harder to do if not all visuals on the page have the same refresh time.)

Current Solution

Today I think the best solution is to refresh every n minutes.

Alternative Ideas

Alternatives include:

1. Refreshing every n minutes on a schedule. (Noted above.)
2. Define certain queries "on-demand" whenever the page loads. Not mutually exclusive with this option, but the refresh-on-demand approach balances flexibility along with render performance; whereas an on-demand query would add significant lag.
3. Automatically queuing data refreshes when a page reaches a certain staleness threshold, along with a notification to the user that the refresh has been queued. This saves the user from needing to push a button, and is cheaper than running every n minutes, but is more complex overall, and may be better implemented on top of on-demand refreshes, as a future iteration.

Examples / Screenshots

Not the same, because this is the Admin view, but Power BI has a feature like this in their web UI:

Show/Hide

Are you interested in contributing this feature?

In theory 'yes', but I don't have capacity as of now, and I am not yet well-versed enough on the underlying codebase to do so efficiently.

Related

• Is there any mechanism to control the update of the chart? #534

[archiewood]

@aaronsteers thanks for opening, always appreciate your input 🙏🏼

A couple of bits of context for anyone who is not intimately familiar with how Evidence works.

How Evidence currently works

Evidence has two "modes"

1. Development (dev): the experience that a developer uses when building a project
2. Build for production (prod): the experience that an end user of evidence sees online

Unlike a lot of tools, these modes operate differently with respect to connecting to data

Development

1. There is a live connection to the data warehouse
2. Queries only run for the page you are currently accessing in the browser
3. Queries run live against the database
4. Queries data is cached for x minutes (currently 15mins), unless the query is changed

Production

1. Evidence's build process generates a static site
2. All queries in the whole project are run at build time (including all templated pages, see Add support for parameterized queries #728)
3. The site does not have a live connection to the database
4. The data from the queries is published alongside the site

It is typical to set up a build schedule to maintain a specific level of data freshness

Tradeoffs with the current appraoch

Pros

• Page loads tend to be very fast
• All updates to the site are behind version control, and changes to the database will never break the live site

Cons

• Build process may run a lot of queries
• Data is only as recent as latest build

[aaronsteers]

Thanks, @archiewood! Yeah, great context - thank you. 🙏

I especially like this part, and would like to keep it for production applications:

All updates to the site are behind version control, and changes to the database will never break the live site

Given that, I think any kind of on-demand refresh or near-realtime features should ideally fail back safely to the last-valid dataset. And very handy for transparency if something like a warning icon with tooltip message (or similar) could appear if any post-build refresh attempts have failed, such as an on-demand refresh request.

[mcrascal]

@aaronsteers something like this is becoming feasible as we make progress on #768 and #973.

It'll be down the road though.

You can play with a prototype version here:
https://github.com/evidence-dev/template/tree/next

The above supports multiple simultaneous data sources, which can be updated outside of the process of building the front-end, which does open up a whole new set of possibilities, including near realtime, different schedules for different sources, incremental source refreshes, and snapshotting source data.

When we bring this to main, the initial versions of the new build/ refresh data will just be for dev mode, but you can track progress on that here: #988

[veyalla]

@mcrascal Any updates for this scenario now that Universal SQL is launched? Can dynamically updating Parquet files (or directory) be reflected in charts without any manual steps to update?