Hex Magic

Hex Magic is a powerful set of AI-driven features to help you do more with data.

danger

Magic features are in public beta, which means that we still expect there to be some glitches and imperfections. These features are experimental. You should not rely on AI-generated code to be accurate, complete, or free from bias. Hex Magic is meant as a way to augment - not replace - human insight and judgement.

Hex Magic features still require you, the human, to click a run button. Hex will not automatically run AI-generated code, and it’s on you to determine whether what was written is correct.

tip

While this feature is available on all workspaces, Community plans are limited to 100 Magic actions per Editor/month.

Getting Started

To activate Hex Magic so that users can opt-in to using it, a workspace Admin needs to head over to the workspace Settings page, and in General settings toggle on the Magic option for the workspace.

Once enabled for the workspace, users will get an opt-in dialog the first time they click a Magic button. Users will find the Magic menu in the top right corner of SQL, Python, Markdown, and R cells.

How to use Hex Magic

Magic generate

ex: show me all orders placed in the last 30 days for > average order price.

To generate new code, open the Magic menu and select "Generate", or bring up the Magic pane in Generate mode with the keyboard shortcut Cmd/ctl+shift+m.

Describe what you want to generate in plain English, being as specific as possible, and hit Enter. As Magic thinks, you'll see the generated code begin to fill in below.

When generation is complete, you can accept the new code by hitting Enter or Tab, or reject it with Esc. You can also use Cmd+Enter to "Keep and Run" the new code immediately.

Magic edit

ex: change this query to look at unusually small orders.

Use the keyboard shortcut Cmd/ctl+shift+e to open the Magic pane in Edit mode.

You can ask Magic Edit to do anything to your code, from answering a different question to performing manual tasks like renaming every table reference. As Magic thinks, you'll see the edited code begin to fill in alongside the previous code, with a line-by-line diff of the changes made.

Accept the edited code by hitting Enter or Tab, or reject it with Esc. You can also use Cmd+Enter to "Keep and Run" the new code immediately.

Magic fix

Magic fix automatically detects and fixes errors in your code. Whenever a cell returns an error, Magic begins preparing a fix, which can be applied with the "Magic fix" button in the error output.

If you suspect your code has an issue but there is no visible error, Magic fixes can also be triggered manually with Cmd/ctl+shift+, or from the Magic menu. In this case, it is often better to use a Magic edit and specifically describe what you think the issue may be.

Magic explain

Magic explain automatically documents code – whether it’s something you wrote, or something unfamiliar you’re seeing for the first time. It works similar to a Magic edit, so you'll see the documented code stream back word-by-word. Try it out with Cmd/ctl+shift+. or from the Magic menu.

Magic explode

Magic explode takes CTEs and "explodes" them into individual chained SQL cells. This can make complex queries more easy to debug, understand, and iterate on.

Explode makes use of query mode, so your exploded queries will not add any additional runtime over the original CTE.

Data privacy

Hex takes your data privacy very seriously:

• Neither Hex nor our third-party model partner (OpenAI) train models using customer data, reducing risk of IP leakage through passed context. This means a model won’t spit out your code to someone else (or vice-versa!)
• Hex doesn’t send underlying customer data to the models. This means that values of specific rows of a table aren’t at risk of leakage through Magic.
• Hex does use schema information and project code as model context. This means that any sensitive information in your table or column names, or in your code, could be passed to a model.
• Magic is built on Hex’s existing, secure data platform. These features are protected by the same practices and policies, including SOC 2 Type II, bug bounty programs, and principles of least-access. You can learn more on our Trust page and read our Magic ToS.

Hex admins can opt-out their entire workspace from Magic from the Admin > General settings page. This will disable Magic for all users.

Hex Magic is currently unavailable on our single-tenant EU and HIPAA compliant hosting stacks.

Tips & Examples

Like all AI-driven features, accuracy can vary based on the model, prompt, and other factors. To improve your querying success and give a sense of what you can do, here's some specific examples and advice on how best to work with Magic.

General tips

Magic knows about table and column metadata

Magic has context on your tables and schemas, so you really can just ask natural language questions and Magic will map to the most relevant tables. However, being as specific as possible in your prompt or saying a relevant table name explicitly will improve response accuracy.

Magic knows what's going on in the rest of your project

Magic also has metadata on your project, so it can reference dataframes and variables that have been created earlier. In the example below, just asking a question about orders prompts Magic to access the orders dataframe from a previous query. It also has access to the columns and datatypes, so it can intelligently operate on the data.

Similarly, if pandas had already been imported in an earlier stage, Magic would have seen that and not re-imported it.

Magic does not know about your data

Magic only has access to metadata, not row-level data. This is done intentionally to make Magic as secure & private as possible, but it means there are some families of tasks Magic has trouble with.

Here, Magic incorrectly attempts to sum a WEIGHT column with data that still has 'lbs' appended to it, since it doesn't know what that column actually looks like. Providing a simple example of the data resolves the problem.

Some key situations where a lack of row-level data access shows:

• Blind operations or transforms where knowing what a string looks like is critical (like above).

• Complex joins where the join column needs to be transformed or compound keys are required. e.g: "find the matching columns containing user join information and use them to join tables".

• Anything else that requires specific knowledge of what a row of data looks like.

If the data is not sensitive, you can overcome this limitation by providing Magic with an example of the relevant data. You can even redact or modify it for privacy reasons, and as long as it's the same format/shape it will work:

It helps to be precise

Magic can be a bit like water, taking the path of least resistance if you aren't specific. Reference specific columns, tables, and give examples of your ideal output to make Magic as accurate as possible.

• Reference a specific table name to get a fine-tuned result:

• Specify exactly the shape of the output you want:

SQL examples

All of the following examples are real queries tested on our demo data. They should serve as good examples of what's possible, and as creative inspiration for you to start testing out Magic in your own workspace.

We test and evaluate Magic on these specific examples, so if you are seeing issues when replicating these, let us know at [email protected].

Query data with natural language

Magic is excellent at going from a plain language question to a functional SQL query. Precision is always helpful in prompts, but you'll be amazed at the kinds of things you can just casually ask for.

• Just ask for top-level business metrics:

• Or ask very specific questions:

• Quickly identify outliers: You could rapidly iterate on this prompt either manually or with Magic to better define what an 'outlier' is.

• Build a simple or complex funnel: Bonus tip: this prompt uses the specific table name technique to force Magic to query an ecommerce dataset rather than our other demo order data.

Date / timestamp operations

Magic is really good at things that require careful syntactic specificity, like datetime operations. Say what kind of filter or parsing you want in plain language, and watch dialect-specific code appear without ever loading up the documentation.

• Easily applying complex date filters: Magic also knows common facts like holidays and seasons, so you can easily zero in on or remove particular parts of the year.

• Converting between dialect-specific code:

Automate repetitive tasks

Magic can operate as a sort of turbocharged find+replace, either writing a complicated regex to actually do find-and-replace, or performing a more complex task like extrapolating an analysis out to other time periods.

• Selecting all columns except a few:

• Repeating variations on an operation many times:

Generate complex patterns

Magic is not limited to just simple repetitive operations or syntactical assists. Try asking for something complicated! Note that the more complex the ask, the more it helps to be specific in your prompt.

• Building a daily active user/monthly active user calculator: This pattern requires a date spine table to be done correctly, which Magic successfully implements.

• Identifying consecutive daily site visitors: This is a great example of a question that seems very simple, but actually requires a somewhat complex pattern.

• Performing cohort analysis:

Python examples

Pandas dataframe operations

Magic is excellent at everyone's least favorite thing: complex dataframe operations.

• Long chained filters and aggregations:

• Refactoring many operations into a one-liner:

• Refactoring a confusing one-liner into more readable, documented components:

• Parsing misformatted timestamps: This prompt is a great example of the 'provide a data example' technique described in General tips.

Exploratory data analysis

• Dig into a specific ad-hoc question:

Use libraries without needing documentation

Often Magic can come up with necessary libraries on its own, but you can also specify which libraries you want it to use for a task and it'll import them if necessary.

• Geocode data and build an interactive map: To make this by hand, chances are you'd have to refer to the documentation for geopy and pydeck to get everything just right.

• Time series forecasting with Prophet: Prophet requires data to be in a particular format, and for a special future dataframe to be created, both of which Magic does automatically.

• Charting with matplotlib: matplotlib can be notoriously verbose when building complex charts. Magic takes care of all of the boilerplate.

• Tweaking chart layouts: Often something is just a tiny bit off, and Magic can easily rebuild charts (or any code) to a different spec.

• Web scraping with beautifulsoup: This is tougher, but Magic can often correctly discover and scrape down data from webpages. Try providing your own url and being specific about how the data is stored on the page to improve results.

Converting and refactoring code

• Converting a chart from matplotlib to plotly:

• Switching between libraries without Googling: Here we switch from a free rate-limited API to an unlimited one, without having to look up and rewrite new syntax.

• Improving performance of slow code: In this specific case, Magic sped up the code 2.4x by doing expensive processing outside of a nested for loop.

Complete analyses

Magic is optimized to return code that helps you do your work— not to actually do the work for you. However, it can still be extremely useful for answering bigger questions in just one prompt, or doing iterative analysis with you.

• Summarize seasonality:

• Ask iterative questions to unpack a dataset: Protip: use generate instead of edit when doing iterative analysis with Magic to preserve your previous work.

Support & Feedback

Your feedback and bug reports are the best way for us to make this feature better. Please email [email protected] with questions, product feedback, or bugs.

During this public beta, we ask that you not email [email protected] every time Hex Magic does not provide a useful suggestion— "Rejecting" a bad suggestion will let us capture that feedback! We are however extremely interested in ways that the product can be improved outside individual prompt accuracy, or if you are consistently running into a problem.

PS: If you hit a bug, it's very helpful to include the debug info, which is easily copied from the bottom left hand support menu.