Skip to main content
Latest release:

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.

info

Projects that are set to run on when a user opens the app will now have results up to 60 minutes old as a result of these changes. Check out the below FAQ for more information on how to update these settings.

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.

Workspace settings to set the default cache timeouts.

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 systemNew caching system
Had to be turned on per cellOn 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 UIMore 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.

Today we’re launching a private beta of Hex Magic: a suite of powerful AI features meant to augment – not replace! – data people. Now, humans can do what they do best, and let the computer do the rest.

🔮 Hex Magic

Hex Magic introduces five Magic tools, accessible from any SQL or Python cell: Generate, Edit, Fix, Explain, and Complete.

Let’s start by writing some new SQL or Python code. With Magic Edit, you can iteratively tweak results to add filters, drill down, or start over:

Oops! A Python error. Let’s see if a Magic Fix can help:

Hex can even document your code with Magic Explain:

Magic features do more than just pass a prompt – they know about database schemas, past operations, and the project’s execution graph, so they can make deeper, more insightful recommendations.

Our early Magic users (magicians?!) tell us this lets them focus on the things humans are uniquely great at: asking questions, working together, and telling stories.

Join the Magic waitlist at hex.tech/magic. Check out the docs at learn.hex.tech/docs/magic.

Happy New Year! Here at Hex we took a few days to rest and recharge, and are feeling re-energized to take on the new year!

And boy oh boy are we taking it on — we’re ushering in 2023 with one of our most exciting features yet: Components.

We have also snuck in a few oft-requested enhancements for some of our existing features - including a big upgrade to Single Value cells. Read on for more...

🧱 Components

Ever find yourself rewriting the same logic time and again? Or copy-pasting a query from a doc? Or finding out your colleague wrote the same thing just a little differently?

Us too, and it isn’t fun. So today, we’re introducing a new way for teams to collaborate consistently within Hex: Components. These are reusable groups of cells that can be written once, and used anywhere.

You can easily create a component from an existing project: just select a few cells, click "New Component" and share it with your team:

You can then share your component with other users on your team or with your workspace, and they can easily reuse the logic across their projects.

Over time, your team can build up a centralized repository of consistent, reusable logic, and empower others to do great things.

Components are available now for everyone (and Teams and Enterprise accounts can share unlimited components with their workspace).

🔢 Single value cells now support dataframes

You can now move seamlessly from a SQL result to a Single Value cell, without taking a detour into Python to extract a specific value!

Yes, that means you can go ahead and delete the Python cells that just have value_to_display = dataframe_name['column_name'][0] in them. Comparisons work seamlessly too.

⏱ Scheduled runs manager

Hex Admins can now manage all scheduled runs in their workspace from a single view. No need to @here the data team and ask who’s using up all those kernels. Scheduled runs manager is available in the Admin panel for Teams and Enterprise accounts.

Other improvements

  • Previously when a dataframe had two columns with the same name, we sometimes renamed the second column for you, but often didn’t, leading to errors in downstream cells that required unique column names. Going forward, dataframes will always have unique column names, making the dataframe easier to use downstream.
  • If your Athena data connection has access to multiple data catalogs, we now make sure to show all of them in the schema browser.
  • We’ve improved how our API handles invalid or extraneous parameters. Rather than ignoring, we will now return a 422 response, with a payload detailing the invalid or unfound parameters.

Hey everyone! We have been cookin’ up some amazing stuff, and have something really special to share today.

📈 All-new chart cell

Our old chart cell was… good? It was fine. Better than writing a dozen lines of matplotlib. But we all know it could be so much better.

We heard you, loud and clear, and we have been working hard on a complete, ground-up re-write, and we’re so excited to share the final product.

This new cell makes it easier to configure charts of all kinds, including better support for things like histograms, multi-series legends, and tooltips. It’s really an entirely-new experience, and we hope you love using it as much as we have during our testing.

Check out the Blog for more info (but really, you should just jump right into Hex and try it out)

⬇️ Visual filtering

This new foundation has allowed us to build something really fun – interactive visual filtering!

With visual filtering enabled, charts return dataframes representing the current selection, just like our no-code Filter cells. This turns charts into a tool for interactive, visual data exploration: you can display the resulting records in a table, or further slice-and-dice them in another visualization.

Visual filtering is also available in published apps, allowing end-users to directly interact with reports, dashboards, or tools you publish in Hex. This can completely replace complex, tedious workflows with multiple input parameters and filter steps. Just point, click, and go.

🎨 Custom color palettes

Our most-frequently requested feature for charts (besides, well, making them better) – you can now configure brand-specific colors and use them across all charts in your organization.

You can configure these in the admin panel, adding up to 20 colors, and specify which palette to use as default. Updating the palette will change the colors on existing charts, so you don’t have to go manually update each one!

Other improvements

  • When you start typing a comment but click away, we now store your unfinished thought in the comment for you to return to later.
  • In the same spirit, we also removed the requirement to hit “Save” when configuring an input cell — clicking out of the UI also saves your settings.
  • CSVs now have a useful filename when you download them, concatenating the cell name with the current timestamp. Be gone, export (12).csv!
  • We fixed a bug where Redshift clusters with multiple databases insisted on showing just the one database. As a result, datashares also show up in the schema browser. While we were at it, we also made Redshift external tables appear as well.
  • PS: Pivot and Filter cells now work with query results. Watch this space for more news ;).

We've got some great updates for you today, from improvements to your most used features to flexibility upgrades and quality of life fixes.

I really encourage you to jump into the product and check out the new Data Browser. It's hard to represent in just a few words and tiny images how flexible and powerful it feels to use. I'd love to hear what you think!

🔎 New data browser

Searching and discovering data in connected data sources just got way better. Our all-new Data Browser is faster, prettier, and more useful than ever before.

Search is improved, schemas and tables unroll in one tree view, and you can pin your favorite databases and schemas— we hope you love it as much as our data team.

To tell you all about that, here’s Amanda, one of our Analytics Engineers, showing you how she uses the data browser:

For more information, check out the docs— or jump right into Hex and try it out!

⌛ Flexible scheduling

Scheduled runs can now be configured to run on custom cron schedules. What the heck’s a custom cron schedule? Cron strings are a shorthand for specifying complex date and time intervals; Say you want to run an app every three days, at 6:05am and at 6:05pm. Cron represents this as 6 5,18 */3 * *.

Now you can use this syntax in your Hex schedules to sync up app runs with warehouse updates, morning syncs, or anything else— say, at 08:04 AM, between day 15 and 16 of the month, and on Thursday and Tuesday, February through March.

Don't speak cron? Me neither. crontab.guru is a great website for constructing complex cron strings, and the Hex scheduler translates to plain English to make sure you know exactly what you’re deploying.

The Projects homepage can now be sorted by recent project views, so you can quickly get a sense of what apps are popular lately in your workspace. The timeframe for view counting is configurable, so you can toggle between all-time classics and up-and-coming stars.

🟢 Hex status page

Hex has a new status page that sits separate from our production infrastructure, so you can always stay up to date with any incidents affecting Hex.

We work hard every day to make sure you don't need to use it, but you can always find it at https://hex.tech/status.

⚠️ Dataframe SQL upgrade

Dataframe SQL cells are getting an upgrade! DuckDB, the backing query engine, has a new version with extended support for both Decimal type columns and columns with timezone-aware timestamps. We will be making this upgrade on November 10, 2022.

Once upgraded, decimal columns will no longer be cast as strings in order to preserve precision. Instead, you’ll now have the option to use the same “Infer numeric type” option as in Database SQL cells (more about this feature here). We expect this change to impact a very small number of projects. To avoid any loss of precision, turn off the “infer numeric datatypes” option on your Dataframe SQL cells. Additionally, projects which rely on Dataframe SQL cells returning Decimals columns as a string type for downstream analysis will need to be updated to instead accept numeric types. If you have question about the impact of this change to your projects reach out to [support](mailto: [email protected]) for more assistance.

Other improvements

  • Postgres data connections can now be configured with both cert-based authentication and user/password authentication.
  • We’ve greatly improved Redshift writeback speeds, which were nothing to write home about before. In a test on a 4,000 row table, execution time dropped by *****31x*****.
  • Dark mode automatically turns on based on your computer’s system preference. You can still choose always-dark or always-light
  • Map cells can now be resized vertically

💱 dbt Semantic Layer integration

Today we’re releasing a powerful, first-class integration with the dbt Semantic Layer.

At the heart of it is a new Metrics Cell: an easy-to-use UI that lets anyone access trusted, governed metrics, without writing any code. Users can specify metrics, dimensions, and time grains, and get back a data frame that they can use to analyze, visualize, and share.

But there’s more – connecting to the dbt Proxy Server unlocks another superpower: the ability to write dbt-flavored SQL, right in Hex. Now you can use refs, macros, and sources directly in your queries.

We’re so excited to unleash the full potential of the work Analytics Engineers do every day, and empower thousands of users to more easily ask and answer questions of data.

Read more about these new features over on the Blog.