Universal SQL Architecture: Transforming data sources into parquet files, which are then queried by a SQL runtime on the user's device, to produce charts and components

Introducing Universal SQL

Our next step towards our ambition to be the universal front-end for data: Interactivity via SQL and connections to simultaneous data sources, all with extraordinary performance

The Universal Front-End for Data

Our vision for Evidence is for it to be the universal front-end for data. A powerful, batteries-included framework combining the best of web frameworks, BI tools, and notebooks into a package that feels approachable to modern data professionals.

By this, we mean:

When we started Evidence, we focused on reports. The humble report: With headers and text, tables and charts, and a well-worn export to pdf button. The thing that all of your users already know how to read, but is so poorly executed in most modern BI tools. If you wanted to generate that type of thing from data in your data warehouse, Evidence was, and is, the best choice for the job.

That focus has worked well for us. Usage of the Evidence framework has been growing steadily by about 5% per week since we started.

But the universal front-end for data can't just be static reports on data in your data warehouse.

Introducing Universal SQL

Using some dropdown filters on a bar chart in Universal SQL

That's why we're so excited to share Universal SQL, the new query engine built into Evidence core, powered by DuckDB's WebAssembly distribution. This is a foundational release that will enable many new features in the coming weeks and months.

Universal SQL introduces three major improvements to the Evidence open-source framework:

  1. Interactives like inputs and filters, in pure SQL
  2. Support for multiple simultaneous data sources, and an open standard for building data source adapters
  3. Extraordinary performance, running calculations in the browser

1. Interactives in Pure SQL

Interactive features have been, by far, the most requested feature from our community. However, the way most web frameworks offer interactivity is by dropping you into JavaScript.

Users telling us they aren't web developers on slack

And most of our users are data professionals, not web developers. They want to write SQL.

Requests to parameterize SQL

And this is what Universal SQL delivers: Interactivity via SQL.

2. Support for Multiple Data Sources

There are precisely zero organizations where all their data lives in a data warehouse. Targets, ad-hoc costs, and custom lookups are frequent culprits and often live in spreadsheets, people's heads or CSV files.

As a result, analytics tools need to be able to pull in data from all the places it lives. With Universal SQL, Evidence brings support for multiple data sources, so you can merge in data, wherever it lives.

10 Github requests for different data sources

On top of that, to our set of supported data sources in Evidence, we have also added the ability to extend Evidence with data source plugins: You can write your own data source adapters to use with Evidence.

3. Extraordinary Performance

The audience for data apps is regular people, and those people's expectations have moved substantially over the last five years. Five seconds to load now feels like an eternity.

Modern BI tools store data on a server, and when you interact with them, send a query to the database to get results, so 2 seconds is a best-case scenario for response times, and 5+ seconds is common.

Skeleton meme

I've sat over the shoulder of enough people trying to access reports to know that this is beyond frustrating. It's prohibitive to achieving work.

Consumer web apps now achieve response times measured in milliseconds. They achieve this in two ways:

With Universal SQL, we're bringing the performance of web apps to data tools.

This simply wasn't possible before now, as you could not do the data re-aggregation required to change a filter on the user's device

But now, the computers that people use at work and carry around in their pockets are very powerful, which allows an increasing amount of data processing to be done on their device, which previously would have been done on a server. This trend continues with each new smartphone and laptop release.

A recent release from the DuckDB project is DuckDB WebAssembly (WASM), which brings the ability to run a SQL database in the browser. It's an ideal fit.

How Universal SQL Works: Cached Data, Queried on your Device

With Universal SQL, we bring together a set of data sources, a client SQL runtime, and a set of interactive components.

Universal SQL Architecture: Transforming data sources into parquet files, which are then queries by a SQL runtime on the users' device, to produce charts and components

Universal SQL Architecture: Data -> Storage -> DuckDB WASM -> Components
  1. Every source is stored in a cache layer: The cache layer consists of a set of Parquet (highly compressed columnar data storage format) files.
  2. An in-browser instance of DuckDB executes queries live against the cache layer: Each parquet file is accessible as a table in the SQL runtime.
  3. Input components allow you to take input parameters from the user: These can be used as parameters in queries or components can issue their own.

Zero JavaScript, Low Latency, Seriously Large Data

As a result of this new architecture, Evidence has a slew of new capabilities.

The syntax for filtering is just SQL where clauses

Filtering in Universal SQL is just SQL where clauses
select count(*) as num_orders from orders where category='${inputs.category_picker}'
Templating a user input into a SQL query

What's Coming Next?

This is just the start of what we can offer with Universal SQL. Stay tuned for:

An Evidence SQL Console

An Evidence SQL Console

We're grateful to our community and supporters who've helped us get this far, and so excited for this next chapter as we build the best front-end for data.