Under the sheets: Our new query experience
Get our internal product spec with all the details of the Equals 24 query and filter experience.
Last week we launched Equals 24, our biggest release since our first. The cornerstone of this release was our new query experience, which we internally referred to as “Query Experience V2” or “QEV2”. Following the popularity of our charts product spec, today we’re sharing the full, unedited product spec for QEV2’s querying and filtering experience. Again, copied and pasted straight from Linear. This spec tackles merging two similar product features, a new fast preview system, and a list of miscellaneous improvements. On to the spec.
Query experience v2: Queries and filters
Note: there are screenshots included here to illustrate product behavior. The Figma file linked in the project should be considered the authoritative source for design details.
We've heard from customers and prospects that filtering in Equals feels clunky and slow compared to competitive products. We're making a number of changes to address this feedback.
First, we are unifying the two related concepts for "filtering" data on a data sheet: query filters and sheet filters. Query filters determine what data is pulled from a data source and inserted into the sheet. Sheet filters exist in the column headers of the sheet and enable you to filter an already queried result set. Both concepts are needed – they solve different jobs – but they should be part of a single system. They should each support the same filtering options, you should be able to convert between them, you should be able to filter calculated columns in both.
Second, we are implementing a fast "preview mode" for query changes. When you change a filter in the query builder, we'll run a "preview query" that will run the query with a LIMIT 100 applied. This will make building a query feel like a "live" experience, rather than today where you have to build with little feedback. Preview mode does not change what data is present in the sheet, it is a view on top of the actual sheet. This is important for keeping it fast and for not breaking calculations elsewhere.
Finally, we are making a number of UX improvements to the query builder. Joins are greatly simplified. And you'll be able to reorder calculated and note columns from the sidebar.
1. Unifying query and sheet filters
Before we get to the changes it's worth understanding why we have and are keeping two systems. The query filters are self explanatory, we need a system for defining what data is in the sheet. That makes sense. But why do we also need sheet filters? Sheet filters most commonly serve a "debugging" job. Answering questions like "why is March's churn rate so high?". Importantly, they do not change the values in the underlying sheet, they just change which rows are shown in the sheet. So, in our example, you can quickly filter all churn events to March and see the rows (i.e. the customers) making up the aggregate number. When you're done you can clear this filter, all the time leaving your analyses unaffected. So we need both filter types. One decides what data is in the sheet, one decides what data is shown in the sheet.
There are a number of problems with our existing filters. Query filters do not have pre-populated controls, e.g. string fields (like email) don't have checkboxes for hiding/showing values. Sheet filters do not support boolean expressions. Query sorts and sheet filter sorts can both be applied, with sheet filters taking precedence. And it's often unclear when filters are applied on top of query results.
Most of these problems are limitations of a particular filter UI but rather than fixing the current system, we believe we can create a more elegant and coherent experience by thinking of query and sheet filters as a single system. Making query and sheet filters a single system does not mean they exist as a single experience. It means for the same task, they use the same UI, that what you can do in one, you can do in the other, and that you can convert sheet filters to query filters (although, not the reverse). To orient you, here is a data sheet with query and sheet filters applied:
Above you can see 1. the query filters, 2. the sheet filter menu button, 3. the filter mode banner, and 4. the add to query button. Starting with the new unified filter controls. When opening a filter from either #1, #2 or #3, you will see the same UI. For certain data types, query builder filters will now need to populate their controls with values from the database. For example, below is the new unified "string" UI. Note how we show a checkbox UI (like we do in sheet filters today) with populated values.
We will not support filtering of calculated and note columns in the query builder sidebar. In some ideal world we would support this but it introduces a number of difficulties. Most notably, it's hard to make the preview mode (described in next section) work well with calculated column filters, as we don't know the value of the filters until we've run the entire query.
Our new unified filter UI unlocks the ability to "convert" sheet filters to query filters. Which you'll see annotated as #4 above. When you click this button, you exit filter mode, and the query is re run with your new filters added. You can also clear filters, returning you to the "underlying data". This functionality is disabled if you have a filter on a calculated or note column.
Sheet filter mode
When you apply sheet filters, you do not change the underlying data in the sheet. This is the same as how sheet filters work today. Rather you change what data is visible in the sheet. We are making changes to how filter mode is presented. We will now show a big light blue banner that communicates you are viewing filtered results (screenshot in previous section). Same as today, the row numbers will reflect the actual row numbers of the displayed data. i.e. if you filter out row 2, the row numbers (on the left) will show as 1, 3, and so on.
Sorting
Today you can sort a data sheet both via query filters and via sheet filters. We are deprecating the sheet filters sort option with this scope of work. There will still be an option to apply a sort via a column header (in the dropdown) but sorts applied through this entry point will simply be added to the query and cause the query to re-run.
SQL editor
Sheet filters can also be applied on SQL editor sheets. In this case, you see the same sheet filter mode banner. However, sheet filters cannot be added to the SQL query. Sorts are unavailable in the column headers while the SQL editor is active – the query must be changed instead.
Existing sheet filters
Ideally we don't maintain two separate sheet filter systems. The goal is to deprecate the existing sheet filters – on and off data sheets. Before doing so we need an estimate of how often the deprecated filter options are used and what percent of customers will be affected. For example we will no longer support a "Top 10" filter for numbers, that is standard in Excel. We will also need a plan for how we migrate these filters to the new system.
Personal sheet filters
Today sheet filters are replicated and shared among all users. We should update sheet filters to be private to the current user. This will be important for some of the query interactions described in the next section. Sheet filters should still be persisted, just per user.
2. Fast previews
Building a query in Equals is a bit of a black box. You don't know whether you picked the right table, whether you're selecting the right columns, whether your filters are correct. For every decision you have to click run query to know whether what you did is correct. This is in contrast to competitive products which offer a more "iterative" query building experience.
We will solve this in our new query experience with "fast previews", pictured above. Whenever you change the table, columns, filters, or sorts, we will run a preview version of the query with a LIMIT 100 applied (disregarding any greater limit applied in the query builder). These preview queries will be displayed in preview mode. Like sheet filter mode above, preview mode does not change the underlying data in the sheet. This means you don't break your analyses while iterating on a query and also ensures the experience is fast (no formula reevaluation). To see the full query results you must hit run query.
When in preview mode, the column headers (A, B, C, etc.) and formula bar are hidden. This is to avoid confusion about what data is in the underlying sheet. This is subtly different than sheet filter mode. In sheet filter mode, every row shown is somewhere in the sheet and so can be shown if the row number is correct. Once in preview mode there is no guarantee that the data is in the underlying sheet, hence the hiding of the column headers. This means the values shown in preview mode are uneditable as well. Calculated and note columns should be shown with dots in place of their values (there are designs; missing from screenshot above), avoiding the need to evaluate formulas for data not in the sheet. There should be an option to discard changes and exit preview mode without running the query (missing from screenshot above).
Preview mode should be personal to the user who made the changes to the query. Only once a query is run – and you are no longer in preview mode – should the changes become visible to other users. Preview mode (or draft queries) does not need to be persisted between reloads, this may be easier to implement.
Unapplied changes
Fast previews will need to be disabled by default for Google BigQuery. Due to BigQuery's pricing structure, users are charged for each column accessed, regardless of row limits. So fast preview could rack up high costs if used on large tables. We should let people opt-in to fast previews in their BigQuery settings. Fast previews also don't make sense for changes to a query's limit. In both these cases we should show the same banner we show for preview mode but instead of saying the results are a preview, we should say there are unapplied changes to the query – with the arrow pointing to run query to see the results.
Interactions with sheet filter mode
To keep things simple we should prevent a user from being in both preview mode and sheet filter mode. If you have sheet filters applied and try to modify the query, we should communicate that you must clear your sheet filters or add them to your query to proceed. We can do this through a modal with three options: 1. cancel, 2. discard sheet filters and continue, 3. add sheet filters to query and continue. Once in preview mode, given the column headers are hidden, it is not possible to apply sheet filters.
We also need to consider the case where one user updates and re-runs the query, and another has sheet filters applied. Given sheet filters are personal to the user, we should try re-apply the filters for the non-querying user. We may need an error state if that is no longer possible. e.g. if a column is no longer available.
3. UX improvements
Finally, there are some miscellaneous UX improvements to the query builder.
Joins
Our current joins UX is quite complicated. It feels heavy weight and at times disorienting. We require users to specify the type of join and then an entire query for the joining table. This is overkill for the common use-cases.
Our new model will be to add "columns" from another table, while specifying a join key. We will not offer different join types, all joins will be LEFT JOINS, i.e. if there are missing values, the column is just empty. This will all be configured in a simple modal:
When you click a join column in the sidebar, it will open the join configuration modal for just that column. So while we support adding join columns in aggregate, we only support editing them one-by-one. This was the simplest thing we could come up with after a lot of exploration.
Finally, join columns should be available as query and sheet filters.
Column re-ordering
We will now support re-ordering calculated columns and note columns from the sidebar. Column re-orders should enter you into preview mode, even if the re-order is with non query columns. Calculated columns and note columns must be adjacent to the last query column, i.e. they can not be placed in the middle. There is a separator in the sidebar above which they cannot be dragged to ensure this.
4. Detailed requirements
This is my attempt at extracting all the requirements mentioned throughout this document. It is quite possible I've missed some however, so please rely on this list and the full document.
All changes are feature flagged. This will be important for a careful rollout.
The query status banner originally on the bottom has moved to be above the sheet.
New filter controls implemented for query filters and sheet filters. Every filter option can be evaluated in memory or converted to a SQL statement, including boolean expressions.
Query filter controls are prepopulated, when necessary, with data from the database. e.g. for string-type checkboxes.
Calculated columns and note columns are not available for filtering in query filters.
Sheet filters can be converted (added) to a query. When sheet filters are added to a query, the query is automatically re-run.
Sheet filters cannot be converted to query filters if there are filters on calculated or note columns. In this case the conversion functionality is disabled.
Sheet filter mode does not change data in the underlying sheet, it only changes what data is shown. i.e. calculations do not change based off filters. This is unchanged from today.
When sheet filters are applied, non matching rows are hidden causing the row numbers in the left gutter to skip. e.g. if row 2 is hidden the row numbers go 1, 3, and so on.
Sorting via sheet filters is no longer an option on data sheets. Sorts applied from data sheet columns are added to the query.
Sheet filters can be applied for SQL editor sheets (instead of the query builder).
SQL editor sheet filter mode shows the same banner, without the option to add the filters to the query.
Sorts from the column headers are unavailable for SQL editor sheets.
Sheet filters should be private to the current user and not shared like they currently are.
Any changes to a queries table, columns, query filters, or sort should be immediately shown in preview mode, which runs the query with a
LIMIT 100applied.Sorts applied from the column headers should not enter preview mode, they should immediately run a query with the new sort applied. i.e. not applied in the sidebar.
Preview mode does not change the data in the underlying sheet.
Preview mode hides the formula bar and column headers.
Preview mode data is not editable.
Preview mode should show calculated columns and note columns, with dots in place of their values. i.e. these should not be evaluated while in preview mode.
Preview mode can be dismissed in the banner, which discards the changes you've made to the query.
Preview mode is private to the user editing the query. Only once a query is run is the updated query visible to other users.
Preview mode (or draft queries) does not need to be persisted between reloads, this may be easier to implement.
Preview mode is disabled by default for BigQuery but can be enabled on the datasource settings page. When changing a query while preview mode is disabled, you see a "Unapplied changes" banner in place of the preview mode banner.
Preview mode does not exist for SQL editor sheets.
You do not enter preview mode while changing the limit. Instead you see the "Unapplied changes" banner.
"Unapplied changes" banner should have an option to discard changes, dismissing the banner.
Preview mode cannot be entered while sheet filters are applied. If sheet filters are applied, they must be discarded or added to the query to make changes to the query. This should be confirmed with the user via a modal.
If you are not the querying user, you should be able to keep your filters and they should be re-applied if another user updates and re-runs the query.
Joins are now added via the "+ Join column" button in the sidebar.
Joins are configured in the new join modal. When configuring a join you choose which columns you want to add and what the join key is.
Joins can only be edited one column at a time, despite their ability to be added in aggregate.
Join columns should be available in query and sheet filters.
Calculated columns and note columns should be shown in the columns, in the query builder sidebar.
Calculated columns and note columns can be reordered in the sidebar, but they must always be adjacent to query data. i.e. cannot be placed in the middle.
Reordering columns in the sidebar should enter you into preview mode. Including calculated and note columns.