A Deep Dive into Hex and the Future of Data Apps

We could solve this using SQL that we send them to run, but we think an app might be a better experience for everyone involved, so we're going to build our first Hex App for them.

Creating a Project

The latest version of Hex offers a reactive analysis format. If you're used to traditional notebooks like Jupyter or R Studio, you know that your only option for running cells is from top-to-bottom or one-at-a-time, but has built an internal DAG of your analysis and can infer the dependencies of one cell downstream to all other cells, meaning you can ask Hex to only run cells that would've changed in response to a change in your upstream inputs. For our example, we'll use the Reactive mode, because it's way cooler (and I managed to get early-access)

The first thing we'll do is update the name on the top left so it's no longer called Untitled Project. For now, Customer Success demo will do.

Getting Data In

As with any data analysis project, the first thing you'll want to do is connect to some data. Hex shines here, with intuitive access to everything from a database, a data warehouse, or even a CSV file.

Data sources can be added at an organization level, which is common for things like data warehouses and databases, but also at a project level, perfect for one-off connections, and demos.

Hex has a great schema browser built-in, and navigating through the schemas, tables, and columns in my browser is a breeze, and somehow faster than the native Snowflake UI that I'm testing against.

Querying Data

Once you've added a source, querying data is as simple as writing some SQL.

We're not quite sure what data we want, or how we're going to structure our final app, so for now, let's try and see if we can build a simple query to look up a user and a workspace just by using an email address. For our example, we'll limit everything to the Hightouch workspace so we don't accidentally share information about any of our real customers.

This is a step in the right direction for our first problem. One thing you might notice is that the result was returned as a data frame called query_result. Let's rename this something more sensible, simply by double-clicking and giving it a new name.

Let's see if we can get the data on the other two questions now.

There's our list of product events, and just as easily, our destinations.

Adding Dynamic Inputs

While we're in a good place, it's easy to see there are a few places we can improve. Having to manually write the user id filters for the 2nd and 3rd queries is painful. What if we could leverage the results of the first query to filter the others for us?

Since the first query could return one or more results, for example, when searching for a name like Josh, we'll want to let our users pick which customer they care about.

Let's add a Code block, where we can write some Python to express the possible items in our list.

user_id_list = users['USER_ID'].tolist()

And then create a multi-select drop down like so:

And we can see two user ids now show up when searching for Josh.

Now this is where the fun begins. We can start templating out our SQL using the User Filter our users have selected. We wrap the name of our filter in {{ ... }}s and Hex will compile our SQL down for us.

We'll add a similar filter for our last query:

And now our 2nd and 3rd queries will update dynamically as our user's pick the user_id they wish to filter on.

But there is still one issue, in our first step, the email address is hard-coded into the SQL. Let's take care of that with another input, this time a text-box, and we'll also add some Python filtering to show how we can take data from SQL into Python and back easily.

You can see here we're using Python to filter using a text box input, rather than SQL, but the principle is the same. You can use whichever language gives you the flexibility you need, which is really powerful.

One neat trick we can leverage is this little box on the top right:

Once we update our cell blocks, Hex can run only the cells that have the updated cells as dependencies.

That's not all though, we can get a graph view of our work! Now it becomes easy to visually model the workflow you've built within Hex, instead of trying to keep it all in your head.

Creating Table Displays

We're almost done. To recap, we've created our queries, made them dynamic using inputs, and now we want to display the results to our users. We'll do this using the Table display cell. While our normal cells offer a preview of the data, the table display lets our users see a paginated view of all the data from a variable.

We'll create three tables, one for users, events, and destinations.

Putting It All Together

Now we're ready to jump into App view and create the view we want our Customer Success team to see. We don't want to confuse them with all the various SQL queries we built, but only the inputs and outputs that are relevant to them.

We'll jump into App View and pick Canvas mode as it's made for building small tools like this.

First, we'll add some Markdown cells to give our users some context to what the app is about.

Next, we'll just add the cells we care about to the app and remove the ones we don't want our users to see. This is as easy as dragging cells from the outline view into the app, or clicking the remove from app button to hide a cell.

When that's all set and done, we'll have an app that looks like the one below. This could still be improved with a bit more thoughtful design, but it should be good enough to get in front of our users for feedback.

The last step is to click Publish to deploy our app for our colleagues, or use the Share button if we want to collaborate on the design together. Here, we'll use Share and send it to our pretend colleagues for pretend review, where we can leave comments directly on cells or even change the phrasing of our instructions as needed.

Once the app is fully published, we can view a live version of the app at any time. Before publishing, Hex will run your entire project as a fresh kernel, so that no weird state-errors crop up. This has helped me a few times when I've accidentally changed a cell without refreshing it, for example.

When we're all done, the Live Version of the app will look something like this:

Final Thoughts

I hope this was a useful walk through of what's possible with Hex. What I especially love about Hex and other tools like Hightouch is how they make it easy to activate data in your data warehouse. As data people, we spend a lot of time building models, cleaning data, testing our pipelines but too often the data ends up sitting there unused after serving some small one-time utility.

With the emergence of new ways of activating this data, I'm hopeful that we'll see a second-wave of data warehouse use cases that go beyond simple reporting and dashboards and into more use-case focused applications that can help teams company-wide.

If you'd like to try out Hex, visit their website to get access, and if you'd like to try Hightouch, you can sign up for free, no credit card required.