Query caching and cancelling, better app settings, and pie

Faster apps? Simpler settings? Delicious pie? Just your average Thursday for the Hex team. We hope you enjoy these new features hot off the press!

🏎️ Rebuilt query caching engine​

Ever tried to comment out a SQL cell to avoid accidentally rerunning an expensive query? Or taken a peek into your database history and wondered why Hex was running the same query three times in a row?

We’re putting an end to these shenanigans, once and for all: We’ve completely rebuilt our query caching engine to avoid running repeated queries. Now, when you run a SQL query in Hex, we’ll check if the same query has been run recently, and if it has, default to returning results from the cache.

The result: faster projects and apps, and fewer queries sent to your database.

We’ve also made major improvements to the caching UI, so it’s easier to understand, override, and tweak. Instead of finding cache settings on individual SQL cells, you’ll find project-wide controls in the Environment sidebar.

Information for existing caching users

Our new query caching system is a complete rebuild of our old system. If you were using our old caching system, read on for an explanation of how they differ. If you were not using the old SQL caching system, you can head straight to the updated docs to find out how the new system works.

What's the TLDR of the new caching system?​

With our new caching system, any time a SQL cell is run, Hex checks if the same query has been run recently. If it has, Hex pulls results from its cache, rather than sending the query to your warehouse.

When developing logic: Results from the cache are clearly shown in on the SQL cell, with a button to rerun the query without cached results, should you need to refresh the results. There’s also a button to rerun an entire project without using cached results.

In published apps: When an app uses results from the cache, it’s indicated on the published app. An app user can choose to rerun the app without cached results to get fresher data.

By default, results from the cache will be used if the same query has been run within the last 60 minutes. Admins can adjust this default for their workspace, with separate timeouts for developing logic, and published apps.

Further, editors can choose to set their own timeouts per project.

For more information, head to the docs.

What are the main differences between this and the old caching system?​

Old caching system	New caching system
Had to be turned on per cell	On by default, with a workspace cache timeout set by admins, which can be further configured per project by editors.
Cache entries were valid until the next scheduled run, or next app load (depending on configuration).	Configurable cache timeout per project (e.g. 60 minutes) with separate settings for logic and app sessions. Additionally can use the next scheduled run as the expiry.
Cache entries could not be shared between projects.	We now check entries based on query text and data connection details, allowing us to share cache entries between projects. This is useful when projects have been duplicated, or you are using components.
Confusing UI	More intuitive UI

What happened to the old SQL caching system?​

The previous SQL caching system has been fully deprecated: our new system is more performant, and easier to understand.

Any projects that leveraged SQL caching in combination with scheduled runs have been migrated to use the equivalent settings in our new system:

My published apps that loaded fresh data now have results up to an hour old. How can I change this?​

The default cache timeout for workspaces is 60 minutes. This means that any published app that is set to run when an user opens the app may pull results from the cache that are up to 60 minutes old, whereas previously they would have always queried the warehouse.

If you have individual apps that should show fresher data, you can update the cache timeouts from the Environments panel of your project. Note: we recommend that you at least keep a timeout of a few minutes, rather than fully disabling the caching. You will need to republish the app for this change to take effect.

An admin can also adjust the workspace default to a shorter timeout, which will update any project that does not have a custom timeout configured.

How do these changes interact with the "Cache default state" settings?​

Previously, we used the word "cache" to describe two distinct Hex features:

• SQL caching: The ability to fetch results of a SQL query from a cache, rather than send the query to the warehouse.
• App caching (or Cache default state): The ability to view the output of a previous run when opening a published app. In this context, the "output" is limited to the front-end output (e.g. a chart visual, but not the data powering it)

While both of these features were built to make Hex faster, they are somewhat distinct in how they work, and the shared name made this confusing.

So, we redesigned the UI for cache default state (see below), but have not changed how this feature works.

🤔 Understandable app settings​

As we rebuilt query caching, we realized we ought to also tackle the other area where we use the word “cache”: the app run options menu.

If you were ever confused about terms like "cache default state" and "auto rerun", this UI overhaul is for you. There's no new functionality here, just much clearer language that makes it easy to get these settings right.

Hot tip: If you expect viewers to interact with your app via input parameters or visual filtering: Use “Show results from a previous run” and “Pre-run cells in background” together for a big speed boost.

You can read the docs on these settings here.

🔪 Query cancelling​

Caching is one way to save money on expensive queries, but what’s even better than only running a query once? Not running a query at all!

Now if you interrupt a SQL cell while it’s running, the actual query running on the database will also be cancelled.

The place I find myself most grateful for this? When I constantly forget to add a LIMIT 100 to a test query on a large table, and have to scramble to cancel the query before it finishes parsing terabytes of data.

🥧 Pie charts 2.0​

In January, we asked you, our users, if we should add Pie charts to Hex, and the results were striking:

Well strap in everyone, cause now it’s February, the past is over, there’s a delicious warm fragrance wafting through the kitchen office, and we just shipped some expertly crafted pie that even your bubbe will be jealous of— directly to your browser. Also, Barry has lost his twitter privileges.

But really— there are times when pie charts are genuinely good visualizations! And there are times when they aren’t… but some people will want to use them anyway. Support for pie charts felt like a missing feature in the new chart cell, so we went ahead and added them.

And if you hate pie charts? You’re the master of your own domain — just choose not to use them.

Other improvements​

• Previously, if you had a SQL cell with no query in it (maybe you just hadn’t gotten to writing your query yet), or a commented out query (maybe you’re saving it for later), Hex would unhelpfully error out, interrupting your flow. Now, the cell will return an empty dataframe instead.
• Chart cells now have built in data labels — head to the Style tab of the Chart Cell to enable them for your chart. We’ve also shipped a bunch of small improvements and bug fixes to charts.
• We fixed a bug where dragging-and-dropping an image into a text cell took a few seconds to render. It should feel snappy again now!
• We shipped some improvements to the scheduled run manager, including filter options for last run status (success, failed) and run cadence (hourly, daily, etc.), and column-level sorting
• Previously, we made editors visit the App builder before they could start adding elements to the App. That was silly of us, so we changed it so that you can start adding elements to the App at any point.
• Single Value cells now have a new comparison type: absolute change. Thanks Nate Sooter for the suggestion.