Home › examples  › austin Show Queries

Austin 311 Discussion Document

Accompanies the Austin 311 example


We love data visualization, but we think the written word is the most powerful communication tool there is.

To introduce our report, we provided our audience with some unambiguous context on the data we're using. We told them what it was, where it came from, when it was last updated, and what some of the headline numbers are.

The values (and dates) in that introductory paragraph are all synced to the summary SQL query, and will stay up to date as results change.

Using Loops to Create Content that Feels Obvious

Throughout the Austin report we include lists, sections, and links that are all the product of a loop.

For example, each of the sections on the three most recent volume spikes (the line graph, the sentence describing the spike, and the link for more information) is the output of a loop. When a new, more recent, spike occurs a new section will be added, and the oldest section will be removed. The order, content and structure of our report responds to the underlying data.

Self-serve by navigation

In the Austin report we show graphs for each of the three most recent volume spikes. We also include a link to a page which describes our methodology for identifying spikes, and a link to "drill" into each spike for more information.

By using parameterized pages and links, we're able to deliver a sophisticated "self-serve" experience without having to teach our audience about dashboard filters or query builders.

With Evidence, we can generate an expansive and internally consistent website from our data warehouse and make it easy for our audience to simply navigate to the information they need.


One of the key features of Evidence is performance. The pages in the Austin report include hundreds of thousands of records, but they load almost instantly.

Expectations about the performance of web applications have changed dramatically over the last few years. Using your reports will feel like a chore if it takes more than a few milliseconds for them to load.

If you want your busy colleagues to engage with your work, it needs to be snappy.

Putting SQL Front and Center

You can click the Show Queries button at the top right of any page that includes SQL and reveal the queries that are powering the report.

Many stakeholders outside of your data team will be able to understand what your SQL is doing, especially when it's presented in-context with the analysis that it powers. We think this sort of transparency has a few benefits:

  1. Promote understanding and confidence in your work
  2. Encourage broader use of the data warehouse beyond the data team
  3. Build a rich library of worked examples for the rest of your data team

Leaving Room to Re-factor

One of the benefits of keeping most of your analytics work in SQL is that it's easy to abstract concepts into tables and views that live in your data warehouse.

We haven't done any up-front data modeling so that our example report is reproducible for anyone with a BigQuery account. But, if we were doing this in a real-life setting, we would have moved the definition of volume spike events into a view in our data warehouse so that we weren't repeating it all over our reporting.

As your data warehouse matures, the queries you will need to write directly into your Evidence reports will become simpler. The better your data warehouse gets, the simpler your reporting SQL will be, and the more accessible it will be to a wide audience.